on 2023 Feb 27 10:34 AM
We like the "Indirect identifier" feature of SQL Anywhere 17 when dealing with varying table schemata, as it helps to omit dynamic SQL.
Now I've stumpled upon the documented limitation that – besides table and column names – indirect identifiers are supported with the "new" object types mutexes and semaphores but not with procedure and function names.
Why is that limitation? IMHO, as procedures are often used like base tables and views within complex queries (where dynamic SQL often gets difficult), it seems worthwhile to use indirect identifiers for those, too.
The following sql block when run in the SQL Anywhere Demo database raises SQLCODE -131 (Syntax error near '`[strProcName') when using indirect identifiers with procedure calls.
begin declare strOwnerName varchar(128) = 'GroupO'; declare strProcName varchar(128) = 'ShowContacts'; select strOwnerName, strProcName; call GroupO.ShowContacts(); -- succeeds select * from GroupO.ShowContacts() MyProc; -- succeeds call `[strOwnerName]`.`[strProcName]`(); -- returns SQLCODE=-131 select * from `[strOwnerName]`.`[strProcName]`() MyProc; -- returns SQLCODE=-131 end;
Request clarification before answering.
The restriction you experienced with SQL Anywhere 17 is most likely due to the fact that procedures and functions are not the same as tables, columns, mutexes, or semaphores. Tables and columns are data objects, mutexes, and semaphores are synchronization objects, and procedures and functions are executable code objects.
When indirect identifiers are used with tables, columns, mutexes, and semaphores, the syntax is resolved at build time and the object is accessible at runtime. When using indirect identifiers with procedures and functions, the syntax is resolved at runtime as well, but the code object is performed at runtime. This adds complexity and possible security vulnerabilities since the database engine must dynamically compile and execute code that is only partially written.
It's worth mentioning that SQL Anywhere 17 supports the use of variables in procedure and function calls, which can give some flexibility when working with different table schemata. For example, you may use the EXECUTE IMMEDIATE command to run a stored procedure with a variable name dynamically:
DECLARE @proc_name VARCHAR(128); SET @proc_name = 'my_proc'; EXECUTE IMMEDIATE 'CALL ' || @proc_name || '();';
At runtime, this syntax produces the procedure call as a string and then executes it with the EXECUTE IMMEDIATE instruction. While this does need dynamic SQL, it can be useful in circumstances where indirect IDs cannot be utilized with procedures and functions.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.