Able to export columns data with table structure for only those selected columns like sqlYog (MXS-4143)

[MXS-4883] Extend /sql/:id/queries to return result set metadata Created: 2023-12-04  Updated: 2024-01-10  Resolved: 2024-01-10

Status: Closed
Project: MariaDB MaxScale
Component/s: REST-API
Affects Version/s: None
Fix Version/s: 24.02.0

Type: Sub-Task Priority: Major
Reporter: Duong Thien Ly Assignee: markus makela
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MXS-4143 Able to export columns data with tabl... Closed
Sprint: MXS-SPRINT-194, MXS-SPRINT-195, MXS-SPRINT-196, MXS-SPRINT-197, MXS-SPRINT-199

 Description   

The GUI needs result set metadata like column datatypes, sizes, and the result set's table name to add the "Export as SQL" option. Parsing the query in the GUI was considered but it's not an optimal approach as the users can have multiple "select" in a stored procedure, the GUI can't parse the store procedure to query additional metadata.
The `mariadb-connector-cpp` seems to have the `ResultSetMetaData` interface, so getting the result set's metadata seems possible.

 Comments   
Comment by Duong Thien Ly [ 2023-12-04 ]

For example:

{
 
   "data":{
 
      "attributes":{
 
         "execution_time":0.00026922799999999999,
 
         "results":[
 
            {
 
               "complete":true,
 
               "data":[
 
                  [
 
                     1
 
                  ]
 
               ],
 
               "fields":[
 
                  "id",
 
                  "name"
 
               ],
 
               "attributes":[
 
                  "int(11)",
 
                  "char(10)"
 
               ],
 
               "table":"t1"
 
            }
 
         ],
 
         "sql":"SELECT id, name FROM test.t1"
 
      }
 
   }
 
}

Generated at Thu Feb 08 04:31:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.