How do I a SQL/REST callback with an array of records

[AppSynergy Support]

I am making a SQL/REST call to GET a array of records.

How can I map that to an SQL table????

[AppSynergy Support]

You callback procedure might look something like this:

BEGIN

DECLARE r, v_rec_count BIGINT;

DECLARE v_resp_body, v_json_record MEDIUMTEXT;

SELECT response_body

INTO v_resp_body

FROM parasql_http_request

WHERE id = param_req_id;

SET r = 0;

SET v_rec_count = JSON_LENGTH(v_resp_body, '$');

WHILE (r < v_rec_count) DO

SET v_json_record = JSON_EXTRACT(v_resp_body, concat('$[',r,']') ); -- get the current record from the array

INSERT INTO my_ladings SET

      lading_uuid = JSON_VALUE(v_json_record, '$.lading_uuid'),

      pro_bill = JSON_VALUE(v_json_record, '$.pro_bill'),

      trip_number = JSON_VALUE(v_json_record, '$.trip_number'),

      load_weight = JSON_VALUE(v_json_record, '$.load_weight'),

      load_weight_actual = JSON_VALUE(v_json_record, '$.load_weight_actual'),

      load_length = JSON_VALUE(v_json_record, '$.load_length'),

      load_pieces = JSON_VALUE(v_json_record, '$.load_pieces'),

      load_pallets = JSON_VALUE(v_json_record, '$.load_pallets')

ON DUPLICATE KEY UPDATE

      pro_bill = JSON_VALUE(v_json_record, '$.pro_bill'),

      trip_number = JSON_VALUE(v_json_record, '$.trip_number'),

      load_weight = JSON_VALUE(v_json_record, '$.load_weight'),

      load_weight_actual = JSON_VALUE(v_json_record, '$.load_weight_actual'),

      load_length = JSON_VALUE(v_json_record, '$.load_length'),

      load_pieces = JSON_VALUE(v_json_record, '$.load_pieces'),

      load_pallets = JSON_VALUE(v_json_record, '$.load_pallets');

-- increment counter

SET r = r + 1;

END WHILE;

END