date format problem with IFMX-JDBC

Hello, We are currently using the JDBC-ODBC -bridge in our application. We would like to convert to the Informix Type4 driver, but are experiencing following (NLS) problem: with ODBC-bridge we have to use the dateformat dd.mm.yyyy, but with Informix JDBC we have to use the format yyyy-mm-dd. Our CLIENT_LOCALE/DB_LOCALE is fi_fi.8859-1 Documentation says that DBDATE can not be changed. Does anybody have any ideas how to overcome this problem? This accompanying Java-class demonstrates the problem. Thank you for your help, Lauri Pietarinen AtBusiness Communications /** * This class is provided to test JDBC-connections * */ package JDBCTest; import java.sql.*; public class JDBCTest { public JDBCTest() { } public static void main(String[] args) { JDBCTest tester = new JDBCTest(); Connection conODBC = null; Connection conIFMX = null; try { Class.forName("com.informix.jdbc.IfxDriver"); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String urlODBC = "jdbc:odbc:TREE" ; String urlIFMX = "jdbc:informix-sqli://lauri2.in.atbusiness.com:1526/" + "lauri_db:INFORMIXSERVER=lauri2_ol;user=informix;password=informix"; conODBC = DriverManager.getConnection( urlODBC, "informix", "informix" ); conIFMX = DriverManager.getConnection( urlIFMX, "informix", "informix" ); trySQL(conODBC); trySQL(conIFMX); } catch( Exception e ) { e.printStackTrace(); } } static void trySQL(Connection con) throws SQLException { Statement stmt = null; PreparedStatement stmt2 = null; System.out.println("Using connection " + con); try { stmt2 = con.prepareStatement("insert into date_test values(1,'1.5.1999')"); <<-- works with ODBC-bridge //stmt2 = con.prepareStatement("insert into date_test values(1,'1999-05-01')"); <<-- works with native JDBC stmt2.execute(); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select d1 from date_test"); while(rs.next()){ System.out.println(rs.getDate("d1")); } rs.close(); } catch( Exception e ) { e.printStackTrace(); } finally{ try{stmt.close();}catch(Exception e){} } } }

Lauri, The problem is that its up to Informix on how they want to implement the interface with their drivers. Having said that, you could subclass and overwrite the methods thus allowing for any date format you want. (You should be able to super the method and use the Informix method from within the child class you created.) -HTH Mikey Lauri Pietarinen wrote: > Hello, > > We are currently using the JDBC-ODBC -bridge in our application. > We would like to convert to the Informix Type4 driver, but are > experiencing > following (NLS) problem: > > with ODBC-bridge we have to use the dateformat dd.mm.yyyy, but with > Informix JDBC we have to use the format yyyy-mm-dd. > > Our CLIENT_LOCALE/DB_LOCALE is fi_fi.8859-1 > > Documentation says that DBDATE can not be changed. > > Does anybody have any ideas how to overcome this problem? > > This accompanying Java-class demonstrates the problem. > > Thank you for your help, > > Lauri Pietarinen > AtBusiness Communications > > /** > * This class is provided to test JDBC-connections > * > */ > > package JDBCTest; > import java.sql.*; > public class JDBCTest { > > public JDBCTest() { > } > > public static void main(String[] args) { > JDBCTest tester = new JDBCTest(); > Connection conODBC = null; > Connection conIFMX = null; > try { > Class.forName("com.informix.jdbc.IfxDriver"); > Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); > > String urlODBC = "jdbc:odbc:TREE" ; > String urlIFMX = > "jdbc:informix-sqli://lauri2.in.atbusiness.com:1526/" + > > "lauri_db:INFORMIXSERVER=lauri2_ol;user=informix;password=informix"; > > conODBC = DriverManager.getConnection( urlODBC, "informix", > "informix" ); > conIFMX = DriverManager.getConnection( urlIFMX, "informix", > "informix" ); > > trySQL(conODBC); > trySQL(conIFMX); > > } > catch( Exception e ) { > e.printStackTrace(); > } > } > static void trySQL(Connection con) throws SQLException { > Statement stmt = null; > PreparedStatement stmt2 = null; > System.out.println("Using connection " + con); > try { > stmt2 = con.prepareStatement("insert into date_test > values(1,'1.5.1999')"); <<-- works with ODBC-bridge > //stmt2 = con.prepareStatement("insert into date_test > values(1,'1999-05-01')"); <<-- works with native JDBC > stmt2.execute(); > > stmt = con.createStatement(); > ResultSet rs = stmt.executeQuery("select d1 from date_test"); > while(rs.next()){ > System.out.println(rs.getDate("d1")); > } > rs.close(); > } > catch( Exception e ) { > e.printStackTrace(); > } > finally{ > try{stmt.close();}catch(Exception e){} > } > } > }

Mike, I did not quite understand what I have to do. Subclass which methods? Secondly, why am I getting these problems? Is it not defined in JDBC how dates should work or is somebody (ODBC?) being to lax? Lauri Mike Segel wrote: > Lauri, > The problem is that its up to Informix on how they want to implement > the interface with their drivers. Having said that, > you could subclass and overwrite the methods thus allowing for any > date format you want. (You should be able to super the method and use the > Informix method from within the child class you created.) > > -HTH > Mikey > >

Lauri Pietarinen wrote: > Mike, > > I did not quite understand what I have to do. Subclass which methods? > Secondly, why am I getting these problems? Is it not defined > in JDBC how dates should work or is somebody (ODBC?) being to lax? > > Lauri Lauri, With JDBC, Sun/Java has defined an interface for how a database is supposed to act. It is up to Informix/<Insert your favorite RDBMS here> to write the native java driver that implements the interface. That means that sometimes the features don't work or exist. Or that they may return a specific string that you have to either parse or make sure that it matches the local environment variables. Again its not a JDBC issue, but an Informix issue. They are the ones implementing the interface. You need to check their docks. Since I don't have my jdbc type 4 drivers yet. (Thanx to an Informix fsck-up), and I don't have the latest and greatest downloaded, I haven't been able to give you a complete example. Give me a couple of days, I'm in the middle of building a couple of new servers and I'm still trying to get a few other things organized. I'll try to give you an example. BTW, where did you get your Java training? -Mikey

Mike Segel wrote: > Lauri, > > and I'm still trying to get a few other things organized. I'll try to give you > an example. > Thank you very much for your help! > > BTW, where did you get your Java training? > Mainly self-studied with books, why do you ask? - Lauri