Page 1 of 1
Check sqlite db table columns
Posted: Sun Sep 19, 2021 2:08 pm
by doctorized
Not really sure if my question belongs here. I have a sqlite database and every now and then I make changes to the database, I add or alter a table’s column. So, I need to check that a user has the latest db schema. I have the following code that tries to do it but it is not working. It cannot delete the temp table and “database table is locked” error returns. Any suggestions?
Code: Select all
Enumeration
#db
EndEnumeration
Procedure CheckDatabaseUpdate(Database, Query$)
Result = DatabaseUpdate(Database, Query$)
If Result = 0
MessageRequester("Error",DatabaseError(),#MB_ICONERROR)
EndIf
ProcedureReturn Result
EndProcedure
Procedure CheckDB()
CheckDatabaseQuery(#db,"SELECT sql FROM sqlite_master WHERE tbl_name = 'Students' AND type = 'table'")
NextDatabaseRow(#db)
Debug GetDatabaseString(#db,0)
If GetDatabaseString(#db,0) <> "CREATE TABLE Students (id CHAR(15) PRIMARY KEY, Lname CHAR(32), Fname CHAR(32), Class CHAR(4))"
CheckDatabaseUpdate(#db,"PRAGMA foreign_keys = 0")
CheckDatabaseUpdate(#db,"BEGIN TRANSACTION")
CheckDatabaseUpdate(#db,"ALTER TABLE Lessons RENAME To sqlitemanager_temp_table_177354083607")
CheckDatabaseUpdate(#db,"CREATE TABLE Lessons (id CHAR(13), LName CHAR(30), Class CHAR(4))")
CheckDatabaseUpdate(#db,"INSERT INTO Lessons (id,LName,Class) Select id,LName,Class FROM sqlitemanager_temp_table_177354083607")
CheckDatabaseUpdate(#db,"DROP TABLE sqlitemanager_temp_table_177354083607")
CheckDatabaseUpdate(#db,"COMMIT")
CheckDatabaseUpdate(#db,"PRAGMA foreign_keys = 1")
EndIf
FinishDatabaseQuery(#db)
EndProcedure
If OpenDatabase(#db, DatabaseFile$, "", "", #PB_Database_SQLite)
CheckDB()
EndIf
Now, I get the followin errors:
there is already another table or index with this name: sqlitemanager_temp_table_177354083607
table Lessons already exists
database table is locked
Re: Check sqlite db table columns
Posted: Sun Sep 19, 2021 2:18 pm
by Fangbeast
Where are you actually querying the database? As far as I can see, CheckDatabaseQuery is NOT a valid database command in pb. Unless you are aliasing the DatabaseQuery() command in your code somewhere?
Re: Check sqlite db table columns
Posted: Sun Sep 19, 2021 2:22 pm
by Fangbeast
Code: Select all
Enumeration
#db
EndEnumeration
Procedure CheckDB()
If DatabaseQuery(#db,"SELECT sql FROM sqlite_master WHERE tbl_name = 'Students' AND type = 'table'")
While NextDatabaseRow(#db)
If GetDatabaseString(#db,0) <> "CREATE TABLE Students (id CHAR(15) PRIMARY KEY, Lname CHAR(32), Fname CHAR(32), Class CHAR(4))"
DatabaseUpdate(#db,"PRAGMA foreign_keys = 0")
DatabaseUpdate(#db,"BEGIN TRANSACTION")
DatabaseUpdate(#db,"ALTER TABLE Lessons RENAME To sqlitemanager_temp_table_177354083607")
DatabaseUpdate(#db,"CREATE TABLE Lessons (id CHAR(13), LName CHAR(30), Class CHAR(4))")
DatabaseUpdate(#db,"INSERT INTO Lessons (id,LName,Class) Select id,LName,Class FROM sqlitemanager_temp_table_177354083607")
DatabaseUpdate(#db,"DROP TABLE sqlitemanager_temp_table_177354083607")
DatabaseUpdate(#db,"COMMIT")
DatabaseUpdate(#db,"PRAGMA foreign_keys = 1")
EndIf
Wend
FinishDatabaseQuery(#db)
Else
Debug "Problem with query:: " + DatabaseError()
EndIf
EndProcedure
If OpenDatabase(#db, DatabaseFile$, "", "", #PB_Database_SQLite)
CheckDB()
EndIf
Re: Check sqlite db table columns
Posted: Sun Sep 19, 2021 2:49 pm
by doctorized
Fangbeast wrote: Sun Sep 19, 2021 2:18 pm
Where are you actually querying the database? As far as I can see, CheckDatabaseQuery is NOT a valid database command in pb. Unless you are aliasing the DatabaseQuery() command in your code somewhere?
I forgot to copy and paste that procedure. I updated my initial code.
Re: Check sqlite db table columns
Posted: Sun Sep 19, 2021 3:33 pm
by infratec
As Fangbeast already written:
You need DatabaseQuery() and not DatabaseUpdate() since you use NextDatabaseRow() to receive the results.
And don't forget FinishDatabase() after DatabaseQuery()
Re: Check sqlite db table columns
Posted: Sun Sep 19, 2021 6:15 pm
by doctorized
Infratec, yes, my bad. I added FinishDatabaseQuery(#db), the code now runs with no errors but it not doing the job. With the below code, txt$ is different from the comparison string, all CheckDabaseUpdate() run with no effect to the table. If I change Update to Query, then I get multiple errors.
Code: Select all
Procedure CheckDB()
CheckDatabaseQuery(#db,"SELECT sql FROM sqlite_master WHERE tbl_name = 'Students' AND type = 'table'")
NextDatabaseRow(#db)
txt$ = GetDatabaseString(#db,0)
FinishDatabaseQuery(#db)
Debug txt$
If txt$ <> "CREATE TABLE Students (id CHAR(15) PRIMARY KEY, Lname CHAR(32), Fname CHAR(32), Class CHAR(4))"
CheckDatabaseUpdate(#db,"PRAGMA foreign_keys = 0")
CheckDatabaseUpdate(#db,"BEGIN TRANSACTION")
CheckDatabaseUpdate(#db,"ALTER TABLE Lessons RENAME To sqlitemanager_temp_table_177354083607")
CheckDatabaseUpdate(#db,"CREATE TABLE Lessons (id CHAR(13), LName CHAR(30), Class CHAR(4))")
CheckDatabaseUpdate(#db,"INSERT INTO Lessons (id,LName,Class) Select id,LName,Class FROM sqlitemanager_temp_table_177354083607")
CheckDatabaseUpdate(#db,"DROP TABLE sqlitemanager_temp_table_177354083607")
CheckDatabaseUpdate(#db,"COMMIT")
CheckDatabaseUpdate(#db,"PRAGMA foreign_keys = 1")
Debug "DONE"
EndIf
EndProcedure
Re: Check sqlite db table columns
Posted: Sun Sep 19, 2021 6:46 pm
by infratec
This is not a code which I can run.
So I can not do any tests.
Add something arround that the call of this procedure makes sense and I will have a closer look.
Re: Check sqlite db table columns
Posted: Sun Sep 19, 2021 8:33 pm
by doctorized
infratec wrote: Sun Sep 19, 2021 6:46 pm
This is not a code which I can run.
So I can not do any tests.
Add something arround that the call of this procedure makes sense and I will have a closer look.
After many tests I found out the issue with the sqlite syntax that solved the problem. Thank you for your time!
Re: Check sqlite db table columns
Posted: Thu Sep 23, 2021 1:33 am
by 4otomax
I use DB Browser for SQLite for syntax checking. It's free and portable, can make SQL queries and have autocomplete.
Re: Check sqlite db table columns
Posted: Sun Sep 26, 2021 5:43 am
by collectordave
Is it not a lot easier to create a table in the database called Version with two fields Major and Minor.
Then a simple query would fetch the database version for you to check.
Then when you change the database you can update the version table, you could even have a text field in the Version table detailing the changes made.