Postgresql out argument for parameters in functions

Forum related to all other 6.x versions of ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist

SiegfriedN
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 18.02.2008, 14:35

Postgresql out argument for parameters in functions

Postby SiegfriedN » 18.02.2008, 14:51

Hi,

How does one get the value returned into a Postgresql 'out' function parameter with Zeoslib?

For example.

Postgres function

'create or replace function test1(in varchar, in char, out varchar);
..etc

When calling the function from a TZQuery

SQL.Text := 'select test1(:par1, :par2)';
..etc

This works great, but how does one get the out parameter value?

I tried to add the out parameter like this before ExecSQL

qry.Params.CreateParam('ouputfld', ftString, ptOutput);

However the 'outfld' param does not contain the value passed from the server after the ExecSQL; (just '')

If this is possible to do this with Zeos? I really hope so.
I am using version 6.6.1 at the moment.

Thanks :)

Siegfried

SiegfriedN
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 18.02.2008, 14:35

solution

Postby SiegfriedN » 22.02.2008, 23:27

Hi,

Postgres returns out parameters as a resultset thus the following returns the out parameter values from the server.

Instead of calling ExecSQL use the Open method of TZQuery.

All the out parameters are then returned in one field seperated by commas. i.e. query.Fields[0].Value
e.g. '('text1',1234)'

One then has to parse out the comma seperated different 'out' parameters.

Great, Go ZeosLib! :D

Siegfried

btrewern
Expert Boarder
Expert Boarder
Posts: 193
Joined: 06.10.2005, 18:51

Postby btrewern » 18.03.2008, 15:43

I'm not sure you should need to parse the result set. It should come back as a proper recordset. I'm currently using 6.6.2rc and functions similar to the following:

Code: Select all

CREATE type test_tp as
  (a integer,
   b text);

CREATE FUNCTION test_fn(code integer) RETURNS SETOF test_tp AS
$$
  SELECT
    a, b
  FROM
    test_table
  WHERE
    code = $1
$$
LANGUAGE 'sql';


then do in a ZQuery

Code: Select all

SELECT * FROM test_fn(:CODE);


This produces a proper recordset and can be used like stored procedures.

I wonder if you did

Code: Select all

select * FROM test1(:par1, :par2);

you might get better results.

Regards,

Ben


Return to “Other 6.x Versions”

Who is online

Users browsing this forum: No registered users and 1 guest