SEO

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailtogsql-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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster