Thread: execute stored function from ms visual foxpro
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 ?
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 ?
On Sat, May 5, 2012 at 6:00 AM, Ilija Vidoevski <ilija.vidoevski@yahoo.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