Home: Perl Programming Help: DBI:
DBI Syntax Issue



ak08820
New User

Nov 5, 2009, 2:47 PM


Views: 9344
DBI Syntax Issue

I am trying to update a text field as follows.

my $stmt = $dbh->prepare('update usertable set userinfo = format(date(), "yyyymmdd") where userindex = ?');
$stmt->execute(27);



When I try to run this, I get the below error.

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2. (SQL-07002) at test.pl line 98.


If i replace the double quotes with sigle quotes, around yyyymmdd, I get

Bad name after yyyymmdd' at 24HrsExpiry.pl line 97.

What am I doing wrong?

Thanks


FishMonger
Veteran / Moderator

Nov 5, 2009, 3:41 PM


Views: 9339
Re: [ak08820] DBI Syntax Issue

You're using the wrong mysql function(s) in your set clause and you're passing the correct parameters to the function(s).

I'm not sure what value you want in the set clause, but you should read up on these functions.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_format

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format


ak08820
New User

Nov 5, 2009, 4:10 PM


Views: 9337
Re: [FishMonger] DBI Syntax Issue


In Reply To
You're using the wrong mysql function(s) in your set clause and you're passing the correct parameters to the function(s).

I'm not sure what value you want in the set clause, but you should read up on these functions.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_format

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format


I should have added that I was using the ODBC driver to a MS Access database. This was not a mySQL database.
Thanks

In Reply To


FishMonger
Veteran / Moderator

Nov 5, 2009, 4:23 PM


Views: 9336
Re: [ak08820] DBI Syntax Issue


In Reply To
I should have added that I was using the ODBC driver to a MS Access database. This was not a mySQL database.
Thanks

In Reply To


I missed that detail in the error message.

However, I'm pretty sure those functions are generic enough sql functions that they should work in an ODBC connection. If I'm right, then the ODBC driver should convert it to the proper syntax for Access.