Going crazy ATM If I execute a particular Query via SQLitePreparedStatements, I get an empty RecordSet. No Errors. If I do execute the query myself (replacing the ? with the actual values) in my favourite SQLite App, I get the results desired. Since PS are hard to debug (properties don’t show up in the debugger!), I have no clue where to begin looking at. Any ideas?
I had a major issue with them recently and found there was an omission in the Xojo docs when inserting into a database. But from the docs here is a select, are you following all the steps? I was missing the BindType part.
Dim sql As String
sql = "SELECT * FROM Player WHERE Team = ? SELECT *
FROM Player WHERE Team = ?"
Dim ps As SQLitePreparedStatement
ps = db.Prepare(sql)
// Identify the type of the first parameter
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
// Bind the first parameter to a value
ps.Bind(0, "Seagulls")
Dim rs As RecordSet rs = ps.SQLSelect?
I feared that I’d get asked to post code. It’s not easy however as it’s part of a huge set of classes (my ORM…). I’ll try to lay out the pieces necessary
[code] dim ps as SQLitePreparedStatement
ps = mDatabase.Prepare(SQL) //SQL (a string) could be ATM “SELECT id FROM (Contacts) WHERE (Column1 LIKE ‘%?%’) OR (Column2 LIKE ‘%?%’) ORDER BY id ASC LIMIT 0, 50;”
if bindings.Ubound > -1 then //bindings is an array of the actual values that need to get bound
for i as integer = 0 to bindings.Ubound
ps.BindType(i, getBindTypeByField(fieldbindings(i))) //getBindTypeOfField is a method that returns the correct bindType based on a table look up -> field type.
ps.Bind(i, bindings(i))
next
end if
[quote=77062:@Mike Charlesworth]I had a major issue with them recently and found there was an omission in the Xojo docs when inserting into a database. But from the docs here is a select, are you following all the steps? I was missing the BindType part.
[/quote]
Yep I got the binding part and the correct bindings are being applied.
Here is some SQLite Prepared Statement code which works on my Windows 8.1 machine with Xojo 2014 r1.
Dim rs as RecordSet
Dim ps as PreparedSQLStatement
ps =SQLitePreparedStatement(db.Prepare ("SELECT * FROM Widgets WHERE Description LIKE ? "))
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, "%P%")
rs = ps.SQLSelect
If rs <> Nil
Msgbox "Successfully grabbed data from SQLite with a Prepared Statement"
End If
The retrieved data has three database rows that are: Integer, Double, and String.
Good to hear that you are getting data, we are making progress. To view the ID (an integer), here is some code which should get this information. The table and column will need to be changed to match your database.
[code] Dim rs as RecordSet
Dim ps as PreparedSQLStatement
ps =SQLitePreparedStatement(db.Prepare("SELECT * FROM Widgets WHERE Description LIKE ? "))
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, “%P%”)
rs = ps.SQLSelect
If rs <> Nil then //Continue if there is data
While Not rs.EOF //continue until we reach the End Of File
MsgBox "The ID field value is: " + Cstr(rs.Field(“ID”).IntegerValue)
rs.MoveNext //move to the next recordset
Wend //get the next row of data
End If[/code]
This code should show the value of the ID in a messagebox. If there are three rows of data, then there will be three messagebox’s being displayed.
EDIT: Changed from a SQLITE_TEXT to a SQLITE_INTEGER
Good to hear that you are getting data, we are making progress. To view the ID (an integer), here is some code which should get this information. The table and column will need to be changed to match your database.
[trim]
This code should show the value of the ID in a messagebox. If there are three rows of data, then there will be three messagebox’s being displayed.
EDIT: Changed from a SQLITE_TEXT to a SQLITE_INTEGER[/quote]
Well, this is the tricky part. I simply get all records of the table. However, if I copy the statement, replace the ? with the actual values and run it in a sql editor, it works fine - the right rows get selected.