POST

POST /rest/api/query

Request Body

SQL文は以下のJSONオブジェクトにラップされます:

{ "sql": "SELECT * FROM SYS.Tables" }

Query Parameters

すべてのパラメータはオプションです。

Parameter

Type

Default value

Description

array

boolean

TRUE

If TRUE, returns a 2D array, otherwise returns an array of objects (see examples below)

headers

boolean

FALSE

Works only with array=TRUE and controls whether the column headers will be included in the response or not

pagination

boolean

FALSE

Activates the pagination mode

requestid

string

""

Allows to use a cursor with the provided identifier which has been buffered in a previous request

limit

long

-1

Restricts the number of results returned from the SQL statement

offset

long

-1

Excludes the number of results returned from the SQL statement

timeout

long

600000

Timeout in milliseconds before closing connection to the CData Virtuality Server (counted after the last use of the connection).

To change the default value, set the desired one via the rest.timeout system property in the standalone.conf.props(.bat) file

Examples

Example 1

POST http://<cdatavirtuality-server-address>:8080/rest/api/query?array=true&timeout=1200000

ボディ (application/json):

{ "sql": "SELECT * FROM SYS.Tables" }
 
STATUS 200
 
[
["datavirtuality","SYS","Columns","Table",null,true,false,"tid:2cb59cfd55db-9bd42fbd-00000001",-1,null,true,false,1],
["datavirtuality","SYS","DataTypes","Table",null,true,false,"tid:2cb59cfd55db-6130d2cf-00000021",-1,null,true,false,2]
]

Example 2

POST http://<cdatavirtuality-server-address>:8080/rest/api/query?array=true&headers=true

ボディ (application/json):

{ "sql": "SELECT * FROM SYS.Tables" }
 
STATUS 200
 
[
["VDBName","SchemaName","Name","Type","NameInSource","IsPhysical","SupportsUpdates","UID","Cardinality","Description","IsSystem","IsMaterialized","OID"],
["datavirtuality","SYS","Columns","Table",null,true,false,"tid:2cb59cfd55db-9bd42fbd-00000001",-1,null,true,false,1],
["datavirtuality","SYS","DataTypes","Table",null,true,false,"tid:2cb59cfd55db-6130d2cf-00000021",-1,null,true,false,2]
 
]

Example 3

POST http://<cdatavirtuality-server-address>:8080/rest/api/query?array=false

ボディ (application/json):

{ "sql": "SELECT * FROM SYS.Tables" }
 
STATUS 200
 
[
{"VDBName":"datavirtuality","SchemaName":"SYS","Name":"Columns","Type":"Table","NameInSource":null,"IsPhysical":true,"SupportsUpdates":false,"UID":"tid:2cb59cfd55db-9bd42fbd-00000001","Cardinality":-1,"Description":null,"IsSystem":true,"IsMaterialized":false,"OID":1},
{"VDBName":"datavirtuality","SchemaName":"SYS","Name":"DataTypes","Type":"Table","NameInSource":null,"IsPhysical":true,"SupportsUpdates":false,"UID":"tid:2cb59cfd55db-6130d2cf-00000021","Cardinality":-1,"Description":null,"IsSystem":true,"IsMaterialized":false,"OID":2}
]

GET

POSTメソッドを使用することを推奨しますが(より安全であるため)、GETメソッドも使用できます(デモ目的や特殊な場合など)。

GET /rest/api/query

Query Parameters

sqlを除くすべてのパラメータはオプションです。

Parameter

Type

Default value

Description

sql

string


Encoded SQL statement

array

boolean

TRUE

If TRUE, returns a 2D array, otherwise returns an array of objects (see example below)

headers

boolean

FALSE

Works only with array=TRUE and controls whether the column headers will be included in the response or not

pagination

boolean

FALSE

Activates the pagination mode

requestid

string

""

Allows to use a cursor with the provided identifier which has been buffered in a previous request

limit

long

-1

Restricts the number of results returned from the SQL statement

offset

long

-1

Excludes the number of results returned from the SQL statement

timeout

long

600000

Timeout in milliseconds before closing connection to the CData Virtuality Server (counted after the last use of the connection).

To change the default value, set the desired one via the rest.timeout system property in the standalone.conf.props(.bat) file

Examples

以下は、GET メソッドを使用してエンドポイントに問い合わせる呼び出しの例です:

GET http://<cdatavirtuality-server-address>:8080/rest/api/query?sql=select%201&array=false&timeout=1200000
 
STATUS 200
 
[{"expr1":1}]

Executing Stored Procedures

ストアドプロシージャを呼び出すこともできます。SQL文の中で二重引用符をエスケープすることを忘れないでください:

{ "sql": "CALL SYSADMIN.addUser(\"name\" => 'test2', \"pwd\" => 'string_pwd', \"role_name\" => 'admin-role')" }

このような場合の典型的な応答は、STATUS 200の空の配列です。

結果セットを返すストアド プロシージャを呼び出すには、通常、SELECT 文を使用します:

{ "sql": "SELECT * FROM (CALL SYSADMIN.getCurrentDWH()) temp" }

Timeout

クエリがタイムアウトした場合、 、以下のようになります:

  • 200 Okレスポンスが返されます;
  • クエリがタイムアウトする前に取得されたデータはJSONで返されます;
  • データの最後に、以下のエラーメッセージがJSONファイルの新しいフィールドとして追加されます:The datasource is closed by timeout. Please, provide a timeout either explicitly in your request or in rest.timeout system propertyrest.timeoutについては、System Propertiesを参照);
  • ログにも同じエラーが記録されています。