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

Thank's WBL
I change table structure and input parametars, but theproblem still exist.
When I sent SQL script from Foxpro side, result data types are OK. Strange!
It is not problem to fix this on Foxpro side, but many times thousands of rows are returned from server.
I want almost everything to do on server side.
I try within same function to create temp table on server side and alter fields structure, but nothig is changed.
Maybe this is ODBC problem ?
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 10:41 AM
Subject: Re: [NOVICE] return values(table) from stored function from MS visual foxpro

The result data type is not related to your existing table. You're using the datatype char for both result columns.
That type is padded with spaces.
I would always advise to use varchar(n) or "text" unless you need the result to be exacly some length, like 254.

However you seem to be using numbers, why don't you use the "integer" data type?

http://www.postgresql.org/docs/9.1/static/datatype.html

HTH

WBL

On Thu, May 24, 2012 at 12:48 PM, Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com> wrote:

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 (
>  konto char,
>  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)
> 
>I use this connection string:
>Connstr="DRIVER={PostgreSQL odbc driver(unicode)};server=localhost;Port=5432;Database=mydatabase; Uid=postgres;Pwd=mypwd;UseServerSidePrepare=1;MaxVarcharSize=254;UnknownsAsLongVarchar=0;TextAsLongVarchar=0;"
>OS Window 7
profesional
> 
>Any
solutions ?

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth