Re: execute stored function from ms visual foxpro

On Sat, May 5, 2012 at 6:00 AM, Ilija Vidoevski
<ilija(dot)vidoevski(at)yahoo(dot)com> wrote:
> I am beginning with Postgres database.
> I create simple table (konta) with two fields
> - customer_id char(9)
> - customer_name char(45)
>
> I made this SQL script
>
> TEXT TO tSQLCmd NOSHOW
>
> Select konta.customer_id,
> konta.customer_name
from konta
> where konta.customer_id between ?mvar1 and ?mvar2
> ORDER By konta.customer_id ;
>
> ENDTEXT
>
>
> When I sent this script form VisualFoxPro 9 SP2 side, returned set from
> Postgres is OK.
>
> I create this stored function (on Postgres side)
>
> CREATE OR REPLACE FUNCTION public.getkonta (
> mvar1 char,
> mvar2 char
> )
> RETURNS SETOF public.konta AS
> $body$
> SELECT *
> FROM konta WHERE konta.customer_id between $1 and $2;
> $body$
> LANGUAGE 'sql'
> VOLATILE
> CALLED ON NULL INPUT
> SECURITY INVOKER
> COST 100 ROWS 1000;
>
>
> I call this function with this code (from VFP side)
>
> mkonto1 = '000000000'
> mkonto2 = '099999999'
>
> If SQLExec(handle,"select getkonta(mkonto1 :=?mkonto1, mkonto2:=?mkonto2);",
> 'temp101') < 0
> Aerror(laError)
> Messagebox(laError[1,2])
> return
> ENDIF
>
> Returned result set contains correct row numbers but with only one memo
> field.
>
> I use this connection string:
> Connstr="DRIVER={PostgreSQL odbc
> driver(unicode)};server=localhost;Port=5432;Database=mydatabase;
> Uid=postgres;Pwd=mypwd;"
>
> What is wrong in my code ?

adjust your function call like so:

select * from getkonta(mkonto1 :=?mkonto1, mkonto2:=?mkonto2);

in postgres, if you omit the fields like that and the function returns
> 1 columns, you'll get a record object back, not the list of fields.

merlin