Topics: |
How to: |
Parameterized SQL allows an SQL statement to be stored within the repository system with parameters imbedded within it. These parameters can be retrieved from XML documents at run time and executed against the SQL statements specified at design time. iWay Explorer creates and maps parameters for the parameterized SQL at design time.
To create a parameterized SQL statement:
The Create Prepared Statement opens, as shown in the following image.
Note: If you are not the owner of the table or tables, the table name must be fully qualified.
To view table metadata while you edit:
The table metadata appears. Use the horizontal and vertical scroll bars to view the entire table. An example of a table displayed in the Create Prepared Statement pane is shown in the following image.
You can browse additional tables if needed using Get Tables found at the bottom of this pane.
The Parameter Name, Data Type, and Value selection information appears at the bottom of the pane, as shown in the following image.
The information for the newly created statement appears in the right pane as shown in the following image.
Use the horizontal and vertical scroll bars to view the properties and parameters.
If your statement is not accurate, an error message appears at the bottom of the pane.
The date on which the statement is created is saved along with other data about the statement. In addition, the date for each parameter is saved. The date information can help debug problems. For example, if a batch statement that references a prepared statement has a modified date earlier than the date listed for the prepared statement, the batch might behave differently than expected. Also, when the design-time and runtime repositories are the same, a deployed service with a date earlier than the modified date shown for the service in design-time might mean that the service behaves differently than intended. In this case, the service should be redeployed.
For information on testing a parameterized SQL statement, see How to Test a Parameterized SQL Statement.
To test a parameterized SQL statement:
The Test Run pane opens on the right for the SQL statement. This pane contains the parameter name, data type, and an input box where you can type the parameter value, as shown in the following image.
For example, provide a sample character value, for example, BELLA, for the following SQL statement:
select * from employee where lname=?
In this example, the values correspond to values of fields found in a table. Parameterized statements may include parameters that are input for SQL functions, for example, the Oracle SQL function TO_DATE(StringParm). In this case, the data type selected is the expected data type of the SQL function. This is why you provide the SQL type when you create the prepared parameterized SQL statement.
The results appear in the Test Run results window in a table format. An example of test results is shown in the following image.
To see the results in XML, click View XML. The following image is an example of test results in XML format.
Click Table View to return to the table format display.
You can edit the parameter name, data type, and value through the Edit Parameters pane.
To edit a Parameterized SQL statement:
To view table metadata while you edit, click View Table. For more details see How to Create a Parameterized SQL Statement
If your statement is not accurate, an error message appears at the bottom of the pane.
For example, the following image shows a parameterized statement before it is edited:
When a new parameter is added to the SQL statement, iWay Explorer adds the new, unnamed parameter automatically to the parameter list. The following image shows the edited SQL statement and the parameters listed below the SQL Statement box:
You can edit the parameter name and data type.
The following image shows the edited parameter in the parameter table.
You can change the position of the parameters in the parameter table to match the order of parameters in the SQL statement by specifying the position in the Position/Delete column. The following image shows the reordering of the parameter rows in the Parameter table based on the position selected. As shown in the image, the third row will become the top row, followed by the two other rows.
The delete setting appears by default in the Position/Delete column of one of the parameter rows. If it is not the parameter you want to delete, you can change the setting for that row.
The following image shows delete selected in the second row, which contains the Company parameter:
You can also change the position of a parameter relative to other parameters in the list by selecting a position number in the parameter row whose position you want to change. This is useful if you delete the middle parameter from a SQL statement and want to reposition the other parameters in the parameter table to match the sequence of parameters in the SQL statement. The following image shows delete selected in the middle row and a position of 1 selected in the third row of the parameter table. This will move the position of the this parameter from the third row to the second row:
How to: |
Because dates can be formatted in many different ways, some applications might have to take extra time transforming a date and time string to the correct XML format. You can specify the date and time format when you design a service so that it does not have to be done in the flow of the message. You can configure and test the date formatter the adapter uses at runtime to parse the request value.
Note: The date format you specify structures the date format for the request document. The date format returned in the response will conform to the date format defined in the database.
To specify the date and time format in a request:
The Customize Datetime Formatter pane appears on the right, as shown in the following image:
The Examples and Pattern tables appear. The following image shows the Examples Table and the Pattern Table. The Examples Table lists date examples. The Pattern Table specifies the letter, component of the date, the date presentation, and examples.
The following image shows sample test results.
You can execute a prepared SQL statement or stored procedure multiple times with different input parameters each time. The major benefits of this feature are as follows:
Note: Multiple processes in one message is not supported. Only one SQL statement or stored procedure can be executed in a single XML execution request block.