Re: 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