Re: 09.03.0100 cursor failures on various architectures

(2014/02/25 20:05), Heikki Linnakangas wrote:
> 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.

ISTM unixODBC team was only bothering about what SQLINTEGER is.
They were not bothering about what ODBC data type SQL_C_LONG
corresponds to.

  In fact, I'd argue that most applications are *not* written
> that way. For example, look at this code example from Microsoft:

We sometimes see such inappropriate examples in Mircorsoft document.
ODBC applications should use ODBC data type variables to insulate
the app from platform-specific requirements.

> 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.

Didn't SQL_C_LONG stand for SQLINTEGER anyway?

 > 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..

Unfortunately I'm not happy with your summary.

My point is simple.
Use ODBC data type variables instead of native C data ones when calling
ODBC APIs.

regards,
Hiroshi Inoue