That Field does not exist in that old .sqlite DB

Databases
#1

I fall in a special case: old database I created years ago have a “missing” field.

When I read that data base, I do not have that case in my code and so my application crashed.

Of course, it tooks a while until I realized what happened.

To be sure, I commented the “missing Field” reading line and the DB was load correctly (this is a service information I added later).

I do not had set a version field then so no help from that.

Ideas ?

#2

Check if all the fields are in the table after the connection, if not, just add it.

#3

Using Database.IndexSchema ?

#4

Naaaa

RS = SQLSelect ("SELECT * FROM TableName limit 1;")
For i As Integer = 1 To RS.FieldCount
   RS.IdxField(i).Name 'Use the Column names to check
Next

If the column is not there:

sqlExecute("ALTER TABLE TableName ADD COLUMN ColumnName TEXT;")

2 Likes
#5

Thank you Ivan.

I will do (after a break, I seem to need one).

The “Replying…” feature is nice: once I noticed it, I stopped and waited until your anwser comes.

#6

I update my users’ databases from time to time, so I can add new features. So I made these, which I call when an update may be needed, to see whether the update is needed:

Sub columnExists (dbh As SQLiteDatabase, colname As String, tablename As String)

Var  sql As String, reg As RowSet

sql = "select " + colname + " from " + tablename

Try
  reg = dbh.SelectSQL (sql)
  Return True
Catch e as DatabaseException
  Return False
end try

end sub

and:

sub tableExists (dbh As SQLiteDatabase, tablename As String)
Var  sql As String, reg As RowSet

sql = "select 1 from " + tablename

Try
  reg = dbh.SelectSQL (sql)
  Return True
Catch e as DatabaseException
  Return False
end try

end sub
1 Like
#7

Thank you. Good ideas.

#8

Ah - I see I forgot to make both methods “as Boolean”.

#9

I too forgot easily these details.

Fortunately, Xojo recalls me when it is the case. ;-:slight_smile:

#10

I implemented Ivan advice (a bit lazy / I need to use the application right now); I added the default value(s) at the same time, so the Field is not empty).

Tim: I saved this page for future use. I love your idea as a supplementary to a file version field.