Item12018: SqlPlugin generating errors for queries spread across multiple lines

I recently updated SqlPlugin, and it seems to no longer work when you have multiple queries on the same page that span multiple lines. In this error below, it clearly has bits from other queries on my page mixed up in this query, so I think it's having trouble matching the end of the block when the query spreads multiple lines. I did test this with disabling all other extensions (using ?debugenableplugins) so I'm pretty sure that SqlPlugin is doing this itself.

Being able to format an sql query across multiple lines and with indentation is critical to being able to maintain that query. I hope this isn't lost functionality. I would strongly suggest adding a multiline query to the unit tests for Sqlplugin.

ERROR: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 at /home/neagile/foswiki/public_html/lib/Foswiki/Plugins/SqlPlugin/Core.pm line 138. at /home/neagile/foswiki/public_html/lib/Foswiki/Plugins/SqlPlugin/Core.pm line 138 Foswiki::Plugins::SqlPlugin::Core::__ANON__() called at /usr/local/lib/perl5/site_perl/5.8.8/Error.pm line 419 eval {...} called at /usr/local/lib/perl5/site_perl/5.8.8/Error.pm line 411 Error::subs::try('CODE(0x2b32580)', 'HASH(0x3972630)') called at /home/neagile/foswiki/public_html/lib/Foswiki/Plugins/SqlPlugin/Core.pm line 160 Foswiki::Plugins::SqlPlugin::Core::handleSQL('Foswiki=HASH(0x3040a50)', 'Foswiki::Attrs=HASH(0x2b32800)', 'TestSqlPluginBroken', 'Sandbox', 'Foswiki::Meta=HASH(0x3503690)') called at /home/neagile/foswiki/public_html/lib/Foswiki/Plugins/SqlPlugin.pm line 57 Foswiki::Plugins::SqlPlugin::handleSQL('Foswiki=HASH(0x3040a50)', 'Foswiki::Attrs=HASH(0x2b32800)', 'TestSqlPluginBroken', 'Sandbox', 'Foswiki::Meta=HASH(0x3503690)') called at /home/neagile/foswiki/public_html/lib/Foswiki/Func.pm line 568 Foswiki::Func::__ANON__('Foswiki=HASH(0x3040a50)', 'Foswiki::Attrs=HASH(0x2b32800)', 'Foswiki::Meta=HASH(0x3503690)') called at /home/neagile/foswiki/public_html/lib/Foswiki.pm line 3071 Foswiki::_expandMacroOnTopicRendering('Foswiki=HASH(0x3040a50)', 'SQL', '"SELECT \x{a} year( registerDate ),month( registerDate ),count( ...', 'Foswiki::Meta=HASH(0x3503690)') called at /home/neagile/foswiki/public_html/lib/Foswiki.pm line 2947 Foswiki::_processMacros('Foswiki=HASH(0x3040a50)', '%SQL{"SELECT * FROM jos_dtregister_locations;" database="anel...', 'CODE(0x2f77e70)', 'Foswiki::Meta=HASH(0x3503690)', 16) called at /home/neagile/foswiki/public_html/lib/Foswiki.pm line 2744 Foswiki::innerExpandMacros('Foswiki=HASH(0x3040a50)', 'SCALAR(0x2f87730)', 'Foswiki::Meta=HASH(0x3503690)') called at /home/neagile/foswiki/public_html/lib/Foswiki.pm line 3253 Foswiki::expandMacros('Foswiki=HASH(0x3040a50)', '%SQL{"SELECT * FROM jos_dtregister_locations;" database="anel...', 'Foswiki::Meta=HASH(0x3503690)') called at /home/neagile/foswiki/public_html/lib/Foswiki/Meta.pm line 3129 Foswiki::Meta::expandMacros('Foswiki::Meta=HASH(0x3503690)', '%SQL{"SELECT * FROM jos_dtregister_locations;" database="anel...') called at /home/neagile/foswiki/public_html/lib/Foswiki/UI/View.pm line 402 Foswiki::UI::View::_prepare('%SQL{"SELECT * FROM jos_dtregister_locations;" database="anel...', 'Foswiki::Meta=HASH(0x3503690)', 0) called at /home/neagile/foswiki/public_html/lib/Foswiki/UI/View.pm line 382 Foswiki::UI::View::view('Foswiki=HASH(0x3040a50)') called at /home/neagile/foswiki/public_html/lib/Foswiki/UI.pm line 316 Foswiki::UI::__ANON__() called at /usr/local/lib/perl5/site_perl/5.8.8/Error.pm line 419 eval {...} called at /usr/local/lib/perl5/site_perl/5.8.8/Error.pm line 411 Error::subs::try('CODE(0x2fbce30)', 'HASH(0x3040730)') called at /home/neagile/foswiki/public_html/lib/Foswiki/UI.pm line 435 Foswiki::UI::_execute('Foswiki::Request=HASH(0x2ff2c40)', 'CODE(0x2ff2800)', 'view', 1) called at /home/neagile/foswiki/public_html/lib/Foswiki/UI.pm line 274 Foswiki::UI::handleRequest('Foswiki::Request=HASH(0x2ff2c40)') called at /home/neagile/foswiki/public_html/lib/Foswiki/Engine/CGI.pm line 41 Foswiki::Engine::CGI::run('Foswiki::Engine::CGI=HASH(0x2a00c60)') called

Here's the markup that's generating this output:

%SQL{"SELECT year( registerDate ),month( registerDate ),count( * ) FROM `jos_users` GROUP BY year( registerDate ) , month( registerDate ) ORDER BY year(registerDate ) DESC , month( registerDate ) DESC LIMIT 12;" database="anelive"}%

-- DavidKramer - 30 Jul 2012

Hi, can you please try again with the new SqlPlugin that I just uploaded to Extensions/Testing? I fixed a problem with error handling. If that doesn't fix it, can you please see if the problem can be replicated with only one %SQL statement on the page?

-- KipLubliner - 03 Aug 2012

Hi David,

I found that this defect was (I assume) accidentally introduced in

http://trac.foswiki.org/changeset/14749

I uploaded 1.03 with the fix to Extensions/Testing, or you can fix it yourself by removing these two lines from trunk/SqlPlugin/lib/Foswiki/Plugins/SqlPlugin/Core.pm (in handleSQL):

    $theQuery =~ m/(.*)/; 
    $theQuery = $1; 

Kip, sorry for the delay. I had assumed if I entered a support item that I would be subscribed to it and therefore notified of updates. It doesn't appear that's the case, nor could I find a way of subscribing.

Yes, commenting out those two lines does fix the problem. Thanks a lot. I assume the next time this is officially released this fix will be in there, and it will overwrite my hack.

Thanks again for following up and fixing this..

-- DavidKramer - 08 Aug 2012

Commenting is disabled while not logged in