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.

asked 07 Nov '13, 14:56

Jason%20Hinsperger's gravatar image

Jason Hinspe...
2.7k63447
accept rate: 35%

Comment Text Removed

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:)

(08 Nov '13, 08:25) Volker Barth

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 )
permanent link

answered 07 Nov '13, 15:30

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826
accept rate: 20%

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

permanent link

answered 08 Nov '13, 02:38

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×10
×7
×3

question asked: 07 Nov '13, 14:56

question was seen: 9,662 times

last updated: 08 Nov '13, 08:25