Page 1 of 1

how to use TZQuery with multi statement sql string

Posted: 11.08.2010, 11:51
by srboslavr
Hi,
I use ZeosLib 6.6 and Delphi 5.0 and MySQL 5.5
Most of thinks works fine, but I have
problem with TZQuery. ( I am moving some app from BDe to ZeosLib).
Lot of queries have multiple statements like:

drop table if exists tempTable;
create temporary table tempTable(
id char(10) not null primary key,
value1 float null,
value2 float null);
insert into tempTable
(id, value1, value2)
select SifArtikla, sum(Kolicina), sum(Kolicina*Cena)
from PredracunSta
group by SifArtikla;

select * from tempTable;


When I call query.open method,
I receive an error:
Can not open a ResultSet

Is there any solution to run multi - statements query?

Best regards,

Posted: 11.08.2010, 14:23
by jeremicm
You can't "drop table if exists..." with open...
also can't create table with open...
try this...

drop table if exists tempTable;
execsql;

create temporary table tempTable(
id char(10) not null primary key,
value1 float null,
value2 float null);
execsql;

insert into tempTable
(id, value1, value2)
select SifArtikla, sum(Kolicina), sum(Kolicina*Cena)
from PredracunSta
group by SifArtikla;
execsql

select * from tempTable;
open;

Posted: 11.08.2010, 18:11
by Pitfiend
execsql is a metod from zquery object. just in case. I think you can send every non select in the same query, call execsql and then set your select statment and open it.

Posted: 12.08.2010, 11:32
by jeremicm
Pitfiend wrote:I think you can send every non select in the same query, call execsql and then set your select statment and open it.
You can, but from my experience, it's safer to exec them one by one... sometimes (when queries are to long or there is to many of them) some of queries won't execute and there's no error to warn about that...

Yes, it's faster to put them all together and it's less code to type... but i don't think it's good way to do it...

Posted: 16.08.2010, 14:33
by guidoaerts
Or you could use a TZSqlProcessor to execute the statements in a script...
Guido