SQL-37000 = Syntax error or access violation
In SQL Server (yes I know you use Access) you don't update timestamps,
they are updated for you.
Try to print out the statement then use some other tool (Access?) to run
the query.
/Roger P
[EMAIL PROTECTED] wrote:
> 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!
>
>
>
