PostgreSQL return param

[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?

[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

[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

[AlexP]

Hello,

When calling a function with SELECT sp_name(params...), PostgreSQL returns RESULT as ResultSet, therefore you should call this parameter using

Code: Select all

UniQuery.Fileds[0].asXXX

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