Adding And Deleting Records In iOS SQLite Database

Targets macOS
1

In the continuing effort to try to crack the code for using a SQLite database in an iOS app, I am now trying to add a new record to a database. The EEiOS example does not include a way to add or delete data, and I don’t see anything directly in the documentation. Is it even possible?

[quote]A forum message dating back to Dec 14, 2014 shows this:

I am trying to insert a database record like this:[/quote]

[quote]Dim rec As New DatabaseRecord
rec.Column(“name”).ToText = “Carrots”
rec.Column(“description”).ToText = “The chopped ones please…”
app.db.InsertRecord(“grocery”,rec)
The database is a Sqlite database.

The errormessage in the debugger is “Can’t find a type with this name” and refers to DatabaseRecord. Do I have to use app.db.SQLExecute(sql) instead to achieve an insert?

Answered by Bob Keeney See post in context
@Jacco Slok The errormessage in the debugger is “Can’t find a type with this name” and refers to DatabaseRecord. Do I have to use app.db.SQLExecute(sql) instead to achieve an insert?
Correct. There is no class based way of inserting data yet.[/quote]

So, I guess you need to use the app.db.SQLExecute(sql) statement for doing this, but I can’t find any reference for this syntax.

I tried using this code but it throws an exception that the database is not loaded:

[code] Dim dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child(“KTM6.sqlite”)

App.KTM6 = New SQLiteDatabase
App.KTM6.DatabaseFile = dbFile

If dbFile.Exists Then
App.KTM6 = New SQLiteDatabase
App.KTM6.DatabaseFile = dbFile
end if
Dim sql as Text

SQL= “INSERT INTO Questions SET Question = ?1, Answer = ?2”
App.KTM6.SQLExecute(sql, txtQuestion.Text, txtAnswer.Text)[/code]

This code does not seem to work either:

App.KTM6.SQLExecute("INSERT INTO Persons (Name, Age) VALUES ('john', 22)") App.KTM6.SQLExecute("COMMIT")

I was wondering what the correct syntax is for adding a record to a sqlite database, or if anyone knows of an example project I can look at?

Any help would be greatly appreciated.

2

You mean this ?
http://developer.xojo.com/sqlitedatabase

3

Hi Norman,

I looked at this page. There is sample code for:

  1. Display SQLite version.
  2. Connecting to existing database.
  3. Creating a new SQLiteDatabase
  4. Creating a new table.
  5. Updating a table.
  6. Creating a Select Statement.

I don’t see any code for adding a new record or deleting an existing record.

4

Your code is displaying an error because you have not connected to the database. See the Connect method.

You’ll want to use SQL to modify the database. In particular, INSERT to add rows; UPDATE to change rows and DELETE to remove rows. You can read about these commands in the Database chapter of the User Guide (Database Operations section) or take a look at the SQLite SQL docs.

A specific database-focused User Guide for iOS is not yet ready, but when used with SQL it does not differ much from what you could do before.

There are now CREATE TABLE, UPDATE, INSERT and DELETE SQL examples on the SQLiteDatabase.SQLExecute page.

5

Thank you Paul. That worked. I really appreciate the help. The code below works if anyone is interested:

Dim db As New SQLiteDatabase

Dim dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child(“KTM6.sqlite”)

If dbFile.Exists Then
db.DatabaseFile = dbFile
If db.Connect Then
//proceed with database operations here…
Else
Dim err As Text = “Could not open the database.”
End If
End If

Dim sql as Text
db.SQLExecute(“Begin Transaction”)
db.SQLExecute(“INSERT INTO MyTable (Field1, Field2, Field3) VALUES ( '” + (txtField1.Text) + “’, '” + (txtField2.Text) + “’, '” + (txtField3.Text) + “’)”)
db.SQLExecute(“COMMIT”)

6

That page also shows you how to NOT use a string concatenation for inserting

From the section entitled “sqlexecute”
[i]Add data to a table:

Dim sql As Text
sql = “INSERT INTO Team (Name, Coach, City, ID) VALUES (?1, ?2, ?3, ?4)”

’ Pass in values after sql instead of doing string replacement
Try
Dim name As Text = “Flying Squirrels”
Dim coach As Text = “Roberts”
Dim city As Text = “Springfield”
’ ID is created automatically because it is a primary key
DB.SQLExecute(sql, name, coach, city)
Catch e As SQLiteException
Dim err As Text = e.Reason
End Try[/i]

7

Yes James I think you really should change your SQL Insert statement over to a parametized query as Norman suggests. Also I don’t think you need the explicit transaction block; I think SQLite has an implied transaction around individual statements so when you’re just executing a single INSERT (or UPDATE, etc) then you shouldn’t need a transaction block.

8

Thank you Norman and Jason. I really appreciate the help.