executeNativeSql
Execute the provided sqlText directly within the data source at dataSourcePath. This can only be used with data sources that support the direct submission of SQL. This cannot be used with published data services.
If isBlocking is TRUE, this operation does not return until the processing associated with the execution has completed; the result element is set. Otherwise, this operation returns immediately and the result element is unset.
If includeMetadata is TRUE, the response includes the metadata element. The metadata element describes the names and types of the column data that return in the result, in this call or in a later call to
getTabularResult.
If maxRows is set, result contains at most maxRows number of rows. If maxRows is fewer than the total number of rows of data available, additional calls to getTabularResult need to be made to get the rest of the available data. Use hasMoreRows element in result to determine if additional data is available. This is more accurate than comparing the number of rows returned with maxRows because the server might opt to return fewer than maxRows.
If users or groups is set, queries against system tables return resources that are accessible by the users and groups in these lists. If they are not set, system table queries return resources that are accessible by the current user. The current user must have the READ_ALL_RESOURCES privilege to set these parameters.
In the response, the completed element returns TRUE if all of the results associated with the execution have been exhausted.
The completed element reports whether all possible results have been retrieved. The requestStatus element reports the status of the server request associated with the execution. The request status can be one of the following:
• STARTED: The request has started. The request has been created, but is not yet running.
• WAITING: The request is waiting in a queue for the server to process the request.
• RUNNING: The request is currently being executed by the server.
• COMPLETED: The execution associated with the request has completed. Results can now be acquired.
• CLOSING: The request is closing.
• SUCCESS: The request closed with success.
• FAILURE: The request closed with failure.
• TERMINATED: The request was terminated.
All system resources associated with this operation are closed when the transaction containing the call completes. This means that resultId is not valid if it is used outside of the transaction that called this execute operation. If you want to use resultId, you must surround the calls to this execute operation and any operations using resultId with an explicit transaction.
Only users with the ACCESS_TOOLS right can call this operation.
Location
/services/webservices/system/admin/execute/operations/
Request Elements
sqlText: The SQL to execute.
isBlocking: If TRUE (default), do not return until execution completes.
includeMetadata (optional): If TRUE, the response contains information about the column names and their types. Defaults to FALSE.
skipRows (optional): The number of rows to skip in the execution output before generating results. If not set, no rows are skipped. If set, the specified number of rows is skipped in the execution output before returning any results. If skipRows is greater than the total possible number of rows, no rows are returned.
maxRows (optional): The maximum number of rows to return. If not set, all rows are returned.
consumeRemainingRows (optional): If set and TRUE, all remaining rows after maxRows are consumed.
users: (optional) For system table queries, returns results that are accessible by the users included in this list. Each user is specified by a name and optional domain.
groups: (optional) For system table queries, returns results that are accessible by the groups included in this list. Each group is specified by a name and optional domain.
dataSourcePath: The path to the data source.
Response Elements
completed: If TRUE, all processing associated with execution has completed.
requestStatus: Status of the server request performing the execution.
metadata (optional): Table metadata listing the column names and types within the result.
rowsAffected (optional): If known, the number of rows affected by the execution (otherwise unset). This includes a count of rows that were skipped (see skipRows element) or consumed (see consumedRemainingRows element).
result (optional): The result data.
hasMoreRows: TRUE if the table has more rows than the number affected (rowsAffected, above).
totalRowCount: Total number of rows in the table.
rows: Type-value pair for each row.
resultId: A handle to the result in the server. Valid only if it is used inside of the transaction that called this execute operation.
requestId: The server request ID associated with execution.
Faults
IllegalArgument: If any elements are malformed.
NotAllowed: If the data source does not support SQL execution.
NotFound: If the data source at the given path does not exist.
RuntimeError: If an error occurs during execution.
Security: If the user does not have the ACCESS_TOOLS right and READ, WRITE and other appropriate privileges on the data source.