execute stored function from ms visual foxpro

From: Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: execute stored function from ms visual foxpro
Date: 2012-05-05 11:00:32
Message-ID: 1336215632.78953.YahooMailNeo@web113105.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 ?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Frank Foerster 2012-05-07 10:09:06 64 bit XID / Wraparound / Streaming Replication
Previous Message Binand Sethumadhavan 2012-05-05 09:07:50 Re: WHERE clause with 40+ conditions