PureBasic Forums - English

Posted: **Sat Nov 16, 2024 8:43 pm**

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

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.

Posted: **Sat Nov 16, 2024 9:37 pm**

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

Posted: **Sun Nov 17, 2024 10:32 am**

You can replace Count(*) with

(SELECT COUNT(*) FROM tblBooks)

But it is 'slow'.

Posted: **Sun Nov 17, 2024 1:50 pm**

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?

Posted: **Sun Nov 17, 2024 4:28 pm**

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.

Posted: **Sun Nov 17, 2024 6:33 pm**

@infratec: Thanks for clarifying the issue.

Posted: **Mon Nov 18, 2024 10:21 pm**

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

Posted: **Tue Nov 19, 2024 12:25 am**

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.

PureBasic Forums - English

SQLITE: Issue with FindNext

SQLITE: Issue with FindNext

Re: SQLITE: Issue with FindNext

Re: SQLITE: Issue with FindNext

Re: SQLITE: Issue with FindNext

Re: SQLITE: Issue with FindNext

Re: SQLITE: Issue with FindNext

Re: SQLITE: Issue with FindNext

Re: SQLITE: Issue with FindNext