Developer Question regarding MySQL Syntax?

[BobMCT]

Most of the time developing with MySQL goes along just fine. Sometimes, however, the amount of data elements to be maintained can exceed hundreds for certain apps. With this amount of data debugging the SQL statements can become tedious due to the sheer number of elements, quoting, commas, etc. Of course the MySQL error messages simply state "Error in your syntax . . ."

So, I am wondering, is anyone aware of a SQL statement verifier that does a reasonable job of verifying? I've searched and tried a number but most of what I found simply repeated that simple message. Of course a good one should incorporate the database definition so that most likely eliminates "simple".

Therefore, any thoughts, suggestions, ideas, etc. which might reduce the amount of time one spends on these types of errors?

Thanks for reading and also for any suggestions.

 

[SkipVought]

Hi,

I know how to spell "MySQL". So this comment will be generic at best.

I've had similar problems with SQL in other applications, and I've helped others debug theirs by odffering this advice. It takes time to organize and it seems to take up a lot of space. But it's great for identifying problems or modifying the query. I like the way it structures the code for ease of understanding.

So here's what I'd like you to do. Post one of your smaller queries. I'll put it through my process and post back an example of what I'd do.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ChrisHirst]

Therefore, any thoughts, suggestions, ideas, etc. which might reduce the amount of time one spends on these types of errors?

Send the query to some visual output so you can read it before sending it to the database server.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[spamjim]

Do you get more than just "Error in your syntax..."?

Normally, one gets "Error in your syntax near...[where the booboo is]"

And of course, a visual tool can be better than terminal/command.

https://dev.mysql.com/downloads/workbench/

http://www.heidisql.com/

https://github.com/webyog/sqlyog-community/wiki/Downloads

Even viewing the query in a text editor that has syntax coloring can be helpful to find that missed punctuation.

https://www.geany.org/

 

[BobMCT]

Thank you for the advice and recommendations. I also use Geany for my main editor and it helps tremendously, especially pairing brackets, quotes, etc.
FWIW - my query string had over fifty elements and my error turned out to be a trailing comma just before the ) Values( clause. I plan to investigate the visual tools referenced so hopefully save some time in the future.
Thanks again...