SQL Like Query failing

1

Hi,

I have had no success parameterizing a SELECT query using LIKE.

This is what I am using:

args = [nIDtext.Text]
sqlList = []
sqlList[0] = ["SELECT * FROM AList WHERE nID LIKE %?% ", args, initSuccess, initFail]
Sql(DB1, sqlList)

This also fails:

sqlList[0] = ["SELECT * FROM AList WHERE nID LIKE '%?%' ", args, initSuccess, initFail]
sqlList[0] = ["SELECT * FROM AList WHERE nID LIKE '%'?'%' ", args, initSuccess, initFail]

This works:

sqlList[0] = ["SELECT * FROM AList WHERE nID LIKE '%" & nIDtext.Text & "%' ", initSuccess, initFail]

Any help would be great.
Thanks.

2

From the Wiki: Using SQLite - NSB App Studio

Parameters
Parameters allow you to simplify your coding by putting the arguments into a separate array. It’s also a more secure way of handling data from users: they will not be able to do an SQL Injection attack on your database.

To use parameters, put a question mark (?) for each item in the Values clause. Then, add a new item to your SQL operation which contains an array of the values.

    args = ['Haley',16,121]
    sqlList[0]=[ "INSERT INTO customerData (name,age,sales) VALUES (?,?,?)", args]

You will find this method is easier to use, requires less code and is easier to read than putting the values inline.

3

Yes, i was able to use parameters successfully for SELECT, INSERT and UPDATE commands. However, LIKE doesn’t seem to work the same way. The failed SQL function is always called.

Is this a little different to the others?

4

This looks like the same issue - with a couple of workarounds.

5

That helped a lot, thanks.
I changed it as below and it works now.

args = [“%” & nIDtext.Text & “%”]
sqlList =
sqlList[0] = ["SELECT * FROM AList WHERE nID LIKE @nID ", args, initSuccess, initFail]
Sql(DB1, sqlList)