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 ?