SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 7.1.228 Pro] - Custom queries

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 7.1.228 Pro] - Custom queries
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 7.1.228 Pro] - Custom queries Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
Please add a pair of $$ between CREATE and SELECT
Tue Dec 02, 2014 12:03 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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;
$$

Tue Dec 02, 2014 12:48 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
I'm not sure about that. Let me ask for help from the team
Tue Dec 02, 2014 12:58 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.