Trouble getting data from SQLite

[JaxMusic]

Good evening. I cannot figure out why this code is not returning the correct information. This is a simple procedure to execute a SQL statement and count the number of rows in the database.

1. No errors are thrown when executing the code.
2. The SQL syntax works fine when using a SQLite Studio.
3. Tried results as both Long and String with the associated library functions GetDatabaseLong and GetDatabaseString
4. Tried both 0 and 1 as the column number (there is only 1 column to return) since I didn't know how it was indexed.
5. I've put in Debug statements to see the SQL and Database name, both variables are populating as intended.

Any ideas? TIA. Jack

Code: Select all

UseSQLiteDatabase()  ;SQL Lite library
    
Procedure ExecCountLines(DatabaseName$,Sql$)
   ;2021-03-03 Jack Phillips
   ;Returns number of lines in query
  result.l ;Results variable Long
   
  ;Open database  this is a library command built into the sqllite library
  OpenDatabase(1, DatabaseName$, "", "", #PB_Database_SQLite)
   
  ;SetDatabaseString(1, 0, DatabaseName$) 
  
    Debug Sql$ ;works as intended
   If DatabaseQuery(1, Sql$,#PB_Database_DynamicCursor)    
     result=GetDatabaseLong(1, 1)  ;tried both column as 0 and as 1, same result each time
    Debug result   
   
     FinishDatabaseQuery(1)
    EndIf
    
   CloseDatabase(1)
   
 EndProcedure
     
    
  ;MAIN 

  Sql.s  ;SQL string For commands
  DBName.s ;Database name
;  Result.l ; result count
  
  ;Get row count
  Sql="Select count(PersonName) As RowCount from Test_names"
  DBName="Test.sqlite" ;works As inteded
  Debug DBName ;works As inteded
  
 ExecCountLines(DBName,Sql)

[skywalk]

Use ":memory:" for the database name and post code that opens it and populates it with some sample data. Then you can run your query too.

[PartTimeCoder]

You are not retrieving the row

try this

Code: Select all

  If DatabaseQuery(1, Sql$,#PB_Database_DynamicCursor) 
    If FirstDatabaseRow(1)
      result=GetDatabaseLong(1, 1)  ;tried both column as 0 and as 1, same result each time
      Debug result 
    EndIf
  EndIf

[infratec]

Yes,

you need NextDatabaseRow()

Code: Select all

EnableExplicit



Define.i DB, i

UseSQLiteDatabase()  ;SQL Lite library


DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)
If DB
  
  If Not DatabaseUpdate(DB, "CREATE TABLE Test_names (PersonName text)")
    Debug DatabaseError()
  EndIf
  
  For i = 1 To 10
    If Not DatabaseUpdate(DB, "INSERT INTO Test_names (PersonName) VALUES ('Test: " + Str(i) + "')")
      Debug DatabaseError()
    EndIf
  Next i
  
  If DatabaseQuery(DB, "SELECT count(*) AS RowCount FROM Test_names")
    If NextDatabaseRow(DB)
      Debug GetDatabaseLong(DB, 0)
    EndIf
    
    FinishDatabaseQuery(DB)
  Else
    Debug DatabaseError()
  EndIf
  
  If DatabaseQuery(DB, "SELECT PersonName FROM Test_names")
    While NextDatabaseRow(DB)
      Debug GetDatabaseString(DB, 0)
    Wend
    
    FinishDatabaseQuery(DB)
  Else
    Debug DatabaseError()
  EndIf
  
  CloseDatabase(DB)
Else
  Debug DatabaseError()
EndIf

At least in PostgreSQL count(*) is faster then count(colname)

[JaxMusic]

You are not retrieving the row

try this

Code: Select all

  If DatabaseQuery(1, Sql$,#PB_Database_DynamicCursor) 
    If FirstDatabaseRow(1)
      result=GetDatabaseLong(1, 1)  ;tried both column as 0 and as 1, same result each time
      Debug result 
    EndIf
  EndIf

Yep. I forgot. Thank you.