SQLINTEGER

SQLINTEGER

Dear developers,

There is some mess with SQLINTEGER definition. MSDN
(http://msdn.microsoft.com/en-us/library/ms714556.aspx) defines
SQLINTEGER to be 'long int'. The unixODBC defines it just 'int' (in
sqltypes.h). This cause a lot of troubles on 64bit platforms. Most of
applications expect to see the 64bit 'long int' number and got 32-bit
'int' instead.

As one of the examples of problems: the PDO_ODBC driver in PHP 5.2.x
will end up with segmentation fault while accessing NULL columns over
unixODBC.

Re: SQLINTEGER

Suren A. Chilingaryan wrote:

>Dear developers,
>
>There is some mess with SQLINTEGER definition. MSDN
>(http://msdn.microsoft.com/en-us/library/ms714556.aspx) defines
>SQLINTEGER to be 'long int'. The unixODBC defines it just 'int' (in
>sqltypes.h). This cause a lot of troubles on 64bit platforms. Most of
>applications expect to see the 64bit 'long int' number and got 32-bit
>'int' instead.
>
>As one of the examples of problems: the PDO_ODBC driver in PHP 5.2.x
>will end up with segmentation fault while accessing NULL columns over
>unixODBC.
>
>thanks,
>Suren
>_______________________________________________
>unixODBC-support mailing list
>[hidden email]>http://mail.easysoft.com/mailman/listinfo/unixodbc-support>
>
>
>

Yep, and if you check, you will find that on windows sizeof(long int) ==
32 so the *nix equivilant would be incorrect if it was a 64 bit type,
there is a seperate 64 bit type in ODBC for this.

I suspect from your description, you are talking about the definition of
a indicator variable (given you mention NULL), and thats a different
thing, the type should be defined as a SQLLEN, and then you can choose
what length you want to use with that. Have a look in the archives for
several descrriptions of SQLLEN issues.

2.2.13pre will be out soon (if people could test the existing snapshot
and report it woild make my life a lot simpiler) and that will default
to sizeof( SQLLEN ) == 64 on 64 bit platforms, but there will still be
problems with apps and drivers that expect 32, so its not a problem
thats going to go away just yet.

Re: SQLINTEGER

Dear Nick Gorham,

> Yep, and if you check, you will find that on windows sizeof(long int) ==
> 32 so the *nix equivilant would be incorrect if it was a 64 bit type,
> there is a seperate 64 bit type in ODBC for this.
Yes, I know. As I understand, two interpretations of ODBC specification
are existing.
a) SQLINTEGER is defined as 'long int', no matter how much bits it is.
b) SQLINTEGER is defined as 32bit integer number

The really bad thing, that different ODBC implementations on Linux are
interpreting this specification in a different way. And this leads to
crashes. Actually, current versions of:
1. unixODBC defines it 'int'
2. libiodbc (http://www.iodbc.org/) defines it 'signed long'
3. OpenLink iODBC (http://openlinksw.com/) defines it 'signed int'

> I suspect from your description, you are talking about the definition of
> a indicator variable (given you mention NULL), and thats a different
> thing, the type should be defined as a SQLLEN, and then you can choose
> what length you want to use with that. Have a look in the archives for
> several descrriptions of SQLLEN issues.
Actually, the problem is in SQLBindCol. It is actually performed by
CLBindCol function (from cur/SQLBindCol.c). The last parameter is
'SQLINTEGER *strlen_or_ind' in which the size of data in column is
returned. If NULL value is stored in column, '-1' is returned.

So php odbc driver supplies pointer 64bit long int, which is treated by
unixODBC as 32bit int pointer. On little-endian architectures it is
perfectly OKey for non-negative numbers. But if '-1' returned, it is
received by php as 4294967295. That obviously causes segmentation fault.

By the way just noticed. unicODBC 2.2.12 in 'sql.h' defines this
parameter to SQLLEN*
SQLRETURN SQL_API SQLBindCol(....., SQLLEN *StrLen_or_Ind)
but actual performing function is using SQLINTEGER*
SQLRETURN CLBindCol(......, SQLINTEGER *strlen_or_ind)

> 2.2.13pre will be out soon (if people could test the existing snapshot
> and report it woild make my life a lot simpiler) and that will default
> to sizeof( SQLLEN ) == 64 on 64 bit platforms, but there will still be
> problems with apps and drivers that expect 32, so its not a problem
> thats going to go away just yet.
OK. Clear. If 2.2.13 pre will use SQLLEN instead of SQLINTEGER in
CLBindCOL definition and SQLLEN would be a 64bit number, the problem
will go away. Thaks a lot, I will check CVS.

Re: SQLINTEGER

Suren A. Chilingaryan wrote:

>Dear Nick Gorham,
>
>
>
>>Yep, and if you check, you will find that on windows sizeof(long int) ==
>>32 so the *nix equivilant would be incorrect if it was a 64 bit type,
>>there is a seperate 64 bit type in ODBC for this.
>>
>>
>Yes, I know. As I understand, two interpretations of ODBC specification
>are existing.
> a) SQLINTEGER is defined as 'long int', no matter how much bits it is.
> b) SQLINTEGER is defined as 32bit integer number
>
>The really bad thing, that different ODBC implementations on Linux are
>interpreting this specification in a different way. And this leads to
>crashes. Actually, current versions of:
>1. unixODBC defines it 'int'
>2. libiodbc (http://www.iodbc.org/) defines it 'signed long'
>3. OpenLink iODBC (http://openlinksw.com/) defines it 'signed int'
>
>
>

Well, I can't comment on other projects choices, but SQLINTEGER IMHO is
always a signed 32bit type, it doesn't make sense any other way.

>>I suspect from your description, you are talking about the definition of
>>a indicator variable (given you mention NULL), and thats a different
>>thing, the type should be defined as a SQLLEN, and then you can choose
>>what length you want to use with that. Have a look in the archives for
>>several descrriptions of SQLLEN issues.
>>
>>
>Actually, the problem is in SQLBindCol. It is actually performed by
>CLBindCol function (from cur/SQLBindCol.c). The last parameter is
>'SQLINTEGER *strlen_or_ind' in which the size of data in column is
>returned. If NULL value is stored in column, '-1' is returned.
>
>

Yes, and if you look at what the type of that parameter is, you will see
its a SQLLEN, not a SQLINTEGER, the confusion and variation is related
to SQLLEN, not SQLINTEGER, if there are applications or drivers that use
SQLINTEGER for that parameter, they are wrong, and should be changed.

>So php odbc driver supplies pointer 64bit long int, which is treated by
>unixODBC as 32bit int pointer. On little-endian architectures it is
>perfectly OKey for non-negative numbers. But if '-1' returned, it is
>received by php as 4294967295. That obviously causes segmentation fault.
>
>By the way just noticed. unicODBC 2.2.12 in 'sql.h' defines this
>parameter to SQLLEN*
> SQLRETURN SQL_API SQLBindCol(....., SQLLEN *StrLen_or_Ind)
>but actual performing function is using SQLINTEGER*
> SQLRETURN CLBindCol(......, SQLINTEGER *strlen_or_ind)
>
>
>

Yes, thats the bug in the application.

>
>
>>2.2.13pre will be out soon (if people could test the existing snapshot
>>and report it woild make my life a lot simpiler) and that will default
>>to sizeof( SQLLEN ) == 64 on 64 bit platforms, but there will still be
>>problems with apps and drivers that expect 32, so its not a problem
>>thats going to go away just yet.
>>
>>
>OK. Clear. If 2.2.13 pre will use SQLLEN instead of SQLINTEGER in
>CLBindCOL definition and SQLLEN would be a 64bit number, the problem
>will go away. Thaks a lot, I will check CVS.
>
>

Well, if the application uses SQLLEN, then as long as both PHP and
unixODBC are built the same way (whichever one) then the problem goes
away, the root of the problem is the out of date definition in PHP from
what you are saying.