SQLite Prepared Statements

Hi all,

Going crazy ATM :wink: 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?

Alex

Me neither. Can you show your code of how you build your prepared statement and as comparison the query string you use in your favourite SQLite App?

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 :slight_smile:

[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

dim rs as RecordSet

rs = ps.SQLSelect()[/code]

[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.

I have a sense that it’s due to my quoting.

 SELECT id FROM (Contacts) WHERE (Column1 LIKE '%?%') OR (Column2 LIKE '%?%') ORDER BY id ASC LIMIT 0, 50;

If Column1 gets bound as text, does it internally add any quotes? How would one do the approximate querying using % then?

Also try

LIKE '%' ? '%' 

Let me know if that works, I had an issue in PHP using ‘% ? %’

Note the additional single quote after the first % and before the second %

Didn’t help either… Marked your post as answer by mistake :frowning:

[quote=77070:@Mike Charlesworth]Also try

LIKE '%' ? '%' 

Let me know if that works, I had an issue in PHP using ‘% ? %’

Note the additional single quote after the first % and before the second %[/quote]

Edit: Neither does it when I add the % to the bind value itself. I then simply get all records.

Dug my code out try this:

LIKE CONCAT('%',?,'%')

[quote=77076:@Mike Charlesworth]Dug my code out try this:

LIKE CONCAT('%',?,'%')

Nope, “no such function”?

Edit:
‘%’ || ? || ‘%’
Doesn’t work either, though it’s supposed to concatenate in Sqlite :slight_smile:

What’s the data type of id (in the database)?

Hello Alex,

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.

Maybe this will help?

[quote=77081:@Eugene Dakin]Hello Alex,

Here is some SQLite Prepared Statement code which works on my Windows 8.1 machine with Xojo 2014 r1.

The retrieved data has three database rows that are: Integer, Double, and String.

Maybe this will help?[/quote]

Then I simply get all records. Hmm. Things getting more and more crazy.

It’s an integer (primary key)

Hi Alex,

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

[quote=77084:@Eugene Dakin]Hi Alex,

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.

Could it be that your internal array fieldbindings() is 0-based, while the fields in RecordSets and PreparedSQLStatement bindings are 1-based?

According to the docs, the SQLitePreparedStatement is 0-based. Am I missing your point?

Yes, you are right.

I found it. Pheeew.

Sqlite is weakly-typed, which is a pain and a cure. You need to explicitly bind it as SQLITE_TEXT if you want to use the LIKE % operator. Jesus.

[quote=77076:@Mike Charlesworth]Dug my code out try this:

LIKE CONCAT('%',?,'%')

Apologies - The above is for MySQL if anyone needs to know for future reference.