unixODBC, PostgreSQL 7.3 + ODBC V3 drivers?

Dear unixODBC and psqlodbc maintainers,

I'm trying to use unixODBC with the latest (CVS) psqlodbc library
with PostgreSQL 7.3.

This is all working fine, but, as of 7.3 PostgreSQL returns
aggregate types (e.g. SUMs, COUNTs) as INTEGER8, and so these
results are (correctly) mapped to string types by the ODBC V2 driver.

On Windows NT, using the experimental psqlodbc V3 driver, things
work properly and I get numbers back. On Linux, however, compiling
the experimental psqlodbc V3 driver results in problems. (I did
this by defining ODBCVER to be 0x0351 in config.h)

When I try to connect to the DB, I get the following error:

> Error: Sql-Database-Data-Error id HY092[28] : [unixODBC]ColAttribute for this type not implemented yet

The relevant place in the SQL log file indicates:

> [ODBC][31769][SQLColAttributes.c][230]
> Entry:
> Statement = 0x80723c8
> Column Number = 1
> Field Identifier = 0
> Character Attr = 0x807dc98
> Buffer Length = 4
> String Length = 0x805cd96
> Numeric Attribute = 0x805cd90
> [ODBC][31769][SQLColAttributes.c][557]
> Exit:[SQL_ERROR]
> DIAG [HY092] [unixODBC]ColAttribute for this type not implemented yet

I have verified that the same call (SQLColAttributes) is made, with
the same arguments (Column Number = 1, Field Identifier = 0), and
returns the correct values (0,19) on NT.

This is with unixODBC v 2.2.4 (same behaviour with all previous versions).

I don't rightly know if this is a problem with unixODBC or with
psqlodbc. Since they are (presumably) the same psqlodbc sources on NT
and linux, module some #defines, and the same client application
making identical calls, it seems to me to me more probable that it's a
unixODBC problem, but I'm sending this message to both interested
parties anyhow. Perhaps someone can help me shed some light on this
issue?

Does anyone have a working ODBC V3 driver on unix for PostgreSQL at
the moment?

Thanks,
--
Alain Picard
Memetrics

Alain Picard wrote:
> Dear unixODBC and psqlodbc maintainers,
>
> I'm trying to use unixODBC with the latest (CVS) psqlodbc library
> with PostgreSQL 7.3.
>
> This is all working fine, but, as of 7.3 PostgreSQL returns
> aggregate types (e.g. SUMs, COUNTs) as INTEGER8, and so these
> results are (correctly) mapped to string types by the ODBC V2 driver.
>
> On Windows NT, using the experimental psqlodbc V3 driver, things
> work properly and I get numbers back. On Linux, however, compiling
> the experimental psqlodbc V3 driver results in problems. (I did
> this by defining ODBCVER to be 0x0351 in config.h)

Hi,

Not trying to duck any problems, but I don't think this is a unixODBC
problem as the message is from the driver, but if you do find otherwise
tell me and I will fix.

--
Nick Gorham
Easysoft Limited http://www.easysoft.com

Alain Picard wrote:
>
> Dear unixODBC and psqlodbc maintainers,
>
> I'm trying to use unixODBC with the latest (CVS) psqlodbc library
> with PostgreSQL 7.3.
>
> This is all working fine, but, as of 7.3 PostgreSQL returns
> aggregate types (e.g. SUMs, COUNTs) as INTEGER8, and so these
> results are (correctly) mapped to string types by the ODBC V2 driver.
>
> On Windows NT, using the experimental psqlodbc V3 driver, things
> work properly and I get numbers back. On Linux, however, compiling
> the experimental psqlodbc V3 driver results in problems. (I did
> this by defining ODBCVER to be 0x0351 in config.h)

How did you build the driver ?
Did you link odbcapi30.o, pgapi30.o and info30.o ?

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

>>>>> Hiroshi Inoue writes:

Hiroshi> How did you build the driver ?
Hiroshi> Did you link odbcapi30.o, pgapi30.o and info30.o ?

Yes. Initially I didn't, and got unresolved symbols.

What I did was:
* check things out from CVS
* update by libtool, aclocal, etc to the versions specified
in the README.unix file
* Issue the following commands
psqlodbc % cp /usr/share/aclocal/libtool.m4 .
psqlodbc % aclocal -I . -I $PGSRC/config
(PGSRC points to a checkout of the PostgreSQL 7.3 sources)
psqlodbc % ./configure --prefix=/tmp --with-unixodbc
edit config.h and add the line
#define ODBCVER 0x351
at the end of it
make install

Nick Gorham, maintainer of unixODBC, thinks the problem is in
psqlodbc; I tend to agree although I'm not sure I understand
what's going on. I got psqlodbc to spit out reams of logging
and debugging data, which I can send you, if you think it'll help.

The upshot right now is that in PGAPI_ColAttributes, the call
is falling through to the default case and raising an error;
it should return the value 19, which corresponds to the PG Type
"Name".

As I said; we've got this driver running fine under Windows
as a ODBC V3. driver.

I'm happy to be your remote debugger and to try things (or patches)
if you have any suggestions.

--
Alain Picard
Memetrics

Alain Picard wrote:
>
> >>>>> Hiroshi Inoue writes:
>
> Hiroshi> How did you build the driver ?
> Hiroshi> Did you link odbcapi30.o, pgapi30.o and info30.o ?
>
> Yes. Initially I didn't, and got unresolved symbols.
>
> What I did was:
> * check things out from CVS
> * update by libtool, aclocal, etc to the versions specified
> in the README.unix file
> * Issue the following commands
> psqlodbc % cp /usr/share/aclocal/libtool.m4 .
> psqlodbc % aclocal -I . -I $PGSRC/config
> (PGSRC points to a checkout of the PostgreSQL 7.3 sources)
> psqlodbc % ./configure --prefix=/tmp --with-unixodbc
> edit config.h and add the line
> #define ODBCVER 0x351
> at the end of it
> make install
>
> Nick Gorham, maintainer of unixODBC, thinks the problem is in
> psqlodbc; I tend to agree although I'm not sure I understand
> what's going on. I got psqlodbc to spit out reams of logging
> and debugging data, which I can send you, if you think it'll help.

I checked unixODBC sources a little. ISTM unixODBC checks
the existence of the function SQLColAttributes and if it
exists, it calls SQLColAttributes( not SQLColAttribute)
passing through the Field Identifier parameter.
Is it right ?

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

>>>>> Hiroshi Inoue writes:
Hiroshi> I checked unixODBC sources a little. ISTM unixODBC checks
Hiroshi> the existence of the function SQLColAttributes and if it
Hiroshi> exists, it calls SQLColAttributes( not SQLColAttribute)
Hiroshi> passing through the Field Identifier parameter.
Hiroshi> Is it right ?

I _think_ that's right, but I'll leave it to Nick or Peter to confirm.

--
Alain Picard
Memetrics

Alain Picard wrote:
>
> >>>>> Hiroshi Inoue writes:
> Hiroshi> I checked unixODBC sources a little. ISTM unixODBC checks
> Hiroshi> the existence of the function SQLColAttributes and if it
> Hiroshi> exists, it calls SQLColAttributes( not SQLColAttribute)
> Hiroshi> passing through the Field Identifier parameter.
> Hiroshi> Is it right ?
>
> I _think_ that's right, but I'll leave it to Nick or Peter to confirm.

I've just committed a change(odbcapi.c) to cvs.
Please try.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

>>>>> Hiroshi Inoue writes:

Hiroshi> I've just committed a change(odbcapi.c) to cvs.
Hiroshi> Please try.

Hiroshi, it works like a charm. There's still a bug remaining,
however, when connecting to a postgresql 7.3 back end; it's
not handling the BIGINTs properly. Here is a patch, for convert.c.

You need to remove the #ifdef WIN32 in the code below, because
we now want to execute this code on unix as well:

#if (ODBCVER >= 0x0300) && defined(ODBCINT64)
/* #ifdef WIN32 */
case SQL_C_SBIGINT:
len = 8;
if (bind_size > 0)
*(SQLBIGINT *) ((char *) rgbValue + (bind_row * bind_size)) = _atoi64(neut_str);
else
*((SQLBIGINT *) rgbValue + bind_row) = _atoi64(neut_str);
break;

case SQL_C_UBIGINT:
len = 8;
if (bind_size > 0)
*(SQLUBIGINT *) ((char *) rgbValue + (bind_row * bind_size)) = _atoi64(neut_str);
else
*((SQLUBIGINT *) rgbValue + bind_row) = _atoi64(neut_str);
break;

/* #endif */ /* WIN32 */
#endif /* ODBCINT64 */

And then you need to add

#ifndef WIN32
#define _atoi64 atoll
#endif

somewhere at the top of that file because _atoi64 doesn't exist under linux.
If you're not using GCC, or are on a system which doesn't support
`atoll', this will break; you can adjust the #define dance as you wish
to support other platforms, if you know how they behave. (I don't).

Also, the code above seems to be wrong for SQL_C_UBIGINT; I doubt that
_atoi64 (or atoll) will return an unsigned long long for a number
above 2^63.

Thank you so much for your _invaluable_ help.

--
Alain Picard
Memetrics

Hiroshi Inoue wrote:
> Alain Picard wrote:
>
>>>>>>>Hiroshi Inoue writes:
>>
>>Hiroshi> I checked unixODBC sources a little. ISTM unixODBC checks
>>Hiroshi> the existence of the function SQLColAttributes and if it
>>Hiroshi> exists, it calls SQLColAttributes( not SQLColAttribute)
>>Hiroshi> passing through the Field Identifier parameter.
>>Hiroshi> Is it right ?
>>
>>I _think_ that's right, but I'll leave it to Nick or Peter to confirm.

Ok, this is my take.

SQLColAttributes is a ODBC 2 call, its depreciated in ODBC 3, so if a
app calls SQLColAttributes, the DM will call SQLColAttributes in the
driver (if it exists), otherwise it will call SQLColAttribute.

If the app calls SQLColAttribute and the driver supports SQLColAttribute
then its passed into the driver. If the driver only has SQLColAttributes
then the DM will map some ODBC 3 values to their ODBC 2 version (if they
differ).

Its made a bit stranger in that the value that SQLGetFunctions returns
is the SAME for the two calls, so the only way the DM can tell which the
driver has if by the functions exported by the shared lib.

Does that help ?

--
Nick Gorham
Easysoft Limited http://www.easysoft.com

> -----Original Message-----
> From: Nick Gorham [mailto:nick(at)easysoft(dot)com]
>
> Hiroshi Inoue wrote:
> > Alain Picard wrote:
> >
> >>>>>>>Hiroshi Inoue writes:
> >>
> >>Hiroshi> I checked unixODBC sources a little. ISTM unixODBC checks
> >>Hiroshi> the existence of the function SQLColAttributes and if it
> >>Hiroshi> exists, it calls SQLColAttributes( not SQLColAttribute)
> >>Hiroshi> passing through the Field Identifier parameter.
> >>Hiroshi> Is it right ?
> >>
> >>I _think_ that's right, but I'll leave it to Nick or Peter
> to confirm.
>
> Ok, this is my take.
>
> SQLColAttributes is a ODBC 2 call, its depreciated in ODBC 3, so if a
> app calls SQLColAttributes, the DM will call SQLColAttributes in the
> driver (if it exists), otherwise it will call SQLColAttribute.
>
> If the app calls SQLColAttribute and the driver supports
> SQLColAttribute
> then its passed into the driver. If the driver only has
> SQLColAttributes
> then the DM will map some ODBC 3 values to their ODBC 2
> version (if they
> differ).
>
> Its made a bit stranger in that the value that SQLGetFunctions returns
> is the SAME for the two calls, so the only way the DM can
> tell which the
> driver has if by the functions exported by the shared lib.
>
> Does that help ?

Thanks. I already committed a change to remove ODBC2.x functions
for the ODBC 3 driver. According to Alain, it solved the
SQLColAttribte(s)
problem at least. However, there exists a BIGINT handling problem.
What I provided is bigint handling under Windows only. Unfortunately
I'm not good at portability issue under *nix.

regards,
Hiroshi Inoue