SQLITE: Issue with FindNext

[XCoder]

The following program has a function FindNext() to get the next row in a database and display it in a debug window.

Code: Select all

EnableExplicit

UseSQLiteDatabase()

Enumeration;{
  #MainWindow=10
  #btnOpenDatabase
  #btnFindFirst
  #FindNext
  #dBase
EndEnumeration
;}

Global searchQuery$="", Quit

Procedure OpenDatabaseFile()
  Protected dbFilename$
  dbFilename$ = OpenFileRequester("Get database",  "",  "*.db", 0)
  If Not OpenDatabase(#dBase, dbFilename$, "", "", #PB_Database_SQLite )
  EndIf
EndProcedure

Procedure FindFirst()
  searchQuery$="SELECT Title, Description, rowid, COUNT(*) FROM tblBooks"
  If DatabaseQuery(#dBase, searchQuery$, #PB_Database_DynamicCursor)
    NextDatabaseRow(#dBase)
    Debug GetDatabaseString(#dBase, 0)
  EndIf
EndProcedure

Procedure FindNext()
  NextDatabaseRow(#dBase)
  Debug GetDatabaseString(#dBase, 0) 
EndProcedure

OpenWindow(#MainWindow, 0, 0, 512, 170, "Main window", #PB_Window_ScreenCentered | #PB_Window_SystemMenu)
ButtonGadget(#btnOpenDatabase, 10, 50, 100, 22, "Open database")
ButtonGadget(#btnFindFirst, 120, 50, 100, 22, "Find first record")
ButtonGadget(#FindNext, 230, 50, 100, 22, "Find next record")

Repeat ;{
  Select WaitWindowEvent()
    Case #PB_Event_CloseWindow
      If EventWindow() = #MainWindow
        Quit=#True
      EndIf
      
    Case #PB_Event_Gadget
      Select EventGadget()
        Case #btnOpenDatabase: OpenDatabaseFile()
        Case #btnFindFirst:FindFirst()
        Case #FindNext: FindNext()
      EndSelect
      
  EndSelect
Until Quit=#True
If IsDatabase(#dBase)
  CloseDatabase(#dBase) 
EndIf

However when I call this function by clicking the "Find next record" button several times the debug window shows only the first record in the database, followed by a blank line, followed by the first record in the database, followed by a blank line, etc.

Why is this function not getting successive rows in the database? HOw can I resolve this issue? The help file shows how to use a while statement to iterate through relevant rows but I need a function that will get the next row in the database when the "Find next record" is clicked.

[mk-soft]

Check next database row ...

SQL Select ... Count(*) ... only one row

Code: Select all

Procedure FindNext()
  If NextDatabaseRow(#dBase)
    Debug GetDatabaseString(#dBase, 0)
  Else
    Debug "No more rows"
  EndIf
EndProcedure

[infratec]

You can replace Count(*) with

(SELECT COUNT(*) FROM tblBooks)

But it is 'slow'.

[XCoder]

Thank you for your replies.

The issue was with using count.

By changing the SELECT statement from

Code: Select all

searchQuery$="SELECT Title, Description, rowid, COUNT(*) FROM tblBooks"

to

Code: Select all

searchQuery$="SELECT Title, Description, rowid, (SELECT COUNT(*) FROM tblBooks) FROM tblBooks"

as suggested by infratec the program works correctly. However, I don't understand why the original code does not work. Shouldn't COUNT(*) give the number of records found in total which is four. Why does (SELECT COUNT(*) FROM tblBooks) solve this problem?

[infratec]

First: this is not a PB problem, it is a SQL 'problem'.

COUNT() is an aggregate function. If it is used it returns only one record, because everything else makes no sense, since the count does not change.

The complete (SELECT ... ) returns also only one result, but for each query.

In general it works, but it is not the best idea, since it takes time to return always the same result.
But maybe the SQL caches the subquery.

Depending on the database you can also add a GROUP BY 'the unique record id field'.
Then COUNT(*) should work too.

[XCoder]

@infratec: Thanks for clarifying the issue.

[infratec]

Ups ... SQLITE

Which always has a unique record id (unless you create the table with explicit disable this feature).

So you can use:

Code: Select all

searchQuery$="SELECT Title, Description, rowid, COUNT(rowid) FROM tblBooks GROUP BY rowid"

Which is faster than the (SELECT ...) field

Tested with:

Code: Select all

Procedure OpenDatabaseFile()
  If OpenDatabase(#dBase, ":memory:", "", "", #PB_Database_SQLite)
    DatabaseUpdate(#dBase, "CREATE TABLE tblBooks (Title TEXT, Description TEXT)")
    DatabaseUpdate(#dBase, "INSERT INTO tblBooks VALUES ('One', 'One bla bla')")
    DatabaseUpdate(#dBase, "INSERT INTO tblBooks VALUES ('Two', 'Two bla bla')")
    DatabaseUpdate(#dBase, "INSERT INTO tblBooks VALUES ('Three', 'Three bla bla')")
    DatabaseUpdate(#dBase, "INSERT INTO tblBooks VALUES ('Four', 'Four bla bla')")
  EndIf
EndProcedure

[skywalk]

SQL is not always obvious especially if you are not always making queries.
For this reason, I use DB Browser for SQLite to verify my queries before committing them to automation.
There are many browsers out there, even online if you like. Just pick one and quickly build your sampleb db and debug your queries.