Could you shed some light on the reason the mysql snippet below fails? Are there any restriction applied to snippets that inhibit certain types of queries?

Code:

$$

DROP TEMPORARY TABLE IF EXISTS tables_2_process;

CREATE TEMPORARY TABLE IF NOT EXISTS tables_2_process
SELECT
CASE
WHEN 'Include' = 'Include' AND t.TABLE_NAME LIKE '%kosar%' THEN 1
WHEN 'Include' = 'Exclude' AND t.TABLE_NAME LIKE '%kosar%' THEN 0
ELSE 0
END AS include
,t.TABLE_SCHEMA as schema_name
,t.TABLE_NAME as table_name
,t.TABLE_TYPE as table_type
,t.`ENGINE` as table_engine
,t.`VERSION` as table_version
/*
,t.`ROW_FORMAT`
,t.TABLE_ROWS
,t.`AUTO_INCREMENT`
,t.CREATE_TIME
,t.UPDATE_TIME
,t.CREATE_OPTIONS
*/
FROM
information_schema.`TABLES` AS t
WHERE 1 = 1
AND t.TABLE_SCHEMA LIKE '%ert%'
AND t.TABLE_TYPE = 'BASE TABLE';

SELECT * FROM tables_2_process AS tp WHERE tp.include = 1;

$$

I get the following error:

Quote:

Code 1064, 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 'CREATE TEMPORARY TABLE IF NOT EXISTS tables_2_process SELECT t.TABLE_SCHEM' at line 2

NOTE: Except for the funny fact that uncommenting the commented part, while still yielding a proper result set, fails to insert into the temporary table (and a normal, pre-created table as well) when some conditions are met (not sure which ones).

Tue Dec 02, 2014 5:07 am

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6642

Some types of database servers like MySQL don't allow comments in non-procedural code. Normally when executing code in an editor connected to MySQL or from command line and scheduled jobs, SQL Assistant automatically removes comments from the code sent to the database server. But it doesn't do that in code snippets because the developer's intentions aren't known and the snippet produced code is not always sent to the database server.

It's less obvious to me why the data is not inserted into a temporary table, or inserted and not visible. I'm speculating that snippet processing engine might be using a different connection. something funny with the data retrieval done there in a different processing thread. Again this is a pure speculation, I don't have an answer for that. Please try adding COMMIT to ensure the transaction is commuted before the SELECT statement is executed.

Tue Dec 02, 2014 11:11 am

gemisigo

Joined: 11 Mar 2010Posts: 1441

I stripped the snippet down to its core and it seems that it fails if there's anything else beside the 'CREATE ...' statement. Having only that works as expected.

Tue Dec 02, 2014 11:30 am

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6642

Please add a pair of $$ between CREATE and SELECT

Tue Dec 02, 2014 12:03 pm

gemisigo

Joined: 11 Mar 2010Posts: 1441

Did exactly that. I also changed Action to Execute and Display Output Results so that I can check the results. And something weird happened. The results were there in the result pane but at the same time I got an error dialog stating that:

Quote:

An error occurred in user-defined query, see Messages pane for details.