[SA 7.1.228 Pro] - Custom queries

[SA 7.1.228 Pro] - Custom queries

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).

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.

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.

Please add a pair of $$ between CREATE and SELECT

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.

but checking Message pane shows that everything was fine.

Any ideas?

Here's the complete snippet, in case you want to experiment:

Code:

$PROMPT(clude_way,Include or exclude mask?,,Prepare tables to process,Include Exclude)$
$PROMPT(schema_mask,Schema mask?,%%,Prepare tables to process)$
$PROMPT(clude_mask,Clude table mask?,%%,Prepare tables to process)$
$$
DROP TEMPORARY TABLE IF EXISTS tables_2_process;
$$
$$
CREATE TEMPORARY TABLE IF NOT EXISTS tables_2_process
SELECT
CASE
   WHEN '$clude_way$' = 'Include' AND t.TABLE_NAME LIKE '$clude_mask$' THEN 1
   WHEN '$clude_way$' = 'Exclude' AND t.TABLE_NAME LIKE '$clude_mask$' 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
FROM
information_schema.`TABLES` AS t
WHERE 1 = 1
AND t.TABLE_SCHEMA LIKE '$schema_mask$'
AND t.TABLE_TYPE = 'BASE TABLE';
$$
$$
SELECT * FROM tables_2_process AS tp WHERE tp.include = 1;
$$

I'm not sure about that. Let me ask for help from the team