Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group