Re: 09.03.0100 cursor failures on various architectures

On 02/26/2014 03:19 AM, Inoue, Hiroshi wrote:
> (2014/02/26 1:56), Heikki Linnakangas wrote:
>> I was thinking of something like below for the docs:
>>
>> ----
>> Don't use SQL_C_xxx. They are not part of the SQL/CLI specification, and
>> hence not reliably portable across platforms.
>
> Why does using SQL_C_XXXX lose portablity?

SQL_C_xxx is a Microsoft-only extension, so it's not guaranteed to work
with all SQL/CLI implementations. In practice, unixODBC defines
SQL_C_xxx too, so in practice an application using them will work.

I think you're advocating for this:

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

From a readability point of view, IMHO that's just bizarre. The
SQLBindCall call is claiming to the driver that the variable is of type
"long", by specifying SQL_C_LONG. But in reality, the variable is of
type SQLINTEGER. You can argue that SQL_C_LONG doesn't mean that the
variable is of type "long", but "SQLINTEGER", but that's just bizarre.
The name SQL_C_LONG very clearly says "long", just like SQL_C_SHORT says
"short".

It works on Windows, because sizeof(long) == sizeof(SQLINTEGER) on
Windows, and it works with unixODBC because unixODBC defines SQL_C_LONG
as 32-bits regardless of the actual width of "long". But it's not a good
way to write an application. Why use the ODBC extension SQL_C_LONG
instead of the SQL_INTEGER type code specified by SQL/CLI? SQL_INTEGER
is more clear, and as a bonus, it also complies with SQL/CLI.

So while the combination of SQL_C_LONG type code and SQLINTEGER variable
works, I would not recommend that. The SQL_INTEGER type code and
SQLINTEGER variable combination is more clear.

> Even though SQL/CLI is a standard, ODBC also has been a (defact?)
> standard which is older than CLI and widely used.
> They are different standard.
> We should begin a project e.g. pgsqlcli when we are to stop
> using SQL_C_XXXX.

ODBC is a superset of SQL/CLI [1]. An application written for SQL/CLI
works with ODBC libraries and drivers. But not the other way round; an
application that's written to the ODBC spec does not necessarily work
with other SQL/CLI implementations.

The driver already works with both ODBC and SQL/CLI, so there's no need
to start a new project for that.

Of course, if you don't care about portability of your application, you
can write it relying purely on ODBC, and use SQL_C_LONG type code and
"long" variables. But if you want to maximize portability, you should
stick to the SQL/CLI spec and use SQL_INTEGER type code and SQLINTEGER
variables.

> > Instead, use the ODBC
>> variable data types, like SQLINTEGER and SQLBIGINT, and the
>> corresponding type codes (e.g. SQL_INTEGER and SQL_BIGINT).
>>
>> For example, the following code is not portable:
>>
>> long empID;
>> ...
>> SQLBindCol(stmt, 1, SQL_C_LONG, (SQLPOINTER) &empID,
>> sizeof(long), &indicator);
>>
>> Instead, do this:
>>
>> SQLINTEGER empID;
>> ...
>> SQLBindCol(stmt, 1, SQL_INTEGER, (SQLPOINTER) &empID,
>> sizeof(SQLINTEGER), &indicator);
>
> Though the code works well, the code is wrong as an ODBC application
> in principle.
> The code works as expected simply because SQL_INTEGER has the same
> value as SQL_C_LONG has fortunately.

That is not an accident. The ODBC specification is a superset of the
SQL/CLI specification. An application written for the SQL/CLI
specification also works with ODBC, so for maximum portability you
should use the SQL/CLI type codes (SQL_INTEGER etc.).

[1] http://msdn.microsoft.com/en-us/library/ms712622%28v=vs.85%29.aspx

- Heikki