Inserting records using custom stored procedure
Posted: 25.10.2008, 21:03
I'm new both to delphi & Zeoslib, so please forgive my stupid questions...
I have a table in database:
And a stored procedure, to insert new records into the table and to return the value of primary key:
I have problems doing it with Zeoslib components. I set the property 'SQL' of ZQuery to 'SELECT * FROM ICD10', and selecting records works fine. Then I set the property 'InsertSQL' of ZUpdateSQL to 'execute procedure PI_ICD10(:IDENT, :CODE, :NAME) RETURNING_VALUES :IDENT_OUT'. I also set the property 'ParamCheck' to false, to prevent automatic creation of paramaters, because I need my own parameters to include output from stored procedure.
But each time I manually create parameters for ZUpdateSQL using forms designer, they keep disapearing after I run my application! And they are ignored during preparation of database query (I got errors about ':IDENT not belonging to the table').
I'm stuck - I don't know where I should create the params in code, so that they aren't ignored (I tried in form's constructor and few other places but the ZQuery never uses them while preparing SQL statement
). I don't know how to assign the values of fields in current record to these params, and how to assign the value returned from my stored procedure back to ZQuery's field 'IDENT'.
Delphi is really hard for me, because it lacks documentation so much!
Please help
I have a table in database:
Code: Select all
create generator gen_icd10_id;
create table icd10 (
ident integer not null,
code varchar(8) not null,
name varchar(255) not null
);
set term ^ ;
alter table icd10 add constraint pk_icd10 primary key (ident);
/* Trigger: ICD10_BI */
create trigger icd10_bi for icd10
active before insert position 0
as
begin
if (new.ident is null) then
new.ident = gen_id(gen_icd10_id,1);
end
^
Code: Select all
CREATE OR ALTER PROCEDURE PI_ICD10 (
i$ident integer,
i$code varchar(8),
i$name varchar(255))
returns (
ident integer)
as
BEGIN
INSERT INTO "ICD10"
(
"IDENT",
"CODE",
"NAME"
)
VALUES
(
:"I$IDENT",
:"I$CODE",
:"I$NAME"
)
RETURNING
"IDENT"
INTO
:"IDENT"
;
SUSPEND;
END^
But each time I manually create parameters for ZUpdateSQL using forms designer, they keep disapearing after I run my application! And they are ignored during preparation of database query (I got errors about ':IDENT not belonging to the table').
I'm stuck - I don't know where I should create the params in code, so that they aren't ignored (I tried in form's constructor and few other places but the ZQuery never uses them while preparing SQL statement
). I don't know how to assign the values of fields in current record to these params, and how to assign the value returned from my stored procedure back to ZQuery's field 'IDENT'.Delphi is really hard for me, because it lacks documentation so much!
Please help