Page 1 of 1

ora and sql with returning statement

Posted: 19.04.2013, 08:50
by goo-mlyny1
Hi,
I would like to use ZEOS with Oracle database and SQL like that:
INSERT INTO tb_test(pole_test) VALUES ('test') returning id into :vid

This SQL works fine when I use ADO components. With ZEOS I am not able to get return value.
This is example, how I try to do this with ADO and ZEOS:
Sql := 'insert into tb_test (pole_test) values (:pole_test) returning id into :vid';
zqPomocnicze.Active := False;
zqPomocnicze.SQL.Clear;
zqPomocnicze.ParamByName('pole_test').Value := 'test';
zqPomocnicze.ParamByName('vid').ParamType := ptInputOutput;
//with parameter ptOutput I got error
//OCI_ERROR: ORA-01008
zqPomocnicze.ParamByName('vid').DataType := ftInteger;
zqPomocnicze.ExecSQL;
S := VarToStr(dmSzkol.zqPomocnicze.Params[1].Value); //S is always empty

Code with Adoquery (SQL is identical)
ADOQuery1.Active := False;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add(SQL);
ADOQuery1.Parameters.ParamByName('pole_test').Value := 'test';
ADOQuery1.Parameters.ParamByName('vid').DataType := ftInteger;
ADOQuery1.Parameters.ParamByName('vid').Direction := pdOutput;
ADOQuery1.ExecSQL;
S := VarToStr(ADOQuery1.Parameters[1].Value); //S contains ID

Do I make something wrong with ZEOS? Or in ZEOS it is impossible to get that ID?

Re: ora and sql with returning statement

Posted: 09.02.2018, 21:15
by EgonHugeist
Do I make something wrong with ZEOS? Or in ZEOS it is impossible to get that ID?
No. Seems the Outparam is not handle. Thats not supported by now. (sorry for that)
Use

Code: Select all

insert into tb_test (pole_test) values (:pole_test) returning id
with zqPomocnicze.open