JDBC BUG with SQL server and sybase SQLAnywhere

JDBC BUG with SQL server and sybase SQLAnywhere

Post by givol » Thu, 05 Mar 1998 04:00:00



i think i found jdbc bug.

when inserting values to MSSQL or syabse to a table with timestamp column at
high rate - the values become  correpted.

try the following program

import java.sql.*;

//***************************************************************
//  This program illustrates a suspected very serious bug found
//  using either Sybase SQLAnywhere or MS-SQL Server over the
//  JDBC-ODBC bridge provided in JDK 1.1.5 both on Windows NT and 95.
//
//  When "fast" consecutive inserts are performed into a table
//  with a datetime field, the values in the record (not only this
//  field) become corrupt after a very short time (approx. 200 records).
//
//  Furthermore, the occurence of the bug is not consistent, in the
//  respect of how often the record get corrupt.
//*****************************************************************

public class SpeedTest
{
    //*************************************************************
    static public void main (String[] argv)
    {
        try
        {
            java.sql.Connection conn  = null;

            // Connects via JDBC-ODBC bridge to an SQL-Server ODBC
            // source (or to Sybase SQLAnywhere):
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            // Replace connection paramegers with those appropriate
            // for a DSN you have defined:
         conn =DriverManager.getConnection(
              "jdbc:odbc:test_db",
              "sa",
              "aA1bB2c");

         // This function is for building the table.
         // comment this if you already have the table named
         // "speed_test" from previous runs and do not want
         // to recreate it.
         buildTable(conn);

         // Illustrates the problem:
         testSpeed(conn);
        }
        catch (Exception e)
        {
         e.printStackTrace();
     }
    }

    //*************************************************************
    // Builds the table.
    //*************************************************************
    static void buildTable(Connection Conn) throws Exception
    {
        try
        {
            Statement S = Conn.createStatement();
            S.execute("drop table speed_test");
            S.close();
        }
        catch (Exception E) {} // disregard problems dropping

        try
        {
            Statement S = Conn.createStatement();
            S.execute("CREATE TABLE speed_test ( counter int NULL ,t_val
datetime NULL )");
            S.close();
        }
        catch (Exception Exp)
        {
            Exp.printStackTrace();
        }
    }

    //*************************************************************
 // insert records at fast rate , each time incrementing the
 // value in the "counter" column.
    //*************************************************************
   static void testSpeed(Connection Conn) throws Exception
   {
    // first, get the maximum value of the current counter.
    // the insertion begins from this value.

    Statement S = Conn.createStatement();
    ResultSet Res = S.executeQuery("select max(Counter) from speed_test");
  Res.next();

  int Counter = Res.getInt(1);
        Res.close();

    System.out.println("STARTING AT " + Counter);
    S.close();

    /********************************************/
    // Now, for the problem. each time the counter is raised by one,
    // and a new row is inserted. after some time, the counter just
    // "go mad" and the new values in the data base are wrong.
    /********************************************/
  for (int i =0 ; i < 1000 ; i++ )
  {
     PreparedStatement P = Conn.prepareStatement("insert into speed_test
(counter ,t_val) values (?,?)");
     P.setInt(1,++Counter);

     P.setTimestamp(2,new Timestamp(0));
            /*
                you can replace the above line with the lines below to
                get changing data, although, you will get the same effect:

                java.util.Date D ;
            Timestamp T ;
                D = new java.util.Date();
                T = new Timestamp(D.getYear(),
                                  D.getMonth(),
                                  D.getDate(),
                                  D.getHours(),
                                  D.getMinutes(),
                                  D.getSeconds(),
                                  0);
             P.setTimestamp(2,T);
            */

     P.executeUpdate();
            P.close();
  }

  // print the last value.
    // the insertion begins from this value.

    Statement S2 = Conn.createStatement();
    ResultSet Res2 = S2.executeQuery("select max(Counter) from speed_test");
  Res2.next();

  int EndCounter = Res2.getInt(1);
        Res2.close();

    System.out.println("END COUNTER = " + EndCounter);
    S.close();

        }

Quote:}

 
 
 

JDBC BUG with SQL server and sybase SQLAnywhere

Post by Michael Donega » Thu, 05 Mar 1998 04:00:00


Your program worked like a champ for me.
However, the last S.close(), should probably be
S2.close().

Also, if you take the prepareStatement and the P.close() out of the
loop, the program runs a whole lot faster. You lose the advantage of
having a PreparedStatement otherwise.

        mkd


> i think i found jdbc bug.

> when inserting values to MSSQL or syabse to a table with timestamp column at
> high rate - the values become  correpted.

> try the following program

> import java.sql.*;

> //***************************************************************
> //  This program illustrates a suspected very serious bug found
> //  using either Sybase SQLAnywhere or MS-SQL Server over the
> //  JDBC-ODBC bridge provided in JDK 1.1.5 both on Windows NT and 95.
> //
> //  When "fast" consecutive inserts are performed into a table
> //  with a datetime field, the values in the record (not only this
> //  field) become corrupt after a very short time (approx. 200 records).
> //
> //  Furthermore, the occurence of the bug is not consistent, in the
> //  respect of how often the record get corrupt.
> //*****************************************************************

> public class SpeedTest
> {
>     //*************************************************************
>     static public void main (String[] argv)
>     {
>         try
>         {
>             java.sql.Connection conn  = null;

>             // Connects via JDBC-ODBC bridge to an SQL-Server ODBC
>             // source (or to Sybase SQLAnywhere):
>             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

>             // Replace connection paramegers with those appropriate
>             // for a DSN you have defined:
>          conn =DriverManager.getConnection(
>               "jdbc:odbc:test_db",
>               "sa",
>               "aA1bB2c");

>          // This function is for building the table.
>          // comment this if you already have the table named
>          // "speed_test" from previous runs and do not want
>          // to recreate it.
>          buildTable(conn);

>          // Illustrates the problem:
>          testSpeed(conn);
>         }
>         catch (Exception e)
>         {
>          e.printStackTrace();
>      }
>     }

>     //*************************************************************
>     // Builds the table.
>     //*************************************************************
>     static void buildTable(Connection Conn) throws Exception
>     {
>         try
>         {
>             Statement S = Conn.createStatement();
>             S.execute("drop table speed_test");
>             S.close();
>         }
>         catch (Exception E) {} // disregard problems dropping

>         try
>         {
>             Statement S = Conn.createStatement();
>             S.execute("CREATE TABLE speed_test ( counter int NULL ,t_val
> datetime NULL )");
>             S.close();
>         }
>         catch (Exception Exp)
>         {
>             Exp.printStackTrace();
>         }
>     }

>     //*************************************************************
>  // insert records at fast rate , each time incrementing the
>  // value in the "counter" column.
>     //*************************************************************
>    static void testSpeed(Connection Conn) throws Exception
>    {
>     // first, get the maximum value of the current counter.
>     // the insertion begins from this value.

>     Statement S = Conn.createStatement();
>     ResultSet Res = S.executeQuery("select max(Counter) from speed_test");
>   Res.next();

>   int Counter = Res.getInt(1);
>         Res.close();

>     System.out.println("STARTING AT " + Counter);
>     S.close();

>     /********************************************/
>     // Now, for the problem. each time the counter is raised by one,
>     // and a new row is inserted. after some time, the counter just
>     // "go mad" and the new values in the data base are wrong.
>     /********************************************/
>   for (int i =0 ; i < 1000 ; i++ )
>   {
>      PreparedStatement P = Conn.prepareStatement("insert into speed_test
> (counter ,t_val) values (?,?)");
>      P.setInt(1,++Counter);

>      P.setTimestamp(2,new Timestamp(0));
>             /*
>                 you can replace the above line with the lines below to
>                 get changing data, although, you will get the same effect:

>                 java.util.Date D ;
>             Timestamp T ;
>                 D = new java.util.Date();
>                 T = new Timestamp(D.getYear(),
>                                   D.getMonth(),
>                                   D.getDate(),
>                                   D.getHours(),
>                                   D.getMinutes(),
>                                   D.getSeconds(),
>                                   0);
>              P.setTimestamp(2,T);
>             */

>      P.executeUpdate();
>             P.close();
>   }

>   // print the last value.
>     // the insertion begins from this value.

>     Statement S2 = Conn.createStatement();
>     ResultSet Res2 = S2.executeQuery("select max(Counter) from speed_test");
>   Res2.next();

>   int EndCounter = Res2.getInt(1);
>         Res2.close();

>     System.out.println("END COUNTER = " + EndCounter);
>     S.close();

>         }
> }


 
 
 

1. Sybase SQLAnywhere 5.0 for NT bugs

Sybase seems to want you to pay THEM to accept bug reports.  Symantec is
the same way.  I am rather ticked with this attitude.  The vendor has sold
a defective product and refuses to ever listen to the complaints.  This is
not the same as requesting support.  When you report bugs you are doing
them a favour.

(1) If you create a large people database and index by a secondary index
postalCode, you can do very rapid searches with SQL of the form: SELECT *
FROM PEOPLE WHERE POSTALCODE BETWEEN 'V5H0A0' AND 'V5H9Z9';  However if you
add an ORDER BY clause of any kind the search will take an EXTREMELY long
time, even if you select only a couple of records.  From the PLAN presented
which says it is building a temp table based on POSTALCODE, I think the
fool thing is searching the ENTIRE POSTALCODE index from beginning to end,
or is reading the entire database beginning to end.  Even when I give it an
estimate of 1% it does the same thing. It should simply scan that little
section of the POSTALCODE index spin off the people records it finds and
sort.

(2) with the -b option on dbeng50 I found many times that even after a
COMMIT-WORK, when I restarted the database, my last actions were not there.
I have not nailed down precisely when this happens.  I suspect it may be
related to running out of RAM.  I found I could bypass the problem by
loading data then building indexes rather than setting up indexes and
loading data.  Building indexes one by one later requires less RAM (and it
turns out is faster even though it takes many passes through the data).

For the JAVA GLOSSARY and the CMP Utilities: <http://mindprod.com>
--
Roedy Green,  Canadian Mind Products
-30-

2. BTRIEVE and NETWARE - what a combination!!??!!

3. Converting from Sybase SQLAnywhere 7 to MS SQL Server 2000

4. Newbie, please, please help!!

5. difference betweek sybase sql server 11 and sqlanywhere?

6. VFP 3.0a??

7. Sybase JDBC bug with java.sql.ResultSet.getString()

8. postgressql : JDBC driver

9. Sybase SQLAnywhere 5.0.5 as linked server trouble

10. JDBC/ODBC Bridge and SQL Server bug

11. MS SQL Server 2000 JDBC Beta : Bug while using DatabaseMetaData.getColumns() method

12. IA-Cedar Rapids Area-258899--ORACLE-SYBASE-SQLSERVER-Systems Analyst - Oracle, Sybase, SQL server