cancel
Showing results for 
Search instead for 
Did you mean: 

How do I ensure triggers and procedures are valid SQL.

Former Member
3,482

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

Breck_Carter
Participant

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...

Query optimization and execution

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.

Query Processing Based on SQL Anywhere 12.0.1 Architecture

Former Member
0 Kudos

Thank you, For taking the time to answer the question. It seems I am just going to have to get use to these issues. As there are multiple people updating meta data in the company. This allows for many run-time gotcha's. I simply do not have the time nor the methodology, to write testing metadata for all the current metadata in the projects in the company.

It would really be sweet if there was some tool that simply validated all the metadata stored in the database.

Kind Regards,

Robert.