Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re: Writing It Myself, DWIM, and Expectations

by tilly (Archbishop)
on Sep 13, 2004 at 16:38 UTC ( #390600=note: print w/ replies, xml ) Need Help??


in reply to 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
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.
      Sybase (and presumably MS-SQL) will silently convert "" to " " as it can't store a zero-length string that is not NULL.

      Michael

        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

        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

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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://390600]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (2)
As of 2014-12-27 06:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (176 votes), past polls