Get indicator value for unbound columns without calling SQLGetData()

Question

Is there any generic solution to get the NULL/NOTNULL status from a column without using SQLBindCol()?

For BLOB/MEMO columns in an ODBC result set, some drivers provides limited support for the SQLGetData() function. For example:

If there are two BLOB columns 3 and 4, the data from column 3 must be fully read before reading 4. And after reading 4, it's not possible to read 3.

So if an abstraction layer provides something like resultset.column(4).isnull(), the isnull() implementation must use SQLGetData() to check for SQL_NULL_DATA. But after this, someone can't use resultset.column(3).read(buf) - or similar functions (okay, you can read all blobs/memo after SQLFetch() step by step into a buffer to avoid any issues but such buffering is very bad inside an abstraction layer...).

One solution is to bind a small buffer and a SQLLEN value as indiciator via SQLBindCol(). SQLFetch() would return SQL_NULL_DATA in the SQLLEN indiciator value if the value (BLOB, etc.) is null, without the need of calling SQLGetData().

But some drivers like Microsoft Native Client ODBC Driver can't use SQLGetData() on bounded columns. So if the indicator is non-NULL, it's not possible to read the data with SQLGetData().

The MS documentation says that it is possible to bind just an indiciator pointer via SQLBindCol and leave dataptr NULL. I've tried this without success. The indiciator gets never modified (initialized with SQL_NULL_DATA). I've tried to set the indicator pointer with SQLSetDescField() manually for the columns (inc desc rec counter, etc.) but nothing changed. It seems that the driver only fills the indicator ptr when a data ptr is available.

Is there any other method the get null/not null status for columns?

Answer 1

Since no one else has attempted any answer I'll give you this:

I don't believe there is a solution that fits your requirement as stated however I think what you believe to be a legitimate requirement is perhaps misplaced.

Just to be clear here we are talking about the SQLGetInfo driver replies for SQL_GETDATA_EXTENSIONS which specify whether columns may be retrieved with SQLGetData out of order, in addition to being bound or not and as you say the MS SQL Server ODBC driver does not allow out of order and does not allow bound columns.

There are plenty of wrappers in various languages around ODBC and many of them have hit the same problem. The problem goes away if you agree on 2 things 1) an application should not select data it does not intend to read 2) an application should specify how much of long column data it wants and whether truncating that data is ok. Once you agree on that it is ok for you to read at least some of the data.

For example, in Perl, DBI has LongReadlen and LongTrunkOk the former specifies the maximum amount of data to read from a long column and the latter says whether truncation is allowable or not. If you look at other languages with ODBC wrappers they pretty much all do the same thing for a good reason.

Sorry if this is not the answer you were looking for.