[Gambas-user] [Gambas Bug Tracker] Bug #1013: gb.db.odbc new GetRecordCount mechanism seem not work with firebird database

[bugtracker at ...3416...]

http://gambaswiki.org/bugtracker/edit?object=BUG.1013&from=L21haW4-

Comment #12 by zxMarce:

Christian,

Well, it looks like my guess was correct. I patched the function so it now does as follows:

  1- Remember the current RowIndex
  2- Fetch the index of the first record (firstIndex)
  3- Fetch the index of the last record (lastIndex)
  4- Go back to the original RowINdex from step 1
  5- Return (lastIndex - firstIndex + 1) as rowcount.

This change yielded correct SELECT row count for MSSQL:

  gb.db.odbc: 0x84cc934: SELECT * FROM SampleTable ORDER BY Field1, Field2
  gb.db.odbc.GetRecordCount: First recno=1
  gb.db.odbc.GetRecordCount: Last recno=63
  gb.db.odbc.GetRecordCount: Record count=63
  gb.db.odbc: -> 63 rows

And also corrected the mishaps for FB, for both a SELECT and a SELECT COUNT(), and you can see that the first record is 0 and not 1 like in MSSQL:

  gb.db.odbc: 0x84cc934: SELECT * FROM Colors
  gb.db.odbc.GetRecordCount: First recno=0
  gb.db.odbc.GetRecordCount: Last recno=7
  gb.db.odbc.GetRecordCount: Record count=8
  gb.db.odbc: -> 8 rows

  gb.db.odbc: 0x84cc934: SELECT COUNT(*) FROM Colors
  gb.db.odbc.GetRecordCount: First recno=0
  gb.db.odbc.GetRecordCount: Last recno=0
  gb.db.odbc.GetRecordCount: Record count=1
  gb.db.odbc: -> 1 rows

The current problem I have now is that I get nice SEGFAULTs when I run an USE <someOtherDatabase> command on MSSQL:

  gb.db.odbc: 0x9db15ac: USE Redemption
  gb.db.odbc: SQLFetchScroll SQL_FETCH_FIRST
  gb.db.odbc: H:1:0:[FreeTDS][SQL Server]Fetch type out of range
  gb.db.odbc: -> -1 rows
  [SEGFAULT!]

So, the function is returning -1 as expected on failure (error occurs when trying to fetch the first record from a recordless command), so the SEGFAULT problem seems to be elsewhere and not in GetRecordCount().

If you want to add the patch yourself and recompile and use the new component, you can grab the following code and give it a try:

/* zxMarce: This is one way -hope there's an easier one- to retrieve a rowset
*  count for SELECT statements. Four steps (must have an scrollable cursor!):
*    1- Remember the current row.
*    2- Seek down to the last row in the rowset
*    3- Get the last row's index (recno)
*    4- Seek back to wherever we were at in step 1
*  20161110 zxMarce: Ok, it did not work that OK for Firebird; it looks like
*  the FB driver returns one-less than the record count (record count seems to
*  be zero-based), so we will instead do as follows, if we have a scrollable
*  recordset:
*    1- Remember the current row.
*    2- Seek up to the first row in the rowset
*    3- Get the first row's index (firstRecNo)
*    4- Seek down to the last row in the rowset
*    5- Get the last row's index (lastRecNo)
*    6- Seek back to wherever we were at in step 1
*    7- Return (lastRecNo - firstRecNo + 1).
*/
int GetRecordCount(SQLHANDLE stmtHandle, SQLINTEGER cursorScrollable)
{
	SQLRETURN retcode;              //ODBC call return values
	int formerRecIdx = 0;           //Where we were when this all started.
	SQLINTEGER myRecCnt = -1;       //Default for when there's no cursor.
	SQLINTEGER firstRecNo = 0;		//20161111 holder for 1st recno.
	SQLINTEGER lastRecNo = 0;		//20161111 holder for last recno.
	char mssg[128];					//Error reporting text.

	//Make sure the statement has a cursor
	if (!(stmtHandle && (cursorScrollable == SQL_TRUE)))
	{
		if (DB.IsDebug())
		{
			fprintf(stderr, "gb.db.odbc: Cannot do GetRecordCount()!\n");
		}
		return ((int) myRecCnt);
	}

	//Tell ODBC we won't be actually reading data (speeds process up).
	//SQL_ATTR_RETRIEVE_DATA = [SQL_RD_ON] | SQL_RD_OFF
	retcode = SQLSetStmtAttr(stmtHandle, SQL_ATTR_RETRIEVE_DATA, (SQLPOINTER) SQL_RD_OFF, 0);
	if (!SQL_SUCCEEDED(retcode))
	{
		reportODBCError("SQLSetStmtAttr SQL_ATTR_RETRIEVE_DATA",
				stmtHandle,
				SQL_HANDLE_STMT);
	}

	//Fetch current row's index so we can return to it when done.
	retcode = SQLGetStmtAttr(stmtHandle, SQL_ATTR_ROW_NUMBER, &formerRecIdx, 0, 0);
	if (!SQL_SUCCEEDED(retcode))
	{
		reportODBCError("SQLGetStmtAttr SQL_ATTR_ROW_NUMBER",
				stmtHandle,
				SQL_HANDLE_STMT);
	}

	//Try to get (back?) to the first record, abort if not possible.
	retcode = SQLFetchScroll(stmtHandle, SQL_FETCH_FIRST, (SQLINTEGER) 0);
	if (!SQL_SUCCEEDED(retcode))
	{
		reportODBCError("SQLFetchScroll SQL_FETCH_FIRST", stmtHandle, SQL_HANDLE_STMT);
		retcode = SQLSetStmtAttr(stmtHandle, SQL_ATTR_RETRIEVE_DATA, (SQLPOINTER) SQL_RD_ON, 0);
		return ((int) myRecCnt);	
	} else {
		//Fetch the first record's index
		retcode = SQLGetStmtAttr(stmtHandle, SQL_ATTR_ROW_NUMBER, &firstRecNo, 0, 0);
		if (SQL_SUCCEEDED(retcode))
		{
			//Inform first recno if in Debug mode and carry on
			if (DB.IsDebug())
			{
				fprintf(stderr, "gb.db.odbc.GetRecordCount: First recno=%d\n", (int) firstRecNo);
			}
		} else {
			//Could not fetch the first recno: Abort!
			reportODBCError("SQLFetchScroll SQL_ATTR_ROW_NUMBER (first recno)", stmtHandle, SQL_HANDLE_STMT);
			retcode = SQLSetStmtAttr(stmtHandle, SQL_ATTR_RETRIEVE_DATA, (SQLPOINTER) SQL_RD_ON, 0);
			return ((int) myRecCnt);
		}
	}

	//Advance the cursor to the last record.
	retcode = SQLFetchScroll(stmtHandle, SQL_FETCH_LAST, (SQLINTEGER) 0);
	if (SQL_SUCCEEDED(retcode))
	{

		//Fetch the last record's index
		retcode = SQLGetStmtAttr(stmtHandle, SQL_ATTR_ROW_NUMBER, &lastRecNo, 0, 0);
		if (SQL_SUCCEEDED(retcode))
		{
			//Set ret value
			if (DB.IsDebug())
			{
				fprintf(stderr, "gb.db.odbc.GetRecordCount: Last recno=%d\n", (int) lastRecNo);
			}

		} else {
			reportODBCError("SQLGetStmtAttr SQL_ATTR_ROW_NUMBER (last recno)", stmtHandle, SQL_HANDLE_STMT);
		}

		//Return cursor to original row.
		retcode = SQLFetchScroll(stmtHandle, SQL_FETCH_ABSOLUTE, (SQLINTEGER) formerRecIdx);
		//Since we have set the "do not read data" statement attribute, this call (may) return
		//code 100 (SQL_NO_DATA) but that's OK for our purposes of just counting rows.
		if (!SQL_SUCCEEDED(retcode) && (retcode != SQL_NO_DATA))
		{
			snprintf(mssg, sizeof(mssg), "SQLFetchScroll SQL_FETCH_ABSOLUTE (code %d) (rec %d)",
			 	 (int)retcode, formerRecIdx);
			reportODBCError(mssg, stmtHandle, SQL_HANDLE_STMT);
		}

	} else {
		reportODBCError("SQLFetchScroll SQL_FETCH_LAST", stmtHandle, SQL_HANDLE_STMT);
	}

	//Tell ODBC we will be reading data now.
	//SQL_ATTR_RETRIEVE_DATA = [SQL_RD_ON] | SQL_RD_OFF
	retcode = SQLSetStmtAttr(stmtHandle, SQL_ATTR_RETRIEVE_DATA, (SQLPOINTER) SQL_RD_ON, 0);
	if (!SQL_SUCCEEDED(retcode))
	{
		reportODBCError("SQLSetStmtAttr SQL_ATTR_RETRIEVE_DATA", stmtHandle, SQL_HANDLE_STMT);
	}

	myRecCnt = (lastRecNo - firstRecNo + 1);
	if (DB.IsDebug())
	{
		fprintf(stderr, "gb.db.odbc.GetRecordCount: Record count=%d\n", (int) myRecCnt);
	}

	return ((int) myRecCnt);

}

Hope that helps. Please give me some feedback as to usability/stability, as I'm afraid I still did not cover all bases.

zxMarce changed the state of the bug to: NeedsInfo.