Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
How to Insert the Current User into a Row Automatically
has no ratings.
Published: 03 Nov 2011
Revised: 23 Jan 2013 - 1613 days ago
Last viewed on: 24 Jun 2017 (5675 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

How to Insert the Current User into a Row Automatically Published by: Bob Cozzi on 03 Nov 2011 view comments(8)

I can't find this anywhere on the google...

I have a table and want to insert the change date/time. Okay, this is easy, just do this in the field named CHGDATE:

CHGDATE  TIMESTAMP NOT NULL IMPLICITLY HIDDEN  
                       FOR EACH ROW ON UPDATE  
                       AS ROW CHANGE TIMESTAMP,

 But I also want the Current User. However, SQL doesn't let me specify:

 

CHANGEDBY Char(10) NOT NULL DEFAULT SYSTEM_USER

 

Suggestions?

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: How to Insert the Current User into a Row Automatically
Posted: 5 years 7 months 20 days 18 hours 23 minutes ago

I'ver never used it, but looks like it should be

CHANGEDBY Char(10) NOT NULL DEFAULT USER

See CREATE TABLE in SQL Ref. "Specifies the value of the USER special register at the time of INSERT or UPDATE as the default value of the column."

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to Insert the Current User into a Row Automatically
Posted: 5 years 7 months 20 days 16 hours 3 minutes ago
Edited: Fri, 04 Nov, 2011 at 13:56:48 (2059 days ago)

I tried that and it wouldn't create the table--but let me verify that.

 

...Position 1 DEFAULT value or identity attribute not valid.

Yep it doesn't allow DEFAULT USER.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: How to Insert the Current User into a Row Automatically
Posted: 5 years 7 months 20 days 15 hours 19 minutes ago

Checked V5R4, 6.1, and 7.1, and they all have it. (e.g., http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzhctabl.htm) Are you on an older release, and could this have been added along the way?

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to Insert the Current User into a Row Automatically
Posted: 5 years 7 months 20 days 15 hours 13 minutes ago
Edited: Fri, 04 Nov, 2011 at 10:54:37 (2059 days ago)

I have lots of different releases... I checked on the v5.2 system, let me go look on the v7.1 system...

 I get an error on v7.1 also.

 CHGBY Char(10) NOT NULL DEFAULT USER,

>>Column or sequence attribute is not valid.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: How to Insert the Current User into a Row Automatically
Posted: 5 years 7 months 20 days 14 hours 44 minutes ago

Eureka!

When I tried to actually create one, got the same error. The help on this says when your DEFAULT is USER, the column must be CHAR or VARCHAR with a minimum length of 18, which I tried and it worked. I do not see anything in CREATE TABLE about the length 18; it is documented in the Special registers topic in Chapter 2.

You could maybe save a little storage by doing VARCHAR(18) ALLOCATE(10), but query is still going to format for 18 unless you use an expression to limit the width.

Posted by: Ringer
Premium member *
Comment on: How to Insert the Current User into a Row Automatically
Posted: 5 years 7 months 20 days 14 hours 4 minutes ago

Cool...

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to Insert the Current User into a Row Automatically
Posted: 5 years 7 months 20 days 12 hours 33 minutes ago
Edited: Fri, 04 Nov, 2011 at 13:30:12 (2059 days ago)

Well I'm not happy with that--still can't do the On UPDATE thing. Frown

So I went back to using a Trigger and this time included the ALWREPCHG(*YES) keyword and it started working:

ADDPFTRG FILE(MYLIB/GENLEDGER)                      
           TRGTIME(*BEFORE) TRGEVENT(*UPDATE)  
           PGM(MYLIB/TRG01) ALWREPCHG(*YES)

By changing the AFTER image in the trigger, I was able to insert the user profile into the original 10-character field.

Thanks for everyone's help!

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: How to Insert the Current User into a Row Automatically
Posted: 5 years 7 months 20 days 12 hours 10 minutes ago
Edited: Fri, 04 Nov, 2011 at 14:03:34 (2059 days ago)

Here is the source code for the TRIGGER Program in RPG IV.

Just change the file referenced by the DBFILE_T data structure and everything will fall into place. Then go into the last few lines of the code and set the fields in the AfterImage to whatever you want. Have fun! Cool

 

     H  OPTION(*NODEBUGIO:*SRCSTMT)
     H  EXTBININT(*YES)
     H  ALWNULL(*USRCTL)
      /IF DEFINED(*CRTBNDRPG)
     H DFTACTGRP(*NO)
     H ACTGRP(*CALLER)
      /ENDIF

      **************************************************************************
      **  Program: TRIGGER                                                    **
      **  Author:  R. Cozzi, Jr.                                              **
      **  Date:    Original: October 2010                                     **
      **  Purpose: To illustrate how RPG IV can be used as a DB Trigger.      **
      **           This particular example is used as an *UPDATE and *INSERT  **
      **           trigger.                                                   **
      **                                                                      **
      **  TO ADD A TRIGGER and allow updates to the AFTER IMAGE, make sure    **
      **  to include the  ALWREPCHG(*YES) parameter on the ADDPFTRG command.  **
      **                                                                      **
      **   For exampe:   ADDPFTRG FILE(MYLIB/mydatabase)                      **
      **                  TRGTIME(*BEFORE) TRGEVENT(*UPDATE)                  **
      **                  PGM(MYLIB/trigger) ALWREPCHG(*YES)                  **
      **                                                                      **
      **           This sample modifies the AFTER image of the record         **
      **           by inserting the USER PROFILE into the field named CHGBY   **
      **           This could, of course be any field in whatever file this   **
      **           trigger applies.                                           **
      **************************************************************************

     D PSDS           SDS
     D  JobNbr                        6A   Overlay(PSDS:264)
     D  JobName                      10A   Overlay(PSDS:244)
     D  JobUser                      10A   Overlay(PSDS:254)

     D usrprf          S             10A   Inz(*USER)

      *  Set this data structure template's EXTNAME file to the 
      *  file to which this Trigger Program applies.
.....D DBFile_T      E DS                  extName(GENLEDGER) Qualified
      /IF DEFINED(*V7R1M0)
     D                                     TEMPLATE
      /ENDIF

      * Pointer based data structures for the input buffer.
     D pNewData        S               *
     D pOldData        S               *

      * Map data structure for before and after images of the record
      *  in the buffer from the external de!--script--ion for the file.
     D afterImage      DS                  based(pNewData)
     D                                     LikeDS(DBFile_T)

     D beforeImage     DS                  based(pOldData)
     D                                     LikeDS(DBFile_T)

     D FILE_t          DS                  Qualified
     D  name                         10A
     D  library                      10A
     D  member                       10A
     D  lib                          10A   Overlay(library)
     D  mbr                          10A   Overlay(member)
     D  file                         10A   Overlay(name)

        //  First parameter passed to a trigger pgm is
        //  the Trigger Header structure. It contains
        //  an offset to the old/new record images.
        //  Use those offsets to assign a PTR the address
        //  of the old/new records. Then you can read that
        //  data in the trigger program as if it were passed
        //  to the program directly.
     D trigParm_T      DS                  Qualified
     D  file                               LikeDS(file_T)
     D  event                         1A
     D  eventTime                     1A
     D  commitLock                    1A
     D  filler3                       3A
     D  ccsid                        10I 0
     D  relRecNo                     10I 0
     D  filler4                       4A
     D  oldRecOffset                 10I 0
     D  oldRecLen                    10I 0
     D  oldNullOffset                10I 0
     D  oldNullLen                   10I 0
     D  newRecOffset                 10I 0
     D  newRecLen                    10I 0
     D  newNullOffset                10I 0
     D  newNullLen                   10I 0
     D  filler16                     16A

         // These CONSTANTS are sued used for trigger verification
     D triggerEvent    DS                  Qualified
     D   Insert                       1A   Inz('1')
     D   Delete                       1A   Inz('2')
     D   Update                       1A   Inz('3')

     D triggerTime     DS                  Qualified
     D   After                        1A   Inz('1')
     D   Before                       1A   Inz('2')

     D triggerCommit   DS                  Qualified
     D   None                         1A   Inz('0')
     D   Change                       1A   Inz('1')
     D   cursorStability...
     D                                1A   Inz('2')
     D   all                          1A   Inz('3')

     D MYTRIGGER       PR                  extPGM('TRIGGER')
     D  trigData                           LikeDS(trigParm_T)
     D  rtnCode                      10I 0

     D MYTRIGGER       PI
     D  trigData                           LikeDS(trigParm_T)
     D  rtnCode                      10I 0
      /free
           if (trigData.File.name <> 'GENLEDGER');
             return;
           endif;

           // Verify this is an Insert/Update + After Trigger
           if NOT (((trigData.Event = triggerEvent.Insert) or
                    (trigData.Event = triggerEvent.Update)) and
                    (trigData.EventTime = triggerTime.Before));
             return;
           endif;

           // Set the record format DS to the actual Before/After data.
           pOldData = %addr(trigData) + trigData.oldRecOffset;
           pNewData = %addr(trigData) + trigData.newRecOffset;

                ///////////////////////////////////////////////
                //  You must modify the fields used here to  //
                //  those in your own database file!         //
                ///////////////////////////////////////////////
           if (trigData.Event = triggerEvent.Update);
              afterImage.chgby = USRPRF;  // On Update set the Changed By user.
           else;
              afterImage.crtby = USRPRF; // On Insert set the
              afterImage.chgby = USRPRF; // Created and Changed By users.
           endif;
           return;
      /end-free