Bug in JDBC Driver with use of… – Informix Archive

Zachi — 25 Apr 2007 09:37:25 -0700 — source: Usenet: comp.databases.informix

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(); if (e1 != null) System.err.println(e1.getMessage()+" ("+e1.getErrorCode()+")"); System.exit(1); } }

↪ replying to Zachi

george simpson — Wed, 25 Apr 2007 15:08:31 -0500 — source: Usenet: comp.databases.informix

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 <zklopman@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(); > if (e1 != null) > System.err.println(e1.getMessage()+" > ("+e1.getErrorCode()+")"); > System.exit(1); > } > } > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

↪ replying to george simpson

scottishpoet — 26 Apr 2007 05:48:35 -0700 — source: Usenet: comp.databases.informix

Looks like these coulkd be new defects I suggest you contact your support provider

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

Related threads

Your privacy choices