Обсуждение: Windows, ODBC drivers and strange points ...

Hello,

I'm trying to use PostgreSQL via ODBC under Windows
and IBM VisualAge Smalltalk. Normally ODBC works
pretty well, but I've found two problems with the
ODBC driver of PostgreSQL, which makes the whole
thing not  working:


a) All column names are returned from the database
  in lowercase and not in the form the client send
  then when doing the "create table ..." statement.

  Which leads to the problem, that other frameworks
  on top of it do not find their columns do make the
  oo-rdbms mapping stuff.

b) All strings returned by the ODBC driver are defined
  in a buffer, which is offered to the ODBC subsystem
  as a string doubled in size - though I told the
  databaase NOT to create a UNICODE db.

  An example: a column has the result type char(15). The
  ODBC drivers put the result of this column into a
  memory with a size of 30 characters.

  The ODBC subsystem now thinks: fine - I've a character
  column with size 30 and creates a string 30 characters
  long and one gets 15 corrects characters and 15 "0" (null)
  bytes.

I've never seen this behaviour with other ODBC drivers
I've used so far, therefore I think, this is pretty
strange behaviour.

I'm using the 8.0.3 database and the 8.0.1.2 drivers.

Any ideas ???


Marten Feldtmann

Hi Marten,

i am using  PostgreSQL 8.0.3 an  ODBC 8.0.1.2 with VisualWorks 7. I am
using a UNICODE database (with the same client connect datatype) and all
works fine because of the result data aspect. It seems, that other than
UNICODE make problems in any case.

Josef Springer

Marten Feldtmann wrote:

> Hello,
>
> I'm trying to use PostgreSQL via ODBC under Windows
> and IBM VisualAge Smalltalk. Normally ODBC works
> pretty well, but I've found two problems with the
> ODBC driver of PostgreSQL, which makes the whole
> thing not  working:
>
>
> a) All column names are returned from the database
>  in lowercase and not in the form the client send
>  then when doing the "create table ..." statement.
>
>  Which leads to the problem, that other frameworks
>  on top of it do not find their columns do make the
>  oo-rdbms mapping stuff.
>
> b) All strings returned by the ODBC driver are defined
>  in a buffer, which is offered to the ODBC subsystem
>  as a string doubled in size - though I told the
>  databaase NOT to create a UNICODE db.
>
>  An example: a column has the result type char(15). The
>  ODBC drivers put the result of this column into a
>  memory with a size of 30 characters.
>
>  The ODBC subsystem now thinks: fine - I've a character
>  column with size 30 and creates a string 30 characters
>  long and one gets 15 corrects characters and 15 "0" (null)
>  bytes.
>
> I've never seen this behaviour with other ODBC drivers
> I've used so far, therefore I think, this is pretty
> strange behaviour.
>
> I'm using the 8.0.3 database and the 8.0.1.2 drivers.
>
> Any ideas ???
>
>
> Marten Feldtmann
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Marten Feldtmann
> Sent: 09 October 2005 12:04
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Windows, ODBC drivers and strange points ...
>
> Hello,
>
> I'm trying to use PostgreSQL via ODBC under Windows
> and IBM VisualAge Smalltalk. Normally ODBC works
> pretty well, but I've found two problems with the
> ODBC driver of PostgreSQL, which makes the whole
> thing not  working:
>
>
> a) All column names are returned from the database
>   in lowercase and not in the form the client send
>   then when doing the "create table ..." statement.
>
>   Which leads to the problem, that other frameworks
>   on top of it do not find their columns do make the
>   oo-rdbms mapping stuff.

No, table names are returns in the correct case:

    Successfully connected to DSN 'foo'.
SQLTables:
In:    StatementHandle = 0x003B18D8, CatalogName = SQL_NULL_HANDLE,
NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0,
    TableName = SQL_NULL_HANDLE, NameLength3 = 0, TableType =
SQL_NULL_HANDLE, NameLength4 = 0
Return:    SQL_SUCCESS=0

Get Data All:
"TABLE_QUALIFIER", "TABLE_OWNER", "TABLE_NAME", "TABLE_TYPE", "REMARKS"
<Null>, "public", "MiXeD_CaSe_TaBlE", "TABLE", ""
<Null>, "public", "UPPER_CASE_TABLE", "TABLE", ""
<Null>, "public", "lower_case_table", "TABLE", ""
3 rows fetched from 5 columns.

Are you sure you quoted the names when you created them? If not, the
server will have folded them to lower case.

> b) All strings returned by the ODBC driver are defined
>   in a buffer, which is offered to the ODBC subsystem
>   as a string doubled in size - though I told the
>   databaase NOT to create a UNICODE db.
>
>   An example: a column has the result type char(15). The
>   ODBC drivers put the result of this column into a
>   memory with a size of 30 characters.
>
>   The ODBC subsystem now thinks: fine - I've a character
>   column with size 30 and creates a string 30 characters
>   long and one gets 15 corrects characters and 15 "0" (null)
>   bytes.

With 3 bytes of data, (ie. The word 'foo') I get:

SQLGetData:
In:        Statementhandle = 0x003B18D8, ColumnNumber = 1,
TargetType = SQL_C_CHAR=1, TargetValuePtr = 0x000952D0, BufferLength =
600,
        StrLen_or_IndPtr = 0x00093E58
Return:    SQL_SUCCESS=0
Out:        *TargetValuePtr = "foo", *StrLen_or_IndPtr = 3

When I request an SQL_C_CHAR, or if I request a SQL_C_WCHAR:

SQLGetData:
In:        Statementhandle = 0x003B18D8, ColumnNumber = 1,
TargetType = SQL_C_WCHAR=-8, TargetValuePtr = 0x00097FF8, BufferLength =
600,
        StrLen_or_IndPtr = 0x0009F2F8
Return:    SQL_SUCCESS=0
Out:        *TargetValuePtr = "foo", *StrLen_or_IndPtr = 6

Regards, Dave.

Actually this is still a point I have to investigate. To made
all stuff working in a correct way I need another information
about how much bytes actually used ... or I need an information
that I have to deal with UNICODE stuff.

Within the ODBC driver interface I deal with cbLength and
cbPrecision (information delivered by the ODBC drivers). The
first one tells the interface, how much  bytes are reserved
for this column and cbPrecision tells me, what was the length
when executing the create statement.

Therefore under PostgreSQL it's:

For CHAR(15), cbLength is ALWAYS 30 and cbPrecision is always
15.

Both information are not enough - I need to know, how much of
cbLength of this buffer I must use to create the string (and
of course how to interpret the byte stream: single byte character
or doubule byte character) - or cbLength changes it's value
depending on the

Perhaps it would be better to have

CHAR(15) -> cbLength = 15, cbPrecision = 15 (if single-byte database)
CHAR(15) -> cbLength = 30, cbPrecision = 15 (if unicode database)

but there also seem to be other parameters within the ODBC
specs to get this information - I just have not found the overall
picture.

Marten

Josef Springer schrieb:

> Hi Marten,
>
> i am using  PostgreSQL 8.0.3 an  ODBC 8.0.1.2 with VisualWorks 7. I am
> using a UNICODE database (with the same client connect datatype) and
> all works fine because of the result data aspect. It seems, that other
> than UNICODE make problems in any case.
>
> Josef Springer
>
>

Dave Page schrieb:

>>
>>
>>a) All column names are returned from the database
>>  in lowercase and not in the form the client send
>>  then when doing the "create table ..." statement.
>>
>>  Which leads to the problem, that other frameworks
>>  on top of it do not find their columns do make the
>>  oo-rdbms mapping stuff.
>>
>>
>
>No, table names are returns in the correct case:
>
>    Successfully connected to DSN 'foo'.
>SQLTables:
>In:    StatementHandle = 0x003B18D8, CatalogName = SQL_NULL_HANDLE,
>NameLength1 = 0, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0,
>    TableName = SQL_NULL_HANDLE, NameLength3 = 0, TableType =
>SQL_NULL_HANDLE, NameLength4 = 0
>Return:    SQL_SUCCESS=0
>
>Get Data All:
>"TABLE_QUALIFIER", "TABLE_OWNER", "TABLE_NAME", "TABLE_TYPE", "REMARKS"
><Null>, "public", "MiXeD_CaSe_TaBlE", "TABLE", ""
><Null>, "public", "UPPER_CASE_TABLE", "TABLE", ""
><Null>, "public", "lower_case_table", "TABLE", ""
>3 rows fetched from 5 columns.
>
>Are you sure you quoted the names when you created them? If not, the
>server will have folded them to lower case.
>
>
>
This means, that under PostgreSQL one should in general quote
the column names when creating them ? Do I also have to quote
them in the SELECT statements ???

>>b) All strings returned by the ODBC driver are defined
>>  in a buffer, which is offered to the ODBC subsystem
>>  as a string doubled in size - though I told the
>>  databaase NOT to create a UNICODE db.
>>
>>  An example: a column has the result type char(15). The
>>  ODBC drivers put the result of this column into a
>>  memory with a size of 30 characters.
>>
>>  The ODBC subsystem now thinks: fine - I've a character
>>  column with size 30 and creates a string 30 characters
>>  long and one gets 15 corrects characters and 15 "0" (null)
>>  bytes.
>>
>>
>
>With 3 bytes of data, (ie. The word 'foo') I get:
>
>SQLGetData:
>In:        Statementhandle = 0x003B18D8, ColumnNumber = 1,
>TargetType = SQL_C_CHAR=1, TargetValuePtr = 0x000952D0, BufferLength =
>600,
>        StrLen_or_IndPtr = 0x00093E58
>Return:    SQL_SUCCESS=0
>Out:        *TargetValuePtr = "foo", *StrLen_or_IndPtr = 3
>
>When I request an SQL_C_CHAR, or if I request a SQL_C_WCHAR:
>
>SQLGetData:
>In:        Statementhandle = 0x003B18D8, ColumnNumber = 1,
>TargetType = SQL_C_WCHAR=-8, TargetValuePtr = 0x00097FF8, BufferLength =
>600,
>        StrLen_or_IndPtr = 0x0009F2F8
>Return:    SQL_SUCCESS=0
>Out:        *TargetValuePtr = "foo", *StrLen_or_IndPtr = 6
>
>Regards, Dave.
>
 Ok, this is true - but this is not my problem. How do I get to know, that
the source type is either SQL_C_CHAR or SQL_C_WCHAR - this is the problem.

I thought this would be possible with the COlumnDesc function - but this
seems not to be the case.

 By the way: what happens if you have UNICODE values in the buffer and
you want the target type SQL_C_CHAR ??? Is this allowed ? What code page
is then used ? The system code page or the application code page ?

 Marten

Marten Feldtmann wrote:

> This means, that under PostgreSQL one should in general quote
> the column names when creating them ? Do I also have to quote
> them in the SELECT statements ???

Exactly. However, this is a database issue, not a driver issue. Quoting
http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS:

> Identifier and key word names are case insensitive. Therefore
>
>UPDATE MY_TABLE SET A = 5;
>
> can equivalently be written as
>
>uPDaTE my_TabLE SeT a = 5;
>
>[...]
>
>Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For
example,the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different
fromthese three and each other. 
>

Regards
     Christian

> -----Original Message-----
> From: Marten Feldtmann [mailto:m.feldtmann@t-online.de]
> Sent: 10 October 2005 12:11
> To: Dave Page
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Windows, ODBC drivers and strange points ...
>
> This means, that under PostgreSQL one should in general quote
> the column names when creating them ? Do I also have to quote
> them in the SELECT statements ???

Yes, if you want to use mixed case.

>  Ok, this is true - but this is not my problem. How do I get
> to know, that
> the source type is either SQL_C_CHAR or SQL_C_WCHAR - this is
> the problem.
>
> I thought this would be possible with the COlumnDesc function
> - but this
> seems not to be the case.

Yeah, that'll just tell you that its SQL_LONGVARCHAR or whatever. As a
general rule, apps will just ask for whatever they want and the driver
will conver as best it can.

In your case, it'd probably be easier to use the ANSI driver, then of
course you know there's no Unicode in sight.

>  By the way: what happens if you have UNICODE values in the buffer and
> you want the target type SQL_C_CHAR ??? Is this allowed ?
> What code page
> is then used ? The system code page or the application code page ?

It converts to the system ANSI (CP_ACP) using WideCharToMultiByte
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/intl/u
nicode_2bj9.asp) iirc.

Regards, Dave

Actually this is still a point I have to investigate. To made
all stuff working in a correct way I need another information
about how much bytes actually used ... or I need an information
that I have to deal with UNICODE stuff.

Within the ODBC driver interface I deal with cbLength and
cbPrecision (information delivered by the ODBC drivers). The
first one tells the interface, how much  bytes are reserved
for this column and cbPrecision tells me, what was the length
when executing the create statement.

Therefore under PostgreSQL it's:

For CHAR(15), cbLength is ALWAYS 30 and cbPrecision is always
15.

Both information are not enough - I need to know, how much of
cbLength of this buffer I must use to create the string (and
of course how to interpret the byte stream: single byte character
or doubule byte character) - or cbLength changes it's value
depending on the

Perhaps it would be better to have

CHAR(15) -> cbLength = 15, cbPrecision = 15 (if single-byte database)
CHAR(15) -> cbLength = 30, cbPrecision = 15 (if unicode database)

but there also seem to be other parameters within the ODBC
specs to get this information - I just have not found the overall
picture.

Marten

Josef Springer schrieb:

Hi Marten,

i am using  PostgreSQL 8.0.3 an  ODBC 8.0.1.2 with VisualWorks 7. I am using a UNICODE database (with the same client connect datatype) and all works fine because of the result data aspect. It seems, that other than UNICODE make problems in any case.

Josef Springer

mit freundlichen Grüssen,
Josef Springer
(Geschäftsleitung)

 -- the software company --

Orlando-di-Lasso Str. 2
D-85640 Putzbrunn

Perhaps you might remember my tests with
PostgreSQL and IBm VisualAge Smalltalk.

I mentioned my problems with column lengths
and stuff like this.

 I still testing (from time to time) with
the 8.0.3 database and use the newest
8.01.00.03 libpq ODBC drivers and now
the situation is getting worse :-((((

 IBM Smalltalk is now not able to get the
correct column size for strings any more.

 I always get either 254 bytes or 508 bytes
as column size.

 We have a table like:

create table "TPERSON" ("ALLCSTVIEW" CHAR(1)
....

 Now IBM Smalltalk calls OdbcSQLDescribeCol
and gets 254 for this column as size in the
return parameter cbPrecision.

 And the alternate call via OdbcSQLColAttribute
with parameter Sql_Column_Lengthreturns  508.

 Actually this is true for ALL string columns

 The PostgreSQL drivers seem strange for me ...
I've done work with other database systems
via this ODBC library, but PostgreSQL does
not give me any real success ....

 If anyone has any help ....

 Marten

>
>
> b) All strings returned by the ODBC driver are defined
>  in a buffer, which is offered to the ODBC subsystem
>  as a string doubled in size - though I told the
>  databaase NOT to create a UNICODE db.
>
>  An example: a column has the result type char(15). The
>  ODBC drivers put the result of this column into a
>  memory with a size of 30 characters.
>
>  The ODBC subsystem now thinks: fine - I've a character
>  column with size 30 and creates a string 30 characters
>  long and one gets 15 corrects characters and 15 "0" (null)
>  bytes.
>
> I've never seen this behaviour with other ODBC drivers
> I've used so far, therefore I think, this is pretty
> strange behaviour.
>
> I'm using the 8.0.3 database and the 8.0.1.2 drivers.
>
> Any ideas ???
>
>
> Marten Feldtmann
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>


--
Marten Feldtmann - Germany - Software Development
Information regarding VA Smalltalk and DMS-system
"MSK - Mien Schrievkrom" at: www.schrievkrom.de

Here a partial answer. The reason was the special
ODBC driver libpq I used.

I installed the 08.01.0101 driver and now the base
routines work again.

Strange, that I get a difference ration of 3:1 between
columnLength and columnPresicion with string based
columns.

a char(1) returns the columnPrecision of 1, but a column
length of 3 which I do not understand.


Marten



Marten Feldtmann schrieb:

> Perhaps you might remember my tests with
> PostgreSQL and IBm VisualAge Smalltalk.
>
> I mentioned my problems with column lengths
> and stuff like this.
>
> I still testing (from time to time) with
> the 8.0.3 database and use the newest
> 8.01.00.03 libpq ODBC drivers and now
> the situation is getting worse :-((((
>
> IBM Smalltalk is now not able to get the
> correct column size for strings any more.
>
> I always get either 254 bytes or 508 bytes
> as column size.
>
> We have a table like:
>
> create table "TPERSON" ("ALLCSTVIEW" CHAR(1)
> ....
>
> Now IBM Smalltalk calls OdbcSQLDescribeCol
> and gets 254 for this column as size in the
> return parameter cbPrecision.
>
> And the alternate call via OdbcSQLColAttribute
> with parameter Sql_Column_Lengthreturns  508.
>
> Actually this is true for ALL string columns
>
> The PostgreSQL drivers seem strange for me ...
> I've done work with other database systems
> via this ODBC library, but PostgreSQL does
> not give me any real success ....
>
> If anyone has any help ....
>
> Marten
>
>
>


--
Marten Feldtmann - Germany - Software Development
Information regarding VA Smalltalk and DMS-system
"MSK - Mien Schrievkrom" at: www.schrievkrom.de

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Marten Feldtmann
> Sent: 16 November 2005 21:41
> To: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Windows, ODBC drivers and strange points ...
>
> Perhaps you might remember my tests with
> PostgreSQL and IBm VisualAge Smalltalk.
>
> I mentioned my problems with column lengths
> and stuff like this.
>
>  I still testing (from time to time) with
> the 8.0.3 database and use the newest
> 8.01.00.03 libpq ODBC drivers and now
> the situation is getting worse :-((((
>
>  IBM Smalltalk is now not able to get the
> correct column size for strings any more.
>
>  I always get either 254 bytes or 508 bytes
> as column size.
>
>  We have a table like:
>
> create table "TPERSON" ("ALLCSTVIEW" CHAR(1)
> ....
>
>  Now IBM Smalltalk calls OdbcSQLDescribeCol
> and gets 254 for this column as size in the
> return parameter cbPrecision.
>
>  And the alternate call via OdbcSQLColAttribute
> with parameter Sql_Column_Lengthreturns  508.
>
>  Actually this is true for ALL string columns
>
>  The PostgreSQL drivers seem strange for me ...
> I've done work with other database systems
> via this ODBC library, but PostgreSQL does
> not give me any real success ....
>
>  If anyone has any help ....

Why are you using such an old development snapshot of the driver?

Regards, Dave.

> -----Original Message-----
> From: Marten Feldtmann [mailto:m.feldtmann@t-online.de]
> Sent: 17 November 2005 17:05
> To: Dave Page
> Subject: Re: [ODBC] Windows, ODBC drivers and strange points ...
>
> The same test I desribed in the first posting
>
> We have a table like:
>
> create table "TPERSON" ("ALLCSTVIEW" CHAR(1))
>
> and does a select *  from  ... and opens
> a cursor for the result and queries all
> columns the following way:
>
> Now IBM Smalltalk calls OdbcSQLDescribeCol
> and gets 1 for this column as size in the
> return parameter cbPrecision.
>
> And with an alternate call it queries
> via OdbcSQLColAttribute  with parameter
> Sql_Column_Length  and this returns 3.

Well, I don't know how Smalltalk is renaming things, but if I use
SQL_DESC_LENGTH, I get 1 as expected. I do get 3 is if I use
SQL_DESC_OCTET_LENGTH. The spec says:

----
This SQLINTEGER record field contains the length, in bytes, of a
character string or binary data type. For fixed-length character or
binary types, this is the actual length in bytes. For variable-length
character or binary types, this is the maximum length in bytes. This
value always excludes space for the null-termination character for
implementation descriptors and always includes space for the
null-termination character for application descriptors. For application
data, this field contains the size of the buffer.
----

psqlODBC is returning the size using pgtype_transfer_octet_length()
which it goes to great lengths to calculate. I'm not sure I fully
understand why it's doing what it does, and frankly I'm wary of changing
it as it was written by Hiroshi Inoue who definitely understood the spec
better than I do!!

Can anyone else comment on this?

Regards, Dave.