ora and sql with returning statement
Posted: 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?
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?