ora and sql with returning statement

Forum related to Oracle Server

Moderators: gto, EgonHugeist, mdaems

goo-mlyny1
Fresh Boarder
Fresh Boarder
Posts: 1
Joined: 19.04.2013, 07:45

ora and sql with returning statement

Postby goo-mlyny1 » 19.04.2013, 08:50

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?

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Re: ora and sql with returning statement

Postby EgonHugeist » 09.02.2018, 21:15

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
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image


Return to “Oracle”

Who is online

Users browsing this forum: No registered users and 1 guest