Обсуждение: psqlODBC text length with no records
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) 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 shouldby longchar type(Memo). And any unknown as longchar type(Unknownsizes=2) 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 -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Hiroshi Saito Sent: Tuesday, May 13, 2014 9:20 AM To: Michael Paquier; Hiroshi Inoue Cc: Heikki Linnakangas; Anna Gershnik; pgsql-odbc@postgresql.org Subject: Re: [ODBC] psqlODBC msi download for Heartbleed Hi all. Sorry very late reaction. Ooops, Openssl version that is provided was 1.0.1f....:-( I would release to adjust with Inoue-san soon. (2014/05/13 19:32), Michael Paquier wrote: > On Tue, May 13, 2014 at 6:11 PM, Heikki Linnakangas > <hlinnakangas@vmware.com> wrote: >> On 05/12/2014 10:01 PM, Anna Gershnik wrote: >>> >>> Hi, >>> >>> I was wondering when a new download with a fix for Heartbleed >>> vulnerability will be available? >>> The latest available here is from March 8, 2014: >>> http://www.postgresql.org/ftp/odbc/versions/msi/ >> >> >> Oh, I didn't realize we ship the SSL libraries in the installer. >> Yeah, we should update those ASAP. >> >> Hiroshi, others, can we put out a new release, with updated SSL >> libraries, please? > Yeah, that's definitely something we should do. > >> Where exactly do the libraries come from, i.e. what is the upstream >> project? If there's anything I can do to help, let me know. > I was planning to have a look at the msvc specs of odbc soon... But > could not really find the time. Btw, I would assume that the openssl > libs come from here: > http://slproweb.com/products/Win32OpenSSL.html > -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc
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 shouldby 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.klaver@aklaver.com
On 05/14/2014 05:51 AM, Barry Bell wrote:
> 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
whenno 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)
I do not have an ODBC test set up available right now.
FYI initcap returns TEXT:
http://www.postgresql.org/docs/9.3/interactive/functions-string.html
initcap(string) text
So what happens if you cast to varchar?:
SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 05/14/2014 06:11 AM, Barry Bell wrote: > Use the cast work fine but we are using the initcap function in a lot of places > And do not want to cast to every place. > (Plus we use a lot of other functions that may have the same issue). > > You said the initcap function return "text", > Is this a "short" text like a varchar > Or a long text like a clob,memo etc? This is text vs varchar(n) or char(n). I show the 'n' because in Postgres a varchar without a length is equivalent to text. See here for all the details: http://www.postgresql.org/docs/9.3/interactive/datatype-character.html > > 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 > > -- Adrian Klaver adrian.klaver@aklaver.com
On 05/14/2014 06:31 AM, Barry Bell wrote:
> I am referring the output of the odbc driver from SQL statement:
> SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
>
> You link only show the date type in postgres and I am asking about the driver output.
This is what you asked:
"You said the initcap function return "text",
Is this a "short" text like a varchar
Or a long text like a clob,memo etc?"
and I answered that question.
> Are you using this statement with the ODBC driver?
No, as I said previously I do not have an ODBC set up available at the
moment.
> (Output from the ODBC driver may be different format then for pg admin etc).
>
> SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
> I am expending a "character varying" or "varchar" output from the sql statement
> But the I getting the postgres "text"
> If the SQL is changed to return records, SELECT initcap('ABCDEF')::varchar vv FROM dual
> The output is varchar
>
> So
> SELECT initcap('ABCDEF')::varchar vv FROM dual
> Works, returns varchar
> So the issues is with:
> SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
> Returning "text", sqllongvar a different type then above,
> Does not change with any unknownsize or datetype setting in ODBC driver.
As I recall this is eventually ending up in a VFP application. So where
are you actually seeing the different behavior, in the app or somewhere
before the app?
>
> Barry Bell
--
Adrian Klaver
adrian.klaver@aklaver.com
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.klaver@aklaver.com
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
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
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). To follow up, from here: http://foxcentral.net/microsoft/WhatsNewInVFP9_Chapter09.htm "Because the DBF structure hasn’t changed significantly (although the new data types have some impact on the structure; see the “How the new data types affect DBF files” section later in this chapter), Varchar fields are actually stored as fixed length fields. However, when you access a Varchar field, the value appears to be trimmed rather than padded with spaces to the length of the field." but "By default, text boxes pad values with spaces to the maximum width of the field. To suppress that behavior, add “F” to the Format property of the Textbox. You should also set MaxLength to the width of the field to ensure the user can enter the maximum number of characters if necessary. To see an example of this, run TestVarcharInTextbox.SCX, type something like “test” in both text boxes, and click on the Results button. Because the second text box has “F” in its Format property, its content is trimmed while the content of the first one is not." > > > Barry Bell > Senior Developer/Analyst -- Adrian Klaver adrian.klaver@aklaver.com
On 05/14/2014 09:03 AM, Barry Bell wrote: > It has to be the Driver/Postgres because VFP does not determin the datatype > (The driver does). That does not preclude the possibility that it changes a data type to meet its view of the world given that data type handling varies greatly from on data system to another. > > 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) From here: http://psqlodbc.projects.pgfoundry.org/docs/config.html Max Varchar The maximum precision of the Varchar and BPChar(char[x]) types. The default is 254 which actually means 255 because of the null terminator. Note, if you set this value higher than 254, Access will not let you index on varchar columns! From here: Varchar can be used the same way Character can: you can index on it (the index keys are padded with spaces to the length of the field, because index keys must be a fixed length), it accepts null values, and Varchar fields have a limit of 254 characters in a table or cursor. If it where me I would set B0 = 253 to see what happens. > > > Barry Bell -- Adrian Klaver adrian.klaver@aklaver.com
On 05/14/2014 09:08 AM, Barry Bell wrote: > None of the below information helps because we are taking the output cursor > From the postgrest sql, putting it into a VFP grid and displaying to the user. > > With theB0(Max varchar)=254 setting, Postgres for lengths 1-254 returns a "char" > In vfp, when displayed to the user in a grid, the value of field is shown > > With theB0(Max varchar)=254 setting, Postgres for lengths=0 or null returns a "Memo" > In vfp, when displayed to the user in a grid, "Memo" is shown in the field instead of the value > > With theB0(Max varchar)=254 setting, Postgres for length over 256 returns a "Memo" > In vfp, when displayed to the user in a grid, "Memo" is shown in the field instead of the value > We only have some code after the sql that sorts the cursor and VFP is unable to sort on the MEMO fields. > > Our issue is when Postgres is returning a fireld with 0 or null length, > The driver is not using the B0(Max varchar) setting. Well you could always try Parse Statements: http://psqlodbc.projects.pgfoundry.org/docs/config.html > > Barry Bell > Senior Developer/Analyst -- Adrian Klaver adrian.klaver@aklaver.com
On 05/14/2014 09:08 AM, Barry Bell wrote: > None of the below information helps because we are taking the output cursor > From the postgrest sql, putting it into a VFP grid and displaying to the user. > > With theB0(Max varchar)=254 setting, Postgres for lengths 1-254 returns a "char" > In vfp, when displayed to the user in a grid, the value of field is shown > > With theB0(Max varchar)=254 setting, Postgres for lengths=0 or null returns a "Memo" > In vfp, when displayed to the user in a grid, "Memo" is shown in the field instead of the value > > With theB0(Max varchar)=254 setting, Postgres for length over 256 returns a "Memo" > In vfp, when displayed to the user in a grid, "Memo" is shown in the field instead of the value > We only have some code after the sql that sorts the cursor and VFP is unable to sort on the MEMO fields. > > Our issue is when Postgres is returning a fireld with 0 or null length, > The driver is not using the B0(Max varchar) setting. Meant to add, it might be helpful to turn on logging in the ODBC manager to see what the driver is actually doing. > > Barry Bell -- Adrian Klaver adrian.klaver@aklaver.com
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
whenno 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.4versions 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
becausethe cache size may not be a good representation of the longest column in the cache.
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.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@postgresql.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
shouldby 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.klaver@aklaver.com
Use the cast work fine but we are using the initcap function in a lot of places
And do not want to cast to every place.
(Plus we use a lot of other functions that may have the same issue).
You said the initcap function return "text",
Is this a "short" text like a varchar
Or a long text like a clob,memo etc?
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 8:58 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 05:51 AM, Barry Bell wrote:
> 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
whenno 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)
I do not have an ODBC test set up available right now.
FYI initcap returns TEXT:
http://www.postgresql.org/docs/9.3/interactive/functions-string.html
initcap(string) text
So what happens if you cast to varchar?:
SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
I am referring the output of the odbc driver from SQL statement:
SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
You link only show the date type in postgres and I am asking about the driver output.
Are you using this statement with the ODBC driver?
(Output from the ODBC driver may be different format then for pg admin etc).
SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
I am expending a "character varying" or "varchar" output from the sql statement
But the I getting the postgres "text"
If the SQL is changed to return records, SELECT initcap('ABCDEF')::varchar vv FROM dual
The output is varchar
So
SELECT initcap('ABCDEF')::varchar vv FROM dual
Works, returns varchar
So the issues is with:
SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
Returning "text", sqllongvar a different type then above,
Does not change with any unknownsize or datetype setting in ODBC driver.
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 9:23 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 06:11 AM, Barry Bell wrote:
> Use the cast work fine but we are using the initcap function in a lot
> of places And do not want to cast to every place.
> (Plus we use a lot of other functions that may have the same issue).
>
> You said the initcap function return "text", Is this a "short" text
> like a varchar Or a long text like a clob,memo etc?
This is text vs varchar(n) or char(n). I show the 'n' because in Postgres a varchar without a length is equivalent to
text.See here for all the details:
http://www.postgresql.org/docs/9.3/interactive/datatype-character.html
>
> 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
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
My issue is with the output of the ODBC driver.
The data type of the output of the ODBC driver changes if you do not have records.
You answers are the data type out of postgres directly without the ODBC driver.
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)
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
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, May 14, 2014 9:50 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 06:31 AM, Barry Bell wrote:
> I am referring the output of the odbc driver from SQL statement:
> SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
>
> You link only show the date type in postgres and I am asking about the driver output.
This is what you asked:
"You said the initcap function return "text", Is this a "short" text like a varchar Or a long text like a clob,memo
etc?"
and I answered that question.
> Are you using this statement with the ODBC driver?
No, as I said previously I do not have an ODBC set up available at the moment.
> (Output from the ODBC driver may be different format then for pg admin etc).
>
> SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0 I am
> expending a "character varying" or "varchar" output from the sql
> statement But the I getting the postgres "text"
> If the SQL is changed to return records, SELECT
> initcap('ABCDEF')::varchar vv FROM dual The output is varchar
>
> So
> SELECT initcap('ABCDEF')::varchar vv FROM dual Works, returns varchar
> So the issues is with:
> SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0 Returning
> "text", sqllongvar a different type then above, Does not change with
> any unknownsize or datetype setting in ODBC driver.
As I recall this is eventually ending up in a VFP application. So where are you actually seeing the different behavior,
inthe app or somewhere before the app?
>
> Barry Bell
--
Adrian Klaver
adrian.klaver@aklaver.com
Currently, the setting B0=254 on the ODBC driver will,
If the string(text) length is 1-254 characters. returns a "varchar"
If the string(text) length is over 254 characters. returns a "text"
If the string(text) length is 0 (or null), it return a "text"(Anyway to change this part?)
The driver settings(B0,Unkown size, unkown type, seem to only apply when data is returned,
If no records are returned (with a length of 0 or null), it returns text, no which settings.
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 9:50 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 06:31 AM, Barry Bell wrote:
> I am referring the output of the odbc driver from SQL statement:
> SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0
>
> You link only show the date type in postgres and I am asking about the driver output.
This is what you asked:
"You said the initcap function return "text", Is this a "short" text like a varchar Or a long text like a clob,memo
etc?"
and I answered that question.
> Are you using this statement with the ODBC driver?
No, as I said previously I do not have an ODBC set up available at the moment.
> (Output from the ODBC driver may be different format then for pg admin etc).
>
> SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0 I am
> expending a "character varying" or "varchar" output from the sql
> statement But the I getting the postgres "text"
> If the SQL is changed to return records, SELECT
> initcap('ABCDEF')::varchar vv FROM dual The output is varchar
>
> So
> SELECT initcap('ABCDEF')::varchar vv FROM dual Works, returns varchar
> So the issues is with:
> SELECT initcap('ABCDEF')::varchar vv FROM dual WHERE 1=0 Returning
> "text", sqllongvar a different type then above, Does not change with
> any unknownsize or datetype setting in ODBC driver.
As I recall this is eventually ending up in a VFP application. So where are you actually seeing the different behavior,
inthe app or somewhere before the app?
>
> Barry Bell
--
Adrian Klaver
adrian.klaver@aklaver.com
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.klaver@aklaver.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@postgresql.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
configurationand 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
andtook a returned type of text and turned it into a memo. Knowing what Postgres returned for initcap led to the cast
tovarchar which solved that particular problem. I understand that may not be workable for the entire code, but at least
itshed 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.klaver@aklaver.com
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). 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
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
None of the below information helps because we are taking the output cursor From the postgrest sql, putting it into a VFP grid and displaying to the user. With theB0(Max varchar)=254 setting, Postgres for lengths 1-254 returns a "char" In vfp, when displayed to the user in a grid, the value of field is shown With theB0(Max varchar)=254 setting, Postgres for lengths=0 or null returns a "Memo" In vfp, when displayed to the user in a grid, "Memo" is shown in the field instead of the value With theB0(Max varchar)=254 setting, Postgres for length over 256 returns a "Memo" In vfp, when displayed to the user in a grid, "Memo" is shown in the field instead of the value We only have some code after the sql that sorts the cursor and VFP is unable to sort on the MEMO fields. Our issue is when Postgres is returning a fireld with 0 or null length, The driver is not using the B0(Max varchar) setting. 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 12:00 PM 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). To follow up, from here: http://foxcentral.net/microsoft/WhatsNewInVFP9_Chapter09.htm "Because the DBF structure hasn't changed significantly (although the new data types have some impact on the structure; seethe "How the new data types affect DBF files" section later in this chapter), Varchar fields are actually stored as fixedlength fields. However, when you access a Varchar field, the value appears to be trimmed rather than padded with spacesto the length of the field." but "By default, text boxes pad values with spaces to the maximum width of the field. To suppress that behavior, add "F" to theFormat property of the Textbox. You should also set MaxLength to the width of the field to ensure the user can enter themaximum number of characters if necessary. To see an example of this, run TestVarcharInTextbox.SCX, type something like "test" in both text boxes, and click on theResults button. Because the second text box has "F" in its Format property, its content is trimmed while the content ofthe first one is not." > > > Barry Bell > Senior Developer/Analyst -- Adrian Klaver adrian.klaver@aklaver.com
Parse statement(C0 in the connection string) has no effect on this. > With theB0(Max varchar)=254 setting, Postgres for lengths=0 or null returns a "Memo" > In vfp, when displayed to the user in a grid, "Memo" is shown in > the field instead of the value Any other settings that may help? -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Wednesday, May 14, 2014 12:19 PM 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 09:08 AM, Barry Bell wrote: > None of the below information helps because we are taking the output > cursor From the postgrest sql, putting it into a VFP grid and displaying to the user. > > With theB0(Max varchar)=254 setting, Postgres for lengths 1-254 returns a "char" > In vfp, when displayed to the user in a grid, the value of field > is shown > > With theB0(Max varchar)=254 setting, Postgres for lengths=0 or null returns a "Memo" > In vfp, when displayed to the user in a grid, "Memo" is shown in > the field instead of the value > > With theB0(Max varchar)=254 setting, Postgres for length over 256 returns a "Memo" > In vfp, when displayed to the user in a grid, "Memo" is shown in > the field instead of the value We only have some code after the sql that sorts the cursor and VFP is unable to sort onthe MEMO fields. > > Our issue is when Postgres is returning a fireld with 0 or null > length, The driver is not using the B0(Max varchar) setting. Well you could always try Parse Statements: http://psqlodbc.projects.pgfoundry.org/docs/config.html > > Barry Bell > Senior Developer/Analyst -- Adrian Klaver adrian.klaver@aklaver.com
On 05/15/2014 05:30 AM, Barry Bell wrote: > Parse statement(C0 in the connection string) has no effect on this. > >> With theB0(Max varchar)=254 setting, Postgres for lengths=0 or null returns a "Memo" >> In vfp, when displayed to the user in a grid, "Memo" is shown in >> the field instead of the value > > Any other settings that may help? I would say not, mainly because I suspect that since changing settings has not changed anything, this is not a driver issue. There is the possibility that the driver and out and out bug, but lack of any other reports would seem to indicate otherwise. Right now what we know is that you push SQL on one end and you get a change in a GUI widget on the other. Testing has shown Postgres is consistent in what it returns from initcap (which is the function at issue). Tweaking the psql ODBC has shown no effect. The data is then taken up by a GUI which exhibits different behavior depending on the SQL output. What we have not seen so far is the actual output from the ODBC driver before VFP gets it, in order to nail down where the type changes. So have had a chance to run with ODBC logging enabled to capture the driver output? > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com