Hi,
Ok, been so busy the last 2 days, I think my brain has gone on strike
How do I add a new column to an already existing SQLite database upon startup?
I basically need to check if col4 exists, if not, add it to the database file, but I cannot work out how to do this?
I wrote this for an app using the old REALSQLDatabase, but it should be almost exactly what youâre looking for:
CreateColumn(dB As REALSQLDatabase, tableName As String, mName As String, mType as String) As Boolean
// Check Column Schema to see if Column exists
Dim rs as RecordSet
Dim COLExists As Boolean
Dim COLCreated As Boolean
rs = dB.FieldSchema(tableName)
If rs <> nil then
While Not rs.EOF
If rs.Field("ColumnName").StringValue = mName then
COLExists = True
End If
rs.MoveNext
Wend
End If
// Create column if it doesn't exist
If NOT COLExists Then
db.SQLExecute("ALTER TABLE " + tableName + " ADD COLUMN " + mName + " " + mType)
db.Commit
if db.Error then// handle errors
App.DisplayDatabaseError true, "ColumnExists()"
return COLExists
end if
COLExists = True
COLCreated = True
Else
COLCreated = False
End If
Return ColCreated
Dim chkCol As Boolean = CreateColumn(App.DonsDB, "Dons", "Country", "varchar")
You donât have to make CreateColumn return a value, but my app required it. Otherwise, declare it like this:
CreateColumn(dB As REALSQLDatabase, tableName As String, mName As String, mType as String)
In the first code example, the Return statement within the Error Handling block should read âReturn COLCreatedâ, not âReturn COLExistsâ. Sorry about that!
That is a method in the main App itself called âDisplayDatabaseError(doRollback As Boolean, wSend As String)â. Inside that method, it checks for errors in the DB and displays a message box containing all the error data, and rolls back the DB if doRollback is positive:
MsgBox "Database Error: " + str(DonsDB.ErrorCode) + EndOfLine + EndOfLine + DonsDB.ErrorMessage + " (sender: " + wSend + ")"
// Rollback changes to the database if specified
if doRollback then
DonsDB.rollback
end
It is an easy way to check for errors when you have to do it a billion times. This way, you can change the wSend String to the name of whatever method you are calling (change âColumnExists()â to the name/parameters of whatever method you are using it in), and any message box that pops up will tell you exactly where the error occurred :). Iâll also add " 1", " 2", " 3", etc. to the string if I have to check for errors several times in one method.
Just an idea: You might try Introspection to automatically find the name of the calling method, and increment a variable each time you need to check for errors. That way you can just copy and paste the code anywhere without manually typing in the method name and increment.
Thanks Thomas.
So if col4 already exists and contains values, the ALTER TABLE SQL statement will not overwrite the data?
Just to be 100% sure - does anyone know if there is any reason why Thomasâ solution should not be used?
Just donât want to get surprised with some kind of unexpected result
Always running an alter will give you a db error saying the column already exists after youâve done it once.
Thats the down side to that way - plus the minor inconvenience of doing something thats not necessary.
But it works.
But you can tell if the tables are already the way you need (see table schema & field schema) and do or not do whatever alters you need.
You can make this as simple or complicated as you want
Dim r as Recordset
r= MSDB.SQLSelect("SELECT rowid, Hidden FROM Screens")
System.DebugLog("Altering table for Hidden column")
If r = Nil Then
MSDB.SQLExecute("ALTER TABLE Screens ADD Hidden Boolean")
If Not DBErrorCheck Then
MSDB.Commit
End If
End If
r=MSDB.SQLSelect("SELECT rowid, Lock FROM Preferences")
If r = Nil Then
MSDB.SQLExecute("ALTER TABLE Preferences ADD Lock Boolean")
If Not DBErrorCheck Then
MSDB.Commit
End If
End If
DBErrorCheck is a function that checks to see if any errors were raised in the database operations.
So I do the recordset and check if itâs NIL. Backwards from what others have suggested.
So the slightly modified code below will try to retrieve the column called Col4. If it does not exist, rs will = Nil, so the SQLExecute will be executed. Then, if there is no error inserting Col4, commit.
Have I understood this correctly (just want to confirm)
[code]Dim rs as Recordset
rs = myDatabase.db.SQLSelect(âSELECT Col4 FROM myTableâ)
If rs = Nil Then
myDatabase.db.SQLExecute(âALTER TABLE myTable ADD Col4 Booleanâ)
If Not myDatabase.db.Error Then
myDatabase.db.Commit
End If
End If[/code]
Yes. That is correct. Select the column you want from the table. If the returned recordset is nil, the column does not exist. So then go in and add it. If the recordset is not nil, then the column is there (note: the recordset may be at EOF and BOF if thereâs no data in it but thatâs OK).
Is a commit necessary in SQLite?
If I remember correctly, the last time I tried to commit on a SQLite database - it threw an error. When I removed it - it worked correctly?
[quote=137206:@Richard Summers]Is a commit necessary in SQLite?
If I remember correctly, the last time I tried to commit on a SQLite database - it threw an error. When I removed it - it worked correctly?
Thanks for all your help - much appreciated.[/quote]
I use Commit all the time. Yes, I believe it is needed to make sure things are saved. I use Commit and Rollback w/o issue.
What you donât need is sending the âEND TRANSACTIONâ statement after commit and Rollback as they do end the transaction.
My preferred method was to check the database exists on startup. If it doesnât, then it would create the db from code. If it did exist, it would use the same code to create a temporary in-memory database, then compare each table to the userâs database. If any columns were removed in the new version, it would rename the current table, create the new table in the userâs database, insert the matching columns from the current table into the new table, then drop the current table. If there werenât any columns to drop, then it would check to see if there were any new columns in the new table, and add them to the current table.
Using this method, all I had to do was make sure the code to create the database was up-to-date. The system would then automatically update the userâs database to match the current schema no matter what changes have been made to the program since their version.