Question about handling of string of length 0 in PreparedStatement.setString()
Hi, I'm wondering if someone has run into this and can
help me understand what's happening.
I'm porting some JDBC code from Another Database to Derby;
I'm using Derby 10.1.1.0 on RedHat Linux.
My program contains a snippet of code something like:
PreparedStatement stmt = conn.prepareStatement(
insert into my_table (a, b) values (?, ?));
stmt.setString(1, 1);
stmt.setString(2, );
stmt.executeUpdate();
Now, it so happens that the second column ('b', above)
is of type INTEGER, and nulls are allowed.
When I run this program in Another Database, what happens
is that the row is inserted, and the value of column 'b'
in the row is set to NULL.
When I run this program in Derby, what happens is that I
get an exception:
org.apache.derby.client.am.SqlException: Invalid character
string format for type INTEGER.
I tried poking around in the JDBC documentation to see what
it said about type conversions, but I got lost, so I thought
I'd ask the Derby list and see what people thought about
this particular behavior.
That is: is it valid for Derby to reject my insert? Or should
it have converted the value to NULL, like Another Database did?
thanks,
bryan
Re: Question about handling of string of length 0 in PreparedStatement.setString()
Hello,
Not sure I can assist with more than my 2 cents. Since a blank
string is different than the absence of value (null), I would argue that
Derby is doing the correct thing, while the other DBMS is guessing at
the user's intentions. The root cause of this specific message is that
internally Derby is using Integer.valueOf which does not recognize as
a valid number.
Thanks;
-- Matt Sanford
[EMAIL PROTECTED] wrote:
Hi, I'm wondering if someone has run into this and can
help me understand what's happening.
I'm porting some JDBC code from Another Database to Derby;
I'm using Derby 10.1.1.0 on RedHat Linux.
My program contains a snippet of code something like:
PreparedStatement stmt = conn.prepareStatement(
insert into my_table (a, b) values (?, ?));
stmt.setString(1, 1);
stmt.setString(2, );
stmt.executeUpdate();
Now, it so happens that the second column ('b', above)
is of type INTEGER, and nulls are allowed.
When I run this program in Another Database, what happens
is that the row is inserted, and the value of column 'b'
in the row is set to NULL.
When I run this program in Derby, what happens is that I
get an exception:
org.apache.derby.client.am.SqlException: Invalid character
string format for type INTEGER.
I tried poking around in the JDBC documentation to see what
it said about type conversions, but I got lost, so I thought
I'd ask the Derby list and see what people thought about
this particular behavior.
That is: is it valid for Derby to reject my insert? Or should
it have converted the value to NULL, like Another Database did?
thanks,
bryan
--
When the going gets tough, the weird turn pro.
-- Hunter S. Thompson
Re: Question about handling of string of length 0 in PreparedStatement.setString()
Hi Bryan,
Nice to see you on the list. It may be that the database which would
remain nameless is Oracle. Oracle treats 0-length strings as null. Note
that Oracle documentation confesses that this is a bad idea and darkly
hints that future versions of Oracle may conform to the standard
practice, which is to distinguish nulls from empty strings.
Derby follows the ANSI standard practice: if you insert a 0-length
string into a column, you'll get a 0-length string when you select from
the column. Similarly, if you insert a null into a column, you'll get a
null when you select from the column. The two are not confused. The
empty string does not represent a number any more than wibble
does. Derby is correctly objecting that your string cannot be coerced
into a number.
Good luck with your port!
Cheers,
-Rick
Bryan Pendleton wrote:
Hi, I'm wondering if someone has run into this and can
help me understand what's happening.
I'm porting some JDBC code from Another Database to Derby;
I'm using Derby 10.1.1.0 on RedHat Linux.
My program contains a snippet of code something like:
PreparedStatement stmt = conn.prepareStatement(
insert into my_table (a, b) values (?, ?));
stmt.setString(1, 1);
stmt.setString(2, );
stmt.executeUpdate();
Now, it so happens that the second column ('b', above)
is of type INTEGER, and nulls are allowed.
When I run this program in Another Database, what happens
is that the row is inserted, and the value of column 'b'
in the row is set to NULL.
When I run this program in Derby, what happens is that I
get an exception:
org.apache.derby.client.am.SqlException: Invalid character
string format for type INTEGER.
I tried poking around in the JDBC documentation to see what
it said about type conversions, but I got lost, so I thought
I'd ask the Derby list and see what people thought about
this particular behavior.
That is: is it valid for Derby to reject my insert? Or should
it have converted the value to NULL, like Another Database did?
thanks,
bryan
