x

To create an SQL statement:

1. If you are not connected to a defined target, connect to one, as described in How to Connect to a Defined Target.
2. Click the Statements node.
3. In the right pane, move the pointer over Operations and select Create Prepared Statement.

   The Create Prepared Statement options open in the right pane, as shown in the following image.

   

4. In the Name field, type a name for the statement.

   iWay Software recommends that you specify a name that describes the service. For example, a name of CustomerIntField could represent a request against the Customer Interface table returning a Field format response document.

5. In the Enter SQL Statement field, type the SQL statement for the adapter to use.

   Note: If the user is not the owner of the table(s), the table name must be fully qualified.

   To view table metadata while you edit:

   1. Click View Table.

      The Schema drop-down list is now available at the bottom of the pane.

   2. Select a schema from the drop-down list and click Get Tables.

      The Table drop-down list is now available under the Schema field.

   3. Select the table you want to view from the drop-down list and click Get Table.

      The table metadata appears at the bottom of the Create Prepared Statement pane. 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 and View Another Table found at the bottom of this pane.

6. When the SQL statement is complete, click Create.

After the SQL statement node is built, you are ready to test the statement.

• For information on testing a regular SQL statement, see How to Test an SQL Statement.
• For information on creating schemas for both parameterized and regular SQL statements, see How to Generate a Schema for a Prepared Statement.

x

To test an SQL statement:

1. Select the SQL statement node you want to test.
2. In the right pane, move the pointer over Operations and select Test Run.

   The Test Run pane opens on the right.

3. Click Test.

   The results appear in the Test Run pane in a table format. The following image is an example of an SQL statement test results.

   

   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.

4. To exit the results window, click OK.

x

You can follow this procedure to edit the SQL for a parameterized SQL statement as well.

To edit an SQL statement:

1. Expand the Statements node in the left pane.
2. Select the regular SQL statement you want to edit.
3. Move the mouse pointer over Operations and select Edit SQL.

   The Edit SQL pane opens on the right, as shown in the following image.

   

   You can view table metadata by clicking View Table. For more details see How to Create a Regular SQL Statement.

4. When your edits are complete, click Create.

x

To create a parameterized SQL statement:

1. If you are not connected to a defined target, connect to one, as described in How to Connect to a Defined Target.
2. Click the Statements node.
3. In the right pane, move the pointer over Operations and select Create Prepared Statement.

   The Create Prepared Statement pane opens on the right, as shown in the following image.

   

4. In the Name field, type a name for the statement.
5. In the Enter SQL Statement field, type the parameterized SQL statement.

   Note: If you are not the owner of the table(s), the table name must be fully qualified.

   To view table metadata while you edit:

   1. Click View Table.

      The Schema drop-down list is now available at the bottom of the pane.

   2. Select a schema from the drop-down list and click Get Tables.

      The Table drop-down list is now available under the Schema field.

   3. Select the table you want to view from the drop-down list and click Get Table.

      The table metadata appears at the bottom of the Create Prepared Statement pane. 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 and View Another Table found at the bottom of this pane.

6. When the parameterized statement is complete, click Create.

   The Parameter Name, Data Type, and Value selection information appears at the bottom of the pane, as shown in the following image.

   

   1. In the Parameter Name column, type a name for each parameter.
   2. In the Data Type column, select a data type for each parameter from the drop-down list, which matches the datatype in the database.
   3. In the Value field, type a value for the parameter.
7. Click Update.

   The properties table for the newly created statement appears in the right pane containing Property and Value columns. The Value column contains a description of the SQL statement, the actual SQL statement, and ellipsis symbols you can click to access parameters and database properties.

   

   Depending on the properties you want to view, click the ellipsis symbol in the Parameters or Database Properties row.

   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 run-time 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.

For information on creating schemas for both parameterized and regular SQL statements, see How to Generate a Schema for a Prepared Statement.

x

To test a parameterized SQL statement:

1. In the left pane, select the parameterized SQL statement node you want to test.
2. In the right pane, move the pointer over Operations and select Test Run.

   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.

   

3. For each parameter, type a value in the Value field.

   For example, provide a sample character value, for example, SMITH, 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.

4. Click Test.

   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.

5. To exit the results, click OK.

x

You can edit the parameter name, data type, and value through the Edit Parameters pane.

To edit a Parameterized SQL statement:

1. Expand the Statements node in the left pane.
2. Select the Parameterized SQL statement you want to edit.
3. In the right pane, move the mouse pointer over Operations and select Edit Parameters.
4. Type the changes to parameter name and value, and select the data type as necessary.

   To view table metadata while you edit, click View Table. For more details see How to Create a Parameterized SQL Statement.

5. When your edits are complete, click Update.

   If your statement is not accurate, an error message appears at the bottom of the pane.

6. If you need to add or delete a parameter, select the parameterized statement, move the mouse pointer over Operations, and select Edit SQL.

   For example, the following image shows a parameterized statement before it is edited:

   

7. Edit the SQL to add the new parameter, for example, COST, and click Update.

   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.

8. Edit the parameter as needed and click Finish or click Update if you are not done editing the statement.

   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.

   

9. Click Change to view the changes in the parameter table without committing them, or click Finish to commit the changes.
10. To delete a parameter, edit the SQL to remove the parameter and click Change.

    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.

11. Select delete from the Position/Delete column for the parameter you are deleting.

    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:

    

12. Click Change to perform the modification without committing the changes, or click Finish to complete the edits and commit the changes.
13. If you want to clear the edits and return the parameter list to its original form, click Undo Edit.
14. If you want to remove all parameters from the list and start fresh, click Clear All.
15. If you are not satisfied with the edits, click Undo Edit to clear the edits you are making and return the parameter list to its original form or click Clear All to return the list to a list of unnamed parameters.
16. Click Finish when you are done with all the edits and want to commit the changes.

x

To specify the date and time format in a request:

1. Select the date parameter in the parameterized SQL statement for which you want to format the date and time.
2. In the right pane, move the mouse pointer over Operations and select Customize Test Datetime Formatter.

   The Customize Datetime Formatter pane appears on the right, as shown in the following image:

   

3. Provide the information as follows:
   1. In the Customized Formatter Pattern box, provide the date and time pattern, or select a Formatter from the Available Formatters drop-down box.
   2. Click Help if you want to see two tables that assist you in configuring the custom formatter, the Pattern Table and an examples table.

      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.

      

   3. Select Use Current Time if NULL to use the current time as the default value if the request value is null.
   4. Select Parse milliseconds since January 1, 1970 (EPOCH) if you want to have the adapter check if the value returned is a long value representing the number of milliseconds since January 1, 1970 (EPOCH).
   5. Select the time zone from the Time Zone drop-down list.
   6. Select Make this date formatter globally available if you want this formatter available for use by other services.
   7. To test a sample date value, click Test and enter a sample value to parse in the Enter a sample value to parse text entry box.
   8. Click Test to test the sample date value.

      The following image shows sample test results.

      

4. Click Apply to commit the changes.

x

To generate a schema for a prepared statement:

1. If you are not connected to a defined target, connect to one, as described in How to Connect to a Defined Target.
2. Click the Statements node.
3. Click the node containing the prepared statement for which you want to generate a schema.
4. In the right pane, move the pointer over Operations and select Generate Schema.

   A table that lists the available schemas appears.

5. To view a schema, click the ellipsis symbol in the Schema column.

The schema is generated and ready to use. You can use the generated request schema to create a sample XML document to be used by the adapter. To add a schema to a business service, see Understanding iWay Business Services.