ZQuery execute sql

manz · Post by **manz** » 08.01.2009, 05:23

Dear all,

Just simply,

does zquery can execute multiple statement in single sql.text ?

for example :
Here the SQL statements :
create temporary table if not exists t_purchase_order
(
po_number varchar(30) not null,
po_item_number varchar(30) not null,
po_date date,
vendor_code varchar(20) default '',
stock_code varchar(40) default '',
delivery_date date,
plant varchar(20) default '',
warehouse_code varchar(20) default '',
po_quantity double precision default 0,
uom_code varchar(20) default '',
pr_number varchar(30) default '',
pr_item_number varchar(30) default '',
pr_date date,
po_type varchar(2) default '',
currency varchar(4) default '',
unitprice double precision default 0,
department varchar(40) default '',
actual_delivery_date date,
remark text,
create_date datetime null,
update_date datetime null,
uname varchar(20) default '',
constraint pk_purchase_order primary key (po_number, po_item_number)
);
delete from t_purchase_order;

and command: zQuery1.ExecSQL;

Thanks
Man'z

6.6.4-stable version

gto · Post by **gto** » 08.01.2009, 12:27

not ZQuery but ZSQLProcessor

Post by **mdaems** » 08.01.2009, 21:06

It should be possible using a ZQuery as well. Try ZConnection1.Properties.Add('CLIENT_MULTI_STATEMENTS=1'); before opening the query. Or set the values using the property editor for ZConnection1.Properties.
This is a mysql only feature, however.
In general gto's solution is advisable for script-like actions as you describe.

Mark

manz · Post by **manz** » 09.01.2009, 04:13

Thanks to gto and mark to resolve this matter.

Manz

manz · Post by **manz** » 09.01.2009, 05:29

BTW, Which is faster :
using zSQLProcessor that handle multi statements or using zQuery that using client_multi_statements at zConnection1.Properties ?

Man'z

Post by **mdaems** » 09.01.2009, 08:00

I guess client_multi_statements as it doesn't split the script and the processor uses zqueries internally. But let me warn you : there is no such thing as correct error handling of 'client_multi_statements' in zeoslib. So if one statement of a series fails this can go completely unnoticed. (I'm not sure if only the first or only the last errorstatus is retrieved)

Mark

manz · Post by **manz** » 12.01.2009, 09:14

Ok, Thanks Mark ...

before we create script-like, we tested first one-by-one statements to make sure the sql in good result.

Thanks again Mark