SQL adapter Standard request and response

The adapter request uses a single element, <action>, that contains the SQL statement to be executed. You can define one <action> element only for each Standard SQL adapter request. If the SQL statement contains a special XML character, the statement must be enclosed in CDATA. 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.

The following table describes the elements of an SQL standard actor adapter request.

Elements of an SQL standard actor adapter request

Element	Description	Required
`<action>`	Specifies the SQL statement to be issued to the database	Yes
`<targets>`	Holds the `<target>` elements	No
`<target>`	Provides the definitions for dynamic targets at run time	No
`<ktgt-cache-file>`	Specifies the location of a valid Kerberos Ticket Granting Ticket (TGT) to obtain a connection through the Kerberos tunnel Note: For the Windows OS, the path should be a fully qualified path using double slashes ('\\') instead of a single slash ('\') to the krb ticket file. Example: C:\\testFolder\\ticketFile <ktgt-cache-file>/tmp/krb5cc_0</ktgt-cache-file>	No
`<character-set>`	Specifies the supporting CharSet Also called character set, it includes identifiers describing a series of universal characters. You must specify the `<character-set>` element only if the target database is MySql.	No

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 a standard (legacy) XML sample for the SQL actor adapter request. 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 standard legacy actor adapter request


<sqladapterRequest>
  <action>select * from table1</action>
</sqladapterRequest>

The adapter request uses the <targets> element to configure multiple SQL adapter target computers. The following figure shows a standard (dynamic) XML sample for the SQL actor adapter request. 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 standard dynamic actor adapter request


<sqlAdapterRequest>
    <targets>
      <target name="">
        <host>10.128.248.29</host>
        <port>3309</port>
        <user-name>root</user-name>
        <password>root</password>
        <subprotocol>mysql</subprotocol>
        <database>test</database>
        <driver>com.mysql.jdbc.Driver</driver>
        <character-set>sjis</character-set>
      </target>
    </targets>
    <action>select * from test</action>
</sqlAdapterRequest>

Note

You can also specify targets (dynamically) that are not in the adapter configuration.

The following figure shows a standard XML sample for the SQL actor adapter request with Sybase Kerberos Authentication. 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 standard actor adapter request with Sybase Kerberos Authentication.


<sqlAdapterRequest>
   <targets>
    <target name="SybaseKrb">
     <url>jdbc:sybase:Tds:10.128.248.105:5000/MASTER</url>
     <user-name>sa</user-name>
     <driver>com.sybase.jdbc3.jdbc.SybDriver</driver>
     <password />
     <database>master</database>
     <ktgt-cache-file>C:\\Program Files\\Mit\\Kerberos\\bin\\krb5cc_bmcadmin</ktgt-cache-file>
     <krb-service-principal>REALOPSLINUX1@SYNAPSE.COM</krb-service-principal>
    </target>
   </targets>
   <action>SELECT * FROM spt_monitor</action>
</sqlAdapterRequest>

The following table displays the elements of an SQL actor adapter response.

Elements of an SQL actor adapter response

Element	Description
`<queryExecuted>`	Syntax of the query defined in the adapter request
`<responseRowCount>`	Number of rows returned in the response
`<responseColumnCount>`	Number of columns returned for each row
`<row>`	Values returned for one row of the query
`<column>`	Column name and value returned in the response

The following figure illustrates an XML sample of the adapter response for the SQL actor adapter (legacy).

XML sample of the SQL actor adapter standard legacy response


<sqlAdapterResponse>
    <row>
        <column name = "description" label = "description">my new value</column>
    </row>
    <metadata>
        <queryExecuted>select * from test</queryExecuted> 
        <execution-milliseconds>4562</execution-milliseconds> 
        <responseRowCount>1</responseRowCount> 
        <responseColumnCount>1</responseColumnCount> 
        <status>success</status> 
    </metadata>
</sqlAdapterResponse>

For a Standard dynamic request, for multiple configurations, if you provide the <target> tag, you observe the <target-outputs> tag in the corresponding response.

The following table displays the additional elements of a dynamic adapter response that are included with the target-output response element.

Elements of an SQL actor adapter standard dynamic response

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.

The following figure illustrates an XML sample of the dynamic adapter response for the SQL actor adapter.

XML sample of the SQL standard actor adapter response (dynamic)


<sqlAdapterResponse> 
    <metadata> 
      <status>success</status> 
    </metadata> 
    <targets-output> 
      <target-output name="ARSA"> 
        <target-metadata> 
          <database>testdb</database> 
          <database-vendor>Oracle</database-vendor> 
          <status>success</status> 
        </target-metadata> 
        <action-response> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">900</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname1</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">800</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname2</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">500</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname3</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">600</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname4</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">800</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname5</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">400</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname6</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">300</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname7</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">700</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname8</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">800</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname8</column> 
          </row>
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">400</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname8</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">300</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname5</column> 
          </row> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">100</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname8</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">200</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname8</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">300</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname5</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">200</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname8</column> 
          </row> 
          <row> 
            <column label="PERSON_ID" name="PERSON_ID">300</column> 
            <column label="LAST_NAME" name="LAST_NAME">Surname5</column> 
          </row> 
          <metadata> 
            <queryExecuted>select PERSON_ID, LAST_NAME from PERSON</queryExecuted> 
            <execution-milliseconds>29</execution-milliseconds> 
            <responseRowCount>16</responseRowCount> 
            <responseColumnCount>2</responseColumnCount> 
            <status>success</status> 
          </metadata> 
        </action-response> 
      </target-output> 
    </targets-output> 
  </sqlAdapterResponse>

The following figure illustrates an XML sample of the adapter response for the SQL actor adapter with Sybase Kerberos Authentication.

XML sample of the SQL standard actor adapter response with Sybase Kerberos Authentication


<sqlAdapterResponse>
  <metadata>
    <status>success</status>
  </metadata>
  <targets-output>
    <target-output name="10.128.248.105">
      <target-metadata>
        <database>master</database>
        <database-vendor>Adaptive Server Enterprise</database-vendor>
        <status>success</status>
      </target-metadata>
      <action-response>
        <row>
          <column name="First_name" label="First_name">Name1</column>
          <column name="Last_name" label="Last_name">Surname1</column>
          <column name="Address" label="Address">ABCD</column>
          <column name="City" label="City">City</column>
          <column name="State" label="State">De</column>
          <column name="Zip" label="Zip">110019</column>
          <column name="Phone" label="Phone">12345678</column>
        </row>
        <row>
          <column name="First_name" label="First_name">Name2</column>
          <column name="Last_name" label="Last_name">Surname2</column>
          <column name="Address" label="Address">ABCD</column>
          <column name="City" label="City">City</column>
          <column name="State" label="State">De</column>
          <column name="Zip" label="Zip">110089</column>
          <column name="Phone" label="Phone">23456789</column>
        </row>
        <metadata>
          <queryExecuted>select * from Rolodex</queryExecuted>
          <execution-milliseconds>76</execution-milliseconds>
          <responseRowCount>2</responseRowCount>
          <responseColumnCount>7</responseColumnCount>
          <status>success</status>
        </metadata>
      </action-response>
    </target-output>
  </targets-output>
</sqlAdapterResponse>

SQL adapter Standard request and response

Comments