Datetime overflow with DBI ODBC setting 19th century dates with placeholders

[iain]

I'm trying to update 19th century dates in SQL Server.

It works fine with dates on or after 1 Jan 1900, but not before. It is also
OK if the 19th century dates are included as literals in the SQL INSERT or
UPDATE command (with $dbh->do or prepare/execute)

Error message: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server
Driver]Datetime field overflow (SQL-22008)(DBD: st_execute/SQLExecute
err=-1) at Pat_DOB_error_report.pl line 13.

I've tried all kinds of options like bind_param, SQL_DATE, CAST(? as
datetime). It seems to be interpreted as a date, but the actual year is not
being processed correctly.

I'm not sure if this is a SQL Server or DBD::ODBC issue. I am using recent
versions of everything.

Any ideas for correction or workaround?

Example, using a table called Patients, which has a field called DOB, type
datetime.....

use DBI;
use strict;
my $dbserver="Myserver"; my $dbdatabase="MyDB"; my $dbWinAuth=1;
my $dsn="driver={SQL
Server};SERVER=$dbserver;DATABASE=$dbdatabase;trusted_connection=yes";

my $dbh = DBI->connect("dbi:ODBC:$dsn") or die "cannot connect to database:
$DBI::errstr \n";
$dbh->{AutoCommit}=1;

my $sth = $dbh->prepare("update Patients set DOB=? where PatientID=10");

$sth->execute('1900-01-01'); # works OK
$sth->execute('1799-12-31'); # gives error

 

[James Willmore]

On Thu, 13 May 2004 18:00:20 +0100, iain wrote:

[ ... ]

Any ideas for correction or workaround?

Example, using a table called Patients, which has a field called DOB,
type datetime.....

use DBI;
use strict;
my $dbserver="Myserver"; my $dbdatabase="MyDB"; my $dbWinAuth=1; my
$dsn="driver={SQL
Server};SERVER=$dbserver;DATABASE=$dbdatabase;trusted_connection=yes";

my $dbh = DBI->connect("dbi:ODBC:$dsn") or die "cannot connect to
database: $DBI::errstr \n";
$dbh->{AutoCommit}=1;

my $sth = $dbh->prepare("update Patients set DOB=? where PatientID=10");

$sth->execute('1900-01-01'); # works OK $sth->execute('1799-12-31'); #
gives error

First ... try to insert the date using the command line utility for the
RDBMS. If it works, then it *should* work in the script. If it doesn't,
then the issue lies with the RDBMS.

Next, you could use the 'debug' function in the DBI module to see what the
"conversation" is between the script and the database. That will shed
some light on why it's not working as expected.

There are more ideas, but give these a try first

HTH

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
"They make a desert and call it peace." -- Tacitus (55?-120?)

 

[iain]

First ... try to insert the date using the command line utility for the
RDBMS. If it works, then it *should* work in the script. If it doesn't,
then the issue lies with the RDBMS.

Next, you could use the 'debug' function in the DBI module to see what the
"conversation" is between the script and the database. That will shed
some light on why it's not working as expected.

There are more ideas, but give these a try first

HTH

Thanks for the suggestion, but I've tried both those - $dbh->trace(5) shows
the date is passed to SQL Server ODBC driver in the same way for both 1901
and 1799.
BTW - I meant to show 1899 not 1799 in my original post - neither work
anyway.
The command line utility (SQL Query) only allows you to use complete SQL DML
statements, not placeholders with parameters (as far as I know). And the
former works fine for 1899 with perl DBI anyway.

It seems to be something to do with ODBC not binding the date parameter
correctly when it would be held as a negative number on the database.

I've also tried the SQL Server profiler on the server side, but haven't got
it to show sufficient detail for
this kind of update.

Iain