Method: POST ~/db/query
Content Type: text/plain
API to run database run SQL and get the selected data. The request content should be a plain text of a list of SQL statements terminated by a semicolon. Note that if you provide multiple SQL SELECT, the service will return the data from the last SELECT statement unless otherwise specified.
This service is READ ONLY.
Respone (application/x-json)
The response of the service is an XJSON object with the following specification.
A note on data type.
Since XJSON is a typed serial notation, you can determine the return data type from the notation. Thus, the attributes is provided additional type information. Use the following table to decode the data type.
| type | size | data type description |
|---|---|---|
| A | - | string |
| I | 11 | integer |
| N | 11 | double |
| $ | n,m | currency |
| D | 8 | date |
| D | 16 | datetime |
| @ | - | datetime |
| L | - | boolean |
| B | - | binary data |
| M | - | text |
[
{
columns: [ columnName, ... ],
coltypes: [ [type, size],
[type, size],
...
],
records: [
[ columnValue, ... ],
[ columnValue, ... ],
...,
],
}
]
Example, single SQL
select orderNo from saomstr where issdate >= '2024-05-15' limit 2 into t_Filter;
select a.orderno, a.issdate, b.style, b.description from SAOMSTR a inner join t_Filter o on o.OrderNo = a.OrderNo inner join SAOSTYLE b on b.OrderNo = a.OrderNo
Reutrn data
[
{
"columns": [ "orderno", "issdate", "style", "description" ],
"coltypes": [
[ "A", 42 ],
[ "@", 19 ],
[ "A", 45 ],
[ "A", 180 ]
],
"records": [
[ "132/24-0405", D2024-07-24T00:00:00, 52366, "ALYSSUM JACKET" ],
[ "132/24-0406", D2024-08-14T00:00:00, 52366, "ALYSSUM JACKET" ]
]
}
]
Instead of running a single SQL SELECT, you can provide multiple SQLs where each SQL end with a semicolon. When providing multiple SQL, the server will only return data from the last SELECT statment. But if you want to get data from other SELECT statement, you need to specify explicitly.
Example, multiple select
SELECT * FROM table1;
SELECT * FROM table2;
You should get the select data of 'table2'.
Example, multple select return mutlipe dataset
SELECT * FROM table1 into table1;
SELECT * FROM table2;
The return data will be a list of two elements with the following structure.
[
{ last select result },
{
<tablename>: { resultset },
...
}
]
**Using Temporay Table**
You may also store data into temporay table to be used by subsequence select. If you use temporay table, the temporary table name must be started with T_.
~~~sql
SELECT * FROM table1 INTO TEMPORARY TABLE T_A;
CREATE INDEX T_A_KEY1 ON (order);
SELECT * FROM table2 LEFT JOIN T_A WHERE T_A.order > 1000;
DROP INDEX T_A_KEY1;
API to download file. Supported method is GET.
url: ~/db/get_file/<file_name>
Request Example
eg: get style image (example with trial505, https://trial505.igx.biz/api)
step 1. get real file name from system image table: ~/db/query
sql: select b.ImageType, b.RealFileName from SAYSKTCH a inner join SYIMAGE b on b.ImageID = a.ImageID where a.OrderNo = 'BEI-TEST24-001'
return:
[{
"columns": ["ImageType","RealFileName"],
"coltypes": [["A",9],["A",120]],
"records": [["JPG","b9b957b159d66ceec231a26c776dd9d390"]]
}]
step 2. url: ~/db/get_file/b9b957b159d66ceec231a26c776dd9d390
Return Example
{
"file_name": "b9b957b159d66ceec231a26c776dd9d390", #file name, same as query param
"file_type": "", #file suffix
"data": "iVBORw0KGgoAAAANSUhEUgAAAPoAAAD6CAIAAAAHjs1qAAAACXBIWXMAAAsSAAALEgHS3X78AAAgAElEQVR4nOy9WZckSXYedu...... sfuTm4f5Wv8uXknobmV/kqf6N8hftX+Q3JV7h/ld+QfIX7V/kNyVe4f5XfkHyF+1f5DclXuH+V35B8hftX+Q3J/w+JbeR8InngWAAAAABJRU5ErkJggg=="
# file data, encoded in Base64
}
If file_type is blank, can get file type from system image table.