executeSqlScript
Execute the provided scriptText directly within the TDV server.
If isBlocking is TRUE, this operation does not return until the processing associated with the execution has completed; the outputs element is set. Otherwise, this operation returns immediately and the outputs element is unset.
If includeMetadata is TRUE, the response includes the metadata element which describes the names and types of the output parameter data provided in the result either in this call or in a later call to
getProceduralResult.
The inputs element contains a set of parameters to be inputs to the execution. A parameter contains two elements:
• definition: The SQL-language data type for this parameter value. For example, VARCHAR(40) or BIGINT.
• value: The value of this parameter.
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.
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 request processing.
• 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.
The outputs element contains a set of ProcValue instance. If the out type is cursor, you should get a resultId, which is a handle to the result in server. You can use it with
getTabularResult and
closeResult. If the out type is not cursor, you should get real output value.
The returned resultId returns a handle to the result in the server. This can be used with
getProceduralResult and
closeResult.
The value returned for output parameters that are cursors is a tabular resultId. This can be used with getTabularResult and closeResult. Closing a tabular resultId only closes that cursor, while closing a procedural resultId closes the procedure and all cursors associated with the procedure.
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 same transaction that called this execute operation. Therefore, 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
isBlocking (optional): If TRUE (the default), do not return until the execution completes.
includeMetadata (optional): If TRUE, the response contains information about the output parameter names and their types. Defaults to FALSE.
inputs (optional): List of definitions and values for parameters to use as execution input.
scriptText: The SQL to be executed.
users (optional): For system table queries, returns results that are accessible by the users included in this list.
groups (optional): For system table queries, returns results that are accessible by the groups included in this list.
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.
outputs (optional): A set of parameter values returned as output from the execution.
result: The result ID.
requestId: The request ID.
Faults
IllegalArgument: If any elements are malformed.
IllegalArgument: If any required parameters in the inputs element are missing.
IllegalArgument: If unexpected parameters are provided in the inputs element
NotAllowed: If an attempt is made to use this operation with an insufficient license. RuntimeError: If an error occurs during execution.
RuntimeError: If the user does not have appropriate privileges on any resources referred to by the scriptText.
Security: If the user does not have the ACCESS_TOOLS right.