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

LoganTennis's gravatar image

LoganTennis
1217712
accept rate: 100%

It would be abnormal for SQL Anywhere to allow invalid SQL to be saved in views, triggers, procedures, functions or events, so it sounds like you hit a particular issue and maybe some details about that might help.

Since you just became a member of this forum just 8 minutes ago I will not be able to find your earlier issue here. I would assume it was not something as simple as a syntax error but some sort of semantic or execution context issue.

As the finding out more about such issues you could attempt a rebuild of the database with or without data. That should show such issues. A manual rebuild allows you to track errors 'in context' because (in GUI mode) DBISQL will show the statement causing the error in context; after which you can continue the rebuild to find any others. Being on the latest EBF/SP while doing that would avoid/identify any bugs that may have already been fixed.

(12 Feb '15, 18:33) Nick Elson S...

So Nick are you saying in more recent versions of sqlanywhere, it does not allow invalid sql to be saved in the database?

(12 Feb '15, 20:37) LoganTennis
1

@nick-elson-sap-sql-anywhere

What @LoganTennis (who is an employee at our company) is saying is that when you create a trigger or stored procedure you are allowed to create them with invalid schema references (E.g. table or column does not exist). When you run the sproc or trigger, it as expected fails, however you are still allowed to create or alter them without issue.

We're looking for a way to be able to stop the trigger or stored procedure from being created/altered in the first place. IF we cannot do this, at least have some way of running a system call (similar to compile views) that would check the stored procedures and triggers.

(12 Feb '15, 21:52) Nick Brooks
Replies hidden
3

This is not possible. Testing is your best friend.

(13 Feb '15, 02:07) Dmitri

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

permanent link

answered 13 Feb '15, 09:09

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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.

(15 Feb '15, 18:30) LoganTennis
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×178
×125
×79
×5

question asked: 12 Feb '15, 18:20

question was seen: 2,939 times

last updated: 15 Feb '15, 18:30