Checking SQL Syntax

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

The dbi driver for sqlite allows me to use the prepare statement to check for SQL syntax problems before executing it. Is there a way to check the SQL syntax of mysql before executing? For some reason, prepare does not work that way with DBD::mysql. Any ideas how I can precheck the SQL syntax in mysql?

sub checkSQL{
my $sql=shift;
my $sth = $dbh->prepare($sql) || return "SQL Syntax Error:" . $DBI::er
+rstr;
return 1;
}
[download]

-------------------------------
Sign up now for a free monthly newsletter service!
http://www.bestgazette.com

Comment on Checking SQL Syntax Download Code

Replies are listed 'Best First'.
Re: Checking SQL Syntax by andreas1234567 (Vicar) on May 14, 2008 at 05:33 UTC
MySQL has a EXPLAIN command that might be helpful. I wonder what's your motivation for checking the syntax, what's wrong with just running the query and handle errors, if any? -- No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]	[reply]
Re: Checking SQL Syntax by ysth (Canon) on May 14, 2008 at 06:00 UTC
Different databases or DBDs divide up the work between prepare and execute differently. Why do you want to do the precheck, anyway? Why not just execute? You could do (untested:) `my $ok = $dbh->do("explain $sql");` [download] -- Online Fortune Cookie Search	[reply] [d/l]
Re: Checking SQL Syntax by dragonchild (Archbishop) on May 14, 2008 at 09:44 UTC
If SQL doesn't parse, there are no adverse effects in any way, at least on Oracle, MySQL, Sybase, SQL*Server, or PostgreSQL. I've never used other RDBMSes, so I can't say for sure, but I'd be shocked if there were. If you're worried about reporting the issue up the chain, the consumer of your DBI wrapper generally doesn't care why something failed, just that it failed at all. I've found it best to just do the whole thing as one big prepare-execute(-fetch) black box and roll errors up from any of the parts. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]
Re: Checking SQL Syntax by grizzley (Chaplain) on May 14, 2008 at 06:58 UTC
I think I saw once a module to do this on CPAN. Did you search there? Maybe e.g. SQL::Statement can help you?	[reply]
Re: Checking SQL Syntax by waba (Monk) on May 14, 2008 at 07:31 UTC
`DBD::sqlite` is the engine through `libsqlite`, whereas `DBD::mysql` is a mere network client. So, until `prepare` actually prepares the statement on the server, you can't (unless you find some checking module/library that speaks MySQL, not just SQL).	[reply] [d/l] [select]
Re: Checking SQL Syntax by perrin (Chancellor) on May 14, 2008 at 14:06 UTC
If you use the optional server-side statement handles feature in MySQL, it will give errors for bad SQL when you prepare.	[reply]