SQL adapter parameterized statement request and response
The parameterized statement adapter request is used to execute SQL statements against data sets. Multiple statements can be executed in a single adapter request. Each statement can contain bind variables. The data values for these variables are escaped using the Java JDBC implicit special character escaping feature.
When you use a context item as an input for an adapter request, you must enclose the adapter request in the <request-data>
elements. However, when you create a static request, <request-data>
is not required and the adapter request starts with the <adapterName-adapter-request>
element.
BMC recommends that you do not include empty nonrequired elements in the adapter request because they might cause errors.
The following table describes the elements of an SQL actor adapter request with the parameterized statements.
Elements of an SQL actor adapter request with parameterized statements
Element | Description | Required |
---|---|---|
| Determines whether to use the transaction for all the SQL queries defined in the adapter request | No |
| Contains the | Yes |
| Contains the query and any parameter sets | Yes |
| Specifies the SQL query to be executed | Yes |
| Contains the | Conditional; required if |
| Contains the | Conditional; required if |
| Contains the | Conditional; required if |
| Specifies the type of data for one of the bind variables | Conditional; required if |
| Specifies the input value for a bind variable | Conditional; required if |
| Holds the | No |
| Provides definitions for dynamic targets at run time | No |
| Specifies whether to use a transaction for all SQL queries defined in the adapter request | No |
| Specifies the supporting CharSet | Conditional; you must specify the |
Note
Transaction support is applicable to Data Manipulation Language (DML) statements only. Transactions do not support standard SQL adapter requests. The SQL adapter does not support transactions when:
- The
<enable-transaction>
tag is not included in the adapter request or has an invalid value. - The value of the
<enable-transaction>
tag is set to false.
Recommendation
The SQL adapter supports the following root tags for an adapter request:
<sqlAdapterRequest>
<sqladapterRequest>
<sql-adapter-request>
BMC recommends that you use the <sqlAdapterRequest>
root tag for the adapter request.
The following figure shows an XML sample of the legacy adapter request for the SQL actor adapter request with a parameterized statement. Use the adapter request XML when you create a custom process by using the Call Adapter activity in BMC Atrium Orchestrator Development Studio.
XML sample of the SQL adapter legacy request with a parameterized statement
<sql-adapter-request>
<targets>
<target name = "targetname">
<host>10.254.1.98</host>
<port>3306</port>
<user-name>root</user-name>
<password>root123</password>
<subprotocol>mysql</subprotocol>
<database>test</database>
<driver>com.mysql.jdbc.Driver</driver>
</target>
</targets>
<enable-transaction>true</enable-transaction>
<statements>
<statement>
<query>select * from table1 where column1 = ?</query>
<parameter-sets>
<parameter-set>
<parameter>
<data-type>VARCHAR</data-type>
<value>test1</value>
</parameter>
<parameter>
<data-type>VARCHAR</data-type>
<value>test2</value>
</parameter>
</parameter-set>
</parameter-sets>
</statement>
</statements>
</sql-adapter-request>
For a parameterized statement dynamic request, if you provide the <targets>
tag in the request for multiple target computers, you observe the <target-outputs>
tag in the corresponding response. The following figure shows an XML sample of the dynamic adapter request for the SQL actor adapter with a parameterized statement.
XML sample of the SQL actor adapter dynamic request with a parameterized statement
<sql-adapter-request>
<targets>
<target name = "mysql"/>
<target name = "oracle1">
<host>localhost</host>
<port>3306</port>
<user-name>root</user-name>
<password>root123</password>
<subprotocol>mysql</subprotocol>
<database>test</database>
<driver>com.mysql.jdbc.Driver</driver>
</target>
</targets>
<enable-transaction>true</enable-transaction>
<action>select * from test</action>
</sql-adapter-request>
The following figure shows an XML sample for the SQL actor adapter request with a stored procedure. Use the adapter request XML when you create a custom process by using the Call Adapter activity in BMC Atrium Orchestrator Development Studio.
XML sample of the SQL actor adapter request with a stored procedure
<sqlAdapterRequest>
<targets>
<target name="mysql"></target>
<target name="oracle"></target>
<target name="oracle1">
<host>localhost</host>
<port>3306</port>
<user-name>root</user-name>
<password>root123</password>
<subprotocol>mysql</subprotocol>
<database>test</database>
<driver>com.mysql.jdbc.Driver</driver>
</target>
</targets>
<procedure>
<name>updateTest</name>
<argument-sets>
<argument-set>
<argument>
<in-out>IN</in-out>
<data-type>VARCHAR</data-type>
<value>sample value</value>
</argument>
<argument>
<in-out>IN</in-out>
<data-type>VARCHAR</data-type>
<value>New sample value</value>
</argument>
</argument-set>
<argument-set>
<argument>
<in-out>IN</in-out>
<data-type>VARCHAR</data-type>
<value>SomeAdapterName</value>
</argument>
<argument>
<in-out>IN</in-out>
<data-type>VARCHAR</data-type>
<value>SqlAdapterName</value>
</argument>
</argument-set>
</argument-sets>
</procedure>
</sqlAdapterRequest>
Notes
- You must enter
Procedure
in the Action box if you use the Call Adapter activity to call a stored procedure in BMC Atrium Orchestrator Development Studio. - If the database is of the SQL Server type and you want to execute a parameterized stored procedure:
- If the parameter is of the IN type, the
<index>
attribute is not required. - If the parameter is of the OUT type, you must provide the correct
<index>
attribute. If you do not provide an<index>
attribute or if you provide an incorrect<index>
, the adapter response contains an error.
- If the parameter is of the IN type, the
The following figure shows an XML sample for the SQL actor adapter request with a stored procedure and a missing <index>
entry.
XML sample of the SQL actor adapter request with a stored procedure and a missing index entry
<sqlAdapterRequest>
<procedure>
<name>test_Out_SP</name>
<argument-set>
<argument>
<in-out>IN</in-out>
<data-type>VARCHAR</data-type>
<value>998899889</value>
</argument>
<argument>
<in-out>OUT</in-out>
<data-type>INT</data-type>
</argument>
</argument-set>
</procedure>
</sqlAdapterRequest>
The following table describes the elements of an SQL actor adapter response to a request with parameterized statements.
Elements of an SQL actor adapter response to a request with parameterized statements
Element | Description |
---|---|
| Contains target-level summary information |
| Specifies the database name |
| Specifies the database vendor name |
| Indicates the status of the request |
| Contains the error message if a statement-level error occurs |
| Contains the |
| Contains the |
| Contains the statement-level summary information |
| Indicates the number of response sets returned by the query |
| Indicates the status of the request |
| Contains the error message if a statement-level error occurs |
| Contains the |
| Contains the information returned by the query |
| Contains the response-level summary information |
| Indicates the status of the request |
| Contains the error message if a response-level error occurs |
| Contains the |
| Contains the query results for a specific parameter set |
| Contains the values returned for one row of the query |
| Specifies the column name and the value returned in the response |
| Contains the result-level summary information |
| Indicates the query that is executed to return the results |
| Specifies the number of rows returned for the result |
| Specifies the number of columns returned for each row |
| Indicates the status of the request |
| Contains the error message if a result level error occurs |
The following figure illustrates an XML sample of the adapter response for the SQL actor adapter with a parameterized statement.
XML sample of the SQL adapter response to a legacy request with a parameterized statement
<sql-adapter-response>
<statement-responses>
<satements-metadata>
<status>Success</status>
</statements-metadat>
<statement-response>
<statement-metadata>
<response-set-count>2</response-set-count>
<status>success</status>
<error>Present only with status of 'error'</error>
</statement-metadata>
<response-sets>
<response-set index="1">
<response-set-metadata>
<status>success</status>
<error>Present only with status of 'error'</error>
</response-set-metadata>
<result-sets>
<result-set>
<row>
<column name="column1">test1</column>
<column name="column2">samplevalue1</column>
</row>
<row>
<column name="column1">test1</column>
<column name="column2">samplevalue2</column>
</row>
<metadata>
<query-executed>select * from table1 where column1 = ? </query-executed>
<row-count>2</row-count>
<column-count>2</column-count>
<status>success</status>
<error>Present only with status of 'error'</error>
</metadata>
</result-set>
</result-sets>
</response-set>
<response-set index="2">
<response-set-metadata>
<status>success</status>
<error>Present only with status of 'error'</error>
</response-set-metadata>
<result-sets>
<result-set>
<row>
<column name="column1">test2</column>
<column name="column2">samplevalue3</column>
</row>
<metadata>
<query-executed>select * from table1 where column1 = ? </queryexecuted>
<row-count>1</row-count>
<column-count>2</column-count>
<status>success</status>
<error>Present only with status of 'error'</error>
</metadata>
</result-set>
</result-sets>
</response-set>
</response-sets>
</statement-response>
</statement-responses>
</sql-adapter-response>
The following figure shows an XML sample for the SQL actor adapter response to a request with a stored procedure and a missing <index>
entry.
XML sample of the SQL actor adapter response to a request with a stored procedure and a missing index entry
<sql-adapter-response>
<procedure-response name="test_Out_SP">
<response-sets>
<response-set index="1">
<response-set-metadata>
<update-count>0</update-count>
<has-result-set>true</has-result-set>
<status>error</status>
<response-set-error>Index tag not provided in case of OUT parameter.
</response-set-error>
<status>error</status>
</response-set-metadata>
</response-set>
</response-sets>
<procedure-metadata>
<response-set-count>1</response-set-count>
<query-executed>{call test_Out_SP(?, ?) }</query-executed>
<status>success</status>
<execution-milliseconds>44</execution-milliseconds>
</procedure-metadata>
</procedure-response>
</sql-adapter-response>
Comments
Log in or register to comment.