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 |
http://sqlanywhere.blogspot.com/2008/02/tip-all-encompassing-try-catch.html
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")