I am trying to get a count of rows in a table from an Access data base using ODBC. However, SQLRowCount only returns a count from UPDATE, INSERT or DELETE, I do not want to modify the database, simply get the number of records. The SQL statement SELECT COUNT will return the number of rows in a table but I am not sure how to obtain the result, anyone have the answer ?

// Build the query
invoke wsprintf,offset szQuery,"SELECT COUNT(ID) FROM %s",
add esp, 12
// Execute the query
invoke SQLExecDirect, , offset szQuery, SQL_NTS


Donkey
Posted on 2008-01-09 14:33:14 by donkey
Hi

I don't know the exact procedure using ODBC and those sql API's, but for MySql and PHP you could do it as follows, by using the mysql AS directive and one of the mysql_fetch commands to access the resource.

   

    // Count the number of rows in database
    $query = "SELECT COUNT(*) AS count FROM table";
    $result = mysql_query($query) or die(mysql_error());
    $row =  mysql_fetch_assoc($result);
    $countresult = $row["count"];

   
    Kayaker
Posted on 2008-01-09 16:26:31 by Kayaker
Something along the lines of SQLGetData should start you off in the right direction.
Posted on 2008-01-09 19:21:57 by SpooK
Hi Spook,

SQLGetData requires a column number, so when I execute my COUNT query the data set is empty and it returns SQL_ERROR (-1) and the buffer (pResult) contains 0 regardless of how many records are in the table. I am using the following code to query the database...

ODBCGetNumRecords FRAME pszTblName, pResult
LOCAL cbData:D
LOCAL lRet:D
LOCAL hStmt:D
LOCAL fDisconnect:D
LOCAL szQuery[256]:B

// Much of this code is stolen from KetilO's ODBC example (with permission)

mov D,-1
// Connect to ODBC if necessary
mov D,FALSE
cmp D, 0
jne >
// Set the fDisconnect flag to true. This will cause the function
// to disconnect from the database when done, otherwise someone else
// has connected so leave the connection open.
mov D,TRUE
invoke ODBCConnect,offset szDbFile
:

// Get our statement handle
invoke ODBCGetStatementHandle
mov ,eax
test eax,eax
jz >>.RELEASEODBC

// Bind to column 1, this is always ID
invoke SQLBindCol,,1,SQL_C_ULONG,,4,offset cbData

// Build our query
invoke wsprintf,offset szQuery,"SELECT COUNT(ID) FROM %s",
add esp, 12

// Execute the query
invoke SQLExecDirect, , offset szQuery, SQL_NTS
cmp ax,SQL_SUCCESS
je >
cmp ax,SQL_SUCCESS_WITH_INFO
jne >>.RELEASESTATEMENT
:

// Get the result
invoke SQLGetData,,1,SQL_C_ULONG,,4,offset cbData
cmp ax,SQL_SUCCESS
je >
cmp ax,SQL_SUCCESS_WITH_INFO
jne >>.RELEASESTATEMENT
:

// Set return to 0 if all is successful
mov D,0

.RELEASESTATEMENT
invoke SQLFreeHandle,SQL_HANDLE_STMT,

.RELEASEODBC
cmp D,TRUE
jne >
invoke ODBCDisconnect
:

.EXIT
mov eax,
ret

ENDF


I have also tried SQLNumResultCols without success though the results are promising for that one.

Edgar
Posted on 2008-01-09 20:14:57 by donkey
Well my experience with databases are using mysql and php like kayaker. Seems to me the best is to query and count each row like doing "SELECT * FROM COUNT" and


$result = mysql_query($query) or die("Query failed : " . mysql_error());
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$cnt++;
}
Posted on 2008-01-10 17:15:34 by roticv
for SQLite, I do a "SELECT COUNT(*) FROM table"... I get my results-callback called a single time, and the argument is the count.
Posted on 2008-01-10 18:52:24 by f0dder
Well, after a whole lot of reading I found a line in an article at MSDN. The aggregate is returned in a row, for example if you need an aggregation on column 1, you execute the SQL statement and then fetch, the aggregation is contained in column 1 of the resulting rowset. I found the info in this article...

Jet Database Engine Version 3.0: ODBC Connectivity

# Queries with aggregation: Jet attempts to do aggregation on the server, since this reduces the number of rows returned to the client, often drastically. For example, the query

SELECT Sum(column1) FROM huge_table


is sent entirely to the server; a single row is returned over the network.


Thanks for the replies...

Edgar
Posted on 2008-01-10 23:39:08 by donkey
Edgar,

There are two solutions:
1. Use SQLBindCol() to bind a SQL_C_ULONG to column 1 (the result of count(*)) and then call SQLFetch()
or
2. (if not binding to the result column) - use SQLFetchScroll(hStmt, SQL_FETCH_NEXT, 0) before you call SQLGetData (that was the reason of error from SQLGetData)

This piece might be useful to diagnose the cause of failure:

DisplaySQLError proc HandleType: DWORD, HandleValue: DWORD
  LOCAL szSQLState[1024] : CHAR
  LOCAL dwNativeError    : DWORD
  LOCAL szMessage[1024]  : CHAR
  LOCAL dwMsgLength      : DWORD

  push ebx
  xor ebx, ebx
  .while TRUE
    add ebx, 1
    invoke SQLGetDiagRec, HandleType, HandleValue, ebx, addr szSQLState, addr dwNativeError, addr szMessage, 1024, addr dwMsgLength
    .if ax == SQL_SUCCESS || ax == SQL_SUCCESS_WITH_INFO
      invoke MessageBox, NULL, addr szMessage, addr szSQLState, 0
    .else
      .break
    .endif
  .endw
  pop ebx
  ret
DisplaySQLError endp
Posted on 2008-01-12 19:57:32 by Morris
Hey Morris,

Great to hear form you again !!

I had found a working solution to the #rec problem by binding the ID column (always column 1 in my tables) and sending the "SELECT COUNT(ID) FROM table" query, followed by SQLFetch. The following code works well and does not generate an SQL error if no records exists, which was the problem I was trying to work around.

ODBCGetCount FRAME pszQuery
uses ebx
LOCAL hStmt :D
LOCAL fDisconnect :D
LOCAL pData :D
LOCAL cbData :D
LOCAL lret :D

// Connect to ODBC if necessary
mov D,FALSE
cmp D, 0
jne >
// Set the fDisconnect flag to true. This will cause the function
// to disconnect from the database when done, otherwise someone else
// has connected so leave the connection open.
mov D,TRUE
invoke ODBCConnect,offset szDbFile
:

mov D,-1
// Get our statement handle
invoke ODBCGetStatementHandle
mov ,eax
test eax,eax
jz >>.RELEASEODBC

mov ,eax

// Allocate memory for the query results
invoke HeapAlloc,,HEAP_ZERO_MEMORY, 4096 ; Must be larger than the largest row size
mov ,eax
mov ebx,eax

invoke SQLBindCol,, 1, SQL_C_ULONG, ebx, 0, offset cbData

invoke SQLExecDirect,,, SQL_NTS
cmp ax,SQL_SUCCESS
je >
cmp ax,SQL_SUCCESS_WITH_INFO
jne >>.RELEASESTATEMENT
:

.READ_RECORD
mov B,0

invoke SQLFetch,
cmp ax,SQL_SUCCESS
je >
cmp ax,SQL_SUCCESS_WITH_INFO
jne >.RELEASESTATEMENT
:
mov eax,
mov D,eax

.RELEASESTATEMENT
invoke SQLFreeHandle,SQL_HANDLE_STMT,

.RELEASEMEM
invoke HeapFree,,0,

.RELEASEODBC
cmp D,TRUE
jne >
invoke ODBCDisconnect
:
mov eax,
ret

.ERROR
xor eax,eax
dec eax
ret

ENDF


Thanks for the info about SQLFetchScroll, I did not realize the reason for the SQLGetData error, makes sense now.

I use the same error message code in my program for quite a few functions, however as you know the error messages can be cryptic and sometimes don't really help much tracking down the problem in code.

Edgar
Posted on 2008-01-13 19:31:14 by donkey

Thanks for the info about SQLFetchScroll, I did not realize the reason for the SQLGetData error, makes sense now.

I use the same error message code in my program for quite a few functions, however as you know the error messages can be cryptic and sometimes don't really help much tracking down the problem in code.


I was getting the 24000 SQLSTATE when I called SQLGetData without prior call to SQLFetch/SQLFetchScroll - a quick glance at the Diagnostics section of the SQLGetData function description in MSDN solved the problem ;)

Anyway, I'm glad that you're glad to see me back :)

Pawel
Posted on 2008-01-14 09:32:33 by Morris