Re: Writing It Myself, DWIM, and Expectations

Now, I know there's a difference between "" and undef in Perl, just like I know there's a difference between "" and NULL in my database.

You do?

Not if your database follows the SQL-92 standard!

Seriously, databases are not supposed to have the idea of an empty string. I learned this from mpeppler when he explained to me why DBD::Sybase was converting empty strings into spaces. Of course to confuse the issue, some databases (eg MySQL IIRC) do allow empty strings.

Comment on Re: Writing It Myself, DWIM, and Expectations

Replies are listed 'Best First'.
Re^2: Writing It Myself, DWIM, and Expectations by iburrell (Chaplain) on Sep 13, 2004 at 17:03 UTC
The SQL-92 standard makes a distinction between empty string and NULL. It is Oracle that considers empty string and NULL to be equivalent. My impression is that Sybase and old versions of SQL Server behave the same way.	[reply]
Re^3: Writing It Myself, DWIM, and Expectations by mpeppler (Vicar) on Sep 14, 2004 at 05:55 UTC
Sybase (and presumably MS-SQL) will silently convert "" to " " as it can't store a zero-length string that is not NULL. Michael	[reply]
Re^4: Writing It Myself, DWIM, and Expectations by Jenda (Abbot) on Sep 14, 2004 at 23:12 UTC
I can't check it at this moment, but I don't think any recent MS SQL (that is 7.0 and up) does this for varchars. I'm sure I would notice. It does change an empty string to a string containing the right number of spaces for char(N) columns, but there it can't do anything better. If I still remember this node tomorrow when I am at work and can try things I'll test it and post an update. Update: Yep, I was right: `create table foo (svarchar varchar(10), schar char(10)) insert into foo (svarchar, schar) values ('', '') insert into foo (schar) values ('') select '>' + svarchar + '<', '>' + schar + '<' from foo` [download] Jenda Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Rick Osborne	[reply] [d/l]
Re^5: Writing It Myself, DWIM, and Expectations by Anonymous Monk on Oct 09, 2004 at 07:28 UTC
Re^2: Writing It Myself, DWIM, and Expectations by jest (Pilgrim) on Sep 13, 2004 at 17:38 UTC
> Now, I know there's a difference between "" and undef in Perl, just like I know there's a difference between "" and NULL in my database. You do? Not if your database follows the SQL-92 standard! Leaving aside the issue of what the SQL-92 standard actually says, and also leaving aside the holy war about whether MySQL is a real database or not, the fact remains that my database, which is MySQL, does treat "" and NULL as different, so my above statement is correct. In the meantime, I've decided to use a different input validator, which is working much better for me.	[reply]


Clear questions and runnable code get the best and fastest answer
	PerlMonks