Check, and if required, add column to existing database?

Databases
1

Hi,
Ok, been so busy the last 2 days, I think my brain has gone on strike :frowning:

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?

Hope someone can help.
Thank you all in advance.

2

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
3

Then pass it one of these:

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)

And pass it one of these:

CreateColumn(App.DonsDB, "Dons", "Country", "varchar")
4

Thanks Adam!
I will take a look at that and work out what is happening.

I really appreciate the code example - I can definitely learn from this.
THANK YOU VERY MUCH!

5

In the first code example, the Return statement within the Error Handling block should read “Return COLCreated”, not “Return COLExists”. Sorry about that!

6

Adam, what does this line of code refer to?

App.DisplayDatabaseError true, "ColumnExists()"

Thanks.

7

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.

8

Thanks Adam - plenty of stuff to learn here :slight_smile:

9

All I do is use ALTER TABLE everytime my app opens, since it doesn’t do anything if the column already exists.

10

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 :slight_smile:

11

Same here.
I additionally check for an error and if it’s column already exists, everything went fine… :wink:

12

Sascha, are you saying if the column already exists, and I try to add it - it will produce an error (which means it already exists) ?
Thanks.

13

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

14

I am just looking for the recommended way to simply add a column to an existing database.

SO basically I can:

  1. Do what Thomas and Sascha do, and simply catch the error (meaning the column already exists).

OR

  1. Do it Adam’s way, which will only add the column it if it does not already exist.
15

I do this (actual code from my app):

  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.

Jon

16

Thanks Jon.

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) :slight_smile:

[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]

Thanks.

17

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).

Jon

18

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.

19

[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.

20

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.