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 asked 29 Aug '10, 21:04 Dan Konigsbach |
During development of the full-text feature we had several discussions about this particular issue: whether or not to return the empty set (if 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 answered 30 Aug '10, 16:47 Glenn Paulley 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) |
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")