http://www.perlmonks.org?node_id=425108

davidj has asked for the wisdom of the Perl Monks concerning the following question:

Ok fellow monks,
This is a strange one. I'm using DBI to update a mysql database. There are 5 integer fields. If I name the last field 'with', I get an SQL syntax error. If I change the name of the field to something else, I don't get the error.
This is the error I get when naming the field 'with'. (All of the fields are integer fields):
DBD::mysql::st execute failed: You have an error in your SQL syntax. +Check the manual that corresponds to your MySQL server version for th +e right syntax to use near 'with) VALUES (11,'14','1','1','1')' at li +ne 1 at ./put1.pl line 25, <CEV> line 1.
I have searched SQL documentation and unless I am missing something, 'with' is not a keyword that would cause confusion. Is there something special about the word in Perl that would be causing this error?

davidj

Replies are listed 'Best First'.
Re: database table named 'with'
by !1 (Hermit) on Jan 26, 2005 at 04:44 UTC

    No, there is nothing special about the way perl is handling it. "with" is a reserved word in mysql, as shown here. The solution is to surround "with" with backticks (`) as described here. Thus your query would become something like:

    my $sth = $dbh->prepare("insert into blah (...,`with`) values(?,?)"); ...
Re: database table named 'with'
by jZed (Prior) on Jan 26, 2005 at 04:54 UTC
    > I have searched SQL documentation and unless I am missing something, 'with' is not a keyword that would cause confusion.

    I'm not sure where you were looking but WITH is a SQL reserved keyword.

Re: database table named 'with'
by jbrugger (Parson) on Jan 26, 2005 at 08:16 UTC
    if you want to use some reserved words (and WANT to keep them), you might concider placing the words between backticks like select * from `with`. You probably find it under the ~ key.
Re: database table named 'with'
by davidj (Priest) on Jan 26, 2005 at 06:46 UTC
    Thank you all for your input. I must admit that my search for with being a keyword in SQL was done after being at work for over 10 hours having already been up for about 10 hours. I guess I should do script troubleshooting after being up for 20 hours straight :)

    davidj
Re: database table named 'with'
by K_M_McMahon (Hermit) on Jan 26, 2005 at 04:42 UTC
    Can you post the Statement as you are performing it in the script? I had a lot of trouble myself getting mysql to work, but I think I have it now and I hopefully can help.

    Sample code from one of my DB scripts:
    $query="INSERT INTO session_log VALUES(\'$curr_time\',\'$user_id\',\'$ +passwd\');"; $worked=$dbh->do($query);
    $worked is a 1 or a 0 depending on the success or failure of the SQL statement.
Re: database table named 'with'
by Grygonos (Chaplain) on Jan 26, 2005 at 16:28 UTC

    I'm not sure if its standard or not.. but in SQL Server I use [fieldname] to take care of reserved fields.. its a little more readable, to me at least, than ``