Re: psqlODBC text length with no records

Tried every setting for unknown size
Abd every settubg fir date tyoe options.
and it always comes back as SQllongvarchar (Memo, CLOB).

The " Text as LongVarChar: PostgreSQL TEXT type is mapped to SQLLongVarchar, otherwise SQLVarchar." Is not working when no records are returned

It will work if the initcap function is not used.

Try it yourself with this sql statement:

SELECT initcap('ABCDEF') vv FROM dual WHERE 1=0

Will return the a sqlongvarchar(Memo,CLOB)

As per your document:

Data Type Options: affects how some data types are mapped:

Text as LongVarChar: PostgreSQL TEXT type is mapped to SQLLongVarchar, otherwise SQLVarchar.
Unknowns as LongVarChar: Unknown types (arrays, etc) are mapped to SQLLongVarChar, otherwise SQLVarchar
Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT

Unknown Sizes: This controls what SQLDescribeCol and SQLColAttributes will return as to precision for character data types (varchar, text, and unknown) in a result set when the precision is unknown. This was more of a workaround for pre-6.4 versions of PostgreSQL not being able to return the defined column width of the varchar data type.

Maximum: Always return the maximum precision of the data type.
Dont Know: Return "Don't Know" value and let application decide.
Longest: Return the longest string length of the column of any row. Beware of this setting when using cursors because the cache size may not be a good representation of the longest column in the cache.

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Tuesday, May 13, 2014 8:28 PM
To: Barry Bell; Hiroshi Saito; Michael Paquier; Hiroshi Inoue
Cc: Heikki Linnakangas; Anna Gershnik; pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] psqlODBC text length with no records

On 05/13/2014 07:04 AM, Barry Bell wrote:
> Hi:
> When calling PostgresODBC (ver 9.2.1) using the following settings
> for VFP(Foxpro)
> BI=2;TextAsLongVarchar=1;UnknownSizes=2;UseServerSidePrepare=1;B0=254;
> B7=1;C5=0;AB=8;" && Debug=1;B2=1
>
> Select initcap(name) cname from customers (where name is type varchar)
> If records are returned, Returns pname as the varchar of length of the
> field (varchar 30 in this case)

So I am going to assume you mean cname above.

> But if I run
> Select initcap(name) cname from customers where 1=0 (no records
> returns) If no records return, cname field as "Memo" (like a CLOB) or
> longvarchar
>
> In my connection string, I am setting B0=254 meaning any strings under 254 chracter should be varchar and over 255 should by longchar type(Memo).
> And any unknown as longchar type(Unknownsizes=2)

Well according to here:

http://psqlodbc.projects.pgfoundry.org/docs/config.html

you should not need to use Unknownsizes for 6.4+ versions of Postgres or if you do use Maximum.

>
> I cannot find any setting that will return the cname field as a
> varchar (short char), instead of a longchar type(Memo, CLOB)
>
> Any ideas?
>
> Barry Bell
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com