inserting data from resultset into oracle database - but not inserting in correct order

From:

bazzer <somersbar@yahoo.com>

Newsgroups:

comp.lang.java.databases

Date:

Mon, 6 Jul 2009 09:01:10 -0700 (PDT)

Message-ID:

<9668e56b-197a-4e10-b3ec-a1779e952c7f@p29g2000yqh.googlegroups.com>

Hi

I am trying to insert the values of a resultset into an oracle
database. The problem is that they seem to be inserting in random
groups, as oppose to the way they are in the resultset.

Basically I have a program getting a certain range of data from a
particular table in a SQL server database, copying that data into a
ResultSet, and then inserting that data from resultset into an oracle
database. The range of data selected from the SQL database, is based
on timestamps. So I basically select a range of data between 2
timestamps. This all works fine if i do it for small difference in
timestamps, say a 3 or 4 hours. The problem is when I try do this for
a longer period, maybe 10hours+.

The data appears in the correct order in the ResultSet. But in the
oracle DB, they appear in groups of timestamps. The oracle DB is
always empty before I insert anything into it. I cannot figure out
whey they do not insert into oracle in same order as in resultset.
Please see below a sample of my code.

In case anyone needs to know the
amount of data, the table in the SQL DB has 147 columns(all of which
I
am selecting), and for 10hours of data, there are about 60 rows. Any
help is greatly appreciated.

code sample:

        //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:database", "username", "password");

        //connect to the Oracle database
        Connection connOra =
            DriverManager.getConnection(
             "jdbc:oracle:thin:@database:1521:db", "username",
"password");

        //create statement for SQL connection
        Statement mySQLstat = connSQL.createStatement(
         ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

        //create statement for Oracle connection
        Statement myOrastat = connOra.createStatement();

        //create ResultSet for SQL data, and populate using SELECT
query
        ResultSet rs = mySQLstat.executeQuery(
        "SELECT * FROM Owenreagh.dbo.T_WTG06_10MINDATA " +
        "WHERE ((T_WTG06_10MINDATA.TTimeStamp>{ts '2009-07-05
05:00:00'}) " +
         "AND (T_WTG06_10MINDATA.TTimeStamp<{ts '2009-07-05
17:00:00'}))");

        //create ResultSetMetaData to get MetaData from ResultSet
        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();

        //loop through each row of the ResultSet
        while(rs.next()) {
            //execute statement to copy the column values in
ResultSet, to corresponding column in the Oracle database
            myOrastat.executeUpdate(
                "INSERT INTO VESTASTURBINEDATA(ID, PROTOCOLVERSIONID,
TTIMESTAMP, ........" +
                "VALUES(" + (rs.getInt("Id")) + "," + (rs.getInt
("ProtocolVersionId")) +
                    ",to_timestamp('" + (rs.getTimestamp
("TTimeStamp")) + "','YYYY-MM-DD HH24:MI:SS:FF')" + ....... ")");

        }//end of while(rs.next())

        //close both statements
        mySQLstat.close();
        myOrastat.close();

        //disconnect from SQL database
        connSQL.close();

        //disconnect from Oracle database
        connOra.close();