cancel
Showing results for 
Search instead for 
Did you mean: 

SET NOEXEC ON in SQL Anywhere

Former Member
26,353

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

Accepted Solutions (0)

Answers (4)

Answers (4)

johnsmirnios
Advisor
Advisor

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

VolkerBarth
Contributor

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.

Breck_Carter
Participant
0 Kudos

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.

VolkerBarth
Contributor

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

Breck_Carter
Participant
0 Kudos

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

Breck_Carter
Participant

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
Former Member
0 Kudos

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.

Breck_Carter
Participant
0 Kudos

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 🙂

Breck_Carter
Participant
0 Kudos

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.

Former Member
0 Kudos

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