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
