elmama
​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


Sample JAVA program to move data between Sybase and Oracle 


This example shows how to move data from a Sybase database table to an Oracle database table.
It was wrtiiten and tested using the Netbeans JAVA IDE

/*
* SybaseTest.java
*
* An example JAVA program that  moves records from a SYBASE database
* to an ORACLE database. Created using the NetBeans IDE.
*
* Created on 02 April 2004, 08:40
*/

import java.sql.*;
import java.util.*;

public class SybaseToOracle {


    public final static String DRIVER="com.sybase.jdbc2.jdbc.SybDriver";
    // Fill in your own SYBASE database path:port/name, user and password below
    public final static String URL="jdbc:sybase:Tds:db_path:port_no/db_name";
    public final static String USER="mysybaseuser";
    public final static String PASS="mysybasepass";

    public final static String DRIVER2="oracle.jdbc.driver.OracleDriver";
    // Fill in your own ORACLE database path:port/name, user and password below
    public final static String URL2="jdbc:oracle:thin:@db_path:port_no:db_name";
    public final static String USER2="myoracleuser";
    public final static String PASS2="myoraclepass";

  // SQL to get stock data from sybase database table

    public final static String sqlstr = " select ticker, cpn, maturity" +
                                                   " from sybase_stock_table";

    /** Creates a new instance of SybaseToOracle */
    public SybaseToOraclet() {
    }

    // This will hold our data temporarily
    public static class SybaseRow {
        String ticker;
        String cpn;
        String maturity;
    }


    public static void main(String[] args) {
        try {
            Class.forName(DRIVER);

            Connection conn=DriverManager.getConnection(URL,USER,PASS);
            Statement stmt=conn.createStatement();
            ResultSet rs=stmt.executeQuery(sqlstr);
            Vector res=new Vector();

            while(rs.next()) {
                SybaseRow row=new SybaseRow();
                row.ticker=rs.getString("ticker");
                row.cpn=rs.getString("cpn");
                row.maturity=rs.getString("maturity");
                res.add(row);
            }
            rs.close();
            stmt.close();
            conn.close();

            // Now we insert the retrieved Sybase data into an Oracle table
            Class.forName(DRIVER2);
            Connection oracleConn=DriverManager.getConnection(URL2,USER2,PASS2);
            PreparedStatement prep=oracleConn.prepareStatement("insert into oracle_stock_table ( "+

            "ticker, cpn, maturity) values ( ?,?,?)");

            oracleConn.setAutoCommit(false);
            Iterator it=res.iterator();
            int count=0;

            while(it.hasNext()) {
                TestRow row=(TestRow)it.next();
                //System.out.println(row.ticker);
                prep.setString(1,row.ticker);
                prep.setString(2,row.cpn);
                prep.setString(3,row.maturity);

/*
                prep.setDouble(2, row.price);
                prep.setDate(3, row.call_date);
*/

                prep.addBatch();
                count++;

                if ((count % 200)==0) {
                    try {

                        prep.executeBatch();
                        oracleConn.commit();

                    } catch (SQLException e) {

                    }
                    prep.clearParameters();
                    System.out.println("Processed record number" + count);
                }

            }
            if((count % 200) != 0) {
                prep.executeBatch();
            }
            oracleConn.commit();

        } catch (Exception e) {
            System.out.println("Error connecting to database "+e);
        }
    }

}

}