Inside VESOFT covers tips and techniques you can use with VESOFT’s products, especially MPEX. As always, I am totally open to getting input from you users of MPEX, Security/3000 or VEAudit/3000 — it helps me learn, too. Send me your tricks, and we’ll get a “3000 for 2000” cap out to you as our thanks.

One of the cuter things VEsoft put into MPEX some years ago was its database access functions. These allow you to write a full-fledged program in MPEX if you like. VEsoft doesn’t recommend it, because of the performance, but the features are suitable for tasks like validating information when you are streaming a job.

Below is a list of the database calls that are supported by MPEX and Streamx (all are database IO functions):

VEDBCLOSE
VEDBCONTROL
VEDBDELETE
VEDBDELETEK
VEDBFIND
VEDBGET
VEDBGETV
VEDBLOCK
VEDBOPEN
VEDBPUT
VEDBPUTL
VEDBUNLOCK
VEDBUPDATE
VEDBUPDATEK

You should notice 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 function. 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. 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.Here’s the list of variables:

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

There are a couple of real-world-type examples below that show STREAMX and an Expression Program (we’ll talk about those in a later column). If, in 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 of all, 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 to do a wild card lookup.

The next interesting thing to note 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 more detail 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 IO. 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 are just a couple of examples of what MPEX offers you through its database access tools. I personally like them a lot for submission time validation on job streams.