You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Ronald Dauster <rp...@gido.de> on 2004/07/08 11:10:53 UTC
Re: [DBUtils] Setting NULL values [was: SQL Server null]
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: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [DBUtils] Setting NULL values [was: SQL Server null]
Posted by Ronald Dauster <rp...@ronald-dauster.de>.
David Graham wrote:
>Thanks for the testing! Which of the 4 columns did you try to insert null
>into?
>
All three simultaneously, but for Oracle I've also tried individual columns
>In my testing, Oracle worked with INTEGER but maybe it was a
>
>
The problem is with the DATE column, probably because there's no
conversion from INTEGER
to DATE. The error is ORA-00932 and it's the
PreparedStatement.executeUpdate that fails
(in contrast to Types.OTHER, where the PreparesStatement.setNull throws
an exception).
Types.INTEGER works for VARCHAR2 and NUMBER columns.
Driver and database are both version 9.2.0.1.0
>different driver version. It looks like we'll have to babysit Oracle and
>use VARCHAR. Please let us know the results of SQL Server and Oracle 10.
>
>David
>
>
Ronald
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [DBUtils] Setting NULL values [was: SQL Server null]
Posted by David Graham <gr...@yahoo.com>.
Thanks for the testing! Which of the 4 columns did you try to insert null
into? In my testing, Oracle worked with INTEGER but maybe it was a
different driver version. It looks like we'll have to babysit Oracle and
use VARCHAR. Please let us know the results of SQL Server and Oracle 10.
David
--- Ronald Dauster <rp...@gido.de> wrote:
> 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: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org