more trouble with SQLitePreparedStatement

Getting Started
1

Can anybody tell me why the following code returns no data? (the select statement itself is correct)
My understanding is that I need to use SQLitePreparedStatement because my db data contains special characters, and the data was inserted with SQLitePreparedStatement as well.

[code]Dim dbFile As FolderItem
dbFile = SpecialFolder.ApplicationData.child(“Bible Notebook”).child(“biblenotebook.sqlite”)

Dim db As New SQLiteDatabase
db.DatabaseFile = dbFile

If db.Connect Then
  
  Dim ps As SQLitePreparedStatement
  ps = SQLitePreparedStatement(db.Prepare("SELECT * FROM data WHERE date='"+me.cell(row,0)+"' AND heading='"+me.cell(row,1)+"'"))
  
  ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)  //db "id" column
  ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
  
  Dim rs As RecordSet = ps.SQLSelect
  
  // viewer is a seperate window

  viewer.viewdate.text = rs.field("date").stringvalue
  viewer.heading.text = rs.field("heading").stringvalue
  viewer.refs.text = rs.field("refs").stringvalue
  viewer.notes.text = rs.field("notes").stringvalue
  
end if[/code]
2

You are not using the PreparedStatement properly. The point is to create placeholders for your data, then have the engine insert the values properly later.

Also, there are only two places for values in the statement but you are binding five.

Try something like this:

      Dim ps As SQLitePreparedStatement = _
        db.Prepare("SELECT * FROM data WHERE date= ? AND heading=?")

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

      dim rs as RecordSet = ps.SQLSelect( me.Cell( row, 0 ), me.Cell( row, 1 ) )
3

Thanks, Kem…I think I’m slowly starting to get my head around SQLitePreparedStatement. Dunno why, but it’s confusing the heck outta me. :wink:

4

It’s a lightbulb kind of thing. It’s confusing until suddenly a lightbulb goes off, and then it makes perfect sense. We all went though it.

5

One more question, if I may…
do I need to use SQLitePreparedStatement to delete data from the db? Suddenly my delete statements don’t seem to work any more.

6

you should use SQLitePreparedStatement whenever the SQL would have a + to concat values.

So typically you need a record ID for delete and so you use a SQLitePreparedStatement.

7

Thanks, Christian.
I’m not deleting via record ID, I’m doing it via:

DELETE FROM data WHERE date='"+me.cell(row,0)+"' AND heading='"+me.cell(row,1)+"'

Sorry to be so thick, but I still don’t get it.

8

you should not use + here and add values from user interface directly.
Use Prepared Statement.

e.g.

DELETE FROM data WHERE date=? AND heading=?

9

Got it. Thanks again!
So the full code for this delete statement is:

[code] Dim dbFile As FolderItem
dbFile = SpecialFolder.ApplicationData.child(“Bible Notebook”).child(“biblenotebook.sqlite”)

  Dim db As New SQLiteDatabase
  db.DatabaseFile = dbFile
  
  If db.Connect Then
    
    Dim ps As SQLitePreparedStatement = _
    db.Prepare("DELETE FROM data WHERE date= ? AND heading=?")
    
    ps.BindType( 0, SQLitePreparedStatement.SQLITE_TEXT )
    ps.BindType( 1, SQLitePreparedStatement.SQLITE_TEXT )
    
    ps.SQLExecute( me.Cell( row, 0 ), me.Cell( row, 1 ) )
    
  end if[/code]

Works so far. have I missed anything?

10

don’t forget to check error property. In case something goes wrong.

11

Right. thanks. :slight_smile: