Why unixODBC SQLTables is always returning an SQLRowCount of 4294967295?

Question

With the following code I always got that value on Oracle databases

SQLHSTMT hStmt = SQL_NULL_HANDLE;
SQLRETURN sret;
SQLLEN rowCount = 0;

const SQLHDBC conn = this->getConnection();
if (conn == NULL)
{
    ret = false;
}

SQLAllocHandle(SQL_HANDLE_STMT, conn, &hStmt);
std::string metaTableName="meta_table";

sret=SQLTables( hStmt,
           NULL, 0, /* no specific catalog */
           NULL, 0, /* no specific schema */
           (SQLCHAR *)metaTableName.c_str(), SQL_NTS,
           NULL, 0); /* no specific type - table or view */

if ((sret != SQL_SUCCESS) && (sret != SQL_SUCCESS_WITH_INFO))
{
    std::cout << "Problem retrieving " << metaTableName << " table from database!" << std::endl);
    ret= false;
}

SQLRowCount(hStmt,&rowCount);
std::cout <<std::endl << "Rows: " <<rowCount<<std::endl ;

No error messages so no apparent cause of this behaviour

Using oracle libs: oracle-instantclient11.2-odbc-11.2.0.1.0-1.x86_64

Any ideas about the reasons of such a behaviour?

score 1 · Answer 1 · answered Jul 20 '12 at 20:29

The number 4294967295 is 2³²-1. Said differently, it is the Two's complement of -1 for a 32-bit integer. Either way, in binary that means every bit is set to "1". Somewhere, it is attempting to interpret the signed value -1 with an unsigned integer.

It's trying to reply with -1 to indicate that it cannot describe the row count. However, there is a data representation problem giving you a weird value. I would suspect this is a driver issue, or perhaps due to a 32-bit to 64-bit anomaly.

score 1 · Accepted Answer · answered Jul 23 '12 at 07:59

1

SQLRowCount returns the number of rows affected by an UPDATE, INSERT, or DELETE statement; an SQL_ADD, SQL_UPDATE_BY_BOOKMARK, or SQL_DELETE_BY_BOOKMARK operation in SQLBulkOperations; or an SQL_UPDATE or SQL_DELETE operation in SQLSetPos - the value being returned when you are not doing any of these can be ignored.

More information about SQLRowCount can be found the the Micrsoft ODBC documentation

answered Jul 23 '12 at 07:59

Richard Spencer

623
7
14

Well, SQLTables should return rows much like a select operation does, hence it should be expected to have a valid rowcount from it. In fact with the postgres driver the count is correctly returned as "1" when the table is present. – a1an Jul 23 '12 at 10:07
1

With great respect you will find a lot of ODBC drivers do not return anything other than -1 (for unknown) from SQLRowCount for select statements. You are getting -1. – bohica Jul 23 '12 at 12:06
That's it, getting the row count after an insert statement correctly gives "1", hence the issue was too much optimism in having the Oracle driver provide row count as the postgres one. – a1an Jul 23 '12 at 15:50
**a1an** : SQLRowCount should work exactly as described in the specification. If ODBC drivers do not follow that, that would be up to them. ODBC application developers should follow the specification also that way they know that they can point their application at any ODBC data source and not just ones that don't follow the specification. – Richard Spencer Jul 24 '12 at 07:12

score 1 · Answer 3 · answered Jul 23 '12 at 08:52

1

Adam Hawkes has given you a clue. I strongly suspect, the ODBC Driver you are using was built with SQLULEN 32 bits but your code and unixODBC were built with SQLULEN 64 bits. Either get another build of the driver or rebuild unixODBC with 32 bit SQLLEN/SQLULEN then rebuild your app.

answered Jul 23 '12 at 08:52

bohica

5,932
3
23
28

SQLRowCount cannot be called with an SQLULEN parameter, it gives me an invalid conversion from ‘SQLULEN*’ to ‘SQLLEN*’ if I use it. The expected parameter is a long int* – a1an Jul 23 '12 at 10:13
You are missing my point. I know SQLRowCount takes a pointer to an SQLLEN. What I'm saying is that you've compiled your code where SQLLEN AND SQLULEN are defined as 64 bit (8 byte) quantities BUT the ODBC Driver is only writing 32 bits (4 bytes) to them. Hence when the driver writes -1 (0XFFFFFFFF) into the first 4 bytes it looks like 4294967295. You could easily prove that by setting rowCount = 0x1111111111111111 then calling SQLRowCount and I bet you no longer get 4294967295. Read my reply again. – bohica Jul 23 '12 at 10:59

score 0 · Answer 4 · edited May 23 '17 at 12:27

I have had the same problem in another situation. It is due to:

byte-order and
storage size.

You used a storage size and order less and incompatible than what it actually needed. happens in 32/64-bit int/float size difference and unix-posix byte-ordering difference.

In my experience, I've been receiving a function call result as int(-4294967295) (note the negative sign, that's byte-ordering) all the time on a 32-bit CentOS server, while on my 64-bit Windows local environment I've been receiving the right result from function. Changing the type indicator of size to a bigger 64-bit compatible (in PHP from SQLT_INT to SQLT_LNG) solved the problem on 32-bit unix server, while maintained the correct result on 64-bit posix local environment.

My experience

Why unixODBC SQLTables is always returning an SQLRowCount of 4294967295?

4 Answers4