Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Datetime overflow with DBI ODBC setting 19th century dates with placeholders

Reply
Thread Tools

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

 
 
iain
Guest
Posts: n/a
 
      05-13-2004
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-2200(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;trus ted_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




 
Reply With Quote
 
 
 
 
James Willmore
Guest
Posts: n/a
 
      05-14-2004
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;trus ted_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?)


 
Reply With Quote
 
 
 
 
iain
Guest
Posts: n/a
 
      05-14-2004

"James Willmore" <(E-Mail Removed)> wrote in message
news(E-Mail Removed) a.net...
> > $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
>


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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bug in DBI::ODBC: DateTime treated as Date David Bennett Ruby 0 01-10-2009 12:34 PM
dbi ODBC problem executing prepared statement taking datetime type niall.macpherson@ntlworld.com Perl Misc 0 04-27-2006 03:23 PM
Dates dates dates dates... SQL and ASP.NET David Lozzi ASP .Net 1 09-30-2005 02:18 PM
DBD::Sybase / DBD::ODBC + FreeTDS woes - placeholders and implicit datatype conversions Richard Gration Perl Misc 1 07-07-2005 11:41 AM
Dates! Dates! Dates! PW ASP General 4 08-09-2004 04:42 PM



Advertisments