SQLite3 - COUNT()

[SeaVipe]

Hi Paul, I'm having a wheel-spinning session with the following:

Code Select Expand

SELECT COUNT(*) FROM main;Variation:

Code Select Expand

SELECT COUNT(task) FROM main WHERE task LIKE '%yard%';The above 2 return no results or errors.
However, this works:

Code Select Expand

q.PrepareQuery( db, "SELECT * FROM main WHERE task LIKE '%yard%';" )I can 'count' the Rows in the query result but COUNT() would appear more correct.

Using your SQLite3 Class from August 2015.

[Paul Squires]

Try this:

Code Select Expand

sql = "SELECT COUNT(*) AS RecCount FROM employees;"
q.PrepareQuery( db, sql )
if q.GetRow() then
   Print "Count = "; q.GetText("RecCount")
end if
q.FinalizeQuery   ' finish with this query so destroy its resources


[Paul Squires]

Also, don't forget that Jose also has a great SQLite class as part of the WinFBX library framework.
Here is some example code to use it:

Code Select Expand


'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx

' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
DIM pSql AS CSQLite = "sqlite3_32.dll"
print pSql.m_hLib

' // Create a new database
' // I'm deleting and recreating the database for testing purposes
DIM cwsDbName AS CWSTR = AfxGetExePathName & "Test.sdb"
IF AfxFileExists(cwsDbName) THEN AfxDeleteFile(cwsDbName)
DIM pDbc AS CSQLiteDb = cwsDbName

' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz text)") <> SQLITE_DONE THEN
   AfxMsg "Unable to create the table"
   END
END IF

' // Insert rows
IF pDbc.Exec("INSERT INTO t (xyz) VALUES ('fruit')") <> SQLITE_DONE THEN AfxMsg "INSERT failed"
IF pDbc.Exec("INSERT INTO t (xyz) VALUES ('fish')") <> SQLITE_DONE THEN AfxMsg "INSERT failed"

' // Prepare a query
DIM pStmt AS CSqliteStmt = pDbc.Prepare("SELECT * FROM t")
PRINT "Column count: ", pStmt.ColumnCount
' // Read the column names and values
DO
   ' // Fetch rows of the result set
   IF pStmt.GetRow = SQLITE_DONE THEN EXIT DO
   ' // Read the columns and values
   FOR i AS LONG = 0 TO pStmt.ColumnCount- 1
      PRINT pStmt.ColumnName(i)
      PRINT pStmt.ColumnText(i)
   NEXT
LOOP

PRINT
PRINT "Press any key..."
SLEEP


[SeaVipe]

Thanks, Paul,

There's the secret:  'RecCount'

I've been back and forth between your class and Jose's class...

So much to learn!