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.
------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Gambas-user mailing list
Gambas-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gambas-user
