Re: psqlODBC text length with no records

It has to be the Driver/Postgres because VFP does not determin the datatype
(The driver does).

With the B0=254(Max Varchar) in the connection string of the ODBC driver is suppose convert
The "text" in postgres to varchar is the length is less then 254.
The issue here is the length is 0 or null, this setting is ignored and returns "text".

Can you provider anymore information on driver setting B0 (Max Varchar)


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.klaver@aklaver.com]
Sent: Wednesday, May 14, 2014 11:38 AM
To: Barry Bell; Hiroshi Saito; Michael Paquier; Hiroshi Inoue
Cc: Heikki Linnakangas; Anna Gershnik; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] psqlODBC text length with no records

On 05/14/2014 08:29 AM, Barry Bell wrote:
> Sending the SQL through the postgres driver to a Postgres ver 9
> server, where it runs, I see the data types in the client software
> that is calling the ODBC driver (in this case VFP, foxpro).

So you are not really looking at the output of the ODBC driver but of VFP.

Are you sure VFP is not doing the type casting?

The reason I ask is demonstrated below where initcap on the Postgres side consistently returns text.

aklaver@test=> \d varchar_test
       Table "public.varchar_test"
  Column |       Type        | Modifiers
--------+-------------------+-----------
  fld_1  | character varying |
  fld_2  | character varying |

aklaver@test=> CREATE TABLE test_1 as select initcap(fld_1), fld_2 from varchar_test ;


aklaver@test=> \d test_1
           Table "public.test_1"
  Column  |       Type        | Modifiers
---------+-------------------+-----------
  initcap | text              |
  fld_2   | character varying |


aklaver@test=> CREATE TABLE test_2 as select initcap(fld_1), fld_2 from
varchar_test where 1=0;

aklaver@test=> \d test_2
           Table "public.test_2"
  Column  |       Type        | Modifiers
---------+-------------------+-----------
  initcap | text              |
  fld_2   | character varying |


aklaver@test=> CREATE TABLE test_3 as select initcap('ABCD');

aklaver@test=> \d test_3


    Table "public.test_3"


  Column  | Type | Modifiers
---------+------+-----------
  initcap | 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
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Wednesday, May 14, 2014 11:27 AM
> To: Barry Bell; Hiroshi Saito; Michael Paquier; Hiroshi Inoue
> Cc: Heikki Linnakangas; Anna Gershnik; pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] psqlODBC text length with no records
>
> On 05/14/2014 07:29 AM, Barry Bell wrote:
>> 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),
>
> Where are you determining the data types?
>
> In other words you run the SQL where and you see the output and data type where?
>
>
>>
>>
>>
>> Barry Bell
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com