#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Tijuana, Mexico
    Posts
    1
    Rep Power
    0

    Question Example accessing SQL server from a java application


    Does anyone has an example for accessing a SQL server database from a java application ? I'm just learning java, and i'm using JDBC to connect to MySQL successfully. But now, i want to drag some information from SQL server and drop it to a MySQL database. The firs part (drag) is what i need. Somebody help me please ?

    Thanks.
  2. #2
  3. No Profile Picture
    alt.sex.programming
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    26
    Rep Power
    0
    well...this is my first java application...so please forgive me my programming style ~
    maybe there's an easier way to do it...if so I would appreciate it if somebody told me so.
    I've cut the whole thing down a little...so don't care about unused vaiables that may be there...

    Code:
    class Syncronize {
        
        public Syncronize() {
            // load drivers
            // load mysql drivers
            try {
                //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                Class.forName("com.mysql.jdbc.Driver");
            }
            catch ( ClassNotFoundException err ) {
                System.out.println( "Error with Driver" + err );
                return;
            }
            // load jdbc-odbc bridge
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            }
            catch ( ClassNotFoundException err ) {
                System.out.println( "Error with Driver" + err );
                return;
            }
        }
        
        
        public void dumpInTempDatabase(String source_table, String dest_table) {
            
            Connection db_source, db_destination;
            Statement stmt_source, stmt_destination;
            ResultSet rSet_source, rSet_destination;
           
            String sql_source, sql_destination, tmp_query;
            int result_columns, result_rows, count;
            
            try {
                db_source = DriverManager.getConnection( "jdbc:odbc:<DSN>");
                stmt_source = db_source.createStatement();
                
                db_destination=DriverManager.getConnection("jdbc:mysql://<host>:<port>/<database>?user=<user>&password=<pass>");
                stmt_destination = db_destination.createStatement();
                
                sql_source = "SELECT * FROM " + source_table;
                rSet_source = stmt_source.executeQuery( sql_source );
                
                ResultSetMetaData meta_source=rSet_source.getMetaData();
                result_columns=meta_source.getColumnCount();
                //System.out.print(result_columns);
                
                // truncate temporary table before inserting data
                sql_destination="TRUNCATE `" + dest_table + "`";
                stmt_destination.executeUpdate( sql_destination );
                
                while ( rSet_source.next() ) {
                    
                    // generate SQL query
                    sql_destination="INSERT INTO `" + dest_table + "` (";
                    
                    for (count=1; count < result_columns; count++) {
                        sql_destination+=meta_source.getColumnName(count);
                        if (count < result_columns-1) {
                            sql_destination+=", ";
                        }
                    }
                    sql_destination+=") VALUES (";
                    for (count=1; count < result_columns; count++) {
                        tmp_query=rSet_source.getString(count);
                        
                        
                        
                        //sql_destination+="'" + db_destination.nativeSQL (rSet_source.getString(count)) + "'";
                        sql_destination+="'" + this.escapeString( tmp_query ) + "'";
                        if (count < result_columns-1) {
                            sql_destination+=", ";
                        }
                    }
                    sql_destination+=")";
                    sql_destination=db_destination.nativeSQL(sql_destination);
                    
                    System.out.println(sql_destination);
                    
                    stmt_destination.executeUpdate( sql_destination );
                    
                }
                
                stmt_source.close();
                db_source.close();
            }
            catch ( SQLException err ) {
                System.out.println( "Error in SQL query: " + err );
                return;
            }
        }
        
        // remember to make this **** binary safe
        public String escapeString(String data) {
            
            char[] tmp = data.toCharArray();
            data = "";
            for (int count=0; count < tmp.length; count++) {
                if (tmp[count] == '\'') {
                    data += "\\'";
                } else {
                    data += String.valueOf(tmp[count]);
                }
            }
            
            return data;
        }
    }

IMN logo majestic logo threadwatch logo seochat tools logo