Re: What is the difference between NULL and "undef"

Ok, I see now, what mistakes in thinking I made. Thanks to all who
informed me. But my original problem is not really solved, I just
made a workaround and I'm not sure, if there is a real solution:
I read in a date from an input-field of an formular. I now have to
check, if this input-variable is empty. If it is, I set it to NULL,
if not, I take it as input.

My program looks now in parts like this:

if ($var3 == 0){ $var3 = NULL } # Testing if empty
else {$var3 = "'$var3'";} # input-field is not empty

$rc = $dbh->do("INSERT INTO test1 (var1,var2,var3)
VALUES ('$var1','$var2',$var3)");

with var3 being my date-variable. I have to juggle with the quotes
to bring in the right value. If I wouldn't do the "'$var3'" in line 2,
there were a parser error of Postgres. If I set the variable $var3
in line 5 in quotes, NULL wouldn't be interpreted in the right way.
That solution works now, but it seems to me a bit fussy. And it is
a lot of testing and transforming, when there are a lot of date-
variables in the program.

Michael

>I believe you are confused about the meaning of "undef." In general,
>variables are classified as undefined in one of 2 ways:
>1) The variable is not declared, or is declared but no value is ever
>assigned to it
>2) The variable is assigned the return value of some function, and the
>function fails, returning undef.
>
>A valid value is not "undef." Things like empty string or the number 0 are
>valid values for a variable to have, and therefore are not "undef." Undef
>should be interpreted as "something is catastrophically wrong with this
>variable."
>
>CGI.pm returns an empty string for text form fields that are not filled out.
>Therefore, it is neccecary to test for the empty string and translate that
>to
>NULL or undef if you want an empty form field to work out to NULL.
>
>----------------------------------------------------------------------------
>On my system, the following mini-program inserts a NULL value:
>my $test_string; #note: no value assigned to test_string - it's undefined
>my $database = DBI->connect("dbi:Pg:dbname=test");
>
>$test_string = $database->quote($test_string);
>$database->do("
> INSERT INTO employees(name)
> VALUES($test_string)
>");

----------------------------------------------------------------------------
The following mini-program inserts an empty string:
my $test_string = ""; #empty string assigned to test_string
my $database = DBI->connect("dbi:Pg:dbname=test");

$test_string = $database->quote($test_string);
$sql_statement = $database->do("
INSERT INTO employees(name)
VALUES($test_string)
");
-Mike

----- Original Message -----
From: "Rudolph, Michael" <Michael(dot)Rudolph(at)telekom(dot)de>
> But my original problem is not really solved
> I read in a date from an input-field of an formular. I now have to
> check, if this input-variable is empty. If it is, I set it to NULL,
> if not, I take it as input.

Unfortunately, there is no way to avoid testing for the empty string and
assigning it to NULL or undef. It would be annoying if the empty string
automatically translated to NULL, because it would be difficult to insert an
empty string into a database.

> My program looks now in parts like this:
> if ($var3 == 0){ $var3 = NULL } # Testing if empty
> else {$var3 = "'$var3'";} # input-field is not empty
> $rc = $dbh->do("INSERT INTO test1 (var1,var2,var3)
> VALUES ('$var1','$var2',$var3)");

You can also do this once for all the form variables instead of doing it
separately for each:
@fields = param();
foreach $current_field (@fields) {
if( param($current_field) eq "" ) {
param_fetch($current_field)->[0] = undef;
}
}

This assumes you are using CGI.pm, but a similar method should be possible
with the hash returned by ReadParse in cgi-lib or cgi_lite. As always,
there's more than one way to do it. =)

-Mike

----- Original Message ----- >
> If I set the variable $var3
> in line 5 in quotes, NULL wouldn't be interpreted in the right way.
> That solution works now, but it seems to me a bit fussy. And it is
> a lot of testing and transforming, when there are a lot of date-
> variables in the program.

Oops...I missed this last part. If you don't want to have to do the quoting
yourself, use the quote() method and make any empty strings undef instead
NULL.

$database = connect();
$name = $database->quote($var);
$database->do("insert into employees(name) values $var");

if $var is undef, it inserts null, if not it properly quotes the string,
including escaping any nasty characters it might include like the
apostrophe.

In the future, you might want to post questions like this to the dbi-users
list; you are more likely to get help there. Also, you may want to take a
look at the book "Programming the Perl DBI," or, if you are more harcore,
try the command "perldoc DBI." This stuff is pretty well documented in
both those resources.

-Mike