Re: return values(table) from stored function from MS visual foxpro

Hi Merilin,

(1) This is code in the POSTGRESQL function

CREATE OR REPLACE FUNCTION public.a_getkonta_table (
  mkontoa varchar,
  mkontob varchar
)
RETURNS TABLE (
  konto varchar,
  naziv varchar
) AS
$body$
begin
    return query
    SELECT
    konta.konto ,
    konta.naziv
    FROM konta
    WHERE konta.konto between mkontoa and mkontob;

end ;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

(2) This is the code to "call" postgresql function on FOXPRO side:

If SQLExec(handle,"select * from a_getkonta_table(?mkontoa,?mkontob)",'temp101') < 0
   Aerror(laError)
   Messagebox(laError[1,2])
   return
ENDIF

(3) In the table konta field konto is varchar(6) and naziv is varchar(45)

In returned set lengh of filed konto and field naziv is 254.

I got the same result when I change the type of fields in table in char and also change code in function.

Ilija

________________________________
From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com>
Cc: Willy-Bas Loos <willybas(at)gmail(dot)com>; "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Sent: Friday, May 25, 2012 8:32 PM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro

On Fri, May 25, 2012 at 8:47 AM, Ilija Vidoevski
<ilija(dot)vidoevski(at)yahoo(dot)com> wrote:
> Thank's
>
> I will try to find solution.
> If  I can not, I will have to stay on MS Sqlsrever 2008.
> Maybe MS front-end app and MS back-end app is best choice.
>
> Ilija
>
> From: Willy-Bas Loos <willybas(at)gmail(dot)com>
> To: Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com>
> Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
> Sent: Friday, May 25, 2012 3:01 PM
>
> Subject: Re: [NOVICE] return values(table) from stored function from MS
> visual foxpro
>
> wait, are you sure that you are running the right function?
> the function is defined by it's input fields(incl types), so
>    a_getkonta_table(mkontoa character varying, mkontob character varying)
> is a different function from
>    a_getkonta_table(mkontoa char, mkontob char)
> so you would have 2 functions.
>
>>Other fields contain data like name, adress ...
> Other fields?? Oh, you mean you have a key-value approach?
>
> i can't really say anything about odbc or foxpro
> there is a seperate list for odbc by the way:
> http://archives.postgresql.org/pgsql-odbc/
>
> cheers,
>
> WBL
>
>
> On Fri, May 25, 2012 at 2:48 PM, Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com>
> wrote:
>
> To varchar.
> I can not change this field to integer.
>
> Other fields contain data like name, adress (street, number), city, country
> etc... and must be char or varchar.
>
> I simply can not understand why when I retrive data from the stored function
> lenght of returnet fields based od char or varchar table field is 254 char.
>
> With this code:
> If SQLExec
>
> (Handle,"select * from konta",'kontaq') < 0
> Aerror(laError)
> Messagebox(laError[1,2])
> Endif
>
> lenght of returned fields are OK.
>
> Ilija
> From: Willy-Bas Loos <willybas(at)gmail(dot)com>
> To: Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com>
> Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
> Sent: Friday, May 25, 2012 12:58 PM
>
> Subject: Re: [NOVICE] return values(table) from stored function from MS
> visual foxpro
>
>
>
> On Fri, May 25, 2012 at 12:49 PM, Ilija Vidoevski
> <ilija(dot)vidoevski(at)yahoo(dot)com> wrote:
>
> I change table structure and input parametars, but the problem still exist.

did you also change the output from the function?

RETURNS TABLE (
  konto
char,
  naziv char
)

should be

RETURNS TABLE (
  konto varchar,
  naziv varchar
)

BTW, I've used foxpro front end + postgres backend before and I can
tell you it works well.

merlin