Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #552 > unrolled thread

Re: copy data from one database to another

Back to article view | Back to comp.lang.java.databases

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: copy data from one database to another Sathish@myc2s.com - 2012-08-12 23:43 -0700
    Re: copy data from one database to another Lew <noone@lewscanon.com> - 2012-08-18 11:09 -0700
    Re: copy data from one database to another Roedy Green <see_website@mindprod.com.invalid> - 2012-08-24 11:02 -0700

#552 — Re: copy data from one database to another

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));
>             }
>         }

[toc] | [next] | [standalone]

#553

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

[toc] | [prev] | [next] | [standalone]

#554

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 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web