Configuring the Stored Procedure Option - EnterWorks_Process_Exchange_(EPX) - 10.5

If you select Stored Procedure at the Access Type option, do the following steps:

1. In the SP Name field, enter the name of the stored procedure the JDBC BIC activity will access in the database that you entered.
   

   Note: If the name of the stored procedure contains spaces, enclose the name in double quotes. For example, "Sales by Year"
2. Enter the data that the stored procedure will use by selecting the data type from the DataType dropdown list. The data type must correspond to the first value expected by the stored procedure.
3. In the IO Type list, select the input/output types. The following are the available input/output types:
   • In describes the parameter to the stored procedure as input.
   • Out describes the parameter to the stored procedure as output.
   • InOut describes the parameter to the stored procedure as both input and output.
4. If you select either Decimal or Numeric as the data type, you can specify the precision for the value if desired by clicking the appropriate number in the Precision list.
5. In the Value field, enter the value for the parameter that you have just configured. You can enter a static value or you can enter a variable that will be defined in the work item. To define the value with a variable, you must escape the variable name with percent signs (%). For example, if the work item contains a variable named param.value, it must be entered as %param.value%.
6. To enter data with null values, type in NULL (all uppercase letters) or do not enter any value at all. On the other hand, string "null" can be specified by typing in the null string with at least one letter in lowercase, for example, null, NUll, nULL or Null.
   Note: Entering of a null value allows the JDBC BIC to create work item properties for result columns that are null. The created workitem property contains an empty string.
7. When you have finished configuring a parameter, click Add to add the parameter to the Parameters field.
8. Repeat the steps 1 to 7 to add each of the parameters expected by the designated stored procedure.

   You can rearrange the parameters that you have added to the Parameters field by moving them up and down in the list. To move a parameter, click the parameter to select it and then click Up or Down to move the parameter up or down in the list, respectively.

   To delete a parameter from the list, select the parameter to be deleted and then click Remove.

   To update a parameter from the list, select the parameter to be updated. Then configure the values in the Data Type, IO Type, Precision, and Value fields. After configuring the values in the fields, click Update.

9. In the Result Set field, enter the fully qualified name for the result set. This is the location in the hash table where the results will be placed (e.g., <user>.<directoryName>).

   To pull any piece of information from a hash table, enter the fully qualified hash table entry name.

   Note: To specify a percent sign in the name itself, escape it by putting a backslash in front of it; for example, Pct\% resolves to Pct%.

   To specify a backslash in the name itself, escape it by putting another backslash in front of it; for example, \\Results resolves to \Results.

10. The Max Rows field specifies the maximum number of rows to be retrieved into the result set in the work item. You can type in a numeric value or a work item property key.
    • For numeric values, all the rows for that query will be returned if you specify a value that exceeds the number of rows.

      Type "0" to return zero rows or "-1" to return all rows. If the input is invalid, the Max Rows field adapts the default value specified in the JDBCBic.config file. Invalid values include negative numbers other than "-1", mixed decimals and non-numeric input.

      In the JDBC.config file, the Max Rows default value of 100 is configurable.

    • For work item property keys, enter the property name enclosed with the "%" sign. For example, if you will use the work item property name Personal_Age type in %Personal_Age% in the Max Rows field. The query will return the number of rows specified in the Personal_Age field in the viewer. If the work item property specified in the Max Rows field does not exist, the query will result to an error.
11. Save the data entered and proceed to another tab by clicking Apply. Clicking OK will also save the data entered and exit the JDBC BIC editor. To cancel saving the data entered, click Cancel.