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

Discussion in 'Perl Misc' started by iain, May 13, 2004.

  1. iain

    iain Guest

    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
     
    iain, May 13, 2004
    #1
    1. Advertising

  2. 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?)
     
    James Willmore, May 14, 2004
    #2
    1. Advertising

  3. iain

    iain Guest

    "James Willmore" <> wrote in message
    news:p...
    > > $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
     
    iain, May 14, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Lozzi

    Dates dates dates dates... SQL and ASP.NET

    David Lozzi, Sep 29, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    881
    Rob Schieber
    Sep 30, 2005
  2. PW

    Dates! Dates! Dates!

    PW, Aug 7, 2004, in forum: ASP General
    Replies:
    4
    Views:
    209
    Mark Schupp
    Aug 9, 2004
  3. David Bennett
    Replies:
    0
    Views:
    128
    David Bennett
    Jan 10, 2009
  4. Richard Gration
    Replies:
    1
    Views:
    154
    Richard Gration
    Jul 7, 2005
  5. Replies:
    0
    Views:
    203
Loading...

Share This Page