Inside VESOFT #04 (Database access)
by
Shawn M. Gordon

One of the cuter things that was put into MPEX some years ago was the database access functions. These allow you to write a full fledged program if you like, VESOFT doesn’t recommend it because of the performance, however if you want to do things like validate information when you are streaming a job.

Below is a list of the various database calls that are supported by MPEX/Streamx/etc..

VEDBCLOSE, database I/O function
VEDBCONTROL, database I/O function
VEDBDELETE, database I/O function
VEDBDELETEK, database I/O function
VEDBFIND, database I/O function
VEDBGET, database I/O function
VEDBGETV, database I/O function
VEDBLOCK, database I/O function
VEDBOPEN, database I/O function
VEDBPUT, database I/O function
VEDBPUTL, database I/O function
VEDBUNLOCK, database I/O function
VEDBUPDATE, database I/O function
VEDBUPDATEK, database I/O function

You should have noticed that there are some intrinsics here that don’t exist in any Image/SQL manual. You might be wondering what VEDBDELETEK, VEDBGETV, VEDBPUTL, and VEDBUPDATEK do. Basically these are implemented for ease of use. In the case of VEDBDELETEK, if you pass a key value then it will do DBLOCK/DBGET/DBELETE/DBUNLOCK for you in a single call. You can take this even further by using the auto-open mode, which allows you to specify all of the database open parameters instead of the database id. In this case the single call will do DBOPEN/DBLOCK/DBGET/DBDELETE/DBUNLOCK/DBCLOSE for you. How’s that for convenient?

The VEDBUPDATEK works the same way as VEDBDELETEK, but for updates. VEDBPUTL, will wrap the DBLOCK..DBULOCK around the DBPUT. VEDBGETV will return a boolean value so that you can use it in a WHILE or IF statement, so you have to specify the return variable inside the funciton, here is the call structure

VEDBGETV (‘varname’, dbid, dset, mode, list[, key]) [BOOL].

The VEDBGET function will return the data buffer into the variable specified, as in:

VEDBGET (dbid, dset, mode, list[, key]) [STR]

There are a number of status array variables that you can also get access to. The VEDBERR and VEEOF are the two most useful, but at least you have some others that are available. You could for example take a key value from a user in a STREAMX job, do a VEDBFIND and then use the VEDBCHAINLEN to return an integer that could be used to build a file of the correct size.

VEDBCHAINLEN, predefined IMAGE variable
VEDBDATALEN, predefined IMAGE variable
VEDBERR, variable set by VEDBxxx functions
VEEOF, variable set by VEDBGET and VEDBGETV
VEDBNEXTREC, predefined IMAGE variable
VEDBPREVREC, predefined IMAGE variable
VEDBRECNUM, predefined IMAGE variable

Here are a couple of real world type examples that show STREAMX and an Expression Program (we’ll talk about those in a later column). If the first example we open our database, and then prompt for a STATE, we keep looping on the state prompt until we get a valid VEDBFIND. There are a couple of interesting things to note here. First off, this is a TPI enabled database, it has Omnidex on it. The STATE-KEY is actually a combination of STATE plus some other fields, it’s not actually an Image key, but an Omnidex key. So we append an “@;” to the key value so that we do a wild card lookup.

The next interesting thing to not is in the IF VEDBFIND statement. You will notice the STRWRITE command, this is so that we pass a specific length value to the key. We want the key value length to match the keys actual length, this is explained in the MPEX manual.

::SETLVAR LOOP,TRUE
::SETLVAR DBID VEDBOPEN('MYDB.PUB,READ,5')                                    
::WHILE LOOP
::   PROMPT STRING STATE = "Enter State ID";&
            CHECK = (LEN(RTRIM(STATE))) > 1;&
            CHECKERROR = "State cannot be blank"
::   SETLVAR STATEKEY = "![RTRIM(UPS(STATE))]" + "@;"
::   IF VEDBFIND(DBID,'STATE-M',1,'STATE-KEY','![STRWRITE(STATEKEY:4)]')
::      SETLVAR LOOP,FALSE
::   ELSE
::      ECHO Invalid State ID - try again
::   ENDIF
::ENDWHILE
::CALC VEDBCLOSE(DBID,'STATE-M',1)

In this next example you will see an expression program doing database I/O. An expression program looks very much like Pascal. In this case we don’t need to specify the STRWRITE on the key value on the VEDBFIND because an expression program allows us to declare variables of a specific length. In this particular program we are checking to see if a record exists in a detail set, if the DBFIND doesn’t find a record, then we DBPUT the record into the dataset.

VAR DB_ID      :STRING[25];
VAR OUTER_JOB  :STRING[26];
VAR END_TIME   :STRING[8];
VAR RD_IN      :STRING[6];
VAR WALL_TIME  :INTEGER;
VAR DB_BUFF    :STRING[256];

try begin
  DB_ID     := VEDBOPEN("TRACE.PUB, WRITE, 1");
  OUTER_JOB := STRWRITE(HPJOBNAME, '.yourgrp.youracct');
  END_TIME  := STRWRITE(CLOCK:'%rh:%0m %x');
  RD_IN     := STRWRITE(TODAY:'%0Y%0M%0D');
  WALL_TIME := HPCONNSECS;

   VEDBFIND(DB_ID, 'RUN-STAT-D;', 'OUTER-JOB;', OUTER_JOB);
   if VEDBERR = 17 then begin
      DB_BUFF := STRWRITE(OUTER_JOB:26, " ":230);
      VEDBPUTL(DB_ID, 'OUTER-JOB-M;', '@;', DB_BUFF);
   end;
   DB_BUFF  := STRWRITE(OUTER_JOB:26, RD_IN:6, END_TIME:8, +
                        WALL_TIME:'I4,ZEROFILL');
   VEDBPUTL(DB_ID, 'RUN-STAT-D;', '@;', DB_BUFF);
end
cleanup
   VEDBCLOSE(DB_ID, "", 1);

This is just a couple of examples of what is available to you with this tool. I personally like it a lot for submission time validation on job streams. Maybe next month I’ll talk more about the expression programs.