Writing End-User Applications to Access Shared Data

SCL Programming Considerations

You can use SAS Component Language (SCL) with SAS/SHARE software to access data through a SAS/SHARE server. SCL has the ability to read and update SAS tables that are used concurrently by other clients or SCL applications. For complete information about SCL, see SAS Component Language: Reference.

A concurrent SCL application opens one SAS data table for update while other SAS operations (possibly in different SAS sessions) have the same data table open for update. You can open other data tables for update by using other invocations of the first SCL application, using a different SAS application or SCL application, or using the FSEDIT or FSVIEW procedure on the table.

Consider the following issues when writing an SCL application that updates data concurrently:

locking rows in SAS tables
implications of locking rows in SCL
programming in PROC FSEDIT and PROC FSBROWSE
locating and fetching control rows
unlocking a row

For an application that uses SAS tables that contain inventory and ordering information for each product in a store, see SAS Component Language (SCL) Application. The purpose of the application is to automate a system that develops orders and maintains the inventory list while sales representatives simultaneously write orders for products.

Locking Rows in SAS Tables

A row in a SAS table is locked implicitly when it is read by a SAS procedure, a DATA step, or an SCL application. A lock on a row is held until a different row is read or until the SCL application calls the UNLOCK function.

When a SAS table is opened for update, only one row can be locked at a time. However, a SAS table can be opened for update more than one time in the same SAS session or in different SAS sessions (through a server), and a different row can be locked by each user who opens the table. For example, if two users are running an SCL application that calls the OPEN function to open a SAS table for update, row 7 can be locked by one user and row 10 can be locked by the other user.

Locking Rows in SCL

Row locking can give a programmer an important advantage, and should be a key consideration in concurrent SCL programming. While an SCL application has a row locked, no other SAS operation (especially in another SAS session) can alter or delete that row. After a lock on a row is released, your application cannot know that the values in that row remain the same; another user might have already modified the values. Any data modifications that you make that are based on the old values might damage the data integrity of the system.

Therefore, you must never assume that the data values in a specific row will not change in a shared table, even though only a very brief amount of time has elapsed between consecutive reads and locks of the row.

When each row in a SAS table can represent a specific instance of a resource that the application must govern, row locking provides a resource-specific, protected period of time in which the application can safely test and modify the state of the resource.

An example of a specific-resource instance is information about one of your customers or the number of items of a specific type that is currently in inventory. The SCL example in SAS Component Language (SCL) Application applies locks to its inventory table to maintain the correct inventory count for each item, even if several sales representatives are simultaneously writing orders for those items.

Programming with PROC FSEDIT and PROC FSBROWSE

Unlike other SCL environments (such as SAS/AF software and the FSVIEW procedure), PROC FSEDIT and PROC FSBROWSE give the SCL programmer multiple labeled sections for structuring an SCL application. The sequence in which these procedures run some of the sections has several implications for concurrent SCL programming.

The INIT section is especially useful in applications that read and update shared data. The initial values of the columns in a row (as currently stored in the SAS table) can be preserved in SCL columns. Preserving initial values in SCL columns is important for applications that update auxiliary tables that are based on the PROC FSEDIT user's modification or on the creation of a row in the primary table. SCL applications that read and update data usually need to perform the following tasks:

determine whether a modified window column actually contains a value that is different from its initial, validated value
explicitly restore the initial values if the user's modifications to a row in the primary table are not allowed because they could not be validated
modify the auxiliary tables because changes (from their initial values) were made to the values in a row in the primary table

Although the MAIN or TERM sections must validate the user's modifications to a row in the primary table and update auxiliary tables, it is usually best that no row of an auxiliary table remain locked between executions of these sections. Such locks prevent other users or applications from modifying the row while a user is working in the current row in the primary table.

Programming with the Data Table and Data Form Classes

The Data Table and Data Form classes in SAS/AF FRAME entries allow you to specify an SCL entry to use for the model SCL. This SCL entry is separate from the frame's SCL entry. Usually, model SCL is used to initialize computed columns and to perform error checking and data validation.

As in PROC FSEDIT, the Data Table and the Data Form objects give the SCL programmer multiple labeled sections for structuring the order in which events will occur for each row in the table. These sections, which include INIT, MAIN, and TERM, work in the same way as explained in Programming with PROC FSEDIT and PROC FSBROWSE.

If multiple instances of the Data Table or the Data Form objects are displayed within a single SAS/AF FRAME entry, the objects share data, then the model SCL for each data table or data form runs separately. The application developer must remember whether a previous object has a lock on a row that the current object attempts to read or update. In addition, the frame SCL might also be working on the shared data, and timing within the frame could be critical. For more information about when SCL labels are run, see SAS Component Language: Reference.

Locating and Fetching Control Rows

SCL provides a set of functions that are useful for locating and fetching the required auxiliary table rows (observations) in a data-concurrent SCL application. However, you should use caution with these functions in applications that access shared data. The return code, which is obtained directly from the called function or from the SYSRC function, must be checked to ensure that a lock was obtained on the row or that an update was successful. The return value, which is generated by the macro invocation %SYSRC(_SWNOUPD), is generated when a fetch or update function fails to lock or update the row because it is locked by another application.

The FETCHOBS table function is useful when the row number can serve as the row identifier. Remember that the FETCHOBS function accepts a relative row number by default. That number might or might not equate to the physical row number. If you can delete rows in the auxiliary table, you probably want to use the ABS option in the FETCHOBS function for absolute row numbering.

The LOCATEC and LOCATEN table functions can be useful for finding rows in small tables when the data can remain sorted by a unique identifier (column) and a binary search is specified. However, due to the overhead of searching with these SCL functions, it is better to use the WHERE and FETCH functions to find the rows. In a shared-data environment, when you use the LOCATEC and LOCATEN functions to find rows, each row must be requested from the server and transmitted to the client's SAS session.

The SYSRC function must be queried for warnings when the LOCATEC and LOCATEN functions find a row because these functions only return a return code of 0 for either condition: row found or row not found. For more information about the LOCATEC and LOCATEN functions, see SAS Component Language: Reference. The following SCL program example checks whether the located row is locked by another task:

gotrec=locatec(data-set-id,var-num,search-string,
                sort-order);
if (gotrec<=0) then do;
   /* Handle row not found */
 end;
else if (sysrc()=%sysrc(_swnoupd)) then do;
   /* Handle row locked */
 end;

Note: The LOCATEC and LOCATEN functions cannot perform binary searches on compressed tables, SAS data views, or SAS data files that have deleted rows. [cautionend]

The more general and, usually, more efficient way to find a row is to use the WHERE function followed by a FETCH function call. The WHERE clause is evaluated in the server's SAS session, and only the row that needs the specifications in the WHERE clause is transmitted to the client's SAS session.

If the WHERE clause does not find the specified row, the FETCH function returns a -1 return code, which indicates that the end of the table has been reached. If the WHERE clause is cleared by issuing a null WHERE function call, the next FETCH call that the application issues fetches the first row in the table. The FETCH call, not the WHERE clause, locks the row (if possible). Notice that the WHERE function returns a harmless warning, %SYSRC(_SWWREP), when the WHERE clause is replaced.

The DATALISTC and DATALISTN selection-list functions help a client to select a valid row. These functions actually fetch the entire selected row into the Table Data Vector (DDV) and lock the row (if possible). Because these functions do not return a system return code, the SYSRC function must be queried for warnings. The DATALISTC and DATALISTN functions might cause the entire SAS table to be read, which means that each row that is read is transferred individually from the server to the client SAS session.

Unlocking Rows

In addition to releasing a lock on the current row by reading another row, an SCL application can use the SCL function UNLOCK. The UNLOCK function leaves the read-pointer at its current position in the table and does not update the DDV.

The OBSINFO function in SCL returns information about the primary table's current row in an FSEDIT application. You can query whether the row has been deleted, locked, or newly created. A row does not attain deleted status until the DELETE command is run on the client. For example, if you specified the CONTROL ENTER statement to force your MAIN section to run, the OBSINFO function will not return a deleted status when issued from the MAIN section (because the DELETE command that was executed on the client has caused MAIN to be run.) However, the OBSINFO function will return a deleted status when the MAIN statement or TERM section is run again.

Top of Page