Welcome to the new home of PowerWire – PowerWire.uk

Update your bookmarks now!

Informing the IBM Community

noxDB – Easily Use JSON in RPG

4.9
(11)

Introduction

In November 2024 I was invited to the Benelux Common conference in the Netherlands to give a couple of workshops, and what a great event it was. Take a look at the photos /user-groups-photos

I had the opportunity to meet up with a good friend of mine Niels Liisberg from Denmark, where we had time to look at a couple of projects his company have been working on.

In this article, I will show a great open-source offering that his company, System & Method A/S, has provided to the community. This project is called noxDB.

This article is part of a series that explores a cohesion framework designed to modernise IBM i applications, enabling them to seamlessly integrate with microservices architectures and CI/CD pipelines.

The RPG code I created to explain these features is in full at the end of this post.

Installation

Firstly, we have a couple of pre-requisites to install prior to installation. These are git and make-gnu.

Install these products on to your IBM i using Open-Source Package Management, in ACS, or from a SSH shell session using Yum.

If you already have them installed, it’s not a bad idea to run this command to ensure that all is reliable.

This can be seen in the figure below.

Both are installed on our server, we are ready to go.

In our SSH terminal session, we firstly clone the noxDB git repository.

A screenshot of a computer

Description automatically generated
  1. Clone the GitHub repository

  2. Change into the noxDB directory

  3. Run the gmake command to perform the installation

  4. All done

How easy was that! A great job with that, no messing with FTP and save files etc.

Let’s give that a big thumbs up.

ISVs please note – use this method!

Using noxDB

Now we are all installed, how do we use it?

The first example I am going to show is reading a simple JSON file that resides in our IFS.

JSON

{
  "employeeId": 1,
  "firstName": "John",
  "lastName": "Doe",
  "position": "Software Engineer",
  "department": "Development",
  "email": "john.doe@example.com"
}
JSON

This single record JSON includes the employee’s unique ID, first name, last name, job title, department and email address.

To read this file, we will be using noxDB and RPG.

I haven’t used RPG for many years, open-source languages have got in the way, but yes, you never forget your first love!

To use noxDB, our RPG must have two things defined. They are

  1. Having the binding directory noxDB defined

  2. Include the noxDB prototypes from QRPGLEREF member noxDB

These can be seen in the code example below.

JSON
ctl-opt BndDir('NOXDB');

/include noxdb/qrpgleref,noxdb
JSON

Once we have these definitions, we use the json_ParseFile procedure to show noxDB where the JSON file resides. This procedure returns a pointer we can use.

Then we use the json_getStr procedure, passing in two parameters, they are the pointer from the step above and the JSON key name.

  1. Set the pointer up to the file on our IFS

  2. Pick out the key-pair values

  3. Join all the variables together

  4. Display the test results

And running our program, we see the results.

How easy was that? Very impressed.

Let us give it a bit more work to do. This time our JSON will contain multiple employees. How do we cater for that?

Let’s say I have been given a JSON file by a client and we need to install it into our HR database.

An example of the structure of the JSON file is shown in the figure below.

This JSON file contains information about six employees. Each record is a JSON object with the following key-value pairs:

  1. employeeId: A unique identifier for each employee

  2. firstName: The employee’s first name

  3. lastName: The employee’s last name

  4. position: The job title of the employee

  5. department: The department where the employee works

  6. email: The employee’s email address

Here’s a summary of each employee:

  1. John Doe – Software Engineer in the Development department.

  2. Jane Smith – Project Manager in the Management department.

  3. Emily Johnson – UI/UX Designer in the Design department.

  4. Michael Brown – Data Analyst in the Analytics department.

  5. Sophia Davis – HR Specialist in the Human Resources department.

  6. James Wilson – Marketing Coordinator in the Marketing department.

This file resides on the IFS in my PowerWire directory.

We must step through JSON objects to get each individual employee.

To achieve that we use the JSON_forEach procedure in a do while loop.

Within this loop, we then use the .this variable to pick out our individual values, just as we did in the first example.

Calling this program, we now get the following results.

And there we go, two examples to show how to extract data from JSON in our RPG.

And onto the last example. This time we will be creating/writing a JSON file to our IFS.

The screen shot below shows what we need to do for this example.

  1. Setup some test data to work with

  2. Set the pointer to the file we will be outputting to

  3. Use the data-gen op code to format the data using noxDB json_DataGen procedure (Editors comment – clever!)

  4. Write the formatted data out to the IFS

  5. Don’t forget to tidy up – remember what you mum said – TIDY UP!

That really was simple, hats off to the guys for providing this as open-source.

Running our program now creates a JSON file in our PowerWire IFS directory as seen below.

If we look at the file, we will see the keys and values in the file.

Conclusion

That wraps up a very quick introduction to noxDB. It is very powerful, and we have only touched a bare minimum of its functionality. Maybe, someone else can write an article on how they are using it.

I must thank, once again, Niels Liisberg and his company for providing this project to the open-source community.

Their GitHub repository can be found at https://github.com/sitemule/noxDB and the documentation can be found at https://sitemule.github.io/noxdb/about

I cannot wait to write about the next project they have provided. Stay tuned folks!

Code Examples

Example 1

SQL

**free                                                                                              
                                                                                                    
// PowerWire Example                                                                                    
// NoxDB Example 1                                                                                  
//                                                                                                  
// © FormaServe Systems Ltd 2024
//                                                                                                  
                                                                                                    
Ctl-Opt BndDir('NOXDB') ;                                                                           
/include qrpgleRef,noxdb                                                                            
                                                                                                    
dcl-s empFile varchar(500)  ;                                                                       
dcl-s empHandler Pointer;                                                                           
                                                                                                    
Dcl-S salary   Packed(11:2);                                                                        
Dcl-S firstName VarChar(50);                                                                        
Dcl-S lastName VarChar(50);                                                                         
Dcl-S department VarChar(50);                                                                       
Dcl-S position VarChar(50);                                                                         
Dcl-S email VarChar(50);                                                                            
Dcl-S msg Char(52) ;                                                                                
                                                                                                    
dcl-ds dsEmployee qualified ;                                                                       
  surname CHAR(20);                                                                                 
  first_name CHAR(15)  ;                                                                            
  address1 VARCHAR(50) ;                                                                            
  address2 VARCHAR(50) ;                                                                            
  address3 VARCHAR(50) ;                                                                            
  address4 VARCHAR(50) ;                                                                            
end-ds;                                                                                             
                                                                                                    
// use our pointer to load the json                                                                 
empHandler = json_ParseFile('/powerwire/single.json');                                              
                                                                                                    
// pick the bones out of the json file                                                              
firstName = json_getStr(empHandler: 'firstName');                                                   
lastName = json_getStr(empHandler: 'lastName');                                                     
position = json_getStr(empHandler: 'position');                                                     
department = json_getStr(empHandler: 'department');                                                 
email = json_getStr(empHandler: 'email');                                                           
                                                                                                    
msg = firstName + ' ' + lastName + ' ' + position + ' ' + department + ' ' + email ;                
                                                                                                    
// what we got back?                                                                                
dsply msg ;                                                                                         
                                                                                                    
// pick the bones out of each record in the json file                                               
//dow json_ForEach(empHandler);                                                                     
//  value = json_GetStr(empHandler.this);                                                           
//enddo;                                                                                            
                                                                                                    
// all done, dont forget to tidy up!                                                                
json_Close(empHandler)  ;                                                                           
                                                                                                    
return ; // time for omers!                                                                         
                                                                                                    
                                                                                                    
SQL

Example 2

SQL

**free                                                                                              
                                                                                                    
// PowerWire Example                                                                                    
// NoxDB Example 1                                                                                  
//                                                                                                  
// © FormaServe Systems Ltd 2024                                                                    
//                                                                                                  
                                                                                                    
Ctl-Opt BndDir('NOXDB') ;                                                                           
/include qrpgleRef,noxdb                                                                            
                                                                                                    
dcl-s empFile varchar(500)  ;                                                                       
dcl-s empHandler Pointer;                                                                           
                                                                                                    
Dcl-S salary   Packed(11:2);                                                                        
Dcl-S firstName VarChar(50);                                                                        
Dcl-S lastName VarChar(50);                                                                         
Dcl-S department VarChar(50);                                                                       
Dcl-S position VarChar(50);                                                                         
Dcl-S email VarChar(50);                                                                            
Dcl-S msg Char(52) ;                                                                                
Dcl-S  value    Varchar(40);                                                                        
                                                                                                    
Dcl-DS list likeds(JSON_ITERATOR);                                                                  
                                                                                                    
dcl-ds dsEmployee qualified ;                                                                       
  surname CHAR(20);                                                                                 
  first_name CHAR(15)  ;                                                                            
  address1 VARCHAR(50) ;                                                                            
  address2 VARCHAR(50) ;                                                                            
  address3 VARCHAR(50) ;                                                                            
  address4 VARCHAR(50) ;                                                                            
end-ds;                                                                                             
                                                                                                    
// use our pointer to load the json                                                                 
empHandler = json_ParseFile('/powerwire/employee.json');                                            
                                                                                                    
 // Using an iterator                                                                               
list = json_SetIterator(empHandler);                                                                
                                                                                                    
dow json_ForEach(list);                                                                             
  value = json_GetStr(list.this);                                                                   
  firstName = json_getStr(list.this : 'firstName');                                                 
  lastName = json_getStr(list.this : 'lastName');                                                   
  msg = firstName + ' ' + lastName ;                                                                
  dsply msg ;                                                                                       
enddo;                                                                                              
                                                                                                    
// all done, dont forget to tidy up!                                                                
json_Close(empHandler)  ;                                                                           
                                                                                                    
return ; // time for omers!                                                                         
                                                                                          
SQL

Example 3

SQL

**free                                                                                              
                                                                                                    
// PowerWire Example                                                                                    
// NoxDB Example 1                                                                                  
//                                                                                                  
// © FormaServe Systems Ltd 2024
//                                                                                                  
                                                                                                    
Ctl-Opt BndDir('NOXDB') ;                                                                           
/include noxdb/qrpgleRef,noxdb                                                                      
                                                                                                    
dcl-s empFile varchar(500)  ;                                                                       
dcl-s empHandler Pointer;                                                                           
                                                                                                    
Dcl-S salary   Packed(11:2);                                                                        
Dcl-S firstName VarChar(50) inz('Andy');                                                            
Dcl-S lastName VarChar(50) inz('Youens');                                                           
Dcl-S department VarChar(50);                                                                       
Dcl-S position VarChar(50);                                                                         
Dcl-S email VarChar(50);                                                                            
Dcl-S msg Char(52) ;                                                                                
Dcl-S value Varchar(40);                                                                            
Dcl-S fileName Varchar(100) inz('/powerwire/employee_out.json');                                    
Dcl-S handle Char(1) ;                                                                              
                                                                                                    
Dcl-DS list  likeds(JSON_ITERATOR);                                                                 
                                                                                                    
dcl-DS row qualified inz;                                                                           
  firstName varchar(50) ccsid(*JOBRUN);                                                             
  lastName varchar(50) ccsid(*JOBRUN);                                                              
  department varchar(50) ccsid(*JOBRUN);                                                            
end-ds;                                                                                             
                                                                                                    
// bung some stuff into the datastructure                                                           
row.firstName = 'Andy';                                                                             
row.lastName = 'Youens';                                                                            
row.department = 'IT';                                                                              
                                                                                                    
// use our pointer to load the json                                                                 
empHandler = json_ParseFile(fileName);                                                              
                                                                                                    
// the DATA-GEN operation is used to generate a structured document from an variable                
// and uses the generator program of json_DataGen - All magical stuff under the covers!             
data-gen row %data(handle: '') %gen(json_DataGen(empHandler));                                      
                                                                                                    
// now use the write json STMF function to write the formatted data out                             
json_WriteJsonStmf(empHandler : fileName  : 1208 : *OFF);                                           
                                                                                                    
                                                                                                    
// all done, dont forget to tidy up!                                                                
json_Close(empHandler)  ;                                                                           
                                                                                                    
return ; // time for omers!                                                                         
SQL

How useful was this post?

Click on a star to rate it!

Average rating 4.9 / 5. Vote count: 11

No votes so far! Be the first to rate this post.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *