IIRC you don't need {ts ''} when using bound parameters. Use the {ts ''} syntax
when actually putting a timestamp in your SQL. Try removing the {ts ''}.
Martin
On 17-Sep-2002 [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!
--
Martin J. Evans
Easysoft Ltd, UK
Development
