Page 1 of 1
PostgreSQL return param
Posted: Fri 09 May 2014 11:32
by yota
In Oracle I can easly get return value of a function in TUniQuery.SQLInsert like:
Code: Select all
BEGIN
:IVAL := SMPKG.AFUNCTION(:PARAM1, :PARAM2, :PARAM3);
END;
The return value will be saved to IVAL (returnParams set to True).
But it doesn't work in PostgreSQL. I tried
Code: Select all
SELECT SMPKG.AFUNCTION(:PARAM1, :PARAM2, :PARAM3) INTO :IVAL;
and
Code: Select all
do $$ BEGIN
:IVAL := SMPKG.AFUNCTION(:PARAM1, :PARAM2, :PARAM3);
END$$;
but none of them are working. How I can get the return value of the function and assign it to the corresponding field in PostgreSQL?
Re: PostgreSQL return param
Posted: Fri 09 May 2014 16:14
by FCS
Hello,
Try this (by simple select)
Code: Select all
function Utworz_ID(P1,P2:string):string;
var
UP:TUniQuery;
begin
UQ:=TUniQuery.Create(nil);
UQ.Connection:=DM_01.UniConnection1;
UQ.SQL.Add('SELECT utworz_id('+P1+','+P2+') Res FROM Table');
UQ.Open;
Result:=UQ.FieldByName('Res').AsString;
UQ.Close;
UQ.free;
end;
or this (by calling a stored procedure)
Code: Select all
function Utworz_ID(P1,P2:string):string;
var
UP:TUniStoredProc;
begin
UP:=TUniStoredProc.Create(nil);
UP.Connection:=DM_01.UniConnection1;
UP.StoredProcName:='utworz_id';
UP.Prepare; //here UP reads parameters defined in stored procedure
UP.ParamByName('PP1').ParamType := ptInput;
UP.ParamByName('PP1').DataType := ftString;
UP.ParamByName('PP1').AsString := P1;
UP.ParamByName('PP2').ParamType := ptInput;
UP.ParamByName('PP2').DataType := ftString;
UP.ParamByName('PP2').AsString := P2;
UP.Execute;
Result:= UP.ParamByName('Result').AsString; // when procedure returns only one variable
Result:= UP.ParamByName('Res_1').AsString; //when procedure returns a few variables
Result:= UP.ParamByName('Res_2').AsString; //when procedure returns a few variables
UP.Close;
UP.Free;
end;
When function returns a dataset, you can navigate through them using standard EOF() loop
Code: Select all
function Utworz_ID(P1,P2:string):string;
var
UP:TUniQuery;
begin
UQ:=TUniQuery.Create(nil);
UQ.Connection:=DM_01.UniConnection1;
UQ.SQL.Add('SELECT utworz_id('+P1+','+P2+') FROM Table'); //returns dataset
UQ.Open;
while not UQ.EOF do begin
Field1:=UQ.FieldByName('Field1_name').AsString;
Field2:=UQ.FieldByName('Field2_name').AsInteger;
UQ.NEXT;
end;
UQ.Close;
UQ.free;
end;
Regards
Michal
Re: PostgreSQL return param
Posted: Mon 12 May 2014 07:25
by yota
These are fine solutions but they doesn't quite fit in what I want to achieve.
In my example I can easly separate business logic of my application to package function. I got "select * from" in TUniQuery.SQL and appropriate action (insert, update, delete) in SQLAction of the component. That allow me not to worry about dataset refresh or writing additional code.
As I'm writing this I found a solution, maybe it will come in handy to someone:
Code: Select all
SELECT SMPKG.AFUNCTION(:PARAM1, :PARAM2, :PARAM3) as IVAL;
The IVAL column will automagicly hop in to dataset.
Kind regards,
yota
Re: PostgreSQL return param
Posted: Mon 12 May 2014 07:50
by AlexP
Hello,
When calling a function with SELECT sp_name(params...), PostgreSQL returns RESULT as ResultSet, therefore you should call this parameter using
P.S. For the time being, PostgreSQL doesn't support parameters in anonymous blocks. You can vote for this functionality implementation at
http://postgresql.uservoice.com/forums/ ... -like-in-o