...
A list of Database is available to choose from the drop-down list.
Figure 3: List of Database options
Driver
Driver class name that should be used to connect to the database. On selecting required value for Database, Driver value is populated with standard value (This can be changed to required value based on driver being used).
...
When database object names (table names, column names, schema names…) contain spaces, they should be wrapped in database dependent special characters.
Figure 4: Wrap DB object name options
Examples:
- " " for Oracle
- [ ] for Microsoft Excel
- no wrap characters for MySQL.
...
Connection configuration can be tested by clicking the Test button in the Managed Connection Factory.
Figure 5: Test Connection Configuration panel after pressing Test button
| Anchor | ||||
|---|---|---|---|---|
|
Business logic configuration details are configured in the second panel, Interaction Configurations. Below is the snapshot of the panel with Expert Poperties view enabled.
Figure 46: Business logic configuration in Interaction configurations panel
...
Click ellipsis 
button against property SQL configuration property to launch the SP Configuration wizard which can be used to configure call statement.
Figure 57: SP Configuration Wizard
...
- Click ellipsis
button against Stored Procedure property to launch Procedure Selection dialog box. To select the Procedure/Function which has to be executed, first click the Refresh button to fetch the Schemas.
Figure 68: Procedure Selection Dialog Filter the Procedure/Function by selecting the required schema and click OK to fill the parameters.
Figure 79: Filtering Stored Procedures based on Schemas
Parameters and their configurations are automatically populated.
Figure 810: Populated ParametersColumn
Description
Parameter
Name of the parameter for named parameters, blank otherwise
Parameter Type
Type of parameter – IN, OUT, INOUT, UNKOWN, RETURN, RESULT
Values of type OUT, INOUT, RETURN, RESULT form output structureData Type
SQL data type of the parameter
Sample Data
NA
- For parameters whose data type is a user-defined data type, Data Type column will be populated by the value 'OBJECT'.
Figure 911: populated Parameter of type User defined - Select the 'User Defined Type' from the DataType drop-down list as shown in the figure below to launch User Types Selection Dialog.
Figure 1012: Selecting User Defined Type - Select the appropriate data type from the dialog.
- After selecting user defined data type, it will populate in the Data Type column for the respective parameter.
Figure 1113: User Types Selection Dialog to select Data type
Figure 1214: Populated User Defined Datatype We need to generate the Input/Output parameters by clicking the Generate Parameters button. Then the input/output parameters will be generated as shown in below figure. After the generation of input/output parameters these parameters will be included in the input and output port xsd's.
Figure 1315: Generated Input/Output Parameters
Each of the columns is explained below.Column Name
Description
BindName
This value is used to generate the schema for the query. In the above figure value for EMPLOYEE_ID (field name) is changed to EMPLOYEE_I. So the schema generated would contain EMPLOYEE_I as the first element instead of default populated value, EMPLOYEE_ID.
BindPosition
The position in the query where this value is bound to.
Warning Do not change this value.
JavaTypeName
JDBC type which maps to Data Type.
SQlTypeName
This defines the data type of this column in the database table. This should be correctly defined.
- Returns Resultset determines whether the configured stored procedure returns resultsets or not.
- Click Ok to close the dialog.
...
The input schema is auto generated based on the configuration provided. When Generate Parameters button is clicked the input parameters required for the execution of the procedure will be added as child elements to the CALL element in the input schema as shown in figure 14.
Figure 1416: Input schema for Procedure with input parameter param1
The input XML to the component will thus be in the format shown in figure 15.
Figure 1517: Sample XML corresponding to the Input Schema
When the property Use Connection details from input is chosen, an additional element ConnectionFactorySettings is added to the input schema, as shown in the figure. Properties that are used to create the connection are present under this element.
Figure 1618: Input schema with ConnectionFactorySettings
...
This is auto generated based on the configuration provided. When Generate Parameters button is clicked the output parameters, if any, required for the execution of the procedure will be added as child elements to the RESULT element in the input schema as shown in figure 17.
Figure 1719: Output schema when there are output parameters
Figure 1820: Output Schema when the Procedure returns result set
If the Return Result Set is set to true then an element ResultSet will be added and results appear as row elements in the output XSD as shown in figure 19.
Figure 1921: Result Set and parameters as output elements in output schema
...
Configure the DBProc component as described in SP Configuration section Use feeder and display components (shown in figure 20) to create a flow to send sample input and check the response respectively.
Figure 2022: Configure the DB Proc component
Figure 2123: Demonstrating Scenario 1 with Sample Input
Figure 2224: Demonstrating Scenario 1 with Sample output
...