Using substitution parameters for clause values

Declared parameters to a stored procedure or function are automatically substituted for place holders within a clause definition each time the stored procedure or function is run. Substitution parameters allow the creation of general web service procedures that dynamically configure clauses at run time. Any substrings that contain an exclamation mark '!' followed by the name of one of the declared parameters is replaced by that parameter's value. In this way one or more parameter values may be substituted to derive one or more clause values at runtime.

Parameter substitution requires adherence to the following rules:

All parameters used for substitution must be alphanumeric. Underscores are not allowed.
A substitution parameter must be followed immediately by a non-alphanumeric character or termination. For example, !sizeXL is not substituted with the value of a parameter named size because X is alphanumeric.
A substitution parameter that is not matched to a parameter name is ignored.
An exclamation mark (!) can be escaped with another exclamation mark.

For example, the following procedure illustrates the use of parameter substitution. URL and HTTP header definitions must be passed as parameters.

CREATE PROCEDURE test( uid CHAR(128), pwd CHAR(128), headers LONG VARCHAR )
    URL 'HTTP://!uid:!pwd@localhost/myservice';
    HEADER '!headers';

You can then use the following statement to call the test procedure and initiate an HTTP request:

CALL test('dba', 'sql', 'NewHeader1:value1\nNewHeader2:value2');

Different values can be used each time this procedure is called.

Encryption certificate example

You can use parameter substitution to pass encryption certificates from a file and pass them to a stored procedure or stored function.

The following example illustrates how to pass a certificate as a substitution string:

CREATE PROCEDURE secure( cert LONG VARCHAR )
URL 'https://localhost/secure'
TYPE 'HTTP:GET'
CERTIFICATE 'cert=!cert;company=test;unit=test;name=RSA Server';

The certificate is read from a file in the following call.

CALL secure( xp_read_file('install-dir\bin32\rsaserver.id') );

This example is for illustration only. The certificate can be read directly from a file using the file= keyword for the CERTIFICATE clause.

No matching parameter name example

Placeholders with no matching parameter name are automatically deleted.

For example, the parameter size would not be substituted for the placeholder in the following procedure:

CREATE PROCEDURE orderitem ( size CHAR(18) )
    URL 'HTTP://localhost/salesserver/order?size=!sizeXL'
    TYPE 'SOAP:RPC';

In this example, !sizeXL is always deleted because it is a placeholder for which there is no matching parameter.

Parameters can be used to replace placeholders within the body of the stored function or stored procedure at the time the function or procedure is called. If placeholders for a particular variable do not exist, the parameter and its value are passed as part of the request. Parameters and values used for substitution in this manner are not passed as part of the request.

Discuss this page in DocCommentXchange.