Re: Bug in JDBC Driver with use of Bulk Insert and Money Data Type

Thanks, George, for the workaround. It works in this test, but I have not tested it in the full scale application. On discussion with IBM, it appears they have another open PMR about bulk loads - this one is about Serial8 datatype. IBM's recommendation: do not use bulk insert (i.e. do not set IFX_USEPUT to "1"). The gain from submitting inserts in batch is mainly due to saving many roundtrips, which is much higher than the gain of the "PUT" insert. And for the readers: I apologize for opening three threads. Google Groups had serious problems for the past several days, and I was not able to see my posts (or anyone else's, for that matter). Thanks, Zachi On Apr 25, 4:08 pm, "george simpson" <georges.i...@gmail.com> wrote: > Hi Zachi, > > I may have a work around for you. > Use "clearParameters();" as demonstrated below in your loadBatch. > > static void loadBatch() { > PreparedStatement i; > try { > i = con.prepareStatement("INSERT INTO moneybug(a,b) > VALUES (?,?)"); > i.setBigDecimal(1, null); > i.setInt(2, 5); > i.addBatch(); > > i.setBigDecimal(1, null); > i.setInt(2, 6); > i.addBatch(); > > i.setBigDecimal(1, new BigDecimal(7.01)); > i.setInt(2, 7); > i.addBatch(); > > // -----> i.clearParameters(); > > i.setBigDecimal(1, null); > i.setInt(2, 8); > i.addBatch(); > i.executeBatch(); > > } catch (SQLException e) { > sqlerr(e); > } > > } > > On 25 Apr 2007 09:37:25 -0700, Zachi <zklop...@gmail.com> wrote: > > > > > In continuation of my previous posts (which might have been lost), I > > have discovered that when setting IFX_USEPUT=1 , money type fields in > > inserts which are assigned a NULL value may get a previous non-null > > value. > > Here is a Java program that demonstrates the bug: > > > package testcase; > > > import java.math.BigDecimal; > > import java.sql.Connection; > > import java.sql.Driver; > > import java.sql.DriverManager; > > import java.sql.PreparedStatement; > > import java.sql.SQLException; > > import java.sql.Statement; > > import java.util.Properties; > > > public class BigDecimalBug { > > public static void main(String[] args) { > > createConnection( > > "jdbc:informix-sqli:// > > <host>:<port>:INFORMIXSERVER=<server>;user=<user>;password=<password>"); > > createDatabaseAndTable(); > > loadDirect(); > > loadBatch(); > > > } > > static Connection con; > > static Statement stmt; > > > static void createDatabaseAndTable() { > > try { > > stmt.execute("CREATE DATABASE bugtest WITH > > BUFFERED LOG"); > > stmt.execute("DATABASE bugtest"); > > stmt.execute("CREATE RAW TABLE moneybug(a > > MONEY(10,2), b INT)"); > > } catch (SQLException e) { > > sqlerr(e); > > } > > } > > > static void dropDatabase() { > > try { > > stmt.execute("DROP DATABASE bugtest"); > > } catch (SQLException e) { > > sqlerr(e); > > } > > } > > > static void loadDirect() { > > PreparedStatement i; > > try { > > i = con.prepareStatement("INSERT INTO > > moneybug(a,b) VALUES (?,?)"); > > i.setBigDecimal(1, null); > > i.setInt(2, 1); > > i.execute(); > > > i.setBigDecimal(1, null); > > i.setInt(2, 2); > > i.execute(); > > > i.setBigDecimal(1, new BigDecimal(3.01)); > > i.setInt(2, 3); > > i.execute(); > > > i.setBigDecimal(1, null); > > i.setInt(2, 4); > > i.execute(); > > > } catch (SQLException e) { > > sqlerr(e); > > } > > } > > static void loadBatch() { > > PreparedStatement i; > > try { > > i = con.prepareStatement("INSERT INTO > > moneybug(a,b) VALUES (?,?)"); > > i.setBigDecimal(1, null); > > i.setInt(2, 5); > > i.addBatch(); > > > i.setBigDecimal(1, null); > > i.setInt(2, 6); > > i.addBatch(); > > > i.setBigDecimal(1, new BigDecimal(7.01)); > > i.setInt(2, 7); > > i.addBatch(); > > > i.setBigDecimal(1, null); > > i.setInt(2, 8); > > i.addBatch(); > > i.executeBatch(); > > > } catch (SQLException e) { > > sqlerr(e); > > } > > > } > > > static void createConnection(String url) { > > try { > > String ifxDriver = "com.informix.jdbc.IfxDriver"; > > > // Register the INFORMIX-JDBC driver > > Driver IfmxDrv = (Driver) Class.forName > > (ifxDriver).newInstance(); > > > // If IFX_USEPUT is not set, this problem does not > > appear! > > Properties pr = new Properties(); > > pr.put("IFX_USEPUT","1"); > > con = DriverManager.getConnection(url, pr); > > > // go to the requested database > > stmt = con.createStatement(); > > } catch (SQLException e) { > > sqlerr(e); > > } catch (Exception e) { > > System.err.println("FAILED: Could not load > > Informix JDBC driver"); > > System.exit(1); > > } > > } > > > static void sqlerr(SQLException e) { > > System.err.println("SQL ERROR > > "+((SQLException)e).getErrorCode()+": > > "+e.getMessage()); > > SQLException e1 = > > ((SQLException)e).getNextException(); > >