I've got it.
The following worked for me.
Regards,
Paul.
""
<bigmousy@exci To: [EMAIL PROTECTED],
[EMAIL PROTECTED]
te.com> cc:
Subject: RE: Syntax error in INSERT /
UPDATE statements
17-09-2002
04:18
Please respond
to bigmousy
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)
timestamp or datetime is a reserved word, change it into:
Date_Time (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) ;
Should be:
use DBI;
use DBD::ODBC 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 Date_Time = ? 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);
Should be:
$ODBCDate = sprintf("%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) ;
The last one is:
$rv = $sth_upd->bind_param(1, $ODBCDate, {TYPE =>
SQL_DATETIME}) ;
$rv = $sth_upd->bind_param(2, 101) ;
$sth_upd->execute ;
$dbh->commit || die $dbh->errstr ;
$dbh->disconnect ;
--- End of Script ---
------------------------------------------------
Changed your e-mail? Keep your contacts! Use this free e-mail change of
address service from Return Path. Register now!
