iGarment Wiki

User Tools

Site Tools

Trace:
en:db_api

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
en:db_api [2026/06/03 09:34] – created administratoren:db_api [2026/06/05 07:14] (current) – administrator
Line 1: Line 1:
-====== iGarment API: Query ======+====== Database API ====== 
 + 
 +===== iGarment API: Query =====
  
 Method: POST ~/db/query Method: POST ~/db/query
 +
 Content Type: text/plain Content Type: text/plain
  
Line 8: Line 11:
 This service is READ ONLY. This service is READ ONLY.
  
-Respone (application/x-json)+**Respone (application/x-json)**
  
 The response of the service is an XJSON object with the following specification. The response of the service is an XJSON object with the following specification.
  
-A note on data type.+''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.+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|}}
  
-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, ... ],     columns: [ columnName, ... ],
Line 40: Line 33:
     ],     ],
   }   }
-]+
 Example, single SQL Example, single SQL
  
-select orderNo from saomstr where issdate >= '2024-05-15' limit 2 into t_Filter; +   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 +   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+ 
 +Return data
  
-[ 
     {     {
         "columns": [ "orderno", "issdate", "style", "description" ],         "columns": [ "orderno", "issdate", "style", "description" ],
Line 61: Line 54:
         ]         ]
     }     }
-] + 
-Query procedure+==== 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. 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 Example, multiple select
  
-SELECT * FROM table1; +  SELECT * FROM table1; 
-SELECT * FROM table2;+  SELECT * FROM table2; 
 You should get the select data of 'table2'. You should get the select data of 'table2'.
  
 Example, multple select return mutlipe dataset Example, multple select return mutlipe dataset
  
-SELECT * FROM table1 into table1; +  SELECT * FROM table1 into table1; 
-SELECT * FROM table2;+  SELECT * FROM table2; 
 The return data will be a list of two elements with the following structure. The return data will be a list of two elements with the following structure.
  
-[+
   { last select result },   { last select result },
   {   {
Line 83: Line 79:
     ...     ...
   }   }
-]+
  
 **Using Temporay Table** **Using Temporay Table**
Line 89: Line 85:
 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_. 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; 
-SELECT * FROM table1 INTO TEMPORARY TABLE T_A; +  CREATE INDEX T_A_KEY1 ON (order); 
-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 =====
  
-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. API to download file. Supported method is GET.
  
Line 101: Line 98:
  
 <file_name> is real file name saved in system, can query it from attach table or system image table. <file_name> is real file name saved in system, can query it from attach table or system image table.
-Request Example+ 
 +**Request Example**
  
 eg: get style image (example with trial505, https://trial505.igx.biz/api) eg: get style image (example with trial505, https://trial505.igx.biz/api)
 +
 step 1. get real file name from system image table: ~/db/query 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'+  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: return:
    [{    [{
Line 113: Line 112:
         "records":  [["JPG","b9b957b159d66ceec231a26c776dd9d390"]]         "records":  [["JPG","b9b957b159d66ceec231a26c776dd9d390"]]
     }]     }]
 +    
 step 2. url: ~/db/get_file/b9b957b159d66ceec231a26c776dd9d390 step 2. url: ~/db/get_file/b9b957b159d66ceec231a26c776dd9d390
  
-Return Example+**Return Example**
  
-{+  {
   "file_name": "b9b957b159d66ceec231a26c776dd9d390",  #file name, same as query param   "file_name": "b9b957b159d66ceec231a26c776dd9d390",  #file name, same as query param
   "file_type": "",   #file suffix   "file_type": "",   #file suffix
   "data": "iVBORw0KGgoAAAANSUhEUgAAAPoAAAD6CAIAAAAHjs1qAAAACXBIWXMAAAsSAAALEgHS3X78AAAgAElEQVR4nOy9WZckSXYedu...... sfuTm4f5Wv8uXknobmV/kqf6N8hftX+Q3JV7h/ld+QfIX7V/kNyVe4f5XfkHyF+1f5DclXuH+V35B8hftX+Q3J/w+JbeR8InngWAAAAABJRU5ErkJggg=="   "data": "iVBORw0KGgoAAAANSUhEUgAAAPoAAAD6CAIAAAAHjs1qAAAACXBIWXMAAAsSAAALEgHS3X78AAAgAElEQVR4nOy9WZckSXYedu...... sfuTm4f5Wv8uXknobmV/kqf6N8hftX+Q3JV7h/ld+QfIX7V/kNyVe4f5XfkHyF+1f5DclXuH+V35B8hftX+Q3J/w+JbeR8InngWAAAAABJRU5ErkJggg=="
    # file data, encoded in Base64    # file data, encoded in Base64
-} +  } 
-If file_type is blank, can get file type from system image table.+   
 +''If file_type is blank, can get file type from system image table.''
en/db_api.1780479274.txt.gzip · Last modified: by administrator