You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by David Graham <gr...@yahoo.com> on 2004/07/05 20:02:40 UTC
Re: [DBUtils] SQL Server null - Not implemented (type is java.sql.Types.OTHER)
It would be nice if every driver accepted null in setObject but I don't
think that's the case. If we could find a common way to set null
parameters in DB2, Oracle, SQL Server, MySQL, and Postgres we could
implement fillStatement to use it. Until then, the QueryRunner subclass
approach is the only solution.
David
--- "Rafael U. C. Afonso" <r....@uol.com.br> wrote:
> Hello:
>
> I had a problem like related by Henri
> Yandell (see
>
http://www.mail-archive.com/commons-dev@jakarta.apache.org/msg42819.html),
> but instead use Oracle I am using SQL
> Server. When I try insert a Null
> parameter in a Insert Query I get this
> message: "Not implemented (type is
> java.sql.Types.OTHER)". I am using
> jTds driver, but MS official driver
> has the same problem (But does not
> explain about Types.Other).
> I read original Source of jTds and I
> see that this message above is thrown
> from a method called
> createParameterMapping() from
> ParameterUtils class. I don't
> understand why they made this, but
> anyway it is a problem from driver,
> not form QueryRunner.
> My solution was create a QueryRunner
> subclass, where I overridden
> fillStatement() method like this:
>
> if (params == null) {
> return;
> }
>
> for(int i = 0; i <
> params.length; i++) {
> stmt.setObject(i + 1,
> params[i]);
> }
>
> I don't verify if current parameter is
> null or not. And this works.
> What do you think?
>
> Thanks,
>
> Rafael Ubiratam Clemente Afonso
> r.u.c.afonso@uol.com.br
> ---------------------------------
> Where is Debug?
> Debug is on the Table!
>
>
__________________________________________________________________________
> Acabe com aquelas janelinhas que pulam na sua tela.
> AntiPop-up UOL - � gr�tis!
> http://antipopup.uol.com.br/
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
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
Re: [DBUtils] SQL Server null - Not implemented (type is java.sql.Types.OTHER)
Posted by Ronald Dauster <rp...@ronald-dauster.de>.
David Graham wrote:
>Also, please check what happens if you just pass
>null into setObject() instead of using setNull().
>
>
Will do that.
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
Re: [DBUtils] Setting NULL values [was: SQL Server null]
Posted by Ronald Dauster <rp...@gido.de>.
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] SQL Server null - Not implemented (type is java.sql.Types.OTHER)
Posted by Ronald Dauster <rp...@gido.de>.
David Graham wrote:
>Also, please check what happens if you just pass
>null into setObject() instead of using setNull().
>
>
Will do that.
Ronald
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [DBUtils] SQL Server null - Not implemented (type is java.sql.Types.OTHER)
Posted by David Graham <gr...@yahoo.com>.
--- Ronald Dauster <rp...@ronald-dauster.de> wrote:
> David Graham wrote:
>
> >--- Ronald Dauster <rp...@ronald-dauster.de> wrote:
> >
> >
> >>[...]
> >>
> >>As an alternative to trying to guess the correct type, there could be
> >>typed null-values
> >>that can optionally be used by a client of QueryRunner. Something
> along
> >>
> >>the lines
> >>public class Nulls {
> >> public static final Nulls VARCHAR = new Nulls(Types.VARCHAR);
> >> ....
> >> public int getType() {...}
> >>}
> >>
> >>and in fillStatement
> >>if (params[i] instanceof Nulls) {
> >> setNull(i+1, ((Nulls) param[i]).getType());
> >>} else if (params[i] != null) {
> >> setObject
> >>} else {
> >> setNull
> >>}
> >>
> >>
> >
> >I'm not sure we need an enum Nulls class to wrap Types.* constants.
> IMO,
> >
> >
> The purpose of the enum is to be used as element of the params array
> which needs Objects and
> Types.VARCHAR ist an int. But I agree that it is a rather cumbersome to
> use and not exactly in
> the spirit of dbutils.
>
> >it would be more straightforward to have a QueryRunner.setNullType(int)
> >method where you could pass in Types.VARCHAR.
> QueryRunner.fillStatement()
> >would then use your type instead of Types.OTHER.
> >
> >
> >
> Good enough, if the dbutils client _knows_ which dataabase it is working
>
> with. Not so good, if the
> Datasource is externally configured.
>
> In addition, the class is documented to be thread-safe _and_ can be used
>
> with different connections.
> Adding state to the class with setNullType will create a race condition
>
> in the (admittely contrived)
> case where one instance of QueryRunner is used with Connections to
> databases that require
> different null types.
>
> >FYI, Oracle's driver doesn't work with Types.OTHER either but it
> accepted
> >every other type I tried (INTEGER, VARCHAR, etc).
> >
> >
> >
> I need to do a few portability tests against several DBMS anyway and
> could add that to my list
> (i. e. wich type works), but not before next week and definitely not
> agains DB2 or Postgres.
> If someone can fill the gaps, maybe we find a type that works on all
> major databases.
That would be great! Also, please check what happens if you just pass
null into setObject() instead of using setNull().
Thanks,
David
> >
> Ronald
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
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
Re: [DBUtils] SQL Server null - Not implemented (type is java.sql.Types.OTHER)
Posted by Ronald Dauster <rp...@ronald-dauster.de>.
David Graham wrote:
>--- Ronald Dauster <rp...@ronald-dauster.de> wrote:
>
>
>>[...]
>>
>>As an alternative to trying to guess the correct type, there could be
>>typed null-values
>>that can optionally be used by a client of QueryRunner. Something along
>>
>>the lines
>>public class Nulls {
>> public static final Nulls VARCHAR = new Nulls(Types.VARCHAR);
>> ....
>> public int getType() {...}
>>}
>>
>>and in fillStatement
>>if (params[i] instanceof Nulls) {
>> setNull(i+1, ((Nulls) param[i]).getType());
>>} else if (params[i] != null) {
>> setObject
>>} else {
>> setNull
>>}
>>
>>
>
>I'm not sure we need an enum Nulls class to wrap Types.* constants. IMO,
>
>
The purpose of the enum is to be used as element of the params array
which needs Objects and
Types.VARCHAR ist an int. But I agree that it is a rather cumbersome to
use and not exactly in
the spirit of dbutils.
>it would be more straightforward to have a QueryRunner.setNullType(int)
>method where you could pass in Types.VARCHAR. QueryRunner.fillStatement()
>would then use your type instead of Types.OTHER.
>
>
>
Good enough, if the dbutils client _knows_ which dataabase it is working
with. Not so good, if the
Datasource is externally configured.
In addition, the class is documented to be thread-safe _and_ can be used
with different connections.
Adding state to the class with setNullType will create a race condition
in the (admittely contrived)
case where one instance of QueryRunner is used with Connections to
databases that require
different null types.
>FYI, Oracle's driver doesn't work with Types.OTHER either but it accepted
>every other type I tried (INTEGER, VARCHAR, etc).
>
>
>
I need to do a few portability tests against several DBMS anyway and
could add that to my list
(i. e. wich type works), but not before next week and definitely not
agains DB2 or Postgres.
If someone can fill the gaps, maybe we find a type that works on all
major databases.
>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] SQL Server null - Not implemented (type is java.sql.Types.OTHER)
Posted by David Graham <gr...@yahoo.com>.
--- Ronald Dauster <rp...@ronald-dauster.de> wrote:
> I think every driver will accept "stmt.setNull" given the correct type
> as second argument.
> Based on this assumption, I see three possible ways to go:
>
> In theory (i. e. with jdk1.4 and conforming drivers)
> stmt.setNull(i + 1, stmt.getParameterMetaData().getParameterType(i +
> 1));
> should provide the correct type for the null value.
>
> In practise, any type might be better than Types.OTHER. I can imagine
> two approaches
> used by the driver:
> - ignore the type, then any typecode is as good as Types.OTHER
> - verify the typecode: then is certainly has to be a type supported by
> the database
> and (not knowing the correct type) ideally one that can be converted
> to the
> columns real type. In this case, the best choice will probably be
> Types.VARCHAR.
>
> As an alternative to trying to guess the correct type, there could be
> typed null-values
> that can optionally be used by a client of QueryRunner. Something along
>
> the lines
> public class Nulls {
> public static final Nulls VARCHAR = new Nulls(Types.VARCHAR);
> ....
> public int getType() {...}
> }
>
> and in fillStatement
> if (params[i] instanceof Nulls) {
> setNull(i+1, ((Nulls) param[i]).getType());
> } else if (params[i] != null) {
> setObject
> } else {
> setNull
> }
I'm not sure we need an enum Nulls class to wrap Types.* constants. IMO,
it would be more straightforward to have a QueryRunner.setNullType(int)
method where you could pass in Types.VARCHAR. QueryRunner.fillStatement()
would then use your type instead of Types.OTHER.
FYI, Oracle's driver doesn't work with Types.OTHER either but it accepted
every other type I tried (INTEGER, VARCHAR, etc).
David
>
> Ronald
>
> >It would be nice if every driver accepted null in setObject but I don't
> >think that's the case. If we could find a common way to set null
> >parameters in DB2, Oracle, SQL Server, MySQL, and Postgres we could
> >implement fillStatement to use it. Until then, the QueryRunner
> subclass
> >approach is the only solution.
> >
> >David
> >
> >[...]
> >
> >>My solution was create a QueryRunner
> >>subclass, where I overridden
> >>fillStatement() method like this:
> >>
> >> if (params == null) {
> >> return;
> >> }
> >>
> >> for(int i = 0; i <
> >>params.length; i++) {
> >> stmt.setObject(i + 1,
> >>params[i]);
> >> }
> >>
> >>[...]
> >>
> >> <>Thanks,
> >>
> >> Rafael Ubiratam Clemente Afonso
> >> r.u.c.afonso@uol.com.br
> >>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
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
Re: [DBUtils] SQL Server null - Not implemented (type is java.sql.Types.OTHER)
Posted by Ronald Dauster <rp...@ronald-dauster.de>.
I think every driver will accept "stmt.setNull" given the correct type
as second argument.
Based on this assumption, I see three possible ways to go:
In theory (i. e. with jdk1.4 and conforming drivers)
stmt.setNull(i + 1, stmt.getParameterMetaData().getParameterType(i + 1));
should provide the correct type for the null value.
In practise, any type might be better than Types.OTHER. I can imagine
two approaches
used by the driver:
- ignore the type, then any typecode is as good as Types.OTHER
- verify the typecode: then is certainly has to be a type supported by
the database
and (not knowing the correct type) ideally one that can be converted
to the
columns real type. In this case, the best choice will probably be
Types.VARCHAR.
As an alternative to trying to guess the correct type, there could be
typed null-values
that can optionally be used by a client of QueryRunner. Something along
the lines
public class Nulls {
public static final Nulls VARCHAR = new Nulls(Types.VARCHAR);
....
public int getType() {...}
}
and in fillStatement
if (params[i] instanceof Nulls) {
setNull(i+1, ((Nulls) param[i]).getType());
} else if (params[i] != null) {
setObject
} else {
setNull
}
Ronald
>It would be nice if every driver accepted null in setObject but I don't
>think that's the case. If we could find a common way to set null
>parameters in DB2, Oracle, SQL Server, MySQL, and Postgres we could
>implement fillStatement to use it. Until then, the QueryRunner subclass
>approach is the only solution.
>
>David
>
>[...]
>
>>My solution was create a QueryRunner
>>subclass, where I overridden
>>fillStatement() method like this:
>>
>> if (params == null) {
>> return;
>> }
>>
>> for(int i = 0; i <
>>params.length; i++) {
>> stmt.setObject(i + 1,
>>params[i]);
>> }
>>
>>[...]
>>
>> <>Thanks,
>>
>> Rafael Ubiratam Clemente Afonso
>> r.u.c.afonso@uol.com.br
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org