Thursday, July 18, 2019

Sending data to a procedure

Within Priority, there exists a documented method for sending data from one procedure or trigger to another procedure or report. I use this frequently when building procedures which by means of the scheduler will send reports by mail. The procedure uses a local copy of one of the 'stack' tables (normally STACK which has precisely one field - ELEMENT - which is of course the table's primary key). For example,
:GROUP = 'NO_CNC'; EXECUTE WINACTIV '-R', 'TEST_NOPROD_METAL', 'STACK', :$.STK, '-g', :GROUP;
What if one wants to send an order to be printed? In this example, an entire record from PORDERS (purchase orders) is copied into a linked table of purchase orders, which is then sent to the WINHTML program.
INSERT INTO PORDERS SELECT * FROM PORDERS O WHERE ORD = :PORD; :FNAME = STRCAT ('D:/PR_SQL/GLASS/', :PAR2, '.pdf'); EXECUTE WINHTML '-d', 'TECH_WWWSHOWPORDER', 'PORDERS', :TMPORDERS, '-pdf', :FNAME;
Sometimes I want to be 'clever' - whenever I hear someone say that, there exists the possibility of making an error. When I want to expand a part's bill of materials, I use a similar technique of entering data into a linked table of parts, but I can also add specific data into a field, data which does not exist in the 'real' table of parts, e.g.
INSERT INTO PART (PART, PARTNAME, FATQUANT) VALUES (:PART, :PNAME, :QUANT);
where :QUANT is a variable which receives its value during the procedure. The very laboured point which I am trying to make here is that at a minimum, one has to insert into the linked table the unique key of the record, along with the automatic key, if it exists. I forgot this the other day when I was working on the procedure to print a chained list of meetings: at first, I used only the automatic key without success. Only when I added the unique key did the called procedure do its job. Had I used the 'INSERT INTO TEST_PHMEETINGS SELECT * FROM TEST_PHMEETINGS A' technique then I wouldn't have had a problem.

Later that evening whilst showering (which is where I do all my heavy thinking), I realised that I could have used a linked STACK table and pass the automatic key. Yes ... and no. This became clear to me during the training session for the meetings when I wanted to print just the current meeting: there would have been no result had I used the STACK technique, for the screen passes a record of meetings to the procedure. Fortunately I had used the correct technique for the printing procedure so this wasn't a problem.

So again: at a minimum, one has to insert into the linked table the unique key of the record, along with the automatic key, if it exists. It might well be easier simply to insert a complete record.

No comments: