How do I avoid text query parser error?

[former_SQLA_member1694911]

In SA 11.0.1, is there a way to have CONTAINS() return NULL if contains-query-string has a syntax error without (a) having to pre-validate the contains-query-string in my application, and without (b) it throwing an exception?

CONTAINS() seems to introduce a new situation. The query string is a very reasonably value to get by prompting the user. In most other cases of user-provided data, as long as you provide some minimal up-front editing, convert the data to the proper format, and avoid SQL-injection, an incorrect user-entered value would result in an expression using it to become NULL or the query returning unexpected values. The few cases that need extra attention (e,g, date/time format), are pretty easy to deal with.

In other words, bad user-entered data could produce poor results, but it's not hard to prevent user-entered data from causing the SELECT to actually fail. That's not the case for the query string. You are simply providing a string, and it's not easy for an application to pre-validate the syntax. In fact, it would be a very bad idea to build validation rules for the current SA version's query string syntax into your application.

If I wrap the CONTAINS() expression in a user-defined function, I could trap the error and return NULL, but I'd be keeping the optimizer from handling the CONTAINS() expression effectively.

I know how to handle SQL errors in my code. By that point, my options are much too limited - I'd like prevent it becoming that.

Are there any options or other magic that would let CONTAINS handle text query parser errors differently?

Thanks, Dan

[Breck_Carter]

http://sqlanywhere.blogspot.com/2008/02/tip-all-encompassing-try-catch.html

[former_SQLA_member1694911]

Thanks, Breck! It's great information. I need to ponder how to fit this into our application structure. (where "ponder" = "drink a lot of coffee while staring at the screen")

[Former Member · Accepted solution]

During development of the full-text feature we had several discussions about this particular issue: whether or not to return the empty set (if CONTAINS is used in the FROM clause) or to evaluate to unknown (if used in a WHERE clause) if the input query was syntactically invalid.

At the end, we decided that it was best to return a syntax error, rather than an empty set or NULL, so that the user could properly distinguish between a valid search that failed to return any result, from a search that contained invalid syntax.

So to answer your specific questions: there is no way to issue a syntactically invalid CONTAINS search condition without raising an error. Trapping the error within a BEGIN-END block, as per Breck's example, is one strategy that may be useful to you. Alternatively, you could prompt your user for specific search patterns, ensure that those components do not contain invalid tokens, and then construct the appropriate CONTAINS search condition within the application.

[former_SQLA_member1694911 · Accepted solution]

Thank you VERY, VERY much, Breck and Glenn!!!!

Unfortunately, the part of the code that "knows" it's doing a CONTAINS search is pretty isolated from and the parts of the code that could either deal with an exception or wrap the query in a BEGIN-END block. But we'll find a way to make it work.

Can I offer a suggestion for the future: a system function to test the validity of a search, so that you could make an expression to test and bypass if it's bad? (Not to diminish the great work you've done already. I LOVE SQL Anywhere.)

2)