Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!npeer02.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
From: Sathish@myc2s.com
Newsgroups: comp.lang.java.databases
Subject: Re: copy data from one database to another
Date: Sun, 12 Aug 2012 23:43:23 -0700 (PDT)
Organization: http://groups.google.com
Lines: 58
Message-ID: <057be9ae-208a-4601-a5b2-9ed8c64f5d9a@googlegroups.com>
References: <35bb23f9-03ca-41a3-9bba-0cd5ee0c3162@z20g2000prh.googlegroups.com>
NNTP-Posting-Host: 203.6.212.121
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1344840203 19603 127.0.0.1 (13 Aug 2012 06:43:23 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 13 Aug 2012 06:43:23 +0000 (UTC)
In-Reply-To: <35bb23f9-03ca-41a3-9bba-0cd5ee0c3162@z20g2000prh.googlegroups.com>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=203.6.212.121; posting-account=nmiU_gkAAAAAwprNQZ2HdQLX_akqql60
User-Agent: G2/1.0
X-Received-Bytes: 3656
Xref: csiph.com comp.lang.java.databases:552

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