Execute Procedures

Version 22.0.8486


Execute Procedures


The exec operation allows users to execute stored procedures defined for each data source. You can retrieve a list of stored procedures by querying the metadata API.

Request Format

POST /rest.rsc/exec

Request Body

All requests must include a JSON body like the following:

{
  "procedure": "<string>",
  "defaultSchema": "<string>",
  "parameters": {
    "@p1": { "dataType": <int>, "direction": <int>, "value": <any> },
    "@p2": { "dataType": <int>, "direction": <int>, "value": <any> },
    ...
  }
}

Fields

procedure Required The name of the stored procedure to execute.
defaultSchema Optional If the name specified with procedure is not prefixed with a schema name, this property must be used to specify a default schema.
timeout Required The command timeout of the SQL query in seconds. Default is 60.
parameters Optional A JSON object containing a list of procedure parameters. All parameter names must begin with @.
  dataType Required The parameter’s data type.
  direction Optional The parameter’s direction (see below). Default is 1 (input parameter).
  value Optional The parameter’s value, if applicable (see below).

Valid values for direction are:

  • 1: Input parameter (value is required)
  • 2: In/out parameter (value is optional; treated as null if absent)
  • 4: Output parameter (value is ignored)
  • 5: Return value (value is ignored)

Response Format

The results of the stored procedure execution are returned using the standard JSON result object. Any parameters with a direction of 2, 4, or 5 are included in the result.

Example

POST https://myconnectserver/rest.rsc/exec

Request Body

{
  "procedure": "UploadDocument",
  "defaultSchema": "Salesforce1",
  "timeout": 60,
  "parameters": {
    "Base64Data": {
      "dataType": 5,
      "direction": 1,
      "value": "VGhpcyBpcyBhIHNhbXBsZSB0ZXh0IGRvY3VtZW50"
    },
    "Name": {
      "dataType": 5,
      "direction": 1,
      "value": "SampleText.txt"
    },
    "FolderId": {
      "dataType": 5,
      "direction": 1,
      "value": "00l40000001Z4HtAAK"
    },
    "Description": {
      "dataType": 5,
      "direction": 1,
      "value": "A Sample txt file uploaded with base 64 data."
    }
  }
}

Response

{
  "results": [
    {
      "schema": [
        {
          "ordinal": 0,
          "catalogName": "CData",
          "schemaName": "Salesforce1",
          "tableName": "UploadDocument",
          "columnName": "Id",
          "columnLabel": "Id",
          "dataType": 5,
          "dataTypeName": "varchar",
          "length": 2000,
          "precision": 2000,
          "scale": 0,
          "nullable": true
        }
      ],
      "rows": [
        [
          "0151W000004EKT8QAO"
        ]
      ]
    }
  ]
}