Re: FIELDPROC Issues

On 13-Apr-2015 14:56 -0500, RWesh@xxxxxxxxxxx wrote:

I used Listing 3 in the following link to create a FIELDPROC program
QGPL/MOBHOMEPAS which should encrypt a variable char column

<http://www.ibm.com/developerworks/data/library/techarticle/dm-1101encryptenhance/#icomments>
I compiled the RPGLE program and I created a separate database
DBMLIB/UMAAAP00 for testing as follows

A R UMAAAF00 TEXT('-
A TEST ENCRYPTION')
A*
A IPIAAA 20A VARLEN(20)
A KYGAAA 11S 2 COLHDG('SALARY')

I then use STRSQL to alter the table and 'protect' field IPIAAA

ALTER TABLE DBMLIB/UMAAAP00
alter column IPIAAA set FIELDPROC QGPL.MOBHOMEPAS

ALTER COMPLETED FOR TABLE UMAAAP00 IN DBMLIB.

For some reason when I go in to add entries through UPDDTA directly
to the file itself and then do a WRKQRY to query the file and view
them I don't see them as encrypted.
Is this not how it's supposed to work? Is anyone able to assist me
with understanding?
Ultimately, I'd like to utilize this program to protect a particular
column in an existing database that we have, but I am having trouble
getting this 'simple' example to work. My assumption is that the
IPIAAA password field will be 'secure' and I will never be able to
see the contents once encrypted whether via query, UPDDTA, or other
means. Thank you for any help you can provide

The Field Procedure *can* allow the value to be decrypted; that is in fact, the entire point of the feature. If the value was to be one-way encrypted, then just use an INSERT and UPDATE trigger to take the in-the-clear value and encrypt the value for storage.

Anyhow, the sample programs at that ULR will *unconditionally* perform the /decryption/; e.g. for the "Listing 3" example program [asterisks added for emphasis] does the inverse action that was performed during the /encryption/ so the effects can be easily tested for round-trip:

"...
ElseIf FuncCode = 4 ; // decode
...
// in this example, *reverse the characters as decryption*
..."

Thus the sample code could be revised to return a [special] value such as *NOTAVL [like DSPSYSVAL QAUDLVL will do for a user missing the required special authority] to indicate that the decrypted value is "Not Available" to the user requesting the /read/ of the datum.