Hi Bryan,
I think that other database description you mention fits mysql. It does not report errors on datatype mismatches. Instead it makes a closest guess conversion and sticks the value there. According to their docs, it does these data conversions to the closest valid value because it had no transaction support until recently. If they started writing down values in columns and wrote the 3rd column and had a data type mismatch on the 4th, they had no way to rollback what they had already written. So they designed it this way.
Derby does report the datatype mismatch errors correctly. In this case empty string "" is not a valid integer. This actually makes life much easier because you can test your application and your database easily and know that it will work correctly when the time comes. If these errors are not reported, it is very hard to check all these details at the app level and have confidence in future runtime correctness. This is not only about NULLs but also about dates, string truncations, numeric values etc. In some other database you could insert an invalid date and end up with 0000-00-00 and never know about it.
Also, keep in mind, NULLs are different compared to other values such that we use "COL1 is NULL" versus "COL1=5". Where all columns are NULL, "COL1=COL2" would evaluate to false where as "COL1 is NULL and COL2 is NULL" would be true.
Regards,
Ali
Matt Sanford <[EMAIL PROTECTED]> wrote:
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) val ues (?, ?)");
> 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
