Re: psqlODBC text length with no records

Behavior seems different if you get the text from table vs fixed value.

The select 'ABCDE' from dual return a text(longchar, memo),
The select initcap(varcharfield) from varchartable returns varchar
The select initcap(varcharfield) from varchartable where 1=0 returns text(longchar, memo),

Barry Bell
Senior Developer/Analyst
Logistics
Harte Hanks
1525 NW 3rd ST
Deerfield Beach FL, 33442
954-429-3771 Ext 267 office
954-281-1464 fax

hartehanks.com / linkedin / twitter / facebook

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Wednesday, May 14, 2014 10:16 AM
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/14/2014 06:56 AM, Barry Bell wrote:
> My issue is with the output of the ODBC driver.

The ODBC driver does not exist in isolation, it depends on information from the input end and outputs depending on configuration and the end point.

> The data type of the output of the ODBC driver changes if you do not have records.

That is the core issue and the one I am trying to narrow down the cause.
>
> You answers are the data type out of postgres directly without the ODBC driver.

Yet it already revealed information. When you ran initcap() without a cast the ODBC driver did what it was supposed to and took a returned type of text and turned it into a memo. Knowing what Postgres returned for initcap led to the cast to varchar which solved that particular problem. I understand that may not be workable for the entire code, but at least it shed light on what is going on.
>
> You need the run the sql through the ODBC driver (which you said you are not).
>
> Can someone else try the test sql through the ODBC driver?
> SELECT initcap(vcharfield) vv FROM table (returns varchar)

Actually, from your previous examples it does not unless you explicitly cast the output of initcap to varchar:

Example 1
"Try it yourself with this sql statement:

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

Will return the a sqlongvarchar(Memo,CLOB)"

Example 2
"SELECT initcap('ABCDEF')::varchar vv FROM dual Works, returns varchar"

> vs
> SELECT initcap(vcharfield) vv FROM table WHERE 1=0 Returns text
>
> Barry Bell
> Senior Developer/Analyst
> Logistics
> Harte Hanks
> 1525 NW 3rd ST
> Deerfield Beach FL, 33442
> 954-429-3771 Ext 267 office
> 954-281-1464 fax
>
> hartehanks.com / linkedin / twitter / facebook
>

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