One of the most powerful features of Lotus Domino is its ability to retrieve data from a variety of sources, including Lotus Notes databases and ODBC-compliant data sources such as Microsoft Access and Lotus Approach. This article will concentrate on the ODBC connection between Notes and the AS/400. I will briefly look at the ODBC configuration, then take a more in-depth look at the Lotus Notes programming required to achieve data retrieval from AS/400 tables. I will also explain both simple column lookups and more intricate SQL queries. Finally, I will show you how to take all this functionality and build a Notes database.
Our Story Begins...
At Zerks Unlimited, James is pondering a directive he has recently received from Jay Griffiths, the companys president. Jay had been getting complaints from personnel at the manufacturing plants about the fact that it takes too long to get the raw materials they have ordered. After a little investigating, James found that a paper trail is slowing the entire process. It goes like this: A paper request is submitted and placed in the plant chiefs inbox. He prices the order from AS/400 tables, approves the request, and places it in the fax outbox. A few times each day, an executive assistant faxes the requests to headquarters. From there, they are given to the Data Entry department, assigned a purchase order number, and passed along to Receiving. This process takes a minimum of four hours to transpire, but it usually takes a day to complete. Jays directive tells James to remove as much of the paper trail as possible and implement an electronic solution. James has one big hurdle, thoughall the product information is stored and updated on AS/400s, which dont have easy workflow tools that the manufacturing plant employees can utilize.
Fortunately, James has just the toolLotus Notes. He has had great success in the past with a help desk application that utilizes many of the Lotus Notes workflow capabilities, such as an approval process and group collaboration. The help desk databases have been running for well over a year, and, because of good planning, the Notes infrastructure still has enough capacity to handle many more applications.
James Proposal
James meets with Jay and proposes a Lotus Notes solution with ties to the AS/400 back-end. James explains that the plant workers, armed with Notes, can fill out a form that not only requests an item, but also exactly prices that item at the same time. (See Figure 1 for the plant workers form.) The pricing information would come from a Notes data request to the AS/400. The plant worker would then press a Submit button that routes the form to his or her team leader. The team leader would approve or deny the request and then route it to Data Entry. Data Entry would receive the Lotus Notes document, press another button to assign a P.O. number and route the document to Shipping and Receiving. Again, the AS/400 would supply the P.O. number. The entire process could take less than two hours, despite the thousands of miles between plant and headquarters. After a small diversion to discuss some AS/400 connectivity issues, we will watch James proposal come to fruition.
AS/400 Connection
A series of articles that appeared in the May/June 1996 issue of Client Access/400 Expert can provide you with information on connecting Windows 95 clients to the AS/400. Once a successful connection is established, the ODBC driver will need to be configured. The ODBC driver setup screen is shown in Figure 2. James uses the Client Access/ 400 ODBC driver to connect to one of his AS/400 machines, named ZERKS01. He will use his AS/400 log-on ID as the user ID and specify PURREQ as the default library. The value you type in Data Source Name is used in referencing this ODBC connection record from Notes, which James will show us later. Finally, James has left all the options set to their default values; you may need to tweak the values for your specific situation. Refer to the Help button for a more in-depth explanation. James will need to set up this ODBC driver on all the users desktops. Of course, each user will have a specific user ID.
Simple Data Retrieval
The most basic Lotus @function for data retrieval is the @DbColumn. As the name implies, the @DbColumn will return a table column. We will first investigate the parts of an @DbColumn statement and then watch James as he applies an @DbColumn to his new Notes database.
ODBCIdentifies that an ODBC data source will be accessed. As seen in Figure 2, the data source is defined in the ODBC setup dialog box. Incidentally, the source name is limited to 32 characters. FailIf a null is encountered, no data will be returned and an error message is generated. DiscardIs the same as coding no value at all, which creates the effect of Replacement valueAll nulls are substituted with the replacement value and returned in the data set. DistinctRemoves all duplicate values on the back-end before the data set is handed over to Lotus Notes. Since Notes is limited to 64K block of returned data, specifying Distinct may allow for a larger data set to be returned. This parameter is optional. Figure 3 shows the properties box for the keyword field titled Product. As you can see in the code that follows, the @DbColumn pulls a list with all unique values, sorted in ascending order from the ITEM column in the PARTLIST table. This Parts ODBC connection was defined in Figure 2. Since we dont expect the part list to change frequently, the Cache option has been specified. This will allow the list to stay in memory for as long as the Product Request database is left open. If the part list changed many times per day, the NoCache option would have been the appropriate choice. It would definitely take longer to retrieve the data, but it is better to retrieve an accurate list slowly than an inaccurate list quickly. The @DbColumn is great for retrieving a column, but what if we need to obtain a specific piece of data? James will utilize both the @DbLookup and @DbCommand to complete the Request form. The @DbLookup is best suited for returning a particular record that matches a cross-referenced key from another column. For instance, we have a two-column table of names and associated phone numbers. With any given name, we can use an @DbLookup to retrieve that persons phone number. Similarly, we can use an @DbCommand to perform an elementary SQL query to retrieve the phone number. Lets examine the two @functions separately. The @DbLookup is identical to the @DbColumn, but with the addition of two parameters: All other parameters have same definitions and rules as @DbColumn. As you can see in the code that follows, @DbCommand has only one divergent parameter: As shown in Figure 3, a plant employee selects a product from the keyword list and then presses the button to the right of the Product Specs field. When the button is pressed, it takes the value from the Product field and does a cross-reference to obtain product specs and price per unit. The shipping and handling cost is computed after the user is prompted for his plant location. Basically, the location is referenced on another AS/400 table and the shipping and handling charge is returned. The completed request form is pictured in Figure 4. Lets take a line-by-line look at James button code, shown in Figure 5. Line 1Using the Parts ODBC connection record and given the Product item from the ITEM column in the PARTLIST table, cross-references the SPECIFICATION column to return value. Only one specification is required to be returned, so no sorting is required. James could have just as easily returned the part number column, or the column that lists the cautions and warnings for the product. Information is cached due to infrequent table changes and the returned value is assigned to the Info field. Line 2Using the Parts ODBC connection record and given the Product item from the ITEM column in the PARTLIST table, cross-references the COST column to return value. Only one cost is expected to be returned, so no sorting is required. Information is cached due to infrequent table changes and the returned value assigned to the Cost field. Line 3Retrieves plant location list from another Notes database. Uses the Notes database replication ID to find the database and then uses the PersonResp view to return the first column. Line 4Prompts the user to select the plants location. Line 5The SQL statement is set using the plant location from Line 4. The shipping and handling cost is returned from the SHIPPING column in the RECEIVING table. This cost data corresponds specifically to the plant location data in the PLANT column. Line 6The @DbCommand uses the SQL statement from Line 5 to retrieve the shipping and handling cost using the Parts ODBC connection record. Since there are frequent changes to shipping and handling costs, a value of NoCache is used. Line 7If an error occurred in @DbCommand from Line 6, the function returns a zero. Otherwise it returns the shipping and handling cost from the query. In either case, the SH field is assigned the results. The variables that begin with the letter t are temporary variables. James uses this naming convention as a way to help other programmers easily identify fields from temporary variables in formulas. In his request form, James programmed with @DbColumns to retrieve entire columns of data from an AS/400 table and then used @DbLookups and @DbCommands to narrow down the data to a specific record. The request form we have seen is just part of James Product Request databases. This request form consists of the first leg of the product requisition workflow process and is activated when the Submit button is pressed. It all starts when Anne, the raw materials master, notices that the Vanadium bin is just below the threshold for ordering more materials. Vanadium is, of course, needed for the manufacturing of Zerks. So, she creates a new request document selecting Vanadium Pellets from the keyword field. Anne then presses the button next to the Product Specs field and when prompted, responds with the plant location of Hancock, MI. The rest of the fields are filled in according to the AS/400 data returned with the Notes ODBC queries. She looks over the document for errors, then presses the Submit button. One day later, she notices that the Vanadium bin has been topped off. Here are a couple of tips that Ill pass along about this application, the first of which applies only if you are using release 3 of Notes. In Lotus Notes R3, you cannot use the ODBC parameter in @DbColumns, @DBLookups, or @DBCommands; but rather you will have to use the DLENS parameter. You will also have to install and configure the Lotus DataLens product to have connectivity to any ODBC-compliant data sources. If problems arise during the ODBC connection set-up, you can use Microsoft Query to help debug the problem. Figure 6 shows one of the possible Lotus Notes error messages. You will have to configure an ODBC connection on the Lotus Domino server to allow Web access to the AS/400 data or allow scheduled agents to run against the AS/400 back-end. Youve seen AS/400 data retrieved from a Lotus Notes document using @functions, but @functions are limited in that they can only retrieve datathey cannot add, modify, or delete data on the AS/400 back-end. However, using three of LotusScripts classes, data can be added, modified, or deleted: ODBCConnections, ODBCQuery, and ODBCResultSet. Another great use for LotusScript is to give Web users the ability to gain access to AS/400 data. In another story, the president of Zerks Unlimited has been surfing the Internet and found that their main competitor, Widgets, Inc., is selling widgets on the Web. How does one easily get information from the AS/400 onto the Web? Lotus Domino to the rescue! Fortunately, James has just finished Zerks migration to Lotus Domino 4.51 and is ready to use the built-in Hyper Text Transfer Protocol (HTTP) server to serve up Notes databases on the Web. More on these projects in forthcoming issues. Figure 1: Plant Workers Request Form Figure 2: AS/400 ODBC Driver Setup Figure 3: Properties Box for Product Keyword Field Figure 4: Completed Request Form Figure 5: Button Code Figure 6: Lotus Notes ODBC Error Message@DbColumn(ODBC :
NoCache; ;;
NoCache or CacheSpecifies whether the column of data will be cached into memory for subsequent lookups. Cache is the default if left blank.
Library path and table name. If the library is the same as the default library defined in the ODBC setup (see Figure 2), then only the table name will need to be coded.
@DbColumn(ODBC:Cache; Parts;;; PARTLIST; ITEM;Distinct:
Ascending)Complex Data Retrieval
@DbLookup(ODBC : NoCache;
; ;
: NullHandle;
@DbCommand(ODBC : NoCache;
Step-By-Step
Putting It All Together
Tips and Techniques Used in This Application
Caveats
1. FIELD Info := @ DbLookup(ODBC : Cache ; Parts ; ; ;
PARTLIST ; SPECIFICATION;ITEM;Product);
2. FIELD Cost := @ DbLookup(ODBC : Cache ; Parts ; ; ; PARTLIST ;COST;ITEM;Product);
3. tLocation :=@ DbColumn(NOTES : Cache; 8625632F:00636F2C; PersonResp; 1);
4. tChoice := @Prompt([OKCANCELLIST];PLANT;Choose your plant location from the following
list:;@Subset(tLocation;1); tLocation);
5. tSQL := SELECT SHIPPING FROM RECEIVING WHERE PLANT = +tChoice+;
6. tSH := @DbCommand(ODBC : NoCache ; Parts ; ; ;
tSQL : Fail);
7. FIELD SH := @If(@ IsError(tSH);0;tSH)
LATEST COMMENTS
MC Press Online