[SOLVED] MySQL temporay table howto ?
lazarus / 16-Databases

----------------------------------------------------------------------
Fripsy · 2015-05-22 09:34

Hi All,

I think I'm having a "blond moment" here 

I'm trying to run the following query against an mySQL DB:

Code: [Select]CREATE TEMPORARY TABLE tmp_test (f1 INT, f2 INT);
INSERT INTO tmp_test (f1, f2) VALUES (1,1), (2,1), (3,1);
SELECT * FROM tmp_test;
This works fine in SQLyog, but I can't seem to find a way to do this
in Lazarus with TSQLQuery or TSQLScript...

What am I missing here ?

Thanks,
Frank.

----------------------------------------------------------------------
Fripsy · 2015-05-22 17:39

Well, I did some more testing, but no go...

Here is what I tried so far:
all commands in the SQL property of a TSQLQuery: this doesn't work:
error on TEMPORARYthe CREATE and INSERT commands in the InsertSQL
property, the SELECT command in the SQL property: this raises an error
on the SELECT statement: table unknownthe CREATE and INSERT commands
in the Script property of a TSQLScript, the SELECT in the SQL of a
TSQLQuery: same error: table unknown
For this to work, all commands have to be executed in the same session
which is clearly not the case in my tries.

So, if anyone knows how to execute those commands in the same session,
or any other solution to work with temporary tables, please feel free
to  point me in the right direction.

Thanks,
Frank

----------------------------------------------------------------------
LacaK · 2015-05-22 17:49

Do you have tried:

Code: [Select]SQLQuery1.SQL.Text := 'CREATE TEMPORARY TABLE tmp_test
(f1 INT, f2 INT);
INSERT INTO tmp_test (f1, f2) VALUES (1,1), (2,1), (3,1);
SELECT * FROM tmp_test;';
SQLQuery1.ExecSQL;

----------------------------------------------------------------------
mirce.vladimirov · 2015-05-22 17:54

separate these commands and execute one by one (freepascal code
follows) :
Code: [Select]sqlquery1.close;
sqlquery1.sql.clear;
sqlquery1.sql.add('CREATE TEMPORARY TABLE tmp_test (f1 INT, f2 INT)');
sqlquery1.execsql;

sqlquery1.close;
sqlquery1.sql.clear;
sqlquery1.sql.add('INSERT INTO tmp_test (f1, f2) VALUES (1,1) ');
sqlquery1.execsql;

sqlquery1.close;
sqlquery1.sql.clear;
sqlquery1.sql.add('INSERT INTO tmp_test (f1, f2) VALUES (2,1) ');
sqlquery1.execsql;

sqlquery1.close;
sqlquery1.sql.clear;
sqlquery1.sql.add('INSERT INTO tmp_test (f1, f2) VALUES (3,1) ');
sqlquery1.execsql;

sqlquery1.close;
sqlquery1.sql.clear;
sqlquery1.sql.add('SELECT * FROM tmp_test');
sqlquery1.open;

I guess you will note the different usage of "sqlquery1.execsql" and
"sqlquery1.open", that is normal,
when we have a sql query which returns an answer (example a "select"
query) then we use ".open", and when we have a sql query which doesnot
return anything (a "insert", "update", "delete" etc. etc.) then we use
".execsql".

----------------------------------------------------------------------
Fripsy · 2015-05-22 22:54

@LacaK

That gives me an error TEMPORARY... unknow.

@mirce.vladimiro

This works like a charm !
Thanks for your help !

I understood from the wiki that ExecSQL only worked wit InsertSQL,
UpdateSQL or DeleteSQL, hence I didn't try it with the SQL property 

Cheers,
Frank.