[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 #3 by Christian FAURE:

Thanks for review this problem,

this is the test code:
  Print "-----------"
  FBConnect() ' connect to database
  Db.Debug = True ' enable debug messages
  Try rs = fb.Exec("select current_date from RDB$DATABASE") 'this select always return one row with current date
  If Error.Code Then
    Print "Error";; Error.Text;; Error.Code
  Endif
  If rs.Available Then
    Print "Result Available!"
    Print rs[0]
  Else
    'when GetRecordCount give 0 rows, then rs.Available is false
    Print "Result not Available"
  Endif
  Print "-----------"

with unmodified source (gambas 3.9.1 - 2.11 from openSuse 42.1) the console show:

-----------
gb.db.odbc: 0xb6da38: select current_date from RDB$DATABASE
gb.db.odbc.GetRecordCount: Success, count=0
gb.db.odbc: -> 0 rows
Result not Available
-----------

i'm sure this select return one row.
then i've modified the odbc component (source from gambas site, v 3.9.1), function do_query like this 

/* Internal function to implement the query execution */
static int do_query(DB_DATABASE *db, const char *error, ODBC_RESULT ** res, const char *qtemp, int nsubst, ...)
{

#ifdef ODBC_DEBUG_HEADER
fprintf(stderr,"[ODBC][%s][%d]\n",__FILE__,__LINE__);
fprintf(stderr,"\tdo_query db %p, ODBC_result res %p, db->handle %p, query = '%s'\n", db, res, db->handle, query);
fflush(stderr);
#endif

	va_list args;
	ODBC_CONN *handle = (ODBC_CONN *)db->handle;
	SQLRETURN retcode= SQL_SUCCESS;
    SQLRETURN retcodecnt= SQL_SUCCESS;
	ODBC_RESULT * odbcres;
	const char *query;
	int i;

	if (nsubst)
	{
		va_start(args, nsubst);
		if (nsubst > 3)
			nsubst = 3;
		for (i = 0; i < nsubst; i++)
			query_param[i] = va_arg(args, char *);

		query = DB.SubstString(qtemp, 0, query_get_param);
	}
	else
		query = qtemp;

	if (DB.IsDebug())
		fprintf(stderr, "gb.db.odbc: %p: %s\n", handle, query);

	GB.AllocZero(POINTER(&odbcres), sizeof(ODBC_RESULT));

	/* Allocate the space for the result structure */

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, handle->odbcHandle, &odbcres->odbcStatHandle);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		GB.Error("Cannot allocate statement handle");
		return retcode;
	}

	retcode = SQLSetStmtAttr(odbcres->odbcStatHandle, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_SCROLLABLE, 0);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		odbcres->Cursor_Scrollable = SQL_FALSE;
	}
	else odbcres->Cursor_Scrollable = SQL_TRUE;

	odbcres->Function_exist = handle->FetchScroll_exist;

	/* Execute the query */
	retcode = SQLExecDirect(odbcres->odbcStatHandle, (SQLCHAR *) query, SQL_NTS);
	if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO))
	{
		SQLFreeHandle(SQL_HANDLE_STMT, odbcres->odbcStatHandle);
		GB.Error("Error while executing the statement");
		return retcode;
	}

	if (res)
	{
		/*retcode = SQLRowCount(odbcres->odbcStatHandle, &odbcres->count);
		if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, odbcres->odbcStatHandle);
			GB.Error("Unable to retrieve row count");
			return retcode;
		}*/
        retcodecnt = SQLRowCount(odbcres->odbcStatHandle, &odbcres->count);
		if (retcodecnt != SQL_SUCCESS && retcodecnt != SQL_SUCCESS_WITH_INFO)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, odbcres->odbcStatHandle);
			GB.Error("Unable to retrieve row count");
			return retcodecnt;
		}
		
		//odbcres->count = GetRecordCount(odbcres->odbcStatHandle, odbcres->Cursor_Scrollable);
		if (DB.IsDebug())
			fprintf(stderr, "gb.db.odbc: -> %d rows\n", (int)odbcres->count);
		*res = odbcres;
	}
	else
	{

		SQLFreeHandle(SQL_HANDLE_STMT, odbcres->odbcStatHandle);
		GB.Free(POINTER(&odbcres));
	}

	return retcode;
}

then the same gambas code print this on the console:
-----------
gb.db.odbc: 0x889a28: select current_date from RDB$DATABASE
gb.db.odbc: -> 1 rows
Result Available!
10/13/2016 00:00:00
-----------

maybe we can add some flag in database driver like "enable_optimizations" to enable the new GetRecordCount and/or other non odbc compliant tuned features?

Best regards.