問題描述
如何在 Adobe Air 的 LIKE 運算符模式中使用命名參數 (How to use named parameters inside LIKE operator pattern in Adobe Air)
I wanted to use a named parameter placeholder inside the LIKE operator pattern so that the argument string is properly escaped.
Here's my modified code where I am using the at‑param placeholder:
var stmt = new air.SQLStatement();
stmt.text = "SELECT * FROM comments WHERE title LIKE '%@search%';";
stmt.parameters["@search"] = "argument string";
stmt.execute();
Doing so yields an SQLError with the following details
message: Error #3315: SQL Error.
details: '@search' parameter name(s) found in parameters property but not in the SQL specified
As suggested by Mike Petty, I tried:
stmt.text = 'SELECT * FROM comments WHERE title LIKE "@%search%";';
Which yields to the same SQL Error details.
Documentation has this:
expr ::= (column‑name | expr) LIKE pattern
pattern ::= '[ string | % | _ ]'
My suspicion is that it is skipped due to the qoutes, any ideas on how to make it work?
‑‑‑‑‑
參考解法
方法 1:
Found a solution for this, basically instead of doing it like this:
var stmt = new air.SQLStatement();
stmt.text = "SELECT * FROM comments WHERE title LIKE '%@search%';";
stmt.parameters["@search"] = "argument string";
stmt.execute();
You have to put a placeholder for the entire LIKE operator pattern and bind the pattern as a parameter.
var stmt = new air.SQLStatement();
stmt.text = "SELECT * FROM comments WHERE title LIKE @search;";
stmt.parameters["@search"] = "%" + userInput + "%";
stmt.execute();
方法 2:
Remove your :string from your string.
Like works just fine as:
SELECT * FROM comments WHERE comments.title LIKE '%string%';
方法 3:
It's hard to tell from your question, but is your current statement either throwing a SQLError, just doesn't compile, or just doesn't return any results?
If I had to guess I'd say you have a few issues here:
- You shouldn't have to qualify the column in the where clause since you only have 1 table to select from
- The parameter string is technically a reserved word by AIR / Actionscript (although the case is different) ‑ still a bit confusing.
Even though the documentation and code allow you to use either the colon, or at‑symbol, my preference is the @ since it's a bit easier to see ;)
Don't forget to add an itemClass definition of the Class you expect for rows ‑ this helps to avoid anonymous objects.
This should be fairly straight forward:
var stmt:SQLStatement = new SQLStatement();
stmt.itemClass = Comment;
stmt.text = 'SELECT * FROM comments WHERE title LIKE "@%search%";';
stmt.parameters['@search'] = userInput.text;
stmt.addEventListener(SQLEvent.RESULT, onResults);
stmt.execute();
(by user1423172、user1423172、Steve Ross、Mike Petty)