How can I use the executeNativeSql api call to run a query?
Article ID: KB0081945
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | 7.0.0 and higher |
Description
How can I use the executeNativeSql api call to run a query?
Issue/Introduction
How can I use the executeNativeSql api call to run a query?
Resolution
1. Decide on a table to query (e.g. /shared/DATASOURCES/oracle_ds_vdi/JOE/PERSONS)
2. Execute /services/webservices/system/admin/execute/operations/executeNativeSql
3. Enter the input parameter in the required XML format. Below is an example of an input parameter that runs the query: select * from Persons.
<execute:executeNativeSql xmlns:execute="http://www.compositesw.com/services/system/admin/execute" xmlns:user="http://www.compositesw.com/services/system/admin/user">
<execute:sqlText>select * from Persons</execute:sqlText>
<execute:isBlocking>1</execute:isBlocking>
<execute:includeMetadata>false</execute:includeMetadata>
<execute:maxRows>10</execute:maxRows>
<execute:consumeRemainingRows>1</execute:consumeRemainingRows>
<execute:dataSourcePath>/shared/DATASOURCES/oracle_ds_vdi/</execute:dataSourcePath>
</execute:executeNativeSql>
The results of the query will be in XML, as shown below:
<execute:executeNativeSqlResponse xmlns:execute="http://www.compositesw.com/services/system/admin/execute" xmlns:common="http://www.compositesw.com/services/system/util/common" xmlns:resource="http://www.compositesw.com/services/system/admin/resource">
<execute:completed>true</execute:completed>
<execute:requestStatus>COMPLETED</execute:requestStatus>
<execute:rowsAffected>1</execute:rowsAffected>
<execute:result>
<execute:hasMoreRows>false</execute:hasMoreRows>
<execute:totalRowCount>1</execute:totalRowCount>
<execute:rows>
<execute:row>
<execute:value>Ann</execute:value>
<execute:value>11</execute:value>
</execute:row>
</execute:rows>
</execute:result>
<execute:requestId>1200499</execute:requestId>
<execute:resultId>1200499-0</execute:resultId>
</execute:executeNativeSqlResponse>
2. Execute /services/webservices/system/admin/execute/operations/executeNativeSql
3. Enter the input parameter in the required XML format. Below is an example of an input parameter that runs the query: select * from Persons.
<execute:executeNativeSql xmlns:execute="http://www.compositesw.com/services/system/admin/execute" xmlns:user="http://www.compositesw.com/services/system/admin/user">
<execute:sqlText>select * from Persons</execute:sqlText>
<execute:isBlocking>1</execute:isBlocking>
<execute:includeMetadata>false</execute:includeMetadata>
<execute:maxRows>10</execute:maxRows>
<execute:consumeRemainingRows>1</execute:consumeRemainingRows>
<execute:dataSourcePath>/shared/DATASOURCES/oracle_ds_vdi/</execute:dataSourcePath>
</execute:executeNativeSql>
The results of the query will be in XML, as shown below:
<execute:executeNativeSqlResponse xmlns:execute="http://www.compositesw.com/services/system/admin/execute" xmlns:common="http://www.compositesw.com/services/system/util/common" xmlns:resource="http://www.compositesw.com/services/system/admin/resource">
<execute:completed>true</execute:completed>
<execute:requestStatus>COMPLETED</execute:requestStatus>
<execute:rowsAffected>1</execute:rowsAffected>
<execute:result>
<execute:hasMoreRows>false</execute:hasMoreRows>
<execute:totalRowCount>1</execute:totalRowCount>
<execute:rows>
<execute:row>
<execute:value>Ann</execute:value>
<execute:value>11</execute:value>
</execute:row>
</execute:rows>
</execute:result>
<execute:requestId>1200499</execute:requestId>
<execute:resultId>1200499-0</execute:resultId>
</execute:executeNativeSqlResponse>
Additional Information
Feedback
Yes
No
Powered by
