How to concatenate LIKE with parameter

Databases
1

I want to be able to use the LIKE command with a passed parameter within a SQLite database using the || concatenation tool using this SQL request:

SELECT id FROM Lockers WHERE id <> :id AND ObfuscatedPathParent LIKE %||:ObfuscatedPathParent
or with single quotes:
SELECT id FROM Lockers WHERE id <> :id AND ObfuscatedPathParent LIKE '%'||:ObfuscatedPathParent

but I get the error (I have already added the SQLPreparedStatementMBS.BindType(…)):

Bind variable/parameter ‘ObfuscatedPathParent’ not found

It seems as though SQLite is absorbing the :ObfuscatedPathParent itself as text and not a parameter. My issue is with SQLDatabaseMBS, but I assume it is the same with SQLiteDatabase.

How can I add a parameter into a LIKE statement in SQLite?

2

I guess you would need to create the complete LIKE pattern (including wildcards) in code before binding it as a parameter.

Var strPlaceholder As String = "%" + ObfuscatedPathParent
("SELECT id FROM Lockers WHERE id <> ? AND ObfuscatedPathParent LIKE ?", theId, strPlaceholder)
3

I’m confused by this. Is ObfuscatedPathParent a field name or is it a string that you’re string to search on?

4

Yes, ObfuscatedPathParent is a field name in the database. With SQLdatabaseMBS you pass a parameter by using a colon on the front. For clarity I pass the same parameter name as the field name but with a prepended colon.

5

I’m confused by this. Is ObfuscatedPathParent a field name or is it a string that you’re string to search on?

I see two examples:

s1 || s2 || s3

and

SELECT 'SQLite ’ || ‘concat’ result;

on that page. Both seem to indicate that you need spaces around the || for one thing.

6

Have you tried using the concat() function instead?

7

If I used the normal SQLite form to find all the records ending with 'ABC", it would be:
LIKE ‘%ABC’
but if I want to use a parameter it becomes:
LIKE ‘%:ABC’
but this doesn’t work since it searches for the records ending in ‘:ABC’.
So I tried using concatenation to insert my parameter, but SQLite seems to absorb it and returns the error.

8

Try it as

‘%’ || :ObfuscatedPathParent

With the spaces surrounding the ||. I’d bet the prepared statement engine is getting confused by the pipes and colon.

9

It handles your paramter like a literal string. Try as @Greg_O recommended a CONCAT(‘%’, ObfuscatedPathParent) or do what i recommended above How to concatenate LIKE with parameter - #2 by Sascha_S. The latter is how i do it most times and other times i do like @Greg_O recommended. Both ways work just fine.

1 Like
10

This simply work here:


Var db As new SQLiteDatabase

db.Connect

var sql As String = "CREATE TABLE tb ( "+_
"id INTEGER PRIMARY KEY AUTOINCREMENT, "+_
"txt TEXT"+_
" );"

db.ExecuteSQL( sql )

db.ExecuteSQL("INSERT INTO tb (txt) VALUES (""ashdfjahdfABC"");")
db.ExecuteSQL("INSERT INTO tb (txt) VALUES (""ashdfjahdfCDE"");")

Var rs As RowSet

Var ending As String = "BC"

rs = db.SelectSQL("SELECT * FROM tb WHERE txt LIKE '%'||:ending", ending)

break

The error is elsewhere if following the syntax as shown in my example.

11

Is there any reason you are avoiding this syntax?

var sSearch as String = "%" + sObfuscatedPathParent
rs = db.SelectSQL("SELECT id FROM Lockers WHERE id <> :id AND ObfuscatedPathParent LIKE ?", sSearch)
12

The reason why people avoid the simple “?” when using SQLITE is just due SQLITE recommendation. They consider it a subpar parametrization (people can error targets missing counts) only existing to follow compatibility with some other DBs.

13

I was more talking about “putting the percent sign in the Xojo code.” The ? was just habit.

14

I know, all people used to MYSQL will do just that. And for an one parameter only I can’t see anything against it too, even in a SQLITE context. :wink:

15

Thanks to all who participated in finding the solution.

There were two caveats to this issue though, I am using MBS’s SQLDatabaseMBS (thus the colons) and not Xojo’s SQLiteDatabase (which uses ?'s), plus I am connecting to a SQLite database and not MySQL, so I don’t have access to the CONCAT() function. So I don’t know if the issue was created by MBS or SQLite.

Either way, @Greg_O 's solution worked by putting spaces around the SQLite || concatenation function ie this works:
id <> :id AND ObfuscatedPathParent LIKE '%' || :ObfuscatedPathParent

16

:man_facepalming: My question was NOT about which placeholder you were using and still remains unanswered.

Unless there’s some really compelling reason I’m going to learn about today, I don’t see why you would want to complicate the SQL in this manner and not just concatenate in Xojo code.

1 Like
17

Nope. Incorrect. Current SQLiteDatabase uses all the SQLITE parameter syntax, like:

? , ordered
?1 , positional
:name , named
@name named.

If you check my example, you can see the :named working.

18

“I don’t see why you [don’t] … just concatenate in Xojo code”

Because the LIKE command is a mongrel to have to escape certain characters (eg \, ;, % and _).

For future searchers, using Tim’s example, but with SQLDatabaseMBS I would normally do this (not tested):

Var sSearch as String = "%" + sObfuscatedPathParent
Var tempSQL As String = "SELECT id FROM Lockers WHERE id <> :id AND ObfuscatedPathParent LIKE :sSearch"
Var tempSQLPreparedStatementMBS As SQLPreparedStatementMBS = db.Prepare(tempSQL)

mySQLPreparedStatementMBS.BindType("sSearch", SQLPreparedStatementMBS.kTypeString)
mySQLPreparedStatementMBS.Bind("sSearch", sSearch)
rs = tempSQLPreparedStatementMBS.SelectSQLMT
1 Like
19

Thanks @Rick_Araujo , I didn’t know that and have learnt even more today.

1 Like