GET - Extracting PICK Data to Pass to DOS or Windows

GET is a powerful facility that follows a similar syntax to the LIST and SORT commands from the standard PICK enquiry language. But, instead of sending the result of the enquiry to the screen or printer, the GET command stores the result in a temporary file, ready to send to any one of the many DOS or Windows products supported by HostAccess. For instance the TCL statement:

GET STAFF NAME AGE SALARY // WITH RETIREMENT = "1991"

could be used to extract the data from the STAFF file that can be subsequently transferred to WordPerfect in mailmerge format and print the letters to all people retiring in 1991. Also:

GET STAFF BREAK-ON DEPT TOTAL SALARY // BY DEPT

could be used to extract the data from the STAFF file that can be subsequently transferred to a LOTUS 123 spreadsheet and plot a piechart of the salary breakdown by department.

The GET command evaluates and processes the enquiry statement and stores the data in a specific port dependent file ready for subsequent transfer to a DOS file.

GET is not involved in passing the data to DOS or to Windows. To pass the data to a DOS or Windows product in the format you require, you only have to run one of the PASS.TO routines provided (see PASS.TO - Passing PICK Data Directly into DOS or Windows Products).

Starting GET

GET may be invoked from the TCL command line and if a select list is active, it will be used. At first sight, the GET syntax may look confusing. However, the command differs only slightly from the standard PICK LIST and SORT commands. The main difference is that GET requires the syntax to be in a specific order with some additional delimiters (the  '//' or  '%% '). At the end of this section, there are some examples that show just how easy it is to use GET. The GET command may be entered at TCL as follows:

GET file {USING dictfile} {output} {suppression} {// sort_selection} {%% listname} {(options}

Where:

file

The name of the PICK Data file.

  

USING dictfile

Optional if separate dictionary required on data file.

  

output

Output list of dictionary definitions in order required. This may also include the BREAK-ON, TOTAL and GRAND-TOTAL modifiers. The following modifiers may also be used if your host system normally supports them from the LIST/SORT commands, AVERAGE, PERCENT etc.

  

suppression

Optional suppression modifiers including ID-SUPP, DET-SUPP, HDR-SUPP, COL-HDR-SUPP.

  

//

The // is used to help GET identify the start of the sort/selection criteria. If not used and there is no active select list GET will prompt for the sort/select the start of the sort/selection criteria. The // can be used on its own to suppress the prompt if no sort/selection criteria is required.

  

sort_selection

If you have specified the // delimiter, the SORT and/or SELECTion criteria should be specified in the normal ACCESS format, e.g. WITH field EQ "100", BY, BY-DSND, etc.

  

%%

The %% is used to help GET find the specified  'listname' in the command. Even though GET will work from the currently active select list, it is useful, for instance when calling GET as a subroutine, to be able to pass a select list name. GET will then do a GET-LIST of  'listname' before processing the GET command.

  

listname

The name of the previously saved list should be specified after the %% delimiter.

  

(options

Everything following the  '(' delimiter is treated as an option. Options can be combined and may be space or comma delimited. They may be:

  
 

B

Suppresses the blank lines between breaks and totals.
 

C

Same as COL-HDR-SUPP.
 

I

Same as ID-SUPP.
 

D

Same as DET-SUPP.
 

H

Same as HDR-SUPP.
 

P

Send the result to the printer instead of the file.
 

S

Suppresses the display of all progress messages.
 

T

Send the result to the terminal screen instead of the file.
 

.

If a full-stop is in the options, this suppresses the dots used as column separators on output reports.
Note: The  'T' and  'P' options are normally used only to check that GET is producing the results you expect, if you are experiencing difficulties.

Using GET

GET is very simple to use, just enter the TCL command as described above and you should have no problems. When the GET routine has finished you are returned to TCL. If any errors occurred, these will be reported.

Some sample TCL commands using GET:

  1. GET SALES BREAK-ON AREA TOTAL COST TOTAL SALES  ID-SUPP DET-SUPP // BY AREA WITH AREA NE "NORTH" %% LIST.A
    Will generate a summary 3 column output file, area, cost and sales using a previously saved list LIST.A

  2. GET SALES AREA DESC PROFIT LY.PROFIT //
    Will generate an output file containing 4 columns, area, desc, profit and last year's profit. Since  '//' is used, you will not be prompted for any sort or selection criteria.

  3. GET SALES AREA DESC PROFIT LY.PROFIT %% LIST.A
    Will generate an output file containing 4 columns, area, desc, profit, last year's profit using a previously saved list called LIST.A.

  4. GET-LIST LIST.A at TCL followed by the above GET command without  '%% LIST.A' will have the same effect.

In each of the above cases, the resulting information is stored in the file File: PIX.OUTPUT.NN.F, where nn is the port number. If the file does not already exist it is created, otherwise it is cleared before use.

This file will normally contain one header record and one or more detail records with sequential keys. These records contain the result of the GET enquiry with an attribute mark delimiting each line of output and a multi-value delimiting each column. Most users will have no interest in the format of the file since they are only interested in passing the result of their enquiry on to their required DOS or Windows product.

After running GET you can run any of the PASS.TO routines to transfer the data to Windows or DOS spreadsheets, wordprocessors, etc. You may run any of the "PASS.TO" routines over and over on the same enquiry without having to re-run GET, because the PIX.OUTPUT.nn.F file remains intact until the next GET.

GET Applications Integration

GET is processed by the subroutine PIX.GET which may be called by any PICK program as follows: 

CALL PIX.GET(OUTPUT,SORT,OPTIONS,LIST.NAME,ERROR)

Where:

OUTPUT

Should start with the file name followed by the output list of dictionaries in the order required. This may also include the
BREAK-ON, TOTAL and GRAND-TOTAL modifiers as well as ID-SUPP, DET-SUPP etc. The following modifiers are also supported if your host system normally supports them from the LIST/SORT commands, AVERAGE, PERCENT, CALC etc.

SORT

This variable should be passed containing the SORT and SELECTION criteria that would normally follow  //.

OPTIONS

Should be the string containing any/all of the options, such as I,D,H,C, etc. documented earlier.

LIST.NAME

If no  '%%' is passed via TCL.INPUT, this variable may be passed as the name of the previously saved list to be used with the enquiry. This variable is optional.

ERROR

Since GET relies on certain HostAccess control files existing, if any of them are not found the user will see an error message on the screen and this variable will be set to TRUE. Your application should take action accordingly, i.e. do not call a "PASS.TO" if ERROR set since the output may be invalid.

Program Example

* Program to automate taking everybody from the STAFF file aged 65
* and over using a GET-LIST name of COMPANY.A.LIST and creating a
* WordPerfect mailmerge file with ID, name, address, age,
* date of birth, and salary information.
* 
OUTPUT = "STAFF NAME ADD1 ADD2 ADD3 AGE DOB SALARY"
*
SELECTION = 'WITH AGE >= "65" BY SURNAME'
*
CALL PIX.GET(OUTPUT,SELECTION,"", "COMPANY.A.LIST",ERROR)
*
CALL PIX.PASS.TO.WORDPERFECT("WP5", "C:\WP\RETIRE.DOC",ERROR)
*
END