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
`<enable-transaction>`	Determines whether to use the transaction for all the SQL queries defined in the adapter request Valid values: true, false (default)	No
`<statements>`	Contains the `<statement>` elements	Yes
`<statement>`	Contains the query and any parameter sets	Yes
`<query>`	Specifies the SQL query to be executed This element is required if the value in the `<query>` element contains a ? character for bind variable substitution. You can use a ? to indicate the location for bind variable substitution.	Yes
`<parameter-sets>`	Contains the `<parameter-set>` elements used as bind variables	Conditional; required if `<query>` contains a ? for bind variable substitution
`<parameter-set>`	Contains the `<parameter>` elements	Conditional; required if `<query>` contains a ? for bind variable substitution
`<parameter>`	Contains the `<data-type>` and `<value>` elements for each bind variable in the `<query>` element	Conditional; required if `<query>` contains a ? for bind variable substitution
`<data-type>`	Specifies the type of data for one of the bind variables Valid values: BIGINT, BOOLEAN, CHAR, DATE, DOUBLE, FLOAT, INTEGER, NUMERIC, TIMESTAMP, VARCHAR	Conditional; required if `<query>` contains a ? for bind variable substitution
`<value>`	Specifies the input value for a bind variable	Conditional; required if `<query>` contains a ? for bind variable substitution
`<targets>`	Holds the `<target>` elements	No
`<target>`	Provides definitions for dynamic targets at run time	No
`<enable-transaction>`	Specifies whether to use a transaction for all SQL queries defined in the adapter request Valid values: true, false (default)	No
`<character-set>`	Specifies the supporting CharSet Also called character set, it includes identifiers describing a series of universal characters.	Conditional; you must specify the `<character-set>` element only if the target database is MySql

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.

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
`<target-metadata>`	Contains target-level summary information
`<database>`	Specifies the database name
`<database-vendor>`	Specifies the database vendor name
`<status>`	Indicates the status of the request Valid values: success, error
`<error>`	Contains the error message if a statement-level error occurs When the value of the `<status>` element is success, this element is absent.
`<statement-responses>`	Contains the `<statement-response>` elements
`<statement-response>`	Contains the `<statement-metadata>` and the `<response-sets>` elements
`<statement-metadata>`	Contains the statement-level summary information
`<response-set-count>`	Indicates the number of response sets returned by the query This must correspond directly to the number of parameter sets provided in the request.
`<status>`	Indicates the status of the request Valid values: success, error
`<error>`	Contains the error message if a statement-level error occurs When the value of the `<status>` element is success, this element is absent.
`<response-sets>`	Contains the `<response-set>` elements
`<response-set>`	Contains the information returned by the query The `index` attribute indicates the sequence of the parameter set as provided in the adapter request.
`<response-set-metadata>`	Contains the response-level summary information
`<status>`	Indicates the status of the request Valid values: success, error
`<error>`	Contains the error message if a response-level error occurs When the value of the `<status>` element is success, this element is absent.
`<result-sets>`	Contains the `<result-set>` element
`<result-set>`	Contains the query results for a specific parameter set
`<row>`	Contains the values returned for one row of the query
`<column>`	Specifies the column name and the value returned in the response
`<metadata>`	Contains the result-level summary information
`<query-executed>`	Indicates the query that is executed to return the results
`<row-count>`	Specifies the number of rows returned for the result
`<column-count>`	Specifies the number of columns returned for each row
`<status>`	Indicates the status of the request Valid values: success, error
`<error>`	Contains the error message if a result level error occurs When the value of the `<status>` element is success, this element is absent.

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>

SQL adapter parameterized statement request and response

Comments