Recent

Author Topic: [SOLVED] MySQL temporay table howto ?  (Read 2156 times)

Fripsy

  • New Member
  • *
  • Posts: 14
[SOLVED] MySQL temporay table howto ?
« on: May 22, 2015, 11:34:52 am »
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.
« Last Edit: May 23, 2015, 12:58:31 am by Fripsy »
Lazarus 1.4.0 r48774 FPC 2.6.4 i386-win32-win32/win64

Fripsy

  • New Member
  • *
  • Posts: 14
Re: MySQL temporay table howto ?
« Reply #1 on: May 22, 2015, 07:39:50 pm »
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
Lazarus 1.4.0 r48774 FPC 2.6.4 i386-win32-win32/win64

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: MySQL temporay table howto ?
« Reply #2 on: May 22, 2015, 07:49:41 pm »
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

  • Full Member
  • ***
  • Posts: 220
Re: MySQL temporay table howto ?
« Reply #3 on: May 22, 2015, 07:54:27 pm »
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

  • New Member
  • *
  • Posts: 14
Re: MySQL temporay table howto ?
« Reply #4 on: May 23, 2015, 12:54:57 am »
@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  ;D

Cheers,
Frank.
« Last Edit: May 23, 2015, 12:57:48 am by Fripsy »
Lazarus 1.4.0 r48774 FPC 2.6.4 i386-win32-win32/win64