DO NOT REPLY [Bug 29212] - Allow user to provide type information for input parameters

[bu...@apache.org]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://issues.apache.org/bugzilla/show_bug.cgi?id=29212>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

http://issues.apache.org/bugzilla/show_bug.cgi?id=29212

Allow user to provide type information for input parameters

dgraham@apache.org changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|                            |FIXED



------- Additional Comments From dgraham@apache.org  2004-08-23 01:54 -------
I've changed the null handling to specify Types.VARCHAR because it works with
every tested driver.  If it still doesn't work in your case, just override
fillStatement() to pass in the type for your driver.  Below is a message from
Ronald Dauster to the commons-user list.  Many thanks to Ronald for the testing!

Ronald Dauster 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-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org