Re: 09.03.0100 cursor failures on various architectures

On 02/25/2014 06:09 AM, Inoue, Hiroshi wrote:
> (2014/02/14 21:54), Heikki Linnakangas wrote:
>> Microsoft has a table of SQL_C_* codes and which C types they correspond
>> to (http://msdn.microsoft.com/en-us/library/ms714556%28v=vs.85%29.aspx)
>> but that's not taking into account other operating systems where the
>> widths of C integer types are different. We didn't explicitly discuss in
>> that psqlodbc mailing list thread if it's sane that SQL_C_LONG means a
>> 32-bit integer regardless of how wide the C "long" type actually is.
>
> I don't think so.
> Where do you find references to OS native C types in ODBC's spec?
> ODBC uses its own SQL types in its spec and each SQL_C_xxxxxxx
> stands for one of them not a native one.

See the page I linked to above. It has a table with a one-to-one-to-one
relationship between the SQL_C_xxx, ODBC C typedef, and the native C
type. For example, for SQL_C_LONG, it says that the corresponding ODBC
type is SQLINTEGER, which equals to C "long int".

Such a one-to-one-to-one relationship is clear as far as Microsoft is
concerned, as in Windows "long int" is always a 32-bit integer. From the
SQL_C_xxx names it's quite clear that SQL_C_LONG refers to the native C
type, "long". And as long as you're working in a Microsoft environment,
there's no confusion because sizeof(long) == sizeof(SQLINTEGER). But on
other platforms, it's a different story.

Looking at my draft copy of the SQL 2011 standard, "Part 3: Call-Level
Interface (SQL/CLI)", I don't see any mention of the SQL_C_* data types.
It was probably left out of the standard, precisely because it would've
caused confusion on non-Microsoft platforms.

> For 32bit integers, use SQL_C_LONG (SQLINTEGER).
> For 64bit integers, use SQL_C_BIGINT (SQLBIGINT).
> There's no type named SQLxxLONGxx.
>
> I can see the following line in sqlext.h provided by Microsoft or unixODBC.
>
> #define SQL_C_LONG SQL_INTEGER /* INTEGER
> */
>
> It means that those driver manager think SQL_C_LONG means SQLINTEGER.
> What meaning does it have to take different way from the driver manager
> whose APIs ODBC applications call?

Yeah, that's how unixODBC has resolved the situation, but it's not
obvious. In fact, I'd argue that most applications are *not* written
that way. For example, look at this code example from Microsoft:

http://technet.microsoft.com/en-us/library/aa177876%28v=sql.80%29.aspx

It does this:

long lEmpID;
...
SQLBindCol(hStmt, 1, SQL_C_LONG, (SQLPOINTER) &lEmpID, sizeof(long),
&pIndicators[0]);

That sample code will *not* work with unixodbc on a big-endian platform
where sizeof(long) == 8.

See also
http://mailman.unixodbc.org/pipermail/unixodbc-support/2008-June/001756.html.
According to that post, while unixODBC takes the stance that SQL_C_LONG
corresponds to a 32-bit SQLINTEGER, iodbc has taken a different view. On
iodbc, SQL_C_LONG means C native type "long", whether it's 32-bits or
64-bits wide. That's reasonable; thanks to that decision, the above
sample code would work on iodbc without modifications. (However, now
that I actually look at the iODBC code in their git repository, ISTM
they're also defining SQL_C_LONG to bit 32-bits, ie. the same as
unixODBC. Weird, maybe they changed their mind at some point..)

Just to be clear, I agree with the decision that SQL_C_LONG means a
32-bit type, regardless of sizeof(long). But it's not obvious! That
decision will break applications.

In summary:

1. Most applications are written with the assumption that SQL_C_LONG
means the native C data type "long". That's clearly what SQL_C_LONG was
supposed to mean when the ODBC specification was written, and that's
what Microsoft's sample code does.

2. That's *not* how psqlODBC or unixODBC interprets it. With unixODBC,
you should use the C data type "SQLINTEGER" with SQL_C_LONG.

3. For maximum portability, an application should avoid using SQL_C_xx.
Instead, always use SQL_INTEGER or SQL_BIGINT, with an SQLINTEGER or
SQLBIGINT variable, and cast to native C types.

We should document this somewhere, like in the FAQ..

- Heikki