One of the most powerful widgets available in Nitro Query is the Edit Grid. Traditionally queries are read-only, but with an Edit Grid you can actually allow users to update records in a single-file data source — right from within the query itself! In some cases you may find you can simply create a query for data entry in lieu of coding an actual app, saving yourself a ton of time and effort. This blog post demonstrates how to use an RPG validation program in conjunction with the Edit Grid widget. A validation program is exactly what it sounds like. When specified, it is called by the query anytime a user attempts to add, update or delete a record. Your validation program is then used to validate that the user has entered everything correctly.
This post assumes you have some basic understanding of how to create apps with Nitro Query in Valence 5. If you’re new to Nitro Query, you may want to take a moment to watch our recent Nitro Query webinar replay first, then come back here.
So let’s get started… To illustrate a typical use for this concept I have created a query on a test system that doesn’t yet have a validation program specified. It is built over the DEMOCMAST customer master demo file included with Valence. Here I am preparing to add a record to the file:
When I click the CREATE button to add the record for a customer number that already exists in DEMOCMAST I get this SQL error message from the database, something a typical user might find confusing:
Let’s now embark on improving this error message for our users. We’ll start by taking a look at where the Edit Grid widget would be defined in Nitro Query:
To see a template for creating a validation program in RPG, click the question mark to the right in the validation program field. This will bring up a window showing the sample code:
So we can begin coding our own validation program by pasting this sample code into our own RPG source member. In my example I will call the program BLOGCVP (blog customer validation program). Here is what I initially changed in my program versus the sample code:
The “inMode” indicates what action the edit grid window is taking (ADD/EDIT/DELETE). The “outError” parameter is what is used to tell the front-end that an error was detected. So with this in mind, we can now scroll down in the source file and insert any validation logic we need for the record being added, updated or deleted, using standard Valence VVIN and VVOUT procedures to communicate with the front end. In my case I just want a simple message to show for the user if they attempt to add a duplicate key to the DEMOCMAST file, which I can do like this:
After compiling the program I can then go back to the widget definition and specify the validation program name:
Now let’s test out adding that duplicate record again:
Now that the validation program is specified and functional I can then go back to the BLOGCVP program and add any additional checks. As soon as I recompile the program those new checks will be active.
Here’s the full RPG source code for this BLOGCVP validation program:
/copy qcpylesrc,vvHspec
** --------------------------------------------------------------
** Copyright (C) 2008-2016 CNX Corporation
** --------------------------------------------------------------
** Object ID: BLOGCVP
** Version: V5.0
** Description: Blog Customer Validation Program
** --------------------------------------------------------------
fdemocmast if e k disk
/copy qcpylesrc,vvDspec
d lSessDs e ds extname(vvsessdata) qualified
d blogcvp pr
d inMode 6a
d outError n
d blogcvp pi
d inMode 6a
d outError n
d SendError pr
d msg 128a const
** --------------------------------------------------------------
** program start
** --------------------------------------------------------------
/free
outError = *off;
// pull in any of the fields needed for validation...
CUSNO = vvIn_num('CUSNO');
// perform validations based on the mode: ADD,EDIT,DELETE
if inMode = 'ADD';
chain CUSNO democmast;
if %found(democmast);
outError = *on;
SendError('Customer already exists');
endif;
elseif inMode = 'EDIT';
elseif inMode = 'DELETE';
lSessDs = vvUtility_getSessVar(VVSESSVAR);
if lSessDs.vvIbmIUser <> 'QSECOFR';
outError = *on;
SendError('Only QSECOFR may delete records');
endif;
endif;
*inlr=*on;
/end-free
** --------------------------------------------------------------
p SendError b
d pi
d msg 128a const
/free
vvOut_toJsonPair('success:false,MSG:' + %trim(msg));
/end-free
p e