Hello,
I'm looking for examples to write a stored procedure with Delphi 7.
I use zeosLib 6.6.1 and pg 8.2.
Thanks if you have it
I tried something like this
ZstoredProc.Params[0].AsString:='titi';
ZstoredProc.Params[1].AsString:='titi';
ZstoredProc.Params[2].AsString:='titi';
ZstoredProc.Params[3].AsString:='titi';
ZstoredProc.Params[4].AsString:='titi';
ZstoredProc.Params[5].AsInteger:=1 ;
ZstoredProc.Params[6].AsString:='titi';
ZstoredProc.StoredProcName:='insertutilisateur';
and after ??
ZstoredProc.Active :=true;
or
ZstoredProc.ExecProc;
return value?
ZstoredProc.Close;
My stored procedure in PG :
CREATE OR REPLACE FUNCTION insertutilisateur(character varying, character varying, character varying, character varying, character varying, integer, character varying)
RETURNS integer AS
$BODY$DECLARE
_nom ALIAS FOR $1;
_prenom ALIAS FOR $2;
_login ALIAS FOR $3;
_password ALIAS FOR $4;
_mail ALIAS FOR $5;
_idstation ALIAS FOR $6;
_userinsert ALIAS FOR $7;
......continue
Delphi7 and Stored Procedures
Moderators: gto, cipto_kh, EgonHugeist, olehs
Don't use ZstoredProc component, use Query component and "embed" stored procedure into SQL eg.
If Your postgres stored proc returns set then use above SQL. If returns single value, then you can use something like 'select storedproc(:param)'
N.B.
Postgres stored procs are actually functions - they always returns something, even when that "something" is "nothing" (return void) and from Delphi point of view, they behave more like tables or views. If you don't need returned values, use ExecSQL method instad Open or Active := true;
Code: Select all
ZReadOnyQuery.SQL := 'select * from storedproc(:param1)';
ZReadOnyQuery.paramByName('param1').value = 1;
ZReadOnyQuery.Open;
N.B.
Postgres stored procs are actually functions - they always returns something, even when that "something" is "nothing" (return void) and from Delphi point of view, they behave more like tables or views. If you don't need returned values, use ExecSQL method instad Open or Active := true;
I try to declare a variable for the return, but always I get the error "this variable don't exists'..
Which kind of variable to declare and how to initialize it.
var
myVar : ???? ?
begin
myVar := ???? ?
ZQuery.SQL.Clear;
ZQuery.Params.Create();
ZQuery.SQL.text := 'select MyFunction(rfC,Id,''now'');FETCH ALL IN rfC' ;
ZQuery.paramByName('rfC').value := ???? ;
ZQuery.paramByName('Id').value := 14;
ZQuery.Open;
//st := ZReadOnlyQuery.Fields[0].AsString;
end;
Which kind of variable to declare and how to initialize it.
var
myVar : ???? ?
begin
myVar := ???? ?
ZQuery.SQL.Clear;
ZQuery.Params.Create();
ZQuery.SQL.text := 'select MyFunction(rfC,Id,''now'');FETCH ALL IN rfC' ;
ZQuery.paramByName('rfC').value := ???? ;
ZQuery.paramByName('Id').value := 14;
ZQuery.Open;
//st := ZReadOnlyQuery.Fields[0].AsString;
end;
What is purpose of myVar? You don't need (and you can't) take query resource into variable as in php.
"select MyFunction(rfC,Id,''now'');FETCH ALL IN rfC" tells me that your function doesn't return set and has rfc as output param so my example function is:
and delphi code would be:
Postgres functions comes in many "flavors" (returns sets or not, output params (or not), array tipes etc. and each of them requires slightly different approach. Hope this example helps.
"select MyFunction(rfC,Id,''now'');FETCH ALL IN rfC" tells me that your function doesn't return set and has rfc as output param so my example function is:
Code: Select all
CREATE OR REPLACE FUNCTION myfunction (inout a integer, b integer) RETURNS integer AS
$body$
begin
a := a+b;
return;
end
$body$
LANGUAGE 'plpgsql';
Code: Select all
ZQuery.SQL.Clear;
// ZQuery.Params.Create(); - DONT DO THAT, see help: TParams.Create()
ZQuery.SQL.text := 'select myfunction (:aparam,:bparam)';
// You dont need cursor FETCH either..
ZQuery.paramByName('aparam').value := 1 ;
ZQuery.paramByName('bparam').value := 14;
ZQuery.Open;
ZQuery.fields[0].asInteger //or ZQuery.fieldbyName['myfunction'].asWhatEver...