| Subject: | Passing dates to MS SQL via DBD::ODBC doesn't work under 5.8 |
In a script I have something like this:
$db = DBI->connect('dbi:ODBC:'.$DSN, $UID, $PWD,
{PrintError => 0, RaiseError => 1,LongReadLen => 65536});
...
$FetchPagesWithUpdatedTranslations
= $db->prepare('exec FetchPagesWithUpdatedTranslations ?, ?');
# product_id, time
...
my $res = $FetchPagesWithUpdatedTranslations->execute(
$prodId, SQLtime($FILES{$section}));
...
sub SQLtime {
my $time = shift() || 0;
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)
= localtime($time);
$year+=1900;
$mon++;
return "$year-$mon-$mday $hour:$min:$sec";
}
The stored procedure is like this:
CREATE PROCEDURE FetchPagesWithUpdatedTranslations (
@ProductId Int,
@Date datetime = NULL
) AS
...
This code works fine with Perl 5.6.1. (ActiveState build 631), but reports
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at ...
with Perl 5.8 (ActiveState build 804). Both running under Win2k server with MS SQL 2000.
I tried it with DBI 1.30 (from PPM) and DBI 1.32 (compiled by myself) and with DBD::ODBC 0.43 (from PPM), 1.02 and 1.04 (compiled by myself) and it reported this error every time under 5.8 and worked fine under 5.6.1.
I then tried to change the stored procedure to
CREATE PROCEDURE FetchPagesWithUpdatedTranslations (
@ProductId Int,
@DateS varchar(20) = NULL
) AS
...
and that worked with both 5.6.1 and 5.8. Do you have any idea what could be wrong? In this case I can change the stored procedure, but in the future this could be a problem.
I attach a test script. It creates a
CREATE PROCEDURE Test__DBD_ODBC_Date_Problem ( @date datetime) AS SELECT @date, @date+1
procedure and tries to call it.
I've tried to use
"{d '$year-$mon-$mday $hour:$min:$sec'}";
"{d '$year-$mon-$mday $hour:$min'}";
"{d '$year-$mon-$mday'}";
"$year-$mon-$mday $hour:$min:$sec"
as the date, and either go the
Invalid character value for cast specification
or
Can't rebind placeholder 1
no matter what.
Thanks, Jenda
use DBI;
$DSN = '';
$username = '';
$password = '';
$db = DBI->connect('dbi:ODBC:' . $DSN, $username, $password, {PrintError => 0,RaiseError => 1,LongReadLen => 65536});
$db->do('CREATE PROCEDURE Test__DBD_ODBC_Date_Problem ( @date datetime) AS SELECT @date, @date+1');
$exec = $db->prepare( q<exec Test__DBD_ODBC_Date_Problem ?>);
$time = time();
eval {
print "Test__DBD_ODBC_Date_Problem '" . SQLtime($time) . "'\n\t\t->\n";
my $res = $exec->execute( SQLtime($time));
if ($res != 0) {
my $rows = $exec->fetchall_arrayref();
foreach my $row (@$rows) {
print "Returned: $row->[0], $row->[1]\nIt's OK.\n";
}
} else {
print "FAILED!!!\n";
}
};
print STDERR $@ if $@;
$db->do('DROP PROCEDURE Test__DBD_ODBC_Date_Problem');
sub SQLtime {
my $time = shift() || 0;
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)
= localtime($time);
$year+=1900;
$mon++;
# return "{d '$year-$mon-$mday $hour:$min:$sec'}";
# return "{d '$year-$mon-$mday $hour:$min'}";
# return "{d '$year-$mon-$mday'}";
return "$year-$mon-$mday $hour:$min:$sec";
}
1;