on 2010 Mar 27 2:08 PM
What's the equivalent of SET NOEXEC ON in SQL Anywhere? I want to validate a stored procedure without creating it.
Request clarification before answering.
You could also try using CREATE TEMPORARY PROCEDURE which will define it for your connection & not add it to any system tables.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Breck: Yes, it does (as documented) - see my edits.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 🙂
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.