Page 1 of 1
Unable to insert records into SQLite database
Posted: Sun Jan 05, 2025 3:45 pm
by 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!
Re: Unable to insert records into SQLite database
Posted: Sun Jan 05, 2025 3:58 pm
by 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)
Re: Unable to insert records into SQLite database
Posted: Sun Jan 05, 2025 4:10 pm
by 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!