Re: return values(table) from stored function from MS visual foxpro
От | Ilija Vidoevski |
---|---|
Тема | Re: return values(table) from stored function from MS visual foxpro |
Дата | |
Msg-id | 1338014353.62492.YahooMailNeo@web113111.mail.gq1.yahoo.com обсуждение исходный текст |
Ответ на | Re: return values(table) from stored function from MS visual foxpro (Merlin Moncure <mmoncure@gmail.com>) |
Список | pgsql-novice |
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;
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
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@gmail.com>
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: Willy-Bas Loos <willybas@gmail.com>; "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.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.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
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
В списке pgsql-novice по дате отправления:
Предыдущее
От: Ilija VidoevskiДата:
Сообщение: Re: return values(table) from stored function from MS visual foxpro
Следующее
От: Victor Whiskey YankeeДата:
Сообщение: could not access file "$libdir/plpgsql": No such file or directory