[SOLVED] MySQL temporay table howto ?

[Fripsy]

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]

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 TEMPORARY
• the CREATE and INSERT commands in the InsertSQL property, the SELECT command in the SQL property: this raises an error on the SELECT statement: table unknown
• the 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]

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]

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]

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