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

* AW: What is the difference between NULL and "undef"
@ 2000-05-17 06:11  Rudolph, Michael <Michael.Rudolph@telekom.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Rudolph, Michael @ 2000-05-17 06:11 UTC (permalink / raw)
  To: 'Michael A. Mayo' <mmayo@mcauleybrooklyn.org>; 'Alessio Bragadini' <alessio@albourne.com>; +Cc: pgsql-sql

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





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

* AW: What is the difference between NULL and "undef"
@ 2000-05-19 08:34  Rudolph, Michael <Michael.Rudolph@telekom.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Rudolph, Michael @ 2000-05-19 08:34 UTC (permalink / raw)
  To: 'Michael A. Mayo' <mmayo@mcauleybrooklyn.org>; +Cc: pgsql-sql

Thank you very much for your help. Unfortunately you had only nearly 
the same solution as I had, so I have to accept it cannot be handled 
directly in SQL. 

Thank you too for your tip with the Perl DBI-book. I've already heard 
of this book and tried to get it, but it is not yet available here in 
germany. My application works now so far (a bit slow, but it works)
and I have learned by writing the program and receiving mails a lot
of programming SQL-applications.

Thanks again

Michael

-----Ursprüngliche Nachricht-----
Von: Michael A. Mayo [mailto:mmayo@mcauleybrooklyn.org]
Gesendet am: Donnerstag, 18. Mai 2000 23:10
An: Rudolph, Michael; 'Alessio Bragadini'
Cc: pgsql-sql@postgresql.org
Betreff: Re: [SQL] What is the difference between NULL and "undef"

----- 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] 2+ messages in thread


end of thread, other threads:[~2000-05-19 08:34 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-17 06:11 AW: What is the difference between NULL and "undef" Rudolph, Michael <Michael.Rudolph@telekom.de>
2000-05-19 08:34 AW: What is the difference between NULL and "undef" Rudolph, Michael <Michael.Rudolph@telekom.de>

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