I'm getting conversion errors (VARCHAR to [SMALL]DATETIME) on an
insert. I have a structure that contains a list of params that need to be
inserted into a db table. Prior to insertion, I make sure that undef
values are treated as SQL NULLs:
for (my $i = 0; $i <= $#array; $i++)
{
if ((!DBI::looks_like_number($array[$i]) && (!$array[$i]))
{
$array[$i] = $dbh->quote($array[$i];
}
}
Then I go through and use bind_param on all the array members, specifying
each params SQL data type:
$sth->bind_param(1, \$array[0], SQL_DATE);
....
I have 4 date values that are inserted into the db... the corresponding db
types are SMALLDATETIME, DATETIME, DATETIME, DATETIME. The values I'm
inserting are (NULL, NULL, 20010429 14:01:35, 20010429 14:10:22). I can
see where NULL may get misinterpreted, but why would the last two values
be misinterpreted as VARCHAR... in fact, it was my understanding that
specifying SQL_DATE would automatically format these values properly.
Prior to doing this, I used a stored proc to insert values like
NULL... the sp was able to tell that NULL meant SQL_NULL and not
"NULL". However, Sybase won't let me bind values to a stored proc call
(those significantly slowing the application since I have to prepare each
time in the loop) and the version of Perl I am using will not let me pass
undef to an SQL statement w/o causing a SEGV signal and a core dump. I
cannot change the Perl setup I have so I have to code around them.
Thanks,
Curt Crandall
