Zeoslib Portal

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

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;

So it works, but I launch a stored procedure which returns an array of results... and how to?

with php
$rc = $myDb->arrayResult();
$myDb->query('select myfunction(\'rc\','.....');FETCH ALL IN rc;');

With Delphi, how to fetch in an array of variant? I presume?

Don't understand... your function returns (set of) array type or you have one or more fields inside result set which are array type or ...?? Some more detailed example would be useful, php is fine.

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;

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.

Thanks Trupka,
your example works but my function retruns some "refcursor AS_DECLARE?"
and there is the trouble...

CREATE FUNCTION getcadeauxagagner(refcursor, integer, date) RETURNS refcursor
AS $_$DECLARE
rc ALIAS FOR $1;
idstation ALIAS FOR $2;
_date ALIAS FOR $3;