Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

database table named 'with'

by davidj (Priest)
on Jan 26, 2005 at 04:20 UTC ( #425108=perlquestion: print w/replies, xml ) Need Help??

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 ``

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://425108]
Approved by Crackers2
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (3)
As of 2021-09-19 23:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?