Re: How to best handle zero length String ("") inserts between different DBMSes - some complain it is null - lee - com.mysql.lists.java

5 messages in com.mysql.lists.javaRe: How to best handle zero length St...

From	Sent On	Attachments
Ralf Hauser	26 Mar 2005 02:07
Ralf Hauser	26 Mar 2005 02:56
lee	26 Mar 2005 11:33
Mark Matthews	27 Mar 2005 07:10
Ralf Hauser	29 Mar 2005 00:57

Subject:	Re: How to best handle zero length String ("") inserts between different DBMSes - some complain it is null
From:	lee (le...@firstsql.com)
Date:	03/26/2005 11:33:37 AM
List:	com.mysql.lists.java

Hi Ralf,

Just ported an application that worked nicely under mysql also to oracle and found an interesting difference: - mysql considers "" as a non-null string - oracle complains that an attempt to insert a null string was made with "".

Is one of the two correct or is that just something that has to be coped with on the application level since that standards do not define this?

I don't know why you say this. The SQL Standard does not consider an empty string as a database NULL. Please 'quote' the portion of the SQL Standard that says anything different. MySQL is correct and follows the standard. Oracle (and SQL Server) are non-standard in considering an empty string as a database NULL. This is an artifact of their physical implementation.

However, using double quote (") for literals is non-standard. The SQL Standard uses " to quote names; it only uses a single quote (') for literals.