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 Wery Volker Barth |
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 answered 27 Mar '10, 19:34 Breck Carter 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. 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 :) 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. |
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
For example (cf. Breck's 3rd example):
will return SQLCODE -131 as expected. ----------Addition---------- In a batch environment, one may use the ERRORLEVEL variable to check the result, something like
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. answered 29 Mar '10, 07:41 Volker Barth 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. @Volker: And now we wait, for your answer to be upvoted to the top! |
You could also try using CREATE TEMPORARY PROCEDURE which will define it for your connection & not add it to any system tables. answered 29 Mar '10, 15:14 John Smirnios |
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 answered 29 Mar '10, 13:13 Daniel Costa... |