REST API: SQL Execution Service

The SQL Execution Service allows you to input one or more SQL statements for submission.
For more information, see SQL Reference.
HTTP Method and URI Path
The URI path has the following format:
POST
<base URL>
/sql/execute/
<datasource>
Where:
  • <base URL>
    Specifies the base URL address for the CA IDMS REST API service
  • <datasource>
    (required) Identifies the CA IDMS system where the API request will be sent and is defined in the datasources.yml file
Example: Execute Multiple SQL Statements Supplied as a List of Strings
https://zoshost.domain.com:10010/api/v1/sql/execute/SYSOD108
Request body:
In the request body, supply one or more SQL statements as a list of strings. Make each SQL statement a separate entry in the list.
{ "sql": [ "select * from demoempl.department where dept_head_id = 1003", "select dept_id, dept_name from demoempl.department where dept_head_id = 1003", "select * from demoempl.department where dept_head_id = 1004", "insert into demoempl.department values (9999, 2004, 'D09', 'TEST DEPARTMENT')", "update demoempl.department set dept_name = 'UPDATE DEPARTMENT' where dept_id = 9999", "delete from demoempl.department where dept_id = 9999", "invalid statement" ] }
Response:
[ { "statementId": 1, "sqlStatement": "select * from demoempl.department where dept_head_id = 1003", "resultSet": [ { "DEPT_ID": 6000, "DEPT_HEAD_ID": 1003, "DIV_CODE": "D09", "DEPT_NAME": "LEGAL" }, { "DEPT_ID": 4200, "DEPT_HEAD_ID": 1003, "DIV_CODE": "D04", "DEPT_NAME": "LEASING - NEW CARS" } ], "updateCount": null, "errors": [] }, { "statementId": 2, "sqlStatement": "select dept_id, dept_name from demoempl.department where dept_head_id = 1003", "resultSet": [ { "DEPT_ID": 6000, "DEPT_NAME": "LEGAL" }, { "DEPT_ID": 4200, "DEPT_NAME": "LEASING - NEW CARS" } ], "updateCount": null, "errors": [] }, { "statementId": 3, "sqlStatement": "select * from demoempl.department where dept_head_id = 1004", "resultSet": [], "updateCount": null, "errors": [] }, { "statementId": 4, "sqlStatement": "insert into demoempl.department values (9999, 2004, 'D09', 'TEST DEPARTMENT')", "resultSet": null, "updateCount": 1, "errors": [] }, { "statementId": 5, "sqlStatement": "update demoempl.department set dept_name = 'UPDATE DEPARTMENT' where dept_id = 9999", "resultSet": null, "updateCount": 1, "errors": [] }, { "statementId": 6, "sqlStatement": "delete from demoempl.department where dept_id = 9999", "resultSet": null, "updateCount": 1, "errors": [] }, { "statementId": 7, "sqlStatement": "invalid statement", "resultSet": null, "updateCount": null, "errors": [ { "sqlState": "42600", "errorCode": -4, "message": "SQL Error: SQLCODE = -4 SQLERC = 6001" }, { "sqlState": "42600", "errorCode": -4, "message": "DB006001 T1173 C-4M330: Unrecognizable token" }, { "sqlState": "42600", "errorCode": -4, "message": "Syntax error: invalid statement" } ] } ]