Possible Bug in Informix JDBC Driver with use of addBatch() with BigDecimal

Discussion in 'Java' started by Zachi, Apr 25, 2007.

  1. Zachi

    Zachi Guest

    Hello all,

    I am observing strange behavior in use of batch inserts of BigDecimal
    (money type in Informix): occasionally, instead of the null value the
    previous non-null value of the field in the batch will be used. This
    problem arises whether I set it the field implicitly or explicitly,
    and does not appear if the insert is executed immediately. The driver
    is 3.0JC3, and it happens on Sun Java on Windows XP and IBM java
    bundled with Informix on Linux.Here is a code to describe the problem:

    ResultSet r = ..."select field1, field2,...money_field,... from tab1
    where...";
    PreparedStatement p = ..."insert into tab2 (field1,
    field2,...money_field,...) values (?,?,...?,...)";

    //implicit NULL setting - inserts non-null values
    while (r.hasNext()) {
    ...
    p.setBigDecimal(i,r.getBigDecimal(i));
    ...
    p.addBatch();
    }
    p.submitBatch();


    //explicit NULL setting - inserts non-null values
    while (r.hasNext()) {
    ...
    BigDecimal b = r.getBigDecimal(i);
    if (r.wasNull()) {
    p.setNull(i, java.sql.Types.BigDecimal);
    } else {
    p.setBigDecimal(i,b);
    }
    ...
    p.addBatch();
    }
    p.submitBatch();



    // non-use of batch - works with implicit and explicit
    while (r.hasNext()) {
    ...
    p.setBigDecimal(i,r.getBigDecimal(i));
    ...
    p.execute();
    }


    Any ideas?

    Thanks,

    Zachi
    Zachi, Apr 25, 2007
    #1
    1. Advertising

  2. Zachi wrote:
    >
    > ResultSet r = ..."select field1, field2,...money_field,... from tab1
    > where...";
    > PreparedStatement p = ..."insert into tab2 (field1,
    > field2,...money_field,...) values (?,?,...?,...)";
    >
    > //implicit NULL setting - inserts non-null values
    > while (r.hasNext()) {
    > ...
    > p.setBigDecimal(i,r.getBigDecimal(i));
    > ...
    > p.addBatch();
    > }
    > p.submitBatch();


    What is submitBatch()? It's not a standard JDBC method.

    --
    Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
    ==============================================================
    * The Ultimate DBMS is here!
    * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
    Lee Fesperman, Apr 27, 2007
    #2
    1. Advertising

  3. Zachi

    Zachi Guest

    On Apr 27, 2:12 pm, Lee Fesperman <> wrote:
    > Zachi wrote:
    >
    > > ResultSet r = ..."select field1, field2,...money_field,... from tab1
    > > where...";
    > > PreparedStatement p = ..."insert into tab2 (field1,
    > > field2,...money_field,...) values (?,?,...?,...)";

    >
    > > //implicit NULL setting - inserts non-null values
    > > while (r.hasNext()) {
    > > ...
    > > p.setBigDecimal(i,r.getBigDecimal(i));
    > > ...
    > > p.addBatch();
    > > }
    > > p.submitBatch();

    >
    > What is submitBatch()? It's not a standard JDBC method.
    >
    > --
    > Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
    > ==============================================================
    > * The Ultimate DBMS is here!
    > * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)


    submitBatch(), as well as its brothers addBatch(), executeBatch() and
    clearBatch() are part
    of the java.sql.Statement , which has been part of the standard since
    1.4.2 (if not earlier). This is an interface to allow sending a batch
    of commands to be executed, instead of sending them one at a time,
    which allows for much higher performance due to eliminating a lot of
    roundtrips.

    Zachi
    Zachi, Apr 27, 2007
    #3
  4. Zachi

    Guest

    On 27 Apr, 22:48, Zachi <> wrote:
    > On Apr 27, 2:12 pm, Lee Fesperman <> wrote:
    >
    >
    >
    >
    >
    > > Zachi wrote:

    >
    > > > ResultSet r = ..."select field1, field2,...money_field,... from tab1
    > > > where...";
    > > > PreparedStatement p = ..."insert into tab2 (field1,
    > > > field2,...money_field,...) values (?,?,...?,...)";

    >
    > > > //implicit NULL setting - inserts non-null values
    > > > while (r.hasNext()) {
    > > > ...
    > > > p.setBigDecimal(i,r.getBigDecimal(i));
    > > > ...
    > > > p.addBatch();
    > > > }
    > > > p.submitBatch();

    >
    > > What is submitBatch()? It's not a standard JDBC method.

    >
    > > --
    > > Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
    > > ==============================================================
    > > * The Ultimate DBMS is here!
    > > * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

    >
    > submitBatch(), as well as its brothers addBatch(), executeBatch() and
    > clearBatch() are part
    > of the java.sql.Statement , which has been part of the standard since
    > 1.4.2 (if not earlier). This is an interface to allow sending a batch
    > of commands to be executed, instead of sending them one at a time,
    > which allows for much higher performance due to eliminating a lot of
    > roundtrips.
    >
    > Zachi- Hide quoted text -
    >
    > - Show quoted text -


    Log a bug with IBM.
    , Apr 28, 2007
    #4
  5. Zachi

    David Harper Guest

    Re: Possible Bug in Informix JDBC Driver with use of addBatch() withBigDecimal

    Zachi wrote:
    > On Apr 27, 2:12 pm, Lee Fesperman <> wrote:
    >> Zachi wrote:
    >>
    >>> ResultSet r = ..."select field1, field2,...money_field,... from tab1
    >>> where...";
    >>> PreparedStatement p = ..."insert into tab2 (field1,
    >>> field2,...money_field,...) values (?,?,...?,...)";
    >>> //implicit NULL setting - inserts non-null values
    >>> while (r.hasNext()) {
    >>> ...
    >>> p.setBigDecimal(i,r.getBigDecimal(i));
    >>> ...
    >>> p.addBatch();
    >>> }
    >>> p.submitBatch();

    >> What is submitBatch()? It's not a standard JDBC method.
    >>
    >> --
    >> Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
    >> ==============================================================
    >> * The Ultimate DBMS is here!
    >> * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

    >
    > submitBatch(), as well as its brothers addBatch(), executeBatch() and
    > clearBatch() are part
    > of the java.sql.Statement , which has been part of the standard since
    > 1.4.2 (if not earlier). This is an interface to allow sending a batch
    > of commands to be executed, instead of sending them one at a time,
    > which allows for much higher performance due to eliminating a lot of
    > roundtrips.


    submitBatch isn't listed in the API docs for java.sql.Statement in Java
    1.4.2 or 1.5.0 or 1.6.0

    The only batch methods listed are addBatch, clearBatch and executeBatch.

    Perhaps submitBatch is some kind of non-standard extension in the
    Informix driver?

    David Harper
    Cambridge, England
    David Harper, Apr 28, 2007
    #5
  6. Zachi

    Zachi Guest

    On Apr 28, 5:37 am, David Harper <-net.com> wrote:
    > Zachi wrote:
    > > On Apr 27, 2:12 pm, Lee Fesperman <> wrote:
    > >> Zachi wrote:

    >
    > >>> ResultSet r = ..."select field1, field2,...money_field,... from tab1
    > >>> where...";
    > >>> PreparedStatement p = ..."insert into tab2 (field1,
    > >>> field2,...money_field,...) values (?,?,...?,...)";
    > >>> //implicit NULL setting - inserts non-null values
    > >>> while (r.hasNext()) {
    > >>> ...
    > >>> p.setBigDecimal(i,r.getBigDecimal(i));
    > >>> ...
    > >>> p.addBatch();
    > >>> }
    > >>> p.submitBatch();
    > >> What is submitBatch()? It's not a standard JDBC method.

    >
    > >> --
    > >> Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
    > >> ==============================================================
    > >> * The Ultimate DBMS is here!
    > >> * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

    >
    > > submitBatch(), as well as its brothers addBatch(), executeBatch() and
    > > clearBatch() are part
    > > of the java.sql.Statement , which has been part of the standard since
    > > 1.4.2 (if not earlier). This is an interface to allow sending a batch
    > > of commands to be executed, instead of sending them one at a time,
    > > which allows for much higher performance due to eliminating a lot of
    > > roundtrips.

    >
    > submitBatch isn't listed in the API docs for java.sql.Statement in Java
    > 1.4.2 or 1.5.0 or 1.6.0
    >
    > The only batch methods listed are addBatch, clearBatch and executeBatch.
    >
    > Perhaps submitBatch is some kind of non-standard extension in the
    > Informix driver?
    >
    > David Harper
    > Cambridge, England


    Sorry, my mistake. submitBatch() was a wrapper for executeBatch() in
    my original code.
    Zachi, Apr 28, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Betty
    Replies:
    1
    Views:
    9,864
    Juha Laiho
    May 21, 2005
  2. Bruce
    Replies:
    4
    Views:
    754
    Bjorn Abelli
    Mar 25, 2006
  3. Replies:
    3
    Views:
    1,225
  4. Zachi
    Replies:
    0
    Views:
    509
    Zachi
    Apr 25, 2007
  5. Stanimir Stamenkov
    Replies:
    4
    Views:
    2,584
    Stanimir Stamenkov
    Jul 18, 2008
Loading...

Share This Page