I just spent the last 2 days weeding out invalid SQL ( ie. SQL that errors out of triggers in a database ) I previously worked with Firebird which would not allow invalid SQL into a database. How can I ensure SQLAnywhere does not have invalid SQL.
By the way am using SQL Anywhere 12.
asked 12 Feb '15, 18:20
First of all, welcome to SQL Anywhere!
There are many differences between SQL Anywhere and other products like Oracle, and one of those differences is the late binding of stored procedure logic with information from the catalog.
When you store a procedure, trigger or event block in the database, it will be scanned and parsed for correctness up to a point. Most simple syntax errors are caught early (missing commas, malformed statements, etcetera) and those errors prevent the block from being stored.
Other errors are not caught until execution time because SQL Anywhere performs "just in time" optimization, and that means references to user, table, column, connection-level variable (CREATE VARIABLE), function and procedure names aren't checked for existence until execution time.
This has many benefits: You can write code that creates and uses objects at execution time, query optimization uses the latest statistics to create the best plans rather than use fixed out-of-date plans, and you don't have to recompile existing procedures because a schema change "invalidated" them.
Late binding also has a major disadvantage: A large class of simple syntax errors (speling misteaks in table and column names) aren't caught until runtime.
The good news is, it's hard to imagine one of these speling misteaks getting past the most basic of "coverage tests" that execute every line of code at least once. As Dmitri says "testing is your best friend."
Personally, I spend most of my time writing and testing SQL Anywhere stored procedure code, I deal with "Sassen! Frassen! Fricken! Fracken!" late binding syntax errors all the time, and they never cause me to wish for early binding because that would increase my workload far more than the late binding errors.
Here are some docs on the subject...
Unlike many other commercial database systems, SQL Anywhere usually optimizes each statement just before executing it. Because SQL Anywhere performs just-in-time optimization of each statement, the optimizer has access to the values of host and stored procedure variables, which allows for better selectivity estimation analysis. In addition, just-in-time optimization allows the optimizer to adjust its choices based on the statistics saved after previous query executions.
answered 13 Feb '15, 09:09