Hi Esteban,
Right
now I'm about to go on holiday so I don't have enough time to rewrite
your whole program (and I'm missing the definitions from the YAJL
header), however, maybe you will find the following useful. Basically,
you can replace almost all of your file I/O code and all of your YAJL code, together with all associated data-structures with this single statement, which will return you your JSON object:
select json_object(
'FILE1':
json_arrayagg(
json_object(
'ISODate': "ISODate",
'AdverseClosure': "AdverseClosure",
'D/MorBoth': "D/MorBoth",
'StatusCode': "StatusCode",
'StatusDescription': "StatusDescription",
'Closure': "Closure",
'StatusDefinition': "StatusDefinition",
'RepeatMonthonMonth': "RepeatMonthonMonth",
'PositiveClosure': "PositiveClosure",
'RepeatEver': "RepeatEver",
'UserCode': "UserCode"
)
)
returning varchar(32500) -- <-- This should be however your required JSON data "lump" is defined
)
from Dictionary_
into :json
where nullif(:FIELD, '') is null or :FIELD = "StatusCode" -- <-- This selects all of :FILE is empty
...this will return you:
{
"FILE1": [
{
"ISODate": "...",
"AdverseClosure": "...",
"D/MorBoth": "..",
"StatusCode": "...",
"StatusDescription": "...",
"Closure": "...",
"StatusDefinition": "...",
"RepeatMonthonMonth": "...",
"PositiveClosure": "...",
"RepeatEver": "...",
"UserCode": "..."
},
{
"ISODate": "...",
"AdverseClosure": "...",
"D/MorBoth": "..",
"StatusCode": "...",
"StatusDescription": "...",
"Closure": "...",
"StatusDefinition": "...",
"RepeatMonthonMonth": "...",
"PositiveClosure": "...",
"RepeatEver": "...",
"UserCode": "..."
},
etc.
]
}
Let me know it if's still helpful and I'll try to rewrite your code using the above SQL when I have time.
Tim.
Later that day 2020-01-24 14H50
Later that day 2020-01-24 14H50
Hi Esteban,
So you mean your RPG program will only support the GET method? In that
case you can rip out almost all of your program code and replace it with
that one SQL statement I sent, I will try to find the time to send you
an example. It wasn't clear to me from the
program what output you were expecting, i.e. where the result of the
JSON conversion ended up, I couldn't find the definition for some of the
types either.
I have been working on a code generator for generating the entire CRUD
code required for an SQL stored procedure that implements a web-service,
given a file name. At the moment it works for MariaDB only but the
concept is quite similar to the program you have,
except it's all SQL. My plan is to one day open source it and make it
work for IBM i too, but at the moment I use it to generate the code for a
private project. Here's an example of the generated output for a table
called "mm_child", as I mentioned, it's in
MariaDB but the concept is the same for DB2 and it demonstrates that
JSON can easily be parsed and constructed in pure SQL.
Tim.
-- *****************************************************************************************************
-- Procedure to represent a entity resource, based loosely on the JSON:API specification here
-- https://jsonapi.org/
-- The following HTTP methods are supported:
-- HEAD Returns a response with no response body (not likely to be useful - may remove)
-- OPTIONS Returns authority and permission information (to be implemented)
-- GET Returns the specified entity, e.g. .../mm_child_entity/1234
-- DELETE Deletes the specified entity, e.g. .../mm_child_entity/1234
-- POST Creates a new entity as specifed in the request body
-- PATCH Updates all or part of the entity specified in the request body
-- *****************************************************************************************************
create or replace procedure `mm_child_entity`(
in REQUEST json, -- Request headers etc. (JSON)
in REQUEST_BODY mediumblob, -- Request body
out RESPONSE json, -- Returned response headers etc. (JSON)
out RESPONSE_BODY mediumblob -- Returned response body
)
language sql
not deterministic
sql security definer
`proc`:
begin
declare HEADERS json;
declare CONTENT_TYPE varchar(256); -- Media type of request/response
declare ACCEPT varchar(256); -- "Accept" header
declare METHOD varchar(10); -- Request method (POST|GET|HEAD...)
declare clientUser varchar(64);
declare eTag char(32);
declare ETAG_ char(32); -- Request ETag
declare ID_ integer; -- Requested resource id
declare exit handler for SQLEXCEPTION
begin
declare ERROR_CODE varchar(5);
declare MESSAGE_TEXT varchar(512);
get diagnostics condition 1 ERROR_CODE = RETURNED_SQLSTATE, MESSAGE_TEXT = MESSAGE_TEXT;
set RESPONSE_BODY = json_set(RESPONSE_BODY, '$', json_object('errors', json_array()));
set RESPONSE_BODY = json_array_append(RESPONSE_BODY, '$.errors', json_object(
'status', ERROR_CODE,
'source', '',
'title', MESSAGE_TEXT,
'detail', ''
));
set RESPONSE = json_set(
RESPONSE, '$.status',
case
when right(ERROR_CODE, 3) between '200' and '500' then right(ERROR_CODE, 3)
else '500'
end
);
end;
-- Initialise response header and body.
set HEADERS = json_object(
'Content-Type', 'application/vnd.api+json',
'Content-Language', 'en'
);
set RESPONSE = json_object('headers', json_extract(HEADERS, '$'), 'status', '200');
set RESPONSE_BODY = json_object();
set clientUser = mm_check_auth(REQUEST);
set CONTENT_TYPE = json_unquote(json_extract(REQUEST, '$.headers.content-type'));
set ACCEPT = json_unquote(json_extract(REQUEST, '$.headers.accept'));
set METHOD = json_unquote(json_extract(REQUEST, '$.method'));
set ID_ = json_unquote(json_extract(REQUEST, '$.params.id'));
set ETAG_ = coalesce(
json_unquote(json_extract(REQUEST, "$.headers.etag")),
json_unquote(json_extract(nullif(REQUEST_BODY, ''), "$.data.meta.etag")),
''
);
-- Ensure that the format of the request body is JSON.
if CONTENT_TYPE <> 'application/json' then
signal sqlstate '38415' set mysql_errno=30001, message_text='Media type application/json required.';
end if;
-- Ensure that client can accept a JSON response.
if regexp_instr(ACCEPT, 'application\/(vnd.api+)?json|\\*\\/\\*') = 0 then
signal sqlstate '38406' set mysql_errno=30001, message_text='Must accept application/json.';
end if;
case METHOD
-- *****************************************************************************************************
-- HEAD, return with no response body - probably serves no purpose at the moment.
-- *****************************************************************************************************
when 'HEAD' then leave `proc`;
-- *****************************************************************************************************
-- OPTIONS, used to request authority and permission information.
-- *****************************************************************************************************
when 'OPTIONS' then
begin
-- This response would be obtained by checking the user's permissions.
set RESPONSE_BODY = json_object('GET', 'true', 'POST', 'true', 'DELETE', 'false');
leave `proc`; -- Skip 404 checking at end of proc
end;
-- *****************************************************************************************************
-- GET.
-- *****************************************************************************************************
when 'GET' then
begin
declare PAGE_INDEX integer;
declare PAGE_SIZE integer;
declare OFFSET integer;
declare RESPONSE_DATA json;
set PAGE_SIZE = coalesce(cast(json_unquote(json_extract(REQUEST, '$.query.pageSize')) as integer), 15);
set PAGE_INDEX = cast(coalesce(json_unquote(json_extract(REQUEST, '$.query.page')), 0) as integer);
set OFFSET = PAGE_INDEX * PAGE_SIZE;
with DATA_SUBSET as (
select *
from mm_child
where ID_ is null or CHL_ID = ID_
),
RESULT as (
select group_concat(
json_object(
"type", "mm_child_entity",
"id", CHL_ID,
"meta", json_object("etag", md5(char(CHL_CHANGED_ON))),
"attributes",
json_object(
"CHL_ID", CHL_ID,
"CHL_GIVEN_NAME", CHL_GIVEN_NAME,
"CHL_SURNAME", CHL_SURNAME,
"CHL_DOB", CHL_DOB,
"CHL_GENDER", CHL_GENDER,
"CHL_NOTES", CHL_NOTES,
"CUS_ID", CUS_ID,
"CHL_CREATED_BY", CHL_CREATED_BY,
"CHL_CREATED_ON", CHL_CREATED_ON,
"CHL_CHANGED_BY", CHL_CHANGED_BY,
"CHL_CHANGED_ON", CHL_CHANGED_ON,
"CHL_STATUS", CHL_STATUS,
".", ""
)
)
order by 1 limit PAGE_INDEX, PAGE_SIZE -- [TODO] Change ordering here as required
) as ENTITY_JSON,
(select count(*) from DATA_SUBSET) as ELEMENTS
from DATA_SUBSET
)
select case
when ELEMENTS = 1 then
json_object(
'data', json_compact(ENTITY_JSON)
)
when ELEMENTS > 1 then
json_object(
'meta',
json_object(
'totalElements', ELEMENTS,
'pageIndex', PAGE_INDEX,
'pageSize', PAGE_SIZE
),
'data', json_compact(concat('[', ENTITY_JSON, ']'))
)
end
into RESPONSE_DATA
from RESULT;
if RESPONSE_DATA is null then
signal sqlstate '38404' set mysql_errno=30001, message_text='Entity not found.';
end if;
set RESPONSE_BODY = json_set(RESPONSE_BODY, '$', json_compact(RESPONSE_DATA));
end;
-- *****************************************************************************************************
-- DELETE.
-- Consider if deleting a current entity would do a soft-delete and
-- deleting a soft-deleted entity would do a hard delete, and how to handle "Are you sure?".
-- *****************************************************************************************************
when 'DELETE' then
begin
-- Read and lock row to be updated in order to check the e-tag (optimistic lock id).
select md5(char(CHL_CHANGED_ON)) into eTag
from mm_child
where CHL_ID = ID_
for update;
if ETAG_ <> eTag then
signal sqlstate '38409' set mysql_errno=30001, message_text='Data changed by another user (e-tag mismatch)';
end if;
delete from mm_child where CHL_ID = ID_;
end;
-- set RESPONSE = json_set(RESPONSE, '$.status', '204');
-- *****************************************************************************************************
-- POST, used to create a new entity (in this case, a entity).
-- *****************************************************************************************************
when 'POST' then
begin
declare ATTRIBUTES json;
set ATTRIBUTES = json_unquote(json_extract(REQUEST_BODY, "$.data.attributes"));
insert into mm_child (
CHL_GIVEN_NAME,
CHL_SURNAME,
CHL_DOB,
CHL_GENDER,
CHL_NOTES,
CUS_ID,
CHL_STATUS,
CHL_CREATED_BY
)
values (
coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_GIVEN_NAME")), CHL_GIVEN_NAME),
coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_SURNAME")), CHL_SURNAME),
coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_DOB")), CHL_DOB),
coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_GENDER")), CHL_GENDER),
coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_NOTES")), CHL_NOTES),
coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CUS_ID")), CUS_ID),
coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_STATUS")), CHL_STATUS),
clientUser
);
set RESPONSE_BODY = json_object("CHL_ID", (select last_insert_id()));
set RESPONSE = json_object('headers', json_extract(HEADERS, '$'), 'status', '201');
end;
-- *****************************************************************************************************
-- PUT, used to "upsert" an entity.
-- *****************************************************************************************************
when 'PUT' then
signal sqlstate '38405' set mysql_errno=30001, message_text='Method not yet supported...';
-- *****************************************************************************************************
-- PATCH, used to partially update an existing entity.
-- *****************************************************************************************************
when 'PATCH' then
begin
declare ATTRIBUTES json;
-- Read and lock row to be updated in order to check the e-tag (optimistic lock id).
select md5(char(CHL_CHANGED_ON)) into eTag
from mm_child
where CHL_ID = ID_
for update;
if ETAG_ <> eTag then
signal sqlstate '38409' set mysql_errno=30001, message_text='Data changed by another user (e-tag mismatch)';
end if;
set ATTRIBUTES = json_unquote(json_extract(REQUEST_BODY, "$.data.attributes"));
-- Update the entity, patching in any values provided in the request body.
update mm_child set
CHL_GIVEN_NAME = coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_GIVEN_NAME")), CHL_GIVEN_NAME),
CHL_SURNAME = coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_SURNAME")), CHL_SURNAME),
CHL_DOB = coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_DOB")), CHL_DOB),
CHL_GENDER = coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_GENDER")), CHL_GENDER),
CHL_NOTES = coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_NOTES")), CHL_NOTES),
CUS_ID = coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CUS_ID")), CUS_ID),
CHL_STATUS = coalesce(json_unquote(json_extract(ATTRIBUTES, "$.CHL_STATUS")), CHL_STATUS),
CHL_CHANGED_BY = clientUser
where CHL_ID = ID_;
end;
-- *****************************************************************************************************
-- Unsupported method.
-- *****************************************************************************************************
else
signal sqlstate '38405' set mysql_errno=30001, message_text='Method not allowed.';
end case;
-- Methods that drop through to here will have been expected to have affected a entity
-- so if nothing was changed then assume the entity wasn't found and throw a 404.
if ROW_COUNT() = 0 then
signal sqlstate '38404' set mysql_errno=30001, message_text='Entity not found.';
end if;
end
The inputs to this SP are the request header information
as a JSON object (including any URL parameters) and the request body
itself, in JSON in this case. This SQL is called from an HTTP server of
my own, which does little more than extract the
header Information from the request into JSON and pass the the body
through to the procedure. The URL to do a get in this example would be
"GET /api/mm_entity?page=1" and the response would look like this:
{
"meta": {
"totalElements": 52,
"pageIndex": "1",
"pageSize": "1"
},
"data": [
{
"type": "mm_child_entity",
"id": 10,
"meta": {
"etag": "02154bbf11b8f66df4ff45f4dd4e79a6"
},
"attributes": {
"CHL_ID": 10,
"CHL_GIVEN_NAME": "Max",
"CHL_SURNAME": "Williamson",
"CHL_DOB": "2018-06-14",
"CHL_GENDER": "M",
"CHL_NOTES": null,
"CUS_ID": 220,
"CHL_CREATED_BY": "tim@mollymoocow.com",
"CHL_CREATED_ON": "2018-11-23 12:47:19",
"CHL_CHANGED_BY": "",
"CHL_CHANGED_ON": "2018-11-23 12:47:19",
"CHL_STATUS": "1",
".": ""
}
}
]
}
No comments:
Post a Comment