Translate an Example to API 2.0, please

[Emile_Schwarz]

Here’s the example:

Var db As New SQLiteDatabase db.DatabaseFile = new FolderItem("Employees.sqlite") Try db.Connect db.SQLExecute("BEGIN TRANSACTION;") db.SQLExecute ("INSERT INTO Employees (Name,Job,YearJoined) VALUES " _ + "('" + TextField1.Text + "','" + TextField2.Text + "','" + TextField3.Text + "');") db.CommitTransaction Catch error As DatabaseException MessageBox("Error: " + error.Message) db.RollbackTransaction End Try

When I replaced db.SQLExecute with db.ExecuteSQL, I get an error (syntax) in the TextFields line. I removed the single quotes, but the error is still there.
The example from the dmg use a DataBaseRecord, not INSERT.

BTW: I corrected the create the data base file and add a TABLE examples to be in tune with the above…

[Sascha_S]

Currently not at my desk. I hope the following is correct:

db.ExecuteSQL("INSERT INTO Employees (Name,Job,YearJoined) VALUES (?,?,?)", TextField1.Value, TextField2.Value, TextField3.Value)

BTW: A select would look like this:

db.SelectSQL( "Select Name,Job,YearJoined from Employees where Name=? or Job=?", Array(TextField1.Value, TextField2.Value) )

[Jay_Madren]

Assuming you’re doing this in a new 2019R2 project (versus a project created prior to 2019R2), you’ll need to change .Text to .Value on the TextFields.

[Sascha_S]

@457372:@Jay Madren

Assuming you’re doing this in a new 2019R2 project (versus a project created prior to 2019R2), you’ll need to change .Text to .Value on the TextFields.

Exactly, but while your’e working at it better go “my” Prepared Statement example. Because it looks like your’e dealing with user input data and in such a case your should always protect your SQL code from possible injections.

[Norman_Palardy]

only downside is it creates a new prepared statement on each call
so for some db’s you lose a pile of the performance advantages of using a prepared statement which is intended to be reused over & over

[Sascha_S]

But he’s starting a Transaction and commiting it right afterwards. I did not wanted to rewrite his whole Method. Just help getting used to the new API.

[Emile_Schwarz]

Thank you for the Value change/ I do not saw that in the Release Notes…

From the example, taken from Xojo 2019r2:

db.DatabaseFile = New FolderItem("MyDB.sqlite")

is marked as deprecated… but works without warning.

BTW: I am starting to love warning at compile time.

Now it works fine.

Excepted for the ’ character: I get a crash for it with strange reason (what Or have to do here ?). That was what I wanted to check.
With SQLExecute: there was no crash, no error, the record was not saved.

[Jay_Madren]

[quote=457593:@Emile Schwarz]Thank you for the Value change/ I do not saw that in the Release Notes…

From the example, taken from Xojo 2019r2:

db.DatabaseFile = New FolderItem("MyDB.sqlite")

is marked as deprecated… but works without warning.

BTW: I am starting to love warning at compile time.

Now it works fine.

Excepted for the ’ character: I get a crash for it with strange reason (what Or have to do here ?). That was what I wanted to check.
With SQLExecute: there was no crash, no error, the record was not saved.[/quote]
Like Sascha, I didn’t actually try your code in 2019R2 (because it also meant having to setup a database to match). But your reply got me curious so I went ahead and tried it. Using your original code and only changing SQLExecute to ExecuteSQL, it works fine for me (actually, it works fine either way).

I think your problem(s) are due to mixing API 1 & 2. SQLExecute doesn’t generate exceptions, so you have to use the old error checking methods to catch any errors. If you had, you probably would have found the reason it wasn’t working.

As for the syntax error you mentioned, you must not have used the exact code as you listed, as it works fine for me as is. So just what ’ character are you referring to? Do you perhaps have a ’ character in the data you’re trying to insert? That would cause a problem with the way your code is written. And that brings us to the new automatic prepared statement like Sascha used. It will properly handle situations like that.

One more thing - you should put the db.Connect in it’s own Try block, separate from the rest of the code. Because if an error occurs in the Connect method, the RollbackTransaction you’re doing in the exception handler will generate another exception.

[Emile_Schwarz]

@457660:@Jay Madren

I think your problem(s) are due to mixing API 1 & 2.

Not really my code.

I take it from the LR and only set the three TextFields. For the record, here is the code in both PushButtons:

PushButton 1: ProcessDB

[code]Sub Pressed() Handles Pressed
// 1. Create a Data Base File
Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem(“MyDB.sqlite”)
Try
db.CreateDatabase
// proceed with database operations…

db.Connect

// App.DB is a SQLite database
Var sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
Try
  db.ExecuteSQL(sql)
  MessageBox("Team table created successfully.")
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

Catch error As DatabaseException
MessageBox("Database not created. Error: " + error.Message)
Return
End Try
End Sub[/code]

PushButton 2: AddRecord

[code]Sub Pressed() Handles Pressed
// Add the TextFields contents as a new Record…
Var db As New SQLiteDatabase
db.DatabaseFile = New FolderItem(“MyDB.sqlite”)

Try
db.Connect
db.ExecuteSQL(“BEGIN TRANSACTION;”) // Was:db.SQLExecute
db.ExecuteSQL ("INSERT INTO Team (Name,Coach,City) VALUES " _
+ “('” + TextField1.Value + “', '” + TextField2.Value + “', '” + TextField3.Value + “');”) // Was:db.SQLExecute and three strings *
db.CommitTransaction

Catch error As DatabaseException
MessageBox("Error: " + error.Message)
db.RollbackTransaction
End Try
End Sub[/code]

• The Table name was different in the LR example.

You can compare with the LR: nearly a Copy/Paste.

I only wanted to know what was different with API 1. :([quote=457660:@Jay Madren]So just what ’ character are you referring to? Do you perhaps have a ’ character in the data you’re trying to insert? That would cause a problem with the way your code is written. And that brings us to the new automatic prepared statement like Sascha used. It will properly handle situations like that.[/quote]
Your guess is correct: I wanted to know what happens and set a ’ in the TextField (and use also some non ASCII characters).

@457660:@Jay Madren

And that brings us to the new automatic prepared statement like Sascha used. It will properly handle situations like that.

This part I do not understand how it is meant to be working. And that - as I already say - was the purpose of this example.

I think I will stop this until November or December once all the frenzy will stop and come back to 2019r2 when .1 or .2 will be released.

Have a nice week end all !

[Emile_Schwarz]

The examples comes from the LR, at the bottom of:

http://documentation.xojo.com/api/databases/sqlitedatabase.html

and:

http://documentation.xojo.com/api/databases/database.html#database-executesql

As is, the above examples worked fine (excepted I wanted to test the ’ character, and so change a bit the write a record part).