SQLitePreparedStatement and SelectSQL return different sort order

[Adam_Gerson]

I am trying to perform a complex sort where matches of a full name get sorted before partial matches of a name. What you see here is row id, last_name, and a sort_key.

When I use SelectSQL it works as I would like it to (with 1 coming before two as the sort_key):

SQL = "SELECT *, ( CASE WHEN LOWER(last_name) = LOWER('"+SearchComboBox.Text +"') THEN 1 WHEN last_name LIKE '%"+SearchComboBox.Text +"%' THEN 2 ELSE 3 END ) AS sort_key  " _
+"FROM people WHERE last_name LIKE '%"+SearchComboBox.Text +"%' " _ 
+ "ORDER BY (CASE WHEN LOWER(last_name) = LOWER('"+SearchComboBox.Text +"')THEN 1 WHEN last_name LIKE '%"+SearchComboBox.Text +"%' THEN 2 ELSE 3 END), first_name"
rowsFound = App.DB.SelectSQL(sql)

However, if I use SQLitePreparedStatement the sort keys are not calculated the same way. Note the “2’s”. I think my SQL is identical:

SQL = "SELECT *, ( CASE WHEN LOWER(last_name) = LOWER(?) THEN 1 WHEN last_name LIKE ? THEN 2 ELSE 3 END ) AS sort_key "  _ 
+ "FROM people WHERE last_name LIKE? " _ 
+ "ORDER BY (CASE WHEN LOWER(last_name) = LOWER(?) THEN 1 WHEN last_name LIKE ? THEN 2 ELSE 3 END), first_name"

Var ps As SQLitePreparedStatement
ps = App.DB.Prepare(SQL)
For i As Integer = 0 To 4
  ps.BindType(i, SQLitePreparedStatement.SQLITE_TEXT)
  ps.Bind(i, "%" + SearchComboBox.Text + "%")
Next
rowsFound = ps.SelectSQL

[Wayne_Golding]

Your SQL is not identical. You are adding “%” before and after all bindpoints, but in the SelectSQL version you are not.

LOWER('"+SearchComboBox.Text +"')
Is not equal to
LOWER('%"+SearchComboBox.Text +"%')

[Adam_Gerson]

Thanks! My eyes had grown blind to it. Fixed with this now more verbose breakdown:

ps.BindType(i, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(i, SearchComboBox.Text)
i = i + 1

ps.BindType(i, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(i, "%" + SearchComboBox.Text + "%")
i = i + 1

ps.BindType(i, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(i, "%" + SearchComboBox.Text + "%")
i = i + 1

ps.BindType(i, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(i, SearchComboBox.Text)
i = i + 1

ps.BindType(i, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(i, "%" + SearchComboBox.Text + "%")
i = i + 1

[MarkusR]

from 2019r2 you can also add the parameters behind the query.
as example
rowsFound = db.SelectSQL("SELECT * FROM Customer WHERE Age=? AND PostalCode=?", Age.Value, PostalCode.Value)

[TimStreater]

Indeed, so one may dispense with all that ps.bind business.