Request for Feedback - Using variables as statement parameters

[Former Member]

We are investigating expanding the ability to use variables in various statements in a future release of SQL Anywhere. This would reduce the reliance on EXECUTE IMMEDIATE, which can often be the source of application errors that cannot be detected until runtime and can be a vector for SQL injection attacks. We are not considering removing EXECUTE IMMEDIATE, just providing some alternatives to make dynamic SQL execution more developer friendly.

For example, DROP CONNECTION < connection-id > currently only allows you to specify an integer connection id. So in order to drop a connection in script, you need to build and execute a statement using EXECUTE IMMEDIATE.

eg.
SET sql-stmt = 'DROP CONNECTION ' + conn-id-variable;
EXECUTE IMMEDIATE sql-stmt;

If we allowed <connection-id> to be a variable, you could execute the statement directly
eg. DROP CONNECTION conn-id-variable;

Are there any statements in SQL Anywhere that you would like to see accept variables in place of identifiers and/or literal values? Examples would be helpful, but are not required.

[VolkerBarth]

I don't have an actual suggestion currently but would like to thanks for the public "RFF" - we surely like to give more feedback on further requests:)

[Breck_Carter]

It's not a burning need IMO. Having to use EXECUTE IMMEDIATE on FORWARD TO is a real irritation because of all the nested quotes that implies, but simple parameter substitution is... well... simple 🙂

For the record, here are all the simple EXECUTE IMMEDIATE strings in Foxhound (there are a sackload of non-simple uses but you're not interested in those)...

STRING ( 'ALTER DBSPACE SYSTEM ADD ', @pages_to_add, ' PAGES' )
EXECUTE IMMEDIATE 'STOP ENGINE UNCONDITIONALLY';
STRING ( 'GRANT CONNECT TO ', @proxy_owner )
STRING ( 'CREATE SERVER ', @proxy_owner, ' CLASS ''SAODBC'' USING ''DSN=V8target;UID=dba;PWD=SQL''' )
STRING ( 'ALTER SERVER ', @proxy_owner, ' CONNECTION CLOSE' )
STRING ( 'ALTER SERVER ', @proxy_owner, ' USING ''DSN=V9target;UID=dba;PWD=SQL''' )
STRING ( 'FORWARD TO ', @proxy_owner, ' ''CHECKPOINT''' )
STRING ( 'FORWARD TO ', @proxy_owner, ' ''DROP CONNECTION ', @connection_number, '''' )
STRING ( 'DROP TABLE ', @proxy_owner, '.proxy_DUMMY' )
STRING ( 'ALTER SERVER ', @p_proxy_owner, ' USING ''', @connection_string, '''' )
STRING ( 'FORWARD TO ', @p_proxy_owner, ' ''SET TEMPORARY OPTION BLOCKING = ''''ON''''''' )
STRING ( 'ALTER PROCEDURE "', @owner_name, '"."', @procedure_name, '" SET HIDDEN' )
STRING ( 'ALTER EVENT "', @owner_name, '"."', @procedure_name, '" SET HIDDEN' )
STRING ( 'ALTER TRIGGER "', @owner_name, '"."', @procedure_name, '" SET HIDDEN' )
STRING ( 'ALTER VIEW "', @owner_name, '"."', @procedure_name, '" SET HIDDEN' )
STRING ( 'DROP PROCEDURE ', @proxy_owner, '.proxy_rroad_connection_properties' )
STRING ( 'DROP CONNECTION ', @connection_number )

[MCMartin]

Probably not exactly what you are asking for, but you could maybe create a solution for my question Materialized view with Parameter?