RE: Syntax error in INSERT / UPDATE statements

 Dear all,

Here is the code that I used to test ODBC operation. The commented portion is the 
source of error. The 1st statement can be execute without error but the 2nd always has 
error. When I modify the 1st statement to fill-up the 3 fields, the same error 
occurred.

This script work on a table call "RealTime" which consists of 3 fields:

Index (Numeric)
timestamp (datetime)
Data (Numeric)

Version of DBI::ODBC is 0.28 
ActiveState perl build 633 ver. 5.6.1
MDAC ver. 2.1
MsAccess Driver ver. 4.00.5303.01

-- Start of Error Message --

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Syntax error 
in UPDATE statement. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at sample.pl line 
32.
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Syntax error 
in UPDATE statement. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at sample.pl line 
32.
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

--- End of Error Message --
--- Start of Script ---

#! /usr/bin/perl


use DBI  qw(:sql_types) ;


my $DBName = '\\\\7920CCJ70392\\C$\\TEMP\\data.mdb' ;

my $DSN = "driver=Microsoft Access Driver (*.mdb);dbq=$DBName" ;
my $dbh = DBI->connect( "dbi:ODBC:$DSN", "", "",
        {RaiseError => 1, PrintError => 1, AutoCommit => 0} ) ||
        die "Unable to connect: " . $DBI::errstr . "\n" ; 

my $sth = $dbh->prepare(q{
        INSERT INTO RealTime (Index, Data) VALUES (?, ?)
        }) || die $dbh->errstr ;

# my $sth_upd = $dbh->prepare(q{
#       UPDATE RealTime SET timestamp = ? WHERE Index = ?
#       }) || die $dbh->errstr ;

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime ;

$ODBCDate = sprintf("{ts '%4d-%02d-%02d %02d:%02d:%02d'}", 
        $year + 1900, $mon, $mday, $hour, $min, $sec);

        print "\n\n\n$ODBCDate\n\n\n" ;

        $rv = $sth->execute(101, 0) ;
#       $rv = $sth_upd->bind_param(1, $ODBCDate, SQL_DATETIME) ;
#       $rv = $sth_upd->bind_param(2, 101) ;
#       $sth_upd->execute ;

$dbh->commit || die $dbh->errstr ;
$dbh->disconnect ;

--- End of Script ---

--- On Mon 09/16, Jeff Urlwin  wrote:
From: Jeff Urlwin [mailto: [EMAIL PROTECTED]]
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
Date: Mon, 16 Sep 2002 08:59:52 -0400
Subject: RE: Syntax error in INSERT / UPDATE statements

> Please give an example of the code.  It very much depends upon the
> database
> you are connecting to, the version of the ODBC driver and, of course,
> your
> code.  Since you are using Access, specifying the version of Access and
> the
> version of your ODBC driver *may* be helpful, but Access has had good
> support for the ODBC date formatting for a while now.  More information
> would be helpful, including the error message itself with your code. 
> Also,
> it would be good to know the version of DBI and DBD::ODBC you are using. 
> I
> suspect DBD::ODBC 0.28, if you are using ActiveState perl and you used
> ppm
> to install.
> 
> Regards,
> 
> Jeff
> 
> 

------------------------------------------------
Changed your e-mail?  Keep your contacts!  Use this free e-mail change of address 
service from Return Path.  Register now!