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

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

 

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>

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*