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

Hello,

I have a DBD syntax error, and of course I can't see the problem. The code containing the little bugger contains 3 long INSERTS each with about 300 names, place holders and variables each.

Just as a preview, here's the error

DBD::mysql::db do failed: You have an error in your SQL syntax; check 
+the manual that corresponds to your MySQL server version for the righ
+t syntax to use near ')' at line 1 at /www/htdocs/click1003/app/strin
+gform.pl line 144.

[download]

From reading other posts and replys I found in searching, more code means more help. but this may be kind of big. Would it be better to post a link to a textfile?

Thanks,
- dstefani

UPDATE 08-24

Well, using DBI->trace for the first time was an enlightening experience. It let me see a few things that I needed to look into, as mentioned in one of the replies, there was an extra comma, but it could only be seen when the place holders where replaced with the real values in the trace output. (very cool). It has to do with my ignorance with proper SQL. As a self taught hack, you tend to learn what you need to know.

This has been a great stop on the learning path, I may have it straightened out now, if I don't I'll trim down one of the queries and make a PC post (monks PC that is)

Thank you all for your input, always valuable.

- dstefani

2005-08-26 jdporter unconsidered (Keep/Edit/Delete: 8/19/0). Consideration was: Change name to "Debugging DBD syntax error"

2005-08-26 jdporter Moved to SoPW.

Retitled by g0n from 'Posting a Question: best practices'.

Replies are listed 'Best First'.
Re: Debugging DBD/SQL syntax error
by jhourcle (Prior) on Aug 23, 2005 at 13:56 UTC

    You can do a few things -- one is the use of <readmore></readmore> tags, which will result in the details not being shown unless someone elects to specifically to view your node.

    You can also post a reply to your question, with additional details, but even then it's still best to use <readmore> tags if it's excessive in length.

    Linking to another site is not recommended, as someone may have something similar to your question in the future, and it's more likely to still have its full context if everything is posted on PerlMonks. (and some people don't like following random links.)

[reply]
[d/l]
[select]
Re: Debugging DBD/SQL syntax error
by davidrw (Prior) on Aug 23, 2005 at 14:06 UTC
    jhourcle's reply above is good for the general case... for your specific case here which appears to be a SQL problem and not a perl one, i would first debug/narrow it a little more. First, put in print $sql; and print join(":",@bind); debug statements (or use DBI's trace functionality) so you can test the sql directly in mysql. If the syntax error still isn't obvious, then try to knock the size down from 300 names to just 3 or so (i assume there's some loop-age to construct the statement so hopefully this is easy), and try to work w/the sql from there. Might be obvious once all the clutter is removed.. If it's still a problem, post it up! I'd recommend posting the smaller sample of what illustrates the issue -- even with <readmore> tags a INSERT statement w/300 names might not get as close a look as a concise version (and hopefully the problem will be easier for a reader to spot).
[reply]
[d/l]
[select]
Re: Debugging DBD/SQL syntax error
by grinder (Bishop) on Aug 23, 2005 at 15:39 UTC

    Regarding SQL in Perl, in general it is wise to always have a method of logging the SQL statement you build, especially when rolling SQL on the fly. Plus a method for dumping the values that are to be bound to a prepared statement.

    Secondly, in your particular instance, you should be able to scatter prints through the code to see which one of the three statements is causing the trouble. You must narrow it down to one.

    Finally, DBI's trace method will let you see exactly what is handed to the underlying DBD driver, with progressively excruciating detail according to how high you crank it up.

    Taking a wild guess here now, I'd say that you have a trailing comma after a field or value. e.g. something like insert into t (c1, c2, c3,) values (1,2,3,)

    Perl doesn't care about trailing commas in lists; in SQL it is an error.

    - another intruder with the mooring in the heart of the Perl

[reply]
Re: Debugging DBD/SQL syntax error
by Util (Priest) on Aug 23, 2005 at 20:03 UTC

    Many of the best practices for posting a question have been gathered in jeffa's epic tutorial, How (Not) To Ask A Question.
    Especially relevant are these sections:

    • Only Post Relevant Code
    • Include Sample Data (I/O)
    • Error Messages
    • Off-site Linking

    For your particular quandary, take the size of your intended post as an indicator that you have more trimming to do. While keeping the same occurrence of the error, can you trim the program down:

    1. To just contain 1 INSERT?
    2. With just 10 names, place holders and variables?
    3. In a command-line (non-CGI) form?
    4. That needs no external data?
    As a bonus, you will often find that the exercise of reducing your program to post-able size will uncover the bad code, and make the solution obvious.

[reply]
Re: Debugging DBD/SQL syntax error
by Anonymous Monk on Aug 23, 2005 at 14:46 UTC
    Well, SQL syntax isn't quite the realm of a Perl forum, but if I were you I'd first trim down the insert (to say, inserting a single row). You will probably still have the error, but there's less clutter to wade through.

    If you are generating the SQL code instead of having the SQL literally in your program, I'd also turn on tracing in the DBI, to verify that you are actually sending what you think you are sending.

[reply]
Re: Debugging DBD/SQL syntax error
by dakedesu (Scribe) on Aug 23, 2005 at 23:50 UTC

    I'll just note that I should have read this post before making my latest post.

    I've also noticed the "tutorial" section on forum interaction has updated quite a bit since I was last here. Is there anyway to subscribe to a section. This mostly because something like tutorials should allow for people to easily see what is new.

    (the tutorial thing added as the section in it for forum behavouir is relevant)

    Anybody else think that would be a good idea?

    -- Jamie Payne
[reply]
Re: Debugging DBD/SQL syntax error
by shiza (Hermit) on Aug 23, 2005 at 15:47 UTC
    Good grammer increases readability. Let's not forget Punctuation, Capitalization, and Paragraphs!
[reply]

      And so does Spelling :-)

      grammer =~ s/e/a/;

      [download]

      —Brad
      "The important work of moving the world forward does not wait to be done by perfect men." George Eliot
[reply]
[d/l]
        Thanks! :)
[reply]

Back to Seekers of Perl Wisdom