Confused about SQLITE Query

[pwillard]

First off - Noob Status

I read this example: http://www.purebasic.fr/english/viewtop ... 13&t=52896

I'm using 5.10-B4

Following that example, I have a database with 20 entries

My code:

Code: Select all

DbHandle = OpenDatabase(#PB_Any, "C:\blahblah\PB\Database\MyParts.sqlite", "", "")
 
 If DatabaseQuery(DbHandle,"SELECT COUNT(*) from MyParts;") <> 0
  NextDatabaseRow(DbHandle)

   Debug " Number of records 'From Select Count(*) : " + GetDatabaseString(DbHandle, 0)

 EndIf

But I always get a "0" response... even though I get:

Code: Select all

sqlite> select count(partid) from myparts;
20
sqlite> SELECT COUNT(*) from MyParts;
20

from SQLITE3


Any clues?

[jassing]

Are you issuing a "UseSQliteDatabase()"

[pwillard]

Yes... just before that I say:

Code: Select all

If UseSQLiteDatabase() = 0
  MessageRequester("SQLite error", "Cannot access the SQLite database environment", #PB_MessageRequester_Ok)
  End
  
EndIf

[skywalk]

Not sure?
Try dropping the trailing ';'?
Try making the Table different than the db Name?

[pwillard]

<facepalm>

Try making the Table different than the db Name?

works now.

[pwillard]

Ugh. Still baffled. I thought I had it.

Same DB file, 2 locations.

If the DB file is : C:\Users\willard\Documents\PB\Database\parts.db it returns 0.

If the DB file is: C:\parts.db it returns 20 like its supposed to.

The only thing I'm changing is the path.

[Fangbeast]

G'day. I just saved this small routine in my root programming directory and ran it. Returned a count of 115,00 (what is in my recipe database at the moment)

I also just made your directory structure on my hard disk and your database name and it still worked.

Code: Select all

DatabaseString.s = "C:\Users\Miki\My Programming\Development, v5.x\Dev-Current\RecipeMuncher\Database\Recipe_Muncher_v1.33b.sqlite"

If UseSQLiteDatabase() <> 0
  
  DatabaseHandle.i = OpenDatabase(#PB_Any, DatabaseString.s, "", "")
  
  If DatabaseQuery(DatabaseHandle.i,"SELECT COUNT(*) from Recipes") <> 0
    
    If NextDatabaseRow(DatabaseHandle.i)
      
      MessageRequester("Records", GetDatabaseString(DatabaseHandle.i, 0), #PB_MessageRequester_Ok)
      
    EndIf
    
    FinishDatabaseQuery(DatabaseHandle.i)
    
  Else
    
    MessageRequester("Query error", "Query failed: " + DatabaseError(), #PB_MessageRequester_Ok)
    
  EndIf
  
Else
  
  MessageRequester("SQLite error", "Cannot access the SQLite database environment", #PB_MessageRequester_Ok)
  
  End
 
EndIf