Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

What's the equivalent of SET NOEXEC ON in SQL Anywhere? I want to validate a stored procedure without creating it.

asked 27 Mar '10, 14:08

Brad%20Wery's gravatar image

Brad Wery
382192126
accept rate: 0%

edited 29 Mar '10, 08:00

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822


You might try using the SQLDIALECT function which returns 'Watcom-SQL' or 'Transact-SQL' if the SQL is valid, and an error message if not.

Beware, however... SQL Anywhere does not fully validate stored procedures at CREATE time. That's because variables and tables must exist before they are referenced but not necessarily when procedures, triggers and other code blocks are created. This is illustrated by the fifth test case below.

CREATE FUNCTION parse_sql ( @sql LONG VARCHAR )
   RETURNS LONG VARCHAR
BEGIN
   DECLARE @result LONG VARCHAR;
   SET @result = SQLDIALECT ( @sql );
   RETURN ( IF @result IN ( 'Watcom-SQL', 'Transact-SQL' )
               THEN 'OK'
               ELSE @result 
            ENDIF );
END;

SELECT parse_sql ( 'CREATE PROCEDURE p() BEGIN MESSAGE ''Hello, World!''; END' )       AS "1",
       parse_sql ( 'CREATE PROCEDURE p() AS MESSAGE ''Hello, World!''' )               AS "2",
       parse_sql ( 'CREATE PROCEDURE p() AS garbage' )                                 AS "3",
       parse_sql ( 'CREATE PROCEDURE p() BEGIN garbage; END' )                         AS "4",
       parse_sql ( 'CREATE PROCEDURE p() BEGIN SET undeclared = undeclared + 1; END' ) AS "Beware!";

1    2    3                       4                       Beware!
OK   OK   Error at character 24   Error at character 35   OK
permanent link

answered 27 Mar '10, 19:34

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

I see. If you drop a table that is referenced in a stored procedure, the only way to see that the procedure is invalid is to run it.

(27 Mar '10, 20:29) Brad Wery

Yes. Having worked with "strict" databases for many years, not having to create every single thing including temporary tables before creating a stored procedure is a feature, not a bug. However, it does delay the detection of some simple syntax errors (like speling misteaks) until the code is executed. Your tests need to execute every piece of code, and if you have any EVENTs you have to check your console log for error messages (because events don't have any client, that's where the errors have to go). You may be viewing this as a bug, which is why I pointed it out :)

(28 Mar '10, 08:04) Breck Carter
Comment Text Removed

You might want to investigate the SYSOBJECT.status column which exists in Version 10 and later... I don't know if it will help you, I don't know what you're trying to accomplish exactly.

(28 Mar '10, 08:11) Breck Carter

A different approach is to run DBISQL/dbisqlc with the -x option and to supply the SQL statements that should be checked as a command or a SQL file.

According to the docs, that

scans commands but does not execute them. This is useful for checking long command files for syntax errors.

For example (cf. Breck's 3rd example):

"%SQLANY11%\bin32\dbisql" -c <ConnectInfo> -x CREATE PROCEDURE p() AS garbage

will return SQLCODE -131 as expected.

----------Addition----------

In a batch environment, one may use the ERRORLEVEL variable to check the result, something like

"%SQLANY11%\bin32\dbisql" -c <ConnectInfo> -x CREATE PROCEDURE p() AS garbage
if errorlevel 1 echo Invalid SQL batch returns ERRORLEVEL: %errorlevel%

Correct syntax returns ERRORLEVEL 0 (EXIT_OK = Success). The above sample sets the ERRORLEVEL to 1 (i.e. EXIT_FAIL = General failure). Can't say if this happens for all kinds of invalid constructs, though.

permanent link

answered 29 Mar '10, 07:41

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

edited 29 Mar '10, 11:16

I went looking for that, you found it :)... does dbisql set an ERRORLEVEL that could be checked in a command file? I'm guessing Brad wants some level of automation.

(29 Mar '10, 09:01) Breck Carter
2

@Breck: Yes, it does (as documented) - see my edits.

(29 Mar '10, 11:11) Volker Barth

@Volker: And now we wait, for your answer to be upvoted to the top!

(29 Mar '10, 14:59) Breck Carter

You could also try using CREATE TEMPORARY PROCEDURE which will define it for your connection & not add it to any system tables.

http://dcx.sybase.com/index.html#1101en/dbreference_en11/create-procedure-user-defined-statement.html

permanent link

answered 29 Mar '10, 15:14

John%20Smirnios's gravatar image

John Smirnios
12.0k396166
accept rate: 37%

I use this procedure to look ahead a change effect. It's designed to work on the SQL Anywhere 11 System Views :

CREATE PROCEDURE "DBA"."ANALYSE_IMPACT"(in cSearch char(128),in cOwner char(128))  
RESULT(OBJET char(10),DESIGNATION char(257))  
BEGIN  
    DECLARE nUser integer;  
    SET nUser = USER_ID(cOwner);  
    SET cSearch = '.*' || UCASE(TRIM(cSearch)) || '.*';  
    SELECT 'PROCEDURE' AS OBJET,proc_name AS DESIGNATION  
        FROM SYSPROCEDURE WHERE creator IN (nUser,USER_ID('DBA'))   
        AND UCASE(proc_defn) REGEXP cSearch   
    UNION ALL  
    SELECT 'TABLE',T.table_name  
        FROM  SYSTAB T JOIN SYSTABCOL C ON T.table_id = C.table_id  
        WHERE T.creator = nUser AND T.table_type = 1 AND   
           (UCASE(T.table_name) REGEXP cSearch OR UCASE(C.column_name) REGEXP cSearch)  
        GROUP BY T.table_name  
    UNION ALL  
    SELECT 'TRIGGER',T.table_name || '.' || C.trigger_name  
        FROM SYSTAB T JOIN SYSTRIGGER C ON T.table_id = C.table_id  
        WHERE T.creator = nUser AND C.trigger_name is not null  
        AND UCASE(C.trigger_defn) REGEXP cSearch  
    UNION ALL  
    SELECT 'VIEW',T.table_name  
        FROM  SYSTAB T JOIN SYSVIEW C ON T.object_id = C.view_object_id  
        WHERE T.creator = nUser AND UCASE(C.view_def) REGEXP cSearch  
    UNION ALL  
    SELECT 'TASK',event_name   
        FROM SYSEVENT   
        WHERE creator = USER_ID('DBA') AND UCASE(source) REGEXP cSearch  
    ORDER BY OBJET,DESIGNATION;  
END  
permanent link

answered 29 Mar '10, 13:13

Daniel%20Costagliola's gravatar image

Daniel Costa...
11
accept rate: 0%

edited 29 Mar '10, 13:51

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:

×36
×1

question asked: 27 Mar '10, 14:08

question was seen: 25,981 times

last updated: 29 Mar '10, 15:14