SQLite multiple like

Databases
1

Coding on almost no sleep, so I’m likely making a stupid mistake however…

I’m trying to do the query below and submit it with the parameter array for each variable.
I’ve debugged that I do have the right number of variables but a result comes back empty that should not.
I’ve also hard coded the query without the placeholders and I do get the desired result.

This doesn’t work:

Select Field1 from TableA where id in (select reference_id from TableB where reference_type = $1 and ( value like ‘%$2%’ or value like ‘%$3%’) order by reference_id desc)

But this does:
Select Field1 from TableA where id in (select reference_id from TableB where reference_type = 0 and ( value like ‘%fizzy%’ or value like ‘%pop%’) order by reference_id desc)

At first I thought it was that I was submitting a variant array for the parameters, rather than the variants one-by-one, but I’ve tested both ways and it makes no difference.

I’m hopeful that someone will point out a blatant mistake so I can jog on.

Thanks in advance.

2

I think the % signs go in the values you pass, not in the prepared statement itself. Ie.,

(value like $2 or value like $3)

and you pass “%fizzy%” and “%pop%” as parameters.

2 Likes
3

Is the right answer. Then you pass the args thus:

x = db.selectSQL (yoursql, “%fizzy%”, “%pop%”)

(with the proper double-quote chars, of course).

4

Tim, that’s the ticket. Thanks so much.