Convert Traditional to PreparedStatement code

I have the code below (as an example) and I want to convert it to use PreparedStatement.

All I can say are:
a. I failed (and I cannot talk about the docuentation good or bad),
b. I spend my whole morning (today), but not only at searches here and there without reaching anything valuable.

Maybe I am loosing patience * with that, I come here for some help.

I do not ask the whole replacement of my code below; just a skeleton with only one Column, so I can elaborate the complete code alone (and continue 'cause I have far more to convert).

TIA,

Emile

[code]
// Dim a DatabaseRecord
Dim DB_Row As New DatabaseRecord

// 1. Feed the DataBaseRecord

// a. Civilits
DB_Row.Column(“Sexe”) = PM_Sexe.Text
DB_Row.Column(“PNom”) = TF_Prenom.Text
DB_Row.Column(“Nom”) = TF_Nom.Text

// b. Adresse
DB_Row.Column(“Adresse1”) = TF_Adresse1.Text
DB_Row.Column(“Adresse2”) = TF_Adresse2.Text
DB_Row.Column(“Zip”) = TF_Zip.Text
DB_Row.Column(“Ville”) = TF_City.Text

// c. Phone-Mail
DB_Row.Column(“Phone”) = TF_Phone.Text
DB_Row.Column(“eMail”) = TF_Mail.Text

// d. Birth
DB_Row.Column(“Birth_Date”) = TF_Born_Date.Text
DB_Row.Column(“Birth_Place”) = TF_Born_Place.Text

// e. La photo
DB_Row.PictureColumn(“Photo_ID”) = cPhoto.Backdrop

// 2. Ajoute le DatabaseRecord
mSQLite.gAccueillants_DB.InsertRecord(“Accueillants”, DB_Row)
If mSQLite.gAccueillants_DB.Error Then
MsgBox “Impossible dajouter cette Fiche.” + EndOfLine + EndOfLine +_
"Erreur " + Str(mSQLite.gAccueillants_DB.ErrorCode) + ": " + mSQLite.gAccueillants_DB.ErrorMessage

Return

End If[/code]

  • Not xojo. I was searching a red fruit from a tree yesterday and found the worst web site I ever saw: the photos are first, then comes the forum (texts) entries without any clue (Index, name) to know what Photo goes to which text absolutely, totally useless. I found the Photo of the fruit I want, but not the comment; how could it be ? :frowning:

Here’s a prepared statement I am currently using (its a method of a data class, hence the Me references):

[code]Dim sql As String
Dim ps As SQLitePreparedStatement

// Insert only if it’s not already in the database (ie - it doesnt have an ID)
If Me.ID < 1 Then
sql = “INSERT INTO playlist (”
sql = sql + "name, "
sql = sql + “sort_order”
sql = sql + “) VALUES (?, ?)”

ps = App.db.Prepare(sql)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, Me.Name)
ps.Bind(1, Me.SortOrder)
ps.SQLExecute

If NOT App.db.Error Then
Me.ID = App.db.LastRowID
End
End[/code]

Use SQLdeLite - it does all the work for you for all supported databases. Https://www.github.com/1701software/SQLdeLite

@Phillip Zedalis - nice! That certainly removes the headaches. Emile still would probably like to understand how to create and use a prepared statement the hard way, though…

Emile, this is the first time I see code with DatabaseRecord and I think that’s why is hard for you to use PreparedStatements. I started learning Xojo and SQLite not long ago and always used the complete SQL Insert statement and I think that’s easier to change to PreparedStatements.

A partial skeleton from your code could be:

[code]Dim ps As SQLitePreparedStatement = mSQLite.gAccueillants_DB.Prepare(“INSERT INTO Accueillants (Sexe,PNom,Nom) VALUES (?, ?, ?)")

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)

ps.Bind(0, PM_Sexe.Text)
ps.Bind(1, TF_Prenom.Text)
ps.Bind(2, TF_Nom.Text)

ps.SQLExecute[/code]

Note: I’m just learning, so my code is just for illustration and most likely have errors.

Multiple ways to skin this cat. We use ActiveRecord and your code would look something like this:

Dim oNewRecord as new Data.Accueillants oNewRecord.Sexe = PM_Sexe.Text oNewRecord.PNom = TF_Prenom.Text oNewRecord.Nom = TF_Nom.Text oNewRecord.Save

ActiveRecord creates the Prepared Statement for you in the background and you never have to worry about it. https://www.bkeeney.com/allproducts/argen/

I haven’t used it, but maybe Kem’s SQLBuilder could help too.

And I guess that’s the point. We all reach that point where doing it the old, drawn out way, is tiring, mistake prone, etc and we’ve come up with ways to make database code easier/faster/better. “Better” is in the eye of the beholder. But in all cases I’d say you really need to know how Xojo works before trying the shortcuts.

@Bob Keeney

Indeed.

And I use my own QueryDb, which does it all above and a bit more, for all database-types I use. (PostgreSQL, sqLite, MySQL)
Think everybody dealing with databases frequently in applications finds his way to work fast and robust. But, it’s very true, you should understand databases, preparedstatements etc. first in dept, before you should start abstracting things.

I take that from the LR. First with REALSQLDataBase (unsure of the name), then I updated my code to SQLite / Xojo.

Also, with the Courier font and some spaces, the code looks nice. This helps - most of the time - to find typing or Copy/Paste errors.

I will read the whole answers after dinner. Thank you all.

Edit: I forget to mention that this is a desktop Application.