Page 1 of 1

Inserting records using custom stored procedure

Posted: 25.10.2008, 21:03
by maakk
I'm new both to delphi & Zeoslib, so please forgive my stupid questions...

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
^
And a stored procedure, to insert new records into the table and to return the value of primary key:

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^
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 :)