f



copy data from one database to another

Hi

I am trying to go about copying data from one database to another. The
source database is SQL server, and the destination is an Oracle
database. While I can access both database' fine, and insert into the
oracle database fine, I am having trouble copying data from the SQL DB
to the Oracle DB. What I was initially trying to do was copy all the
required data into a resultSet, and then transfer this into the Oracle
DB. But i probablt cannot insert this block of data in the resultSet
directly into the Oracle db i supposed??? If anyone could suggest a
better more efficient way to transfer the data i would be grateful.
Please see below a snippet of the code for transferring the data. For
the moment I am only copying rows between a certain timestamp. Its
seems to run fine, and I am not getting an error, but when I check the
Oracle database, there is no data in it.

        //setup the drivers
        DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver
());
        DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());

        //connect to the MySQL database
        Connection connSQL = DriverManager.getConnection(
        "jdbc:odbc:Owenreagh", "username", "password");
        System.out.println("connected to SQL DB successfully");

        //connect to the Oracle database
        Connection connOra = DriverManager.getConnection(
            "jdbc:oracle:thin:@hostname:1521:DBname",       // URL
            "username",       // username
            "password"        // password
        );
        System.out.println("connected to Oracle DB successfully");

        Statement mySQLstat = connSQL.createStatement();
        Statement myOrastat = connOra.createStatement();

        ResultSet rsSQL = mySQLstat.executeQuery(
        "SELECT * FROM Owenreagh.dbo.T_WTG06_10MINDATA WHERE
((T_WTG06_10MINDATA.TTimeStamp>{ts '2009-01-01 00:10:00'}) AND
(T_WTG06_10MINDATA.TTimeStamp<{ts '2009-01-01 00:10:30'}))");

        ResultSetMetaData rsSQLmd = rsSQL.getMetaData();
        int numberOfColumns = rsSQLmd.getColumnCount();
	int max = numberOfColumns + 1;

        int index;
        while(rsSQL.next()){
            for(index = 1; index < max; index++) {
                myOrastat.executeQuery("INSERT INTO VESTASTURBINEDATA
VALUES" +
                                       rsSQL.getString
(index));
            }
        }
0
bazzer
6/12/2009 11:43:51 AM
comp.lang.java.databases 3049 articles. 0 followers. samyaksulabh (16) is leader. Post Follow

9 Replies
837 Views

Similar Articles

[PageSpeed] 24

On Fri, 12 Jun 2009 04:43:51 -0700, bazzer wrote:

> Hi
> 
> I am trying to go about copying data from one database to another. The
> source database is SQL server, and the destination is an Oracle
> database. While I can access both database' fine, and insert into the
> oracle database fine, I am having trouble copying data from the SQL DB
> to the Oracle DB. What I was initially trying to do was copy all the
> required data into a resultSet, and then transfer this into the Oracle
> DB. But i probablt cannot insert this block of data in the resultSet
> directly into the Oracle db i supposed??? If anyone could suggest a
> better more efficient way to transfer the data i would be grateful.
>
Most databases can export and import tables as a CSV files. Your easiest 
method may be to use these utilities. You may find it helpful to turn off 
constraints, etc. on the Oracle DB until you've populated all the tables.

As you may need to do the transfer more than once, the time spent 
scripting the export and import operations would be time well spent.
 
In any case your INSERT operation is (a) slow: use a prepared statement 
to speed it up and (b) incorrect: you are running a separate INSERT for 
each column in each row in the result set. 


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
6/12/2009 1:17:45 PM
On Jun 12, 2:17=A0pm, Martin Gregorie <mar...@address-in-sig.invalid>
wrote:
> On Fri, 12 Jun 2009 04:43:51 -0700, bazzer wrote:
> > Hi
>
> > I am trying to go about copying data from one database to another. The
> > source database is SQL server, and the destination is an Oracle
> > database. While I can access both database' fine, and insert into the
> > oracle database fine, I am having trouble copying data from the SQL DB
> > to the Oracle DB. What I was initially trying to do was copy all the
> > required data into a resultSet, and then transfer this into the Oracle
> > DB. But i probablt cannot insert this block of data in the resultSet
> > directly into the Oracle db i supposed??? If anyone could suggest a
> > better more efficient way to transfer the data i would be grateful.
>
> Most databases can export and import tables as a CSV files. Your easiest
> method may be to use these utilities. You may find it helpful to turn off
> constraints, etc. on the Oracle DB until you've populated all the tables.
>
> As you may need to do the transfer more than once, the time spent
> scripting the export and import operations would be time well spent.
>
> In any case your INSERT operation is (a) slow: use a prepared statement
> to speed it up and (b) incorrect: you are running a separate INSERT for
> each column in each row in the result set.
>
> --
> martin@ =A0 | Martin Gregorie
> gregorie. | Essex, UK
> org =A0 =A0 =A0 |

Thanks Martin.

Ya unfortunately the data we want can vary, and so I cant import whole
tables, so the CSV method wont work. Unless theres a way to pull in
varying columns/rows using the CSV files im not aware of. Just thought
there might have been some way to do this i was overlooking. And yes
after a lot of messing around with the code I have the ResultSet
populating the Oracle DB with small amounts of data. Realised the
errors in my code. Just trying to test it with the whole lot now. And
thanks for the advice on the prepare statement. Just checked up about
it there and will def be using that.

Thanks again.

Barry
0
bazzer
6/12/2009 2:59:49 PM
On Fri, 12 Jun 2009 07:59:49 -0700, bazzer wrote:

> Ya unfortunately the data we want can vary, and so I cant import whole
> tables, so the CSV method wont work. Unless theres a way to pull in
> varying columns/rows using the CSV files im not aware of.
>
You might want to consider writing a Java program that can select rows 
for output to CSV files. That, plus shell or Perl scripting can be useful 
for editing data in cases where the schemas don't entirely match.

> Just thought
> there might have been some way to do this i was overlooking. 
>
Have you looked at the JDBC facilities for reading meta-data from a DBMS? 
That may be useful if columns in equivalent tables in the two databases 
don't correspond in number or order.
 

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |
0
Martin
6/12/2009 7:13:14 PM
On Jun 12, 8:13=A0pm, Martin Gregorie <mar...@address-in-sig.invalid>
wrote:
> On Fri, 12 Jun 2009 07:59:49 -0700, bazzer wrote:
> > Ya unfortunately the data we want can vary, and so I cant import whole
> > tables, so the CSV method wont work. Unless theres a way to pull in
> > varying columns/rows using the CSV files im not aware of.
>
> You might want to consider writing a Java program that can select rows
> for output to CSV files. That, plus shell or Perl scripting can be useful
> for editing data in cases where the schemas don't entirely match.
>
> > Just thought
> > there might have been some way to do this i was overlooking.
>
> Have you looked at the JDBC facilities for reading meta-data from a DBMS?
> That may be useful if columns in equivalent tables in the two databases
> don't correspond in number or order.
>
> --
> martin@ =A0 | Martin Gregorie
> gregorie. | Essex, UK
> org =A0 =A0 =A0 |

Yep im using metadata aswell for a number of things. Seem to have it
working fine now transferring data. Do you know what sort of limits
there are with using ResultSets? Id like to stick with what iv got now
rather than looking at CSV files, since I have it working. But at the
moment im trying to figure out if I could hit any problems whereby the
ResultSet could have a limit on the amount of data it can take.

Thanks.

Barry
0
bazzer
6/15/2009 10:11:14 AM
bazzer, 15.06.2009 12:11:
> Yep im using metadata aswell for a number of things. Seem to have it
> working fine now transferring data. Do you know what sort of limits
> there are with using ResultSets? Id like to stick with what iv got now
> rather than looking at CSV files, since I have it working. But at the
> moment im trying to figure out if I could hit any problems whereby the
> ResultSet could have a limit on the amount of data it can take.

That depends on the driver. 

Some drivers are caching the full result in memory before returning from executeQuery() (such as Microsoft's JDBC driver and jTDS) other drivers only load a row each time ResultSet.next() is called (such as the Oracle driver)

But the Microsoft driver and jTDS can be configured to not cache the full result set (selectMethod=cursor)


Thomas
0
Thomas
6/15/2009 10:39:25 AM
On Jun 15, 11:39=A0am, Thomas Kellerer <YQDHXVLMU...@spammotel.com>
wrote:
>
> That depends on the driver.
>
> Some drivers are caching the full result in memory before returning from =
executeQuery() (such as Microsoft's JDBC driver and jTDS) other drivers onl=
y load a row each time ResultSet.next() is called (such as the Oracle drive=
r)
>
> But the Microsoft driver and jTDS can be configured to not cache the full=
 result set (selectMethod=3Dcursor)
>
> Thomas


Thats great thanks Thomas!

Barry
0
bazzer
6/15/2009 11:23:35 AM
On Friday, June 12, 2009 5:13:51 PM UTC+5:30, bazzer wrote:
> Hi
> 
> I am trying to go about copying data from one database to another. The
> source database is SQL server, and the destination is an Oracle
> database. While I can access both database' fine, and insert into the
> oracle database fine, I am having trouble copying data from the SQL DB
> to the Oracle DB. What I was initially trying to do was copy all the
> required data into a resultSet, and then transfer this into the Oracle
> DB. But i probablt cannot insert this block of data in the resultSet
> directly into the Oracle db i supposed??? If anyone could suggest a
> better more efficient way to transfer the data i would be grateful.
> Please see below a snippet of the code for transferring the data. For
> the moment I am only copying rows between a certain timestamp. Its
> seems to run fine, and I am not getting an error, but when I check the
> Oracle database, there is no data in it.
> 
>         //setup the drivers
>         DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver
> ());
>         DriverManager.registerDriver(new
> oracle.jdbc.driver.OracleDriver());
> 
>         //connect to the MySQL database
>         Connection connSQL = DriverManager.getConnection(
>         "jdbc:odbc:Owenreagh", "username", "password");
>         System.out.println("connected to SQL DB successfully");
> 
>         //connect to the Oracle database
>         Connection connOra = DriverManager.getConnection(
>             "jdbc:oracle:thin:@hostname:1521:DBname",       // URL
>             "username",       // username
>             "password"        // password
>         );
>         System.out.println("connected to Oracle DB successfully");
> 
>         Statement mySQLstat = connSQL.createStatement();
>         Statement myOrastat = connOra.createStatement();
> 
>         ResultSet rsSQL = mySQLstat.executeQuery(
>         "SELECT * FROM Owenreagh.dbo.T_WTG06_10MINDATA WHERE
> ((T_WTG06_10MINDATA.TTimeStamp>{ts '2009-01-01 00:10:00'}) AND
> (T_WTG06_10MINDATA.TTimeStamp<{ts '2009-01-01 00:10:30'}))");
> 
>         ResultSetMetaData rsSQLmd = rsSQL.getMetaData();
>         int numberOfColumns = rsSQLmd.getColumnCount();
> 	int max = numberOfColumns + 1;
> 
>         int index;
>         while(rsSQL.next()){
>             for(index = 1; index < max; index++) {
>                 myOrastat.executeQuery("INSERT INTO VESTASTURBINEDATA
> VALUES" +
>                                        rsSQL.getString
> (index));
>             }
>         }

0
Sathish
8/13/2012 6:43:23 AM
Sathish@myc2s.com wrote:
> bazzer wrote:

Is this a repost?

>> I am trying to go about copying data from one database to another. The
>> source database is SQL server, and the destination is an Oracle
>> database. While I can access both database' fine, and insert into the
>> oracle database fine, I am having trouble copying data from the SQL DB
>> to the Oracle DB. What I was initially trying to do was copy all the
>> required data into a resultSet, and then transfer this into the Oracle
>> DB. But i probablt cannot insert this block of data in the resultSet
>> directly into the Oracle db i supposed??? If anyone could suggest a
>> better more efficient way to transfer the data i would be grateful.
>> Please see below a snippet of the code for transferring the data. For
>> the moment I am only copying rows between a certain timestamp. Its
>> seems to run fine, and I am not getting an error, but when I check the
>> Oracle database, there is no data in it.

Using the source database's tools, export the source data to a format 
recognized by both RDBMSes, such as certain CSV formats. Using the destination 
database's tools, import the data from that format.

If you must use Java, use 'PreparedStatement'. You might accidentally be 
performing a random SQL injection attack on yourself with the code you posted.

-- 
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg
0
Lew
8/18/2012 6:09:18 PM
On Sun, 12 Aug 2012 23:43:23 -0700 (PDT), Sathish@myc2s.com wrote,
quoted or indirectly quoted someone who said :

>If anyone could suggest a
>> better more efficient way to transfer the data i would be grateful.

Using your strategy, I think you will do best if your intermediate
form an a Java object with a field for each record.

Another way to do it is to use the database export utility to produce
some sort of CSV file, then import it.  If the files are not 100%
compatible use http://mindprod.com/products1.html#CSV 
to convert the file.  You will need one file per table.
-- 
Roedy Green Canadian Mind Products http://mindprod.com
A new scientific truth does not triumph by convincing its opponents and making them see the light,
but rather because its opponents eventually die, and a new generation grows up that is familiar with it.
~ Max Planck 1858-04-23 1947-10-04 


0
Roedy
8/24/2012 6:02:26 PM
Reply: