SQLite EDIT vs UPDATE

Targets iOS
1

I’m working on my first iOS app so please bear with me. I’m using Eddies Electronics from the sample projects as a guide.

The sample project uses the following syntax to update a record in the database:

  Dim sSQL As Text
  sSQL = "UPDATE Customers SET FirstName = ?1, LastName = ?2, " + _
  "Address = ?3, City = ?4, State = ?5, Zip = ?6, Phone = ?7, " + _
  "Email = ?8,  WHERE pkRecID = ?9"
  
  // Pass in values after sql instead of doing string replacement
  App.dbSQL.SQLExecute(sSQL, FirstName, LastName, Address, City, State, _
  Zip, Phone, Email, pkRecID)

In my desktop apps I use (and prefer over the above) the following:

  
  sSQL = "SELECT * FROM Customers WHERE pkRecID = " + str(iCurrentRecord)  
  rsRecall = dbSQL.SQLSelect(sSQL)
  if dbSQL.Error then
    MsgBox(dbSQL.ErrorMessage)
    Quit
  end if
  
  if tfGoesByName.Text = "" then 
    tfGoesByName.Text = tfFirstName.Text
  end if
  
  rsRecall.Edit
  rsRecall.Field("Status").StringValue               = pmStatus.Text
  rsRecall.Field("FirstName").StringValue            = tfFirstName.Text
  rsRecall.Field("LastName").StringValue             = tfLastName.Text
.
.
.
  rsRecall.Field("Image").PictureValue               = cImage.Backdrop
  rsRecall.Field("Comments").StringValue             = taComments.Text
  rsRecall.Update

Is the latter version of code available in iOS or am I forced to use the UPDATE format?

I also like to use the NEW database record followed by the InsertRecord statement…

thanks in advance…

2

my personal opinion… focus on the SQL solution. it may not be your “preferred” method… but it is the only method that will work on ALL platforms (and I’m not talking about desktop vs ios… I’m talking about ANY SQL database engine on ANY computer).

Not only is is smaller in size (as evidenced by your own example above)… its faster… its portable … and its a skillset you can tranfer to other environments in the future.

I have been working with various types of databases and SQL for longer than I care to admit, and I will say even way back in VB6 days I always used an SQL solution over attempting to Edit/change/Update.

And as an extension, you can use SQL to update ALL of the records in a table at once, or via a transaction table…

my 2cents

3

RecordSet editing is not available with iOS so you’ll have to use the SQL UPDATE method shown in the example and the docs for iOSSQLiteDatabase.SQLExecute.