Prepared Statement help needed.

Databases
1

Hi,
I have never used a Prepared Statement to UPDATE a database record, and I am therefore, struggling with the syntax :frowning:
Could someone please help me?

The first code segment below, is my CURRENT WORKING CODE.

[code] // UPDATE THE DATABASE IF BOTH TEXT FIELDS HAVE DATA
If TitleField.text <> “” And CodeField.text <> “” Then
rs.Edit
rs.field(“Title”).StringValue=TitleField.text
rs.field(“Code”).StringValue=CodeField.Text
rs.Update

// CHECK FOR DATABASE ERROR
If db.Error Then
  MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
  Return
  
Else
  
  // COMMIT THE CHANGES
  db.commit
  
  // UPDATE THE LISTBOX
  MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle
  
  // CLOSE THE WINDOW
  Self.Close
  
end if

End If[/code]

The second code segment below, is my new prepared statement SO FAR.

[code] // PROCEED IF BOTH TEXT FIELDS HAVE DATA
If TitleField.text <> “” And CodeField.text <> “” Then

// PREPARE THE STATEMENT

// THE LINE BELOW IS THE LINE I AM STRUGGLING WITH
Dim ps As SQLitePreparedStatement = db.Prepare(“UPDATE Snippets SET (Title, Code) WHERE (?, ?);”)

// BIND THE UPDATED TEXT FIELD VALUES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.Bind(0, TitleField.text)
ps.Bind(1, CodeField.text)

// INSERT THE RESULT INTO THE RECORDSET
Dim rs As RecordSet = ps.SQLExecute


    // CHECK FOR DATABASE ERROR
If db.Error Then
  MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
  
Else
  // CLOSE THE EDIT WINDOW
 EditWindow.close
  
  // CLEAR THE LISTBOX
  MainWindow.Listbox1.deleteAllRows
  
  // POPULATE THE LISTBOX
  while not rs.eof
    MainWindow.Listbox1.addRow rs.Field("Title").StringValue
    rs.moveNext
  wend
  
end If

else
// DISPLAY ERROR MESSAGEBOX
MsgBox(“Please ensure both fields are completed, and then retry!”)

End if[/code]

2

Richard, I keep thinking this is the same thread. Can you differentiate your subjects?

3

And what’s wrong with your current code? Why do you want to replace it?

(Keep in mind that Update already uses a PreparedSQLStatement in the background.)

4

I was under the impression that I should always use a prepared statement, due to the fact that it works nicely with apostrophes which need to get saved?

5

So does rs.edit/rs.update.

6

Oh.
Thank you very much - I really appreciate the help!

7

Richard, set aside the prepared statement for a moment. How would you code the update in SQL? Forget the editfields, just use fixed literal values in the update statement.

update snippets set …

Fill in the rest in pure sql. Only then will you be ready to convert it to Xojo, much less a prepared statement. Remember, SQL is a language of its own. You need to master it before you can hope to be proficient enough to code it in another language (Xojo).

8

Tim,
I understand how to format the pure SQL syntax:

UPDATE Snippets SET Title='Hello World', Code='blah blah' WHERE language='Xojo';

The problem I have is converting the syntax into the prepared statement format.

I can use SQLSelect statements, and also SQLExecute statements - but prepared statements are new to me.

9

Great! Now replace the literal values with “?” and you’ve got a prepared statement.

Dim ps As SQLitePreparedStatement = db.Prepare("UPDATE Snippets SET Title=?, Code=? WHERE language=?")
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, TitleField.text)
ps.Bind(1, CodeField.text)
ps.Bind(2, LanguageField.text)
10

Tim,
It was that darned first line’s syntax which I could not work out - but now your explanation has made it clear.

However, I am trying to only update the entry which has the same Title Column value as what is selected in a listbox row.
When I use the code below - it replaces my entire database with those values?

[code]Dim ps As SQLitePreparedStatement = db.Prepare("UPDATE Snippets SET Title=?, Code=?, WHERE Title=?")

// SET THE BIND TYPES
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)

// SET THE BIND VALUES
ps.Bind(0, TitleField.text)
ps.Bind(1, CodeField.text)
ps.Bind(2, MainWindow.Listbox1.cell(MainWindow.Listbox1.listindex,0))[/code]
11

Richard we just open sourced SQLdeLite which makes this significantly easier. Check it out at: https://github.com/1701software/SQLdeLite

12

Thanks Phillip,
I was already going to take a look at that later :slight_smile: