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
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 TrueSight Orchestration 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 TrueSight Orchestration 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>
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 TrueSight Orchestration 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
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
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>