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 company’s 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 chief’s 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. Jay’s directive tells James to remove as much of the paper trail as possible and implement an electronic solution. James has one big hurdle, though—all the product information is stored and updated on AS/400s, which don’t have easy workflow tools that the manufacturing plant employees can utilize.
Fortunately, James has just the tool—Lotus 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 user’s 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.
•“ODBC”—Identifies 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. • • • “Fail”—If a null is encountered, no data will be returned and an error message is generated. “Discard”—Is the same as coding no value at all, which creates the effect of “Replacement value”—All nulls are substituted with the replacement value and returned in the data set. •“Distinct”—Removes 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 don’t 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 person’s phone number. Similarly, we can use an @DbCommand to perform an elementary SQL query to retrieve the phone number. Let’s 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. Let’s take a line-by-line look at James’ button code, shown in Figure 5. Line 1—Using 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 2—Using 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 3—Retrieves 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 4—Prompts the user to select the plant’s location. Line 5—The 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 6—The @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 7—If 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 I’ll 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. You’ve seen AS/400 data retrieved from a Lotus Notes document using @functions, but @functions are limited in that they can only retrieve data—they cannot add, modify, or delete data on the AS/400 back-end. However, using three of LotusScript’s 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 Worker’s 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 “Cache”—Specifies 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)
Book Reviews
Resource Center
Node.js on IBM i Webinar Series Pt. 2: Setting Up Your Development Tools
Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:Profound Logic Solution Guide
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.Power10 Power Hour
IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:Comply in 5! Well, actually UNDER 5 minutes!!
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.Combating Ransomware: Building a Strategy to Prevent and Detect Attacks
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.Top IBM I Security Vulnerabilities and How to Address Them
IT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.What Most IBM i Shops Get Wrong About the IFS
Can you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:Backup and Recovery on IBM i: Your Strategy for the Unexpected
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:Manage IBM i Messages by Exception with Robot
Managing messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:Easiest Way to Save Money? Stop Printing IBM i Reports
The thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:Hassle-Free IBM i Operations around the Clock
For over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:Why Migrate When You Can Modernize?
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.The Power of Coding in a Low-Code Solution
When it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:Low Code: A Digital Transformation of Supply Chain and Logistics
Supply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:Node Webinar Series Pt. 1: The World of Node.js on IBM i
Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.IBM i Transformation Risks Every Business Leader Should Know
Join us for this hour-long webcast that will explore:What to Do When Your AS/400 Talent Retires
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online