Home page Home page Home page Home page
Pixel Header R1 C1 Pixel
Pixel Header R2 C1 Pixel
Pixel Header R3 C1 Pixel
By Sprezz | Friday 25 November 2011 15:40 | 0 Comments
A developer recently asked on the Works forum if it was possible to use the table browser from within a program. Well the simple answer is "Yes". The slightly more complex answer is that since so many of the OI tools are now written in OI itself it is frequently possible to use these tools from within programs. The first thing we need to know is what the table browser window is called. It may come as a surprise to those more familiar with obscure naming conventions to find out that it is called "TABLE_BROWSER". So to call it programmatically we'd simply

atWindow = Start_Window("TABLE_BROWSER", @Window)

Of course launching it might not be enough - we might also want to populate it with the contents of a table. To do this we need to know the control names. To save you the investigative work we've included a table below :-

TABLE_BROWSER.DATA_TABLE The results edit table
TABLE_BROWSER.NUM_RECORDS The number of records to display
TABLE_BROWSER.TABLENAME The table name edit field
TABLE_BROWSER.TEXT_1 The Tablename static
TABLE_BROWSER.TEXT_2 The Number of Records static

So to launch the browser and load the AVERY_LABELS table we could

 Declare Function set_Property, start_Window        

 atWindow = Start_Window("TABLE_BROWSER", @Window)
 objxArray =  atWindow : ".TABLENAME" 
 propArray = "DEFPROP"
 dataArray = "AVERY_LABELS"     

 dataArray = set_Property( objxArray, propArray, dataArray )     

 Call send_Event(atWindow : ".BTN_LOAD", "CLICK")
By APK | Friday 4 November 2011 12:14 | 0 Comments
Finishing up our series on extended selects and readnexts, we will now turn our attention to managing the cursors.

When working with the extended select and readnext statements, the developer is required to maintain the status of the select and readnext cursor. Additionally, as we hinted in our previous entries on Latent and Resolved Selects and Selects and File Resizing, the extended select and readnext statements allow the developer to work with multiple cursors. Multiple cursors means that it's possible to have up to 9 active select statements working at any particular time.

We'll start with cursor status, since that needs to be maintained no matter how many selects may be active at any given time.

We previously mentioned that the extended select syntax is:

select table by columns using cursor then...

While that's true, it's a little simplistic as there are two other keywords, SETTING and ASSIGNING, that you may use as well. The following table describes each keyword in a little more detail:

UsingAssigns new criteria to the cursor. If a new table is specified, then the existing criteria is lost.
SettingAssigns the criteria to the next available cursor.
AssigningAllows you to assign the results of the cursor to a different table. Any existing results are assigned to the new table.

There are a total of 9 cursors available to the developer (0 through 8) and they can all be active at any given time. By allocating specifically nominated cursors, you can set the system so that cursor 1 is processing your CUSTOMERS table while cursor 2 is processing your INVOICES table. This would be useful if you wanted a report on your invoices sorted in a very specific customer order. As you readnext each customer using cursor 1, you can then issue a new series of selects against cursor 2 finding the invoices for each customer.

Note: While cursor 0 is not reserved exclusively for system use, it is the cursor the system uses. When working with multiple cursors, it's best to only use cursors 1 through 8.

We do realize that you can simply call btree.extract() to return results from the INVOICE table. However, using that you will find that the rows will be returned in key sorted order. If you want any additional sorting then you will need to process the sort, which will normally require an additional file pass. Additionally, since you are managing the cursors, you do not have to worry as much about the system using the cursor from under you, or issuing select statements in subroutines. The SETTING keyword ensures you have a new cursor to manage your selection.

USING keyword
The USING keyword is the most analogous to the basic system SELECT statement. By issuing repeated USING calls to a specific cursor, you can refine the results of your select. Normally, this is mostly used when making a two pass selection. The first pass would resolve based on indexed fields, while the second pass can trim down the results working on a subset of the data. Unlike a standard system select, you cannot take the results of one table and apply the results to a different table. If you do, the original results will be cleared and only the new criteria will be applied to the new table. The assigning keyword allows this functionality.

SETTING keyword
The SETTING keyword finds the next unused cursor and applies the criteria to that cursor. It's the recommended method of starting off a new set of criteria. Which cursor you use doesn't matter, and if you nominate specific cursors, you might accidentally step on a cursor you are using. So, for a two pass report, you would set up the initial select using SETTING, then finish it off with USING.

The ASSIGNING keyword is only valid on an active cursor. It is used when you wish to assign existing criteria from one table to a different table.

MODE keyword
Sometimes you may not know at design time which keyword you require. MODE allows you to specify the criteria using a variable. The variable must contain an integer value which identifies the keywork type.

KeywordMode Value

For example, if you do not know if you are working with an existing cursor, because you are allowing the user to refine the result list, you can allocate "setting" or "using" through a simple check.

* // refineFlag is defined elsewhere and indicates if this
* // is an existing selection being refined
if (refineFlag = TRUE$ ) then
   cursorMode = 1 ; * // using
end else
   cursorMode = 2 ; * // setting

select hTable by columns mode cursorMode else

Finally, once you have the criteria resolved as you wish, you need to issue the READNEXT command. It's important to remember to include the "USING cursorVariable" portion of the READNEXT when working with multiple cursors. Without this section, the system will assume you are using cursor 0.

READNEXT also supports an optional "by" clause. It's used to determine the direction keys are extracted from a cursor:

0ATAscending Terminating.
1ANAscending Non-Terminating.
2DT Descending Terminating.
3DNDescending Non-Terminating.

For example:

select "MYFILE" by "SORT_FIELD" setting cursorVar else
readnext thisKey using cursorVar by DT else

will return the keys in the reverse order returned in the cursor, giving the same results as:

select "MYFILE" by "#SORT_FIELD" setting cursorVar else
readnext thisKey using cursorVar by AT else

Note: When using the literals (AT, AN, DT & DN) you must not enclose them in quotes.

CLEARSELECT allows you to clear a cursor by referencing the cursor number you wish to clear.

clearSelect cursorVar

Additional Commands
A little known feature of the Basic+ language is the ability to use a cursor instead of a file handle in file I/O statements. These can be used in READ, WRITE, DELETE, LOCK, UNLOCK, MATREAD and MATWRITE. The basic syntax is the same for each command, just substitute "CURSOR cursorNumber" for the file handle.
For example

read atRecord from cursor cursorVar, atID else

As you've seen, working with multiple cursors gives a developer much more flexibility in working with the system. The small increase in code length and complexity is a small price to pay for the productivity gains you can achieve. As anecdotal proof of this, I once modified a sales report that used a PERFORM SELECT and a series of BTREE.EXTRACTS which was taking over 6 hours to run into a two cursor selection routine and dropped the execution time to just over an hour.

Labels: , , , ,

Pixel Footer R1 C1 Pixel