db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Question about handling of string of length 0 in PreparedStatement.setString()
Date Tue, 23 Aug 2005 23:35:29 GMT
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
>
>


Mime
View raw message