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:

Figure 1: Form window prior to clicking "create" button
Figure 1: Form window on Edit Grid prior to clicking CREATE button

 

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:

Blog-2
Figure 2: Generic SQL error message

 

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:

Figure 3: Nitro Query widget definition
Figure 3: Nitro Query widget definition

 

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:

Figure 4: Validation program (RPG)
Figure 4: Validation program template (RPG)

 

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:

Figure 5: RPG code for validation
Figure 5: F and D specs for customized validation program

 

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:

Figure 6: Main source code for customized validation program
Figure 6: Main source logic for customized validation program

 

After compiling the program I can then go back to the widget definition and specify the validation program name:

Figure 7: Linking of validation program to widget
Figure 7: Linking of validation program to widget

 

Now let’s test out adding that duplicate record again:

Figure 8: SQL error message replaced with more user friendly text
Figure 8: SQL error message replaced with more user friendly text

 

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    
CategoryEdit gridNitro App BuilderTip of the MonthValenceValidation Program