There are situations when the SQL statement cannot be defined completely at configuration time. In Expert mode, select the Dynamic SQL checkbox to switch to dynamic SQL mode, where parts of the statement (or even the whole statement) are supplied by dataslot values.
Dynamic SQL can be useful in many situations. As an example, consider a case where customer information is stored in a simple database table, as shown below:
Table 11. Dynamic SQL statement example
ID (INTEGER)
Name (CHARACTER)
Phone (CHARACTER)
100
George Smith
(111) 555-1111
101
Mike Green
(222) 555-2222
102
Jeff Black
(333) 555-3333
103
Dennis Gray
(444) 555-4444
...
...
...
Table 11 provides a list of customer ID-numbers; the task is to use the DB Adapter to select the data about all the customers in the list.
Note: If you would like to try the Database Adapter with the example discussed here, instructions about creating the customer database table are given in Step 1 of Tutorial 2, in Using the Database Adapter in Progress OpenEdge Business Process Modeler: User's Guide.
The SQL statement for getting the customer data from an ID-list would be:
SELECT * FROM CUSTOMER_INFO WHERE ID IN (100, 103, 104)
In the above example, 100, 103, and 104 are the customer IDs to select. Since the number of IDs in this list can vary, we cannot use prepared SQL statements (where the number of parameters is fixed), and are forced to switch to dynamically constructing the SQL.
To create a dynamic SQL statement:
1. Open the Properties view of a DBAdapter workstep. Open the Configuration tab and click Configure, displaying the Database Adapter Configurator dialog box.
2. From the SQL Statement tab, click Expert mode. Select the Dynamic SQL checkbox. Click Select as the type. In the SQL Statement panel, enter the following:
select * FROM CUSTOMER_INFO WHERE ID IN (@IDLIST)
Note that we used the variable @IDLIST in place of the list of customer ID numbers.
3. Click Variables to specify the type for the @IDLIST variable.
Although an individual Customer ID is a number, a comma-separated list of IDs is not–that is the reason why we must select "CHARACTER" as the variable type.
When you have defined the @IDLIST variable, click OK to return to the SQL Statement dialog box.
4. Click Get Result MetaData in the SQL Statement dialog box to invoke the Query Tester dialog box. Enter a list of values for the @IDLIST variable, and click in the row or press TAB to enable the values. Click Execute to show the list of selected customers in the QueryResult panel.
Replacing the variable values in the SQL statement is literal— meaning that you should be very careful when dynamically generating statements. For example, if the list we were providing consisted of Strings, and not numbers, the SQL syntax would require that the values are enclosed in single-quotation marks:
‘abc’,‘def’,‘xyz’
You must make sure that the string values you provide to the Database Adapter conform to the SQL syntax.
In the example above, we used a variable to substitute only a small part of the SQL statement — the list of ID numbers. It is also possible to replace more parts of the statement, or even to have the whole statement defined with a single variable, and to provide the complete SQL in a CHARACTER dataslot.
Additional caution must be exercised when using dynamic SQL. Ensuring that a dynamic statement is syntactically and semantically correct is each user’s responsibility.
Switching to dynamic SQL does not impact Database Adapter operations including defining the Input and Output parameters and dataslot mapping.
