SQL Queries

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 property（rest.timeoutについては、System Propertiesを参照）；
ログにも同じエラーが記録されています。