Working with SQLite Column

The following code works perfectly. Does exactly what I want it to do. However, it’s depreciated. I would like to bring the code up to 2023 R 1.1 (API 2) standards. I have already tried the SelectSQL with a Try/Catch. The catch part of the try doesn’t seem to catch the NilException so that I can process it and move on. Where do I even start at this point. Tried a bunch of stuff from the forums, examples, etc… Still can’t seem to catch that NilException to process it. Perhaps there is a better way to code it than with a try/catch?

FYI - I already tried just replacing SQLSelect with SelectSQL thinking it would assign a Nil value to dbtest (declared as a rowset). Nope, still throws the NilException.

Any assistance in getting started would be much appreciated.

Working code:
// Create a recordset.
var dbtest as recordset
//Check for the signal dbm field, added in 4.6
dbtest = db.SQLSelect(“SELECT dsigdbm FROM Logbook”)
If dbtest = Nil Then
db.BeginTransaction
db.ExecuteSQL(“ALTER TABLE Logbook ADD COLUMN dsigdbm text”)
db.CommitTransaction
End If

To check for the existence of a column, try something like that instead of intentionally causing an error.

Try
  Var Schema As RowSet = db.TableColumns("Logbook")
  Var ColumnExists As Boolean
  While Not Schema.AfterLastRow
    ColumnExists = Schema.Column("ColumnName").StringValue = "dsigdbm"
    If ColumnExists Then
      Exit While
    End If
    Schema.MoveToNextRow
  Wend
  If Not ColumnExists Then
    db.ExecuteSQL("ALTER TABLE Logbook ADD COLUMN dsigdbm TEXT;")
  End If
Catch Err As RuntimeException
  // Something went wrong
End Try

If you want it to be API2 you better use API2 methods and classes consistently:

// Create a RowSet
var dbtest as RowSet
//Check for the signal dbm field, added in 4.6
dbtest = db.SelectSQL (“SELECT dsigdbm FROM Logbook”)
If  (dbtest=Nil)  Then
  db.BeginTransaction
  db.ExecuteSQL (“ALTER TABLE Logbook ADD COLUMN dsigdbm text”)
  db.CommitTransaction
End If

Short and fast generic API2:

Try
  db.ExecuteSQL("SELECT dsigdbm FROM Logbook LIMIT 1;") // Check for the column
catch // missing column
  db.ExecuteSQL("ALTER TABLE Logbook ADD COLUMN dsigdbm text;")
End

Short and safer SQLite only:

Try
  db.ExecuteSQL("SELECT dsigdbm FROM Logbook LIMIT 1;") // Check for the column
catch e
  If e.Message.IndexOf("no such column")>=0 Then // SQLite only
    db.ExecuteSQL("ALTER TABLE Logbook ADD COLUMN dsigdbm text;")
  Else
    MessageBox("Error on LogBook:"+e.Message)
  End
End

I have two methods I use when moving the user from one version of the app to the next, when it involves database changes:

Sub columnExists (dbh As SQLiteDatabase, colname As String, tablename As String) as Boolean
// Use when the database and table exist, and we want to know if the column exists
Var  sql As String
sql = "select " + colname + " from " + tablename + " limit 1"
Try
  Call dbh.SelectSQL (sql)
  Return True
Catch e as DatabaseException
  Return False
end try
End Sub

and:

Sub tableExists (dbh As SQLiteDatabase, tablename As String) as Boolean
// Use to check if a table exists in the database
Var  sql As String
sql = "select 1 from " + tablename
Try
  Call dbh.SelectSQL (sql)
  Return True
Catch e as DatabaseException
  Return False
end try
End Sub

Thus I keep the business of checking and updating the database/tables entirely separate from the business logic.

You shouldn’t intentionally fire an error when you can avoid it by inspecting the schema. Exceptions are slow (in comparison) and can make your transaction state unpredictable.

1 Like

I guess we need some benchmark here, specially with a schema with several columns where Xojo will loop n times trying to locate the last one (like 50) instead of letting the DB engine solve it using faster algorithms.

Well, you could just do it yourself too:

SELECT cid FROM pragma_table_info('Logbook') WHERE name = 'dsigdbm';

If it returns a row, the column exists.