Unable to insert records into SQLite database

[Quin]

I'm sure this is because I'm missing some basic aspect of SQLite, I just started really learning how to use it a couple days ago, but this has me stumped. I've gotten it down to this minimal example:

Code: Select all

EnableExplicit

UseSQLiteDatabase()
OpenFile(0, "test.db")
CloseFile(0)
OpenDatabase(0, "test.db", "", "")
DatabaseUpdate(0, "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)")
SetDatabaseString(0, 0, "John")
SetDatabaseString(0, 1, "Doe")
DatabaseUpdate(0, "INSERT INTO users (firstname, lastname) VALUES (?, ?)")
CloseDatabase(0)

When I run this, the table is created, but the record never gets inserted, even though DatabaseUpdate() returns 1. What am I doing wrong?
Thanks!

[benubi]

It works, you have no permission to write to that file path, that's all.

I made a few modifications

Code: Select all

UseSQLiteDatabase()
OpenFile(0, GetUserDirectory(#PB_Directory_Documents) + "test.db")
CloseFile(0)
OpenDatabase(0,  GetUserDirectory(#PB_Directory_Documents) +  "test.db", "", "")
DatabaseUpdate(0, "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)")
SetDatabaseString(0, 0, "John")
SetDatabaseString(0, 1, "Doe")
DatabaseUpdate(0, "INSERT INTO users (firstname, lastname) VALUES (?, ?)")

If DatabaseQuery(0,"SELECT * FROM users;")
  Define cols = DatabaseColumns(0)
  Define i
  Define row$
  ; titles
  For i=0 To cols-1
    If row$<>#Empty$
      row$=row$+#TAB$
    EndIf 
    row$=row$+DatabaseColumnName(0,i)
  Next 
  Debug row$
  While NextDatabaseRow(0)
    row$=""
   For i=0 To cols-1
    If row$<>#Empty$
      row$=row$+#TAB$
    EndIf 
    row$=row$+GetDatabaseString(0,i)
  Next 
      Debug row$
    row$=""
  Wend 
  FinishDatabaseQuery(0)
EndIf 

CloseDatabase(0)

[Quin]

Hahaha, it turns out it was even simpler than that.
In my large application using SQLite, I forgot to SELECT * FROM mytable before iterating over all the rows, thus making it look like there was nothing being added. And the filesize never changed, so I thought nothing was happening

Thanks for your help!