Names. Dates. Addresses. Titles. Phone numbers. Email addresses. What are all these items? They're just data, and data is not information until you take advantage of it and use it. With all of the different programs and hardware that we use to store data, it can be overwhelming for some people to get the information they want and put it to good use. What you ultimately want is the ability to access that data through other programs.
In the AS/400 market, Lotus Notes R5, one of the newer applications, is a nice way to store, retrieve, and keep track of data. From my experience, I've found that it's a very robust system for collaborative environments, but, like many people in the world, I use an additional piece of hardware and tool suite: the PC and Microsoft Office (specifically Office 97 and higher) respectively. In this article, I am going to help break down the barriers between your PC applications and data in your Notes databases. Specifically, I will show you tools that allow you to interact with Notes data on an AS/400 and how to bring that data down to the PC level. What? The AS/400 and the PC sharing data? I know that this can be a foreign concept to some, but trust me. The results are worth it.
Lotus makes an ODBC driver for Notes/Domino databases called NotesSQL. Although Domino databases are not relational databases, such as DB2 for OS/400 (DB2/400), the NotesSQL driver makes Notes data look relational to ODBC-enabled applications. By installing NotesSQL, you will gain access to Notes data for applications such as Seagate Software's Crystal Reports; Microsoft Visual Basic, Access, and Word; and many other ODBC-enabled applications. You could even write Active Server Pages (ASP) applications that display Notes data on Internet pages.
The heart of database access for Windows-based applications is ActiveX Data Objects (ADO). ADO is an easy-to-use programming interface that abstracts the programmer from the intricacies of using the ODBC API. Further along in this article, I will show you how to use ADO to access your Notes data and create a simple ASP address book.
Tools of the Trade
The first thing you need is Microsoft Data Access Components (MDAC), the primary tool in Microsoft's Universal Data Access strategy. MDAC ships with Microsoft Office, Windows 98, and Windows 2000, but it is best to download the latest version from the
Microsoft Web site at www.microsoft.com/data/download.htm. The file you want to download (as of the writing of this article) is MDAC 2.1.2.4202.3 (GA). MDAC consists of the most current versions of ADO, OLE DB providers and services, and ODBC drivers and managers.
The next step is to head out to the Lotus Web site at www.lotus. com/home.nsf/welcome/techzone and download NotesSQL. This file installs the ODBC driver that allows ODBC-enabled tools such as Crystal Reports, Visual Basic, Access, Excel, and other programs to read and update information stored in Domino databases. Just click the NotesSQL listing on the right side of the screen under the heading Tools. This will take you to the page that has the current version of NotesSQL, Version 2.06. There are two files that you will want to download. The first is the actual driver, weighing in at 1.7 MB, and the second is the Notes documentation database, which you may or may not want to read depending on your technological skills (or willingness to cure your insomnia).
Also on this page is a set of sample applications that Lotus has built to illustrate the use of NotesSQL and how it interfaces with Domino databases. The samples show interfacing of ASP, Crystal Reports, Access, and NetObjects Fusion with Domino data. I highly recommend that you download and peruse these sample applications to gain further understanding of the capabilities of NotesSQL.
Now What?
Once these files have been downloaded, you are going to need administrative rights if you are using Windows NT. (If you are using Windows 95, just proceed to install the files.) First, start with the MDAC file. Find all the files in your Windows Explorer, then double- click the MDAC file name and allow the MDAC file to install itself. This is very straightforward, with no challenges at this stage. Once the file is installed, the program will want to reboot your computer to finish the installation. Let it reboot, then continue with the next step.
Before installing NotesSQL, you need to know at least one piece of information. If you want to set up all of your connections right away, you need to know the actual file name of the Domino database or databases. Installation of NotesSQL is not much different from installation of MDAC. Use Windows Explorer to find the file you downloaded and double-click it, and the driver setup will start. At what seems like the middle of the installation (not the end of it), the setup program will display an ODBC connection screen, but don't worry: This is supposed to happen.
If the data source is to be used with Microsoft Internet Information Server (IIS) or other system programs, click the System DSN tab. Otherwise, you can define your data source by using the User DSN tab. Either way, click the Add button to define a new ODBC data source. ODBC will immediately display a screen asking you to select the driver to use in creating the new data source. Select the Lotus NotesSQL driver (*.nsf) and click the Finish button. You should now see a screen like the one in Figure 1.
In the Data Source Name (DSN) field, give the data source a name that will represent the database you are connecting to. I usually create a name that has no spaces and is all lowercase. This reduces the chance that a program will not like the DSN I choose, thereby forcing me to rename it later.
The next field is Description. Enter anything you like there. Then comes the Server field. First, click the drop-down arrow and see whether or not the server you normally connect to is listed. It probably will be, but if it is not, type in the Notes server name. The Notes server name is usually in the format of name of server/notes domain (e.g., Notes/midrange computing).
Once the Server field is filled in, you will notice that the Database field will be populated for you with one of the databases found on the Notes servers. Click the drop- down arrow and find the database name that you want to connect to.
Most of the other options do not need to be changed from their default values, so click OK to define your data source. You will be returned to the ODBC Data Source Administrator screen, and, at this point, you can click OK to finish installation of NotesSQL. Finishing the installation consists of viewing the obligatory readme and clicking the Finish button. Now you are ready to turn data into information.
Heigh-Ho! Heigh-Ho! It's Off to Work We Go
You now have in place the tools that will help you retrieve data from a Domino database. For the remainder of this article, I will be talking about a simple VBScript program that creates an interactive address book by using IIS, ASP, and NotesSQL. To see this program in action, visit the MC Web site at www.midrangecomputing.com/mc and either download the code or run the script online.
I'll start with the ASP scripts. For test purposes, you are going to access a Notes database that contains a Notes form, which is essentially a table with fields for name and standard address and phone number information. I am keeping the example simple to give you a basic understanding of what happens.
Before you can run this example yourself, you need to know the names of the Notes fields that you are going to access. You need that information to know how to create an SQL statement that will access data in the Notes form.
Looking at the scripts, the first thing to notice is the open method of the ADO Connection object. This method references a data source that was set up using the ODBC administrator. During installation of the NotesSQL data source, you entered a DSN. If you try to run this script, remember to put that name in the DSN= field. Both the DSN and the password are case-sensitive. It is also important to realize that ASP scripts must run under the NT system account. This means that they cannot interact with the Windows desktop. Therefore, you must ensure that the data source you set up for connecting to a Notes database does not require passwords. If it does, you cannot use that data source with ASP scripts. Your Notes administrator should be able to supply you with a Notes user ID that does not require a password pop-up.
The script basically executes an SQL Select statement to generate a list of names from MC's corporate address book and displays each name as a hyperlink. Clicking the hyperlink calls the NameDetails.asp script, which displays that person's address, phone number, and other contact information. Use these two script files as an example of how to retrieve Notes data by using the ODBC driver.
Drive for Show and for Dough!
The NotesSQL driver offers new possibilities for tapping a resource that may not have been directly available to you before. It allows you to use Domino data as if it were relational, expanding the capabilities of your current tools by integrating Domino data with your PC and other programs at your organization. Best of all, NotesSQL opens up new doors to help you do your job.
-04-00.png)
Figure 1: NotesSQL finds your Notes server and provides you with the names of databases found on the server.
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.
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.
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.
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:
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.
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