[Yerman]

Hi, I'm developing a project where i need to copy whatever Sybase has in its Database, directly into Oracle... the resctriction is that i have to do this via a JAVA program, i have the Oracle and the Sybase classes and is as follows
my code compiles, and it gives you back a resultset for whatever you want, and here comes the tricky part, which is the part i'm stuck on, how can i manipulate a resultset in a way that i can export that information into an oracle database, do i need to create a table? or what? because i can get it to display it but i need it to go to Oracle... any suggestion would be nice, is this the right forum? can anyone help??? thanx in adavanced for your help.
Yerman

package sybaseoracle;

import java.io.*;
import java.sql.*;
import com.sybase.jdbcx.*;
import com.sybase.jdbc3.tds.*;
import com.sybase.jdbc3.jdbc.*;
import com.sybase.jdbc3.timedio.*;
import com.sybase.jdbc3.utils.*;
import java.util.*;
import java.sql.ResultSet;

public class SybaseMainClassRS {

private static final String Make = "create table Test ( " + " id INT PRIMARY KEY, firstName VARCHAR(20), lastName VARCHAR(20), " + " title VARCHAR(20), salary VARCHAR(20) " + ")";

public static Connection getConnection() throws ClassNotFoundException, SQLException {
String driver = "com.sybase.jdbc3.jdbc.SybDataSource";
String url = "jdbc:sybase:Tds:W2VZ2DTT02:5000/testsybase";
String username = "sa";
String password = "";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}

public static void main(String args[]) {
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
stmt = conn.createStatement();
stmt.executeUpdate(Make);
stmt.executeUpdate("insert into Test(id, firstName, lastName, title, salary) values(100, 'German', 'Garcia', 'Analyst', '3000')");
stmt.executeUpdate("insert into Test(id, firstName, lastName, title, salary) values(200, 'German', 'Grajeola', 'Architect', '5000')");
System.out.println("Table Created.");
} catch (ClassNotFoundException e) {
System.out.println("error: failed to load Sybase driver.");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("error: failed to create a connection object.");
e.printStackTrace();
} catch (Exception e) {
System.out.println("other error:");
e.printStackTrace();
}
try {
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id FROM Test WHERE id = 200");
while (rs.next()) {
System.out.println(rs.getInt(1));
}
} catch (SQLException e) {
System.out.println("Missing Query Statement");
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();

} catch (Exception e) {
System.out.println("Can't close the connection");
e.printStackTrace();
}
}
}
}

[trvishi]

From what it looks like, this is not a one-off migration, but you want a persistent program to be exporting and importing from Sybase to Oracle?

[aflorin27]

Let's set the discussion to tables only. For each table you have in Sybase:
- you go to Oracle and see if the table exists
- if the table doesn't exist or it doesn't have the right structure, create the table (or alter)
- here you have a problem: you have to translate Sybase datatypes to Oracle (example: money or smalldatetime Sybase datatypes)
- for each record in the Sybase table, generate the appropriate INSERT statement to the Oracle table

[mike_bike_kite]

Originally Posted by Yerman

Hi, I'm developing a project where i need to copy whatever Sybase has in its Database,

Do you not know what it is you’ll be copying? is it just a table or a small set of tables? Is it everything in these tables or just changes since last transfer?

Originally Posted by Yerman

directly into Oracle...

Why not just use the bulk copy out of Sybase and then load that file into Oracle?

Originally Posted by Yerman

the resctriction is that i have to do this via a JAVA program,

Why JAVA or are you just masochistic?

Mike

[Yerman]

hi All,
i will be copying all the information that the tables have with them, the plan is as follows

open a connection to Sybase, extract the information via a stored procedure or select statement depends on what my superiors want, that would be my resultset, then open a connection to Oracle paste the info to the tables, its the same exact thing on every table the only thing is that one lives in Sybase and the other one would live in Oracle...

i tried explaining the benefits of bulk upload, from one to another but they want a straight line by line approach don't ask me why, performance wise this will bomb out since it will be 20000+ records in every push...
and they want to use JAVA since they are both running in unix so it made sense to them to have it like that... and taking into consideration that i have to do this 300 times as i have 300 different servers that i need to push out this information... (they want it this way) idiots they complicate things to much...

one suggestion was; make the resultset be extracted into a file a .dat and just use sql*loader but they said memory is much faster than a hard drive and can be prone to errors...

another suggestion that i made was to use hibernate, no such luck also... they are stubborn as hell and I'm stuck doing this, only I'm not that experienced in JAVA...

[mike_bike_kite]

I’d try:

• Bulk copying the data out from one table on (Sybase?) and onto a target server (Oracle?) just to make sure you can get a common format.
• Then I’d build a “test” shell script to just loop through the servers and tables copying the data across.
• You’ll want to truncate/delete data before loading the new data in.
• You’ll also want to check for errors and perhaps do a row check at the end as well.
• Try forcing various errors to happen and make sure you trap them.

The end program should be about 30 lines long with all the error checking. If they ask why you’re doing it this way then just say you’re checking all the environments are set up correctly - a quick shell script is the fastest way to do this. At the end of the run you could comment that “hey it actually does everything the end product is supposed to do – why don’t we use it?”. If they still insist on spending a bunch of time doing it in java then you could write a little java program that just called the shell script (assuming that's possible in java)?

Mike

[pprevot]

Hi all,

Another approach would be to use a direct connection between the servers using proxy tables. The java program then only creates the objects and reports what job is done. Both Sybase and Oracle have products to link to another vendor.

Philippe

[trvishi]

Originally Posted by trvishi

From what it looks like, this is not a one-off migration, but you want a persistent program to be exporting and importing from Sybase to Oracle?

You still didnt answer my question

Mikes way bulkcopy is probably the easiest and cost-effective way.

If this is a permenant requirement with minimal latency requirement and if you do have Replication resources, then you can setup Replication Server from Sybase to Oracle. Its not that cost-effective though considering the RepServer dba knowledge required. But can work out a lot better as a long term solution if you have money to throw at it.

[Yerman]

guys made the code... just finished it.... thanx all for contributing...

package Connecting;

import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Connection;
import java.io.*;
import java.sql.CallableStatement;
import oracle.jdbc.driver.OracleConnection;
import com.sybase.jdbcx.*;
import com.sybase.jdbc3.tds.*;
import com.sybase.jdbc3.jdbc.*;
import com.sybase.jdbc3.timedio.*;
import com.sybase.jdbc3.utils.*;
import java.util.*;

public class SybaseConnector {

Connection o_Connection = null;
Statement o_Statement = null;
ResultSet o_ResultSet = null;

Connection s_Connection = null;
Statement s_Statement = null;
ResultSet s_ResultSet = null;
CallableStatement proc = null;

String o_Driver = "oracle.jdbc.driver.OracleDriver";
String o_Url = "jdbcracle:thin:@198.162.1.103:1521/Test";
String s_Driver = "com.sybase.jdbc3.jdbc.SybDataSource";
String s_Url = "jdbc:sybase:Tds:198.162.1.103:5000/Test_MF";
//198.162.1.103
public SybaseConnector() {

//Load driver
try {
Class.forName(o_Driver);
Class.forName(s_Driver);
System.out.println("Driver for Oracle loaded Successfully");
System.out.println("Driver for Sybase loaded Successfully");
}
catch (ClassNotFoundException ex) {
System.out.println("Oracle Driver was not loaded successfully");
System.out.println("Sybase Driver was not loaded successfully");
ex.printStackTrace();
}
}

public void doWork() {

String query = "";
String inserting ="";

//String execute = "";
//String create = "";
try {
//Create connection object
o_Connection = DriverManager.getConnection(o_Url, "system", "9876");
System.out.println("Connected to Oracle Successfully");
s_Connection = DriverManager.getConnection(s_Url, "sa", "");
System.out.println("Connected to Sybase Successfully");

//Create Statement String objects
query = "SELECT * FROM Employee";
inserting = "Insert INTO EmpTest Values (?,?,?,?,?)";

//Execute the query
s_Statement = s_Connection.createStatement();
s_ResultSet = s_Statement.executeQuery(query);

//PreparedStatement ps_Statement;
PreparedStatement ps_Statement = o_Connection.prepareStatement(inserting);

//Loop through the results
while (s_ResultSet.next()) {
System.out.print(s_ResultSet.getString(1) + " " + s_ResultSet.getString(2) + " " + s_ResultSet.getString(3) + " " + s_ResultSet.getString(4) + " " + s_ResultSet.getString(5)+"\n");

for(int i = 1;i < 6;i++) {
ps_Statement.setString(i, s_ResultSet.getString(i));
}

ps_Statement.executeUpdate();
ps_Statement.clearParameters();
//o_Statement.executeQuery(inserting + s_ResultSet.getString(1) + ", " + s_ResultSet.getString(2) + ", " + s_ResultSet.getString(3) + ", " + s_ResultSet.getString(4) + ", " + s_ResultSet.getString(5) + ")");
System.out.println("Values Inserted Correctly");
}

} catch (SQLException ex) {
ex.printStackTrace();
System.out.println(ex);
} finally {

try {
if (o_ResultSet != null)
o_ResultSet.close();
if (s_ResultSet != null)
s_ResultSet.close();
if (o_Statement != null)
o_Statement.close();
if (s_Statement != null)
s_Statement.close();
if (o_Connection != null)
o_Connection.close();
if (s_Connection != null)
s_Connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
System.out.println(ex);
}
}
}

public static void main(String[] args) {
SybaseConnector Test = new SybaseConnector();
Test.doWork();
}
}

[Yerman]

its not the best solution.... that's kind of what i wanted to ask next... can you tell me performance wise... which is best... a line by line insertion which my code does... or a stored procedure... i will be pushing out 30,000 records at first Stored Procedure may look seducing but i skimmed through some pages from o'rielly a jdbc book problem is i can't find the book no more... that states that line by line insertion is faster... --now i can try to make my code more dynamically to accept or search all tables from a database (i wish) it will take a long time for me to code that(can anyone help on that) so based on experience or numbers which one would be better... i say 30,000 or it can be more or it can be less.... but that's the target.. and of course this would be a nightly thing...
thanx experts for all your help...

[trvishi]

Originally Posted by Yerman

its not the best solution.... that's kind of what i wanted to ask next... can you tell me performance wise... which is best... a line by line insertion which my code does... or a stored procedure... i will be pushing out 30,000 records at first Stored Procedure may look seducing but i skimmed through some pages from o'rielly a jdbc book problem is i can't find the book no more... that states that line by line insertion is faster... --now i can try to make my code more dynamically to accept or search all tables from a database (i wish) it will take a long time for me to code that(can anyone help on that) so based on experience or numbers which one would be better... i say 30,000 or it can be more or it can be less.... but that's the target.. and of course this would be a nightly thing...
thanx experts for all your help...

RDBMSes are set based and not line based. So, the more you batch the better. And the more native tools you use bcp (sybase), import/export (oracle) the better.

[mike_bike_kite]

can you tell me performance wise... which is best

Optimal solution shown here

i skimmed through some pages from o'rielly a jdbc book problem is i can't find the book no more... that states that line by line insertion is faster

I think you should try reading a little slower – it almost certainly stated the opposite. If it did say that line by line is faster then I’d ask for your money back on the book.

based on experience or numbers which one would be better

Why not just try using the bulk copy and load (for sybase and oracle) to just transfer the data from one system to the other. It should be only a few lines of shell script and will show you how fast the data can be loaded.

Mike