DBD::ODBC and date/time field overflow 
Author Message
 DBD::ODBC and date/time field overflow

When I run the program below, I get an error.  But if I set
$placeholders=0, it will run correctly and I can print out the results of
the query.

The error is

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access 97
Driver]Datetime field overflow  (SQL-22008)(DBD: st_execute/SQLExecute
err=-1) at C:\PerlProg\hhe-query\try-q.pl line 29.

I've searched the Perl/DBI book, the DBI and DBD::ODBC docs, and several
Dejanews and web searches, but I can't find any information on what I'm
doing wrong.  I've found a few places where someone asked a similar
question, but I've yet to see a reply that had an answer.

If I've missed something I should have noticed, a pointer to the correct
docs would be appreciated.  I'm not sure what I'm doing wrong.

#!/usr/bin/perl -w
use strict;
use DBI qw(:sql_types);

my $placeholders=1;

my $statement = qq|select OPEN_DATE FROM combined
    WHERE (OPEN_DATE Between #1/1/1999# And #2/1/1999#)
    ORDER BY OPEN_DATE; |  ;

$statement = qq|select OPEN_DATE from combined
    where (OPEN_DATE between ? and ?)
    ORDER BY OPEN_DATE|
  if $placeholders;


my $dbh = DBI->connect('DBI:ODBC:HHElocal', '', '',
    {RaiseError=>1, LongReadLen=>1000} );
my $sth = $dbh->prepare( $statement )
    or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";

if ($placeholders) {

        $sth->bind_param($i+1, $query_parms[$i], SQL_DATE) or die $!;
        $sth->bind_param($i+1, $query_parms[$i], SQL_DATE) or die $!;
    }

Quote:
}

my $rv = $sth->execute()
    or die "Cannot execute SQL: ", $dbh->errstr(), "\n";

--
David Wall



Tue, 18 Feb 2003 08:32:41 GMT  
 DBD::ODBC and date/time field overflow

Quote:

>Driver]Datetime field overflow  (SQL-22008)(DBD: st_execute/SQLExecute
>err=-1) at C:\PerlProg\hhe-query\try-q.pl line 29.

Its an ODBC error, so you're best of checking the MSKB and search for that
error code, it turns up a few entries which might help.

Also, stupid question as this seems, why do your date formats have #'s around
them?

Col.

---
Colin Keith
Systems Administrator
Network Operations Team
ClaraNET (UK) Ltd. NOC



Thu, 20 Feb 2003 19:36:06 GMT  
 DBD::ODBC and date/time field overflow


Quote:


>>Driver]Datetime field overflow  (SQL-22008)(DBD: st_execute/SQLExecute
>>err=-1) at C:\PerlProg\hhe-query\try-q.pl line 29.

>Its an ODBC error, so you're best of checking the MSKB and search for
>that error code, it turns up a few entries which might help.

>Also, stupid question as this seems, why do your date formats have #'s
>around them?

Not a stupid question, but probably a stupid thing for me to do.  The
database I'm querying is in MS Access97 (not my fault <g>), and that's how
you denote a date in Access' version of SQL.  I hadn't yet found the page
in the Perl/DBI book that notes the proper date format (one of them) for
DBD::ODBC as {d 'YYYY-MM-DD'}.  Once I fixed that, the problem went away.

I posted a note Saturday (2 Sep 2000) saying that I'd found my mistake, but
for some reason my original post was missing from my news server, so I
couldn't do a proper reply to it.

I'm pretty pleased now.  My little web interface to this database has made
some people in my division pretty happy. Many of them had no need to use
Access except to query this database, and this saves them time and cursing
when they want to look up something.  I had fun writing it, too, even
though I had to corrupt my original design a bit to accomodate some of the
revision requests.  It's probably pretty amateurish, but it's flexible
enough that I can adapt it to fit future revisions or other databases with
minimal changes.  Most of it is configured from a single hash of hashes
describing the fields to be searched.  And to think I wrote it almost as a
lark, since my boss left me alone for a while and I sat down with some Perl
books and started playing....

--
David Wall



Sat, 22 Feb 2003 23:18:31 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. DBD::ODBC and date/time fields

2. DBD::ODBC, Microsoft Access Date/Time help

3. DATE/TIME problem with Microsoft Access and DBD::ODBC

4. DBD:ODBC and memo fields

5. DBD::ODBC DBI and MS Access memo field

6. DBD::ODBC DBI and Microsoft Access memo field

7. DBD/DBI ODBC and Microsoft Access 97 memo field

8. DBD::ODBC truncates Memo type Access field

9. ODBC Sql Date field

10. Win32::ODBC and Access Date & Boolean Fields

11. DBD::ORACLE and date fields

12. Using ORACLE DATE field with DBD-Oracle

 

 
Powered by phpBB® Forum Software