iGarment Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Database API ======

===== iGarment API: Query =====

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.''

{{:en:screenshot_2026-06-03_163929.png?400|}}

  {
    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

Return 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" ]
        ]
    }

==== Query procedure ====

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_.

  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;
  
===== iGarment API: Get file =====

API to download file. Supported method is GET.

url: ~/db/get_file/<file_name>

<file_name> is real file name saved in system, can query it from attach table or system image table.

**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.''