Search

Query Operation

Version 22.0.8486

Query Operation

The query operation allows users to send a SQL query to the connections exposed by CData Connect. SELECT, INSERT, UPDATE, DELETE, and EXEC statements are all supported (though the EXEC operation should be preferred for the latter). The results of the query are returned in a serialized JSON response.

Request Format

POST /rest.rsc/query

Request Body

All requests must include a JSON body like the following:

{
  "query": "<string>",
  "defaultSchema": "<string>",
  "schemaOnly": <bool>,
  "timeout": <int>,
  "parameters": {
    "@p1": { "dataType": <int>, "value": <any> },
    "@p2": { "dataType": <int>, "value": <any> },
    ...
  }
}

Fields

query Required The SQL statement(s) to execute. Separate multiple statements with semi-colons ;.
defaultSchema Optional If any tables mentioned in query are not prefixed with a schema name, this property must be used to specify a default schema.
schemaOnly Optional If true, the result will only include column metadata. Default is false.
timeout Required The command timeout of the SQL query in seconds. Default is 60.
parameters Optional A JSON object containing a list of query parameters. All parameter names must begin with @.
  dataType Required The parameter’s data type.
  value Required The parameter’s value.

Response Format

The query’s result set(s) are returned in the standard JSON result object.

Example

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

Request Body

{
  "query": "SELECT Id, AccountName, AccountNumber FROM Salesforce1.Account LIMIT 5",
  "timeout": 60,
  "parameters": {
  }
}

Response

{
  "results": [
  {
       "schema": [
  ​    {
  ​     "TABLE_CATALOG": "CData",
  ​     "TABLE_SCHEMA": "Salesforce1",
  ​     "TABLE_NAME": "Account",
  ​     "COLUMN_NAME": "Id",
  ​     "DATA_TYPE": 5,
  ​     "TYPE_NAME": "varchar",
  ​     "COLUMN_SIZE": 18,
  ​     "PRECISION": 18,
  ​     "SCALE": 0,
  ​     "IS_KEY": false,
  ​     "IS_NULLABLE": false,
  ​     "REMARKS": null,
  ​     "ORDINAL_POSITION": 0,
  ​     "IS_AUTOINCREMENT": false,
  ​     "IS_GENERATEDCOLUMN": false
  ​    },
  ​    {
  ​     "TABLE_CATALOG": "CData",
  ​     "TABLE_SCHEMA": "Salesforce1",
  ​     "TABLE_NAME": "Account",
  ​     "COLUMN_NAME": "Name",
  ​     "DATA_TYPE": 5,
  ​     "TYPE_NAME": "varchar",
  ​     "COLUMN_SIZE": 255,
  ​     "PRECISION": 255,
  ​     "SCALE": 0,
  ​     "IS_KEY": false,
  ​     "IS_NULLABLE": false,
  ​     "REMARKS": null,
  ​     "ORDINAL_POSITION": 1,
  ​     "IS_AUTOINCREMENT": false,
  ​     "IS_GENERATEDCOLUMN": false
  ​    },
  ​    {
  ​     "TABLE_CATALOG": "CData",
  ​     "TABLE_SCHEMA": "Salesforce1",
  ​     "TABLE_NAME": "Account",
  ​     "COLUMN_NAME": "AccountNumber",
  ​     "DATA_TYPE": 5,
  ​     "TYPE_NAME": "varchar",
  ​     "COLUMN_SIZE": 40,
  ​     "PRECISION": 40,
  ​     "SCALE": 0,
  ​     "IS_KEY": false,
  ​     "IS_NULLABLE": true,
  ​     "REMARKS": null,
  ​     "ORDINAL_POSITION": 2,
  ​     "IS_AUTOINCREMENT": false,
  ​     "IS_GENERATEDCOLUMN": false
  ​    }
     ],
     "rows": [
  ​    [
  ​     "0011W00002SoIgXQAV",
  ​     "Acme",
  ​     "000001"
  ​    ],
  ​    [
  ​     "0011W00002ShNlMQAV",
  ​     "Great Cakes",
  ​     "000002"
  ​    ],
  ​    [
  ​     "0011W00002TEDa8QAH",
  ​     "Billboard Tech",
  ​     "000003"
  ​    ],
  ​    [
  ​     "0011W00002SiUBQQA3",
  ​     "Firefly",
  ​     "000004"
  ​    ],
  ​    [
  ​     "0011W00002SZpGEQA1",
  ​     "Auto One",
  ​     "000005"
  ​    ]
   ]
  }
 ]
}