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

I apologize, I didn't see the beginning of this thread. If you have a problem
with Visual FoxPro accessing remote datas, you can ask your question on the
profox mailing list. Just look at www.leafe.com  It's free and VERY efficient.

Best regards

--
Jean MAURICE
Grenoble - France - Europe
www.j-maurice.fr
www.atoutfox.org
www.aedtf.org

On Fri, May 25, 2012 at 8:47 AM, Ilija Vidoevski
<ilija.vidoevski@yahoo.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@gmail.com>
> To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
> Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.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.vidoevski@yahoo.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@gmail.com>
> To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
> Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.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.vidoevski@yahoo.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

I want to migrate from MS Sqlserver 2008 r2 express to Postgresql

I use postgresql 9.1.3

I create this stored function (on Postgres side)

CREATE OR REPLACE FUNCTION public.a_getkonta_table (

  mkontoa char,

  mkontob char

)

RETURNS TABLE (

  kontochar,

  naziv char

) 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;

I call this function with this code (from Microsoft VisualFoxPro side)

mkontoa = '000000000'

mkontob = '099999999'

If SQLExec(handle,"select * from a_getkonta_table(?mkontoa,?mkontob)",'temp101') < 0

   Aerror(laError)

   Messagebox(laError[1,2])

   return

ENDIF

Returned result set contains correct row numbers but fields length is 254.

Structure of table konta is

Konto char(9)

Naziv char(45)

.
.
.
RETURNS TABLE (  konto char,  naziv char ) AS ...

RETURNS TABLE (  konto char(9),  naziv char(45)  ) AS ...

Luiz,

How can I chage to your code :

RETURNS TABLE (  konto char(9),  naziv char(45)  )

I try  to execute sql script

CREATE OR REPLACE FUNCTION a_getkonta_table1(IN mkontoa character, IN mkontob character) RETURNS TABLE(konto character (9), naziv character (45)) AS
$BODY$
begin    return query   SELECT    konta.konto,   konta.naziv   FROM konta    WHERE konta.konto between mkontoa and mkontob;

end ;
$BODY$ LANGUAGE plpgsqlVOLATILE COST 100 ROWS 1000;

but after that I gotRETURNS TABLE(konto character, naziv character) AS
Ilija

CREATE OR REPLACE FUNCTION a_getkonta_table(IN mkontoa character, IN mkontob character) RETURNS SETOF record AS
$BODY$
begin    return query   SELECT    konta.konto,   konta.naziv   FROM konta    WHERE konta.konto between mkontoa and mkontob;

end ;
$BODY$ LANGUAGE plpgsqlVOLATILE

CREATE OR REPLACE FUNCTION a_getkonta_table(IN mkontoa character, IN mkontob character) RETURNS SETOF a_getkonta_table_type  AS
...

then we now can use the query without "AS (...)" clausule:

select * from a_getkonta_table( ?mkontoa , ?mkontob )

Hope this help.

Luiz K. Matsumura