Douglas Wilson wrote:
> From: "Ron MacNeil" <[EMAIL PROTECTED]>
> > It is interesting however that insert #9 below will actually insert a
> > NULL value into the table, I had always assumed it would be the empty
> > string.
>
> > let scratch = ""
> > insert into my_table values(scratch); #9
>
> I think I vaguely remember this (about 8 years of Informix,
> but Oracle lately). If you have all your insert values in an array,
> then you can just do something like this:
> for (@insert_values) {
> next unless defined;
> $_ = undef if $_ eq '';
> }
>
> $sth->execute(@insert_values);
>
> You could even make an insert_cleanup function or something
> that loops over @_ instead of something hard coded.
> Hope someone thinks of something better...
You could also experiment with map.
You guys have pretty much got it all figured out. With regard to Number 9;
arguably it is a bug in I4GL, but it is one that is so deep-seated that it
would break practically everything if it were ever changed. The SQL
standard says that if you do INSERT INTO my_table VALUES(''), then the
value inserted is not null - hence it is treated as a single blank. But
I4GL is dealing with program variables and the behaviour that LET scratch =
"" is equivalent to LET scratch = NULL is setting the variable in the
program, not fiddling the database. String concatenation with the comma
operator treats nulls differently than the double-pipe operator, too.
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED])
Guardian of DBD::Informix 1.00.PC2 -- see http://dbi.perl.org/
#include <disclaimer.h>
