pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: What is the difference between NULL and "undef"
3+ messages / 1 participants
[nested] [flat]

* Re: What is the difference between NULL and "undef"
@ 2000-05-16 17:18 Michael A. Mayo <mmayo@mcauleybrooklyn.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Michael A. Mayo @ 2000-05-16 17:18 UTC (permalink / raw)
  To: Rudolph, Michael <Michael.Rudolph@telekom.de>; 'Alessio Bragadini' <alessio@albourne.com>; +Cc: pgsql-sql

---- Original Message -----
From: "Rudolph, Michael" <Michael.Rudolph@telekom.de>
> I do that statement from Perl via DBI without placeholders.
> According to the DBI-Documentation, it should be possible:
> "Undefined values or undef can be used to indicate null values",
> this sentence is an excerpt from the doc. My application reads in
> input text-fields from a web-formular, so do I always have to
> do a check, if the field is empty and than put it explicit to NULL?

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: What is the difference between NULL and "undef"
@ 2000-05-18 18:32 ` Michael A. Mayo <mmayo@mcauleybrooklyn.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Michael A. Mayo @ 2000-05-18 18:32 UTC (permalink / raw)
  To: Rudolph, Michael <Michael.Rudolph@telekom.de>; 'Alessio Bragadini' <alessio@albourne.com>; +Cc: pgsql-sql

----- Original Message -----
From: "Rudolph, Michael" <Michael.Rudolph@telekom.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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: What is the difference between NULL and "undef"
@ 2000-05-18 21:09 ` Michael A. Mayo <mmayo@mcauleybrooklyn.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Michael A. Mayo @ 2000-05-18 21:09 UTC (permalink / raw)
  To: Rudolph, Michael <Michael.Rudolph@telekom.de>; 'Alessio Bragadini' <alessio@albourne.com>; +Cc: pgsql-sql

----- 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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-05-18 21:09 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-16 17:18 Re: What is the difference between NULL and "undef" Michael A. Mayo <mmayo@mcauleybrooklyn.org>
2000-05-18 18:32 ` Michael A. Mayo <mmayo@mcauleybrooklyn.org>
2000-05-18 21:09 ` Michael A. Mayo <mmayo@mcauleybrooklyn.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox