database table named 'with'

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.
[download]

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

Comment on database table named 'with' Download Code

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(?,?)"); ... [download]	[reply] [d/l]
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.	[reply]
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.	[reply] [d/l]
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	[reply]
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);` [download] $worked is a 1 or a 0 depending on the success or failure of the SQL statement. -Kevin	[reply] [d/l]
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 `` Grygonos	[reply] [d/l]