As promised, I have tested different strategies for passing null values to different databases. Although I still have MS SQL Sever and Oracle 10 on my list, I can already offser some results:
I have testet 6 different strategies: 1. PreparedStatement.setObject(col, null) (as proposed in this thread) 2. setNull(col, Types.VARCHAR) 3. setNull(col, Types.INTEGER); 4. setNull(col, Types.OTHER) (the current strategy of dbutils) 5. setNull(col, Types.NULL) 6. setNull(col, exact type) (where the type has been determined by performing a query and using the ResultSetMetaData)
I ran the tests agains the following databases
Firebird 1.5/firebirdsql 1.5RC3 Oracle 9/ Thin driver MySQL 4.0/Msql Connecttor 3.0 and mm.mysql 2.0.4 MaxDB 7.5 HSQLDB 1.7.1 MS Access/ODBC Brdge
and got the following results: MySQL, MaxDB, Firebird and HSQLDB work with every strategy Access fails with 1, 4, and 5 Oracle fails with 1, 3, 4, 5, and, surprisingly, 6
Strategry 2 (Types.VARCHAR) is the only one that worked in all cases.
The test consists of an insert of one row into a table with four columns - a varchar used as key (receiving a non-null value) - a small varchar/varchar2 in Oracle - an integer/unconstraint number in Oracle - a timestamp/date in Oracle
The Testprogram, scripts and complete results are available upon request.
Ronald
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
