12
Wed, Jun
4 New Articles

Creating Client/Server Applications with ODBS

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Drivers that conform to Microsoft's Open Database Connectivity (ODBC) standard bring new client/server capabilities to the AS/400. But what exactly is ODBC and how does it work?

ODBC allows any PC (client) with an ODBC driver to access data from almost any database server (including the AS/400). ODBC is used primarily in a client/server environment, where the server can hold large amounts of data and the client, very often a PC, offers inexpensive processing power but limited storage capacity. The client application is not aware of how the data is retrieved; it simply issues a request for data to the ODBC driver and receives its requested data in reply. PC Support/400 provides the link between your PC and the AS/400.

The purpose of this article is to show you how easily you can access your AS/400 database through the IBM ODBC driver using Microsoft's Visual Basic. An elementary example of a client/server application created with Visual Basic is provided to illustrate how simple ODBC makes accessing AS/400 data from a PC.

Alternatives to ODBC

You do have other client/server programming options on the AS/400, but many of them could have restrictions or limitations which impact your development efforts. For example, developers attempting to create client/server applications without ODBC might choose to use Dynamic Data Exchange (DDE) between their applications and a PC Support program called DQSERVER (Data Queue Server), which in turn communicates with the AS/400 through data queues. However, unlike files, data queues can't be saved. If the link between the PC and the AS/400 fails during an update, there's a good chance that data will be lost in transit with no prospect of recovery.

Another alternative is to use Remote SQL to access data on the AS/400. This method allows PC applications to access AS/400 data directly; but it requires a tedious, database-specific application program interface (API). An interface developed to access data on the AS/400 won't necessarily work on another database system. This is where ODBC enters the picture.

The advantage of ODBC is that your application can access not only the AS/400 but any database that allows ODBC access. The benefit of this approach isn't really apparent with a single application for a single platform. It becomes strikingly obvious if you're developing products that provide more generic functions. ODBC gives you the option to change either the client or the server, often without rewriting the application.

On the Market

Currently, three AS/400-oriented ODBC drivers are on the market: Born Software's ODBC/400, ShowCase Corporation's ODBC Dynamic Link Library (DLL), and IBM's ODBC driver. This article focuses on the IBM offering.

In some cases, ODBC drivers work hand-in-hand with ODBC-compliant software; the driver acts as middleware between the client- and server-based software. In other cases, ODBC drivers are built into the component software.

At present, at least two AS/400-friendly database products utilize ODBC. Microsoft's Access can-upon proper setup of an ODBC driver on your PC- retrieve data from your AS/400 directly. Lotus Corporation's Approach product employs ODBC but, as of this writing, the AS/400 has problems understanding it. This is because ODBC has three levels of compliance: Core level, Level 1, and Level 2. Core level allows the ODBC connection and simple SQL statements to be executed at the server level. Level 1 allows data "GET" and "PUT" commands. Level 2 uses foreign keys and assign privileges. Approach uses Level 2 to converse. Unfortunately, IBM's ODBC driver isn't fully Level 2-compatible yet.

As for programming languages, Microsoft's Visual C++ version 1.5 is ODBC- compatible, as is Computer Associates' CA-Realizer. If you use the ODBC driver with these products, you'll probably encounter some frustration because you'll be programming close to the metal. When working with these languages, you need Microsoft's ODBC Software Development Kit (SDK) and Microsoft's Developer's Network CD (known in the trade as MSDNCD), totaling around $200. In addition, you must be conversant with API-oriented Windows programming.

The application presented in this article requires Visual Basic Professional Edition 3.0 (the standard edition doesn't support ODBC). Unlike the previously mentioned languages, Visual Basic allows you to program at a higher level. It is a relatively easy language to learn and use, which accounts for its popularity. If you plan to use Visual Basic in a client/server environment, you can use this article as your stepping stone to application development that utilizes ODBC.

Installing IBM's ODBC Driver

IBM's ODBC is free, but it doesn't come on tapes-not even cumulative tapes. You must order it electronically as Program Temporary Fix (PTF) SF15426. When you download PTF SF15426, you'll notice three other PTFs tagging along: SF15199, SF15200, and SF15201. Fortunately, you can load (LODPTF) and apply (APYPTF) these PTFs on the fly-and no IPL is required. However, make sure that PC Support/400 is inactive during the application process.

You'll be disappointed if you're counting on the ODBC documentation to be a part of IBM's PTF cover letters-it's not. However, the good news is that the documentation comes in Windows' help format, a more readable format than an SEU source file. To access the documentation, run the following command from Windows:

WINHELP I:QIWSFL2EHNODBC.HLP

Since the documentation is in Windows' help format, it has hypertext capabilities and it prints out nicely too. Once the PTFs are installed, go to the PC on which you intend to run ODBC and bring up Windows.

From the Windows program manager, click on File and then on Run, and type:

I:QIWSFL2EHNSTP.EXE

You should install the ODBC driver and the Administration utility. (By default, both options will be selected.) During the installation process, you are asked to select from a list of available drivers. Select the AS/400 PC Support ODBC Driver. Then click on Install and Continue. When asked to add a new data source, bypass this window by selecting Continue. (The addition of data sources is covered in detail in a moment.)

Upon completion of this installation process, you'll find a new directory called ODBC on your PC and a new program group within Windows called Microsoft ODBC. Open the group (if it's not opened already) and you'll find an icon that looks like 1. At this point, you're on your way to configuring your database for ODBC access.

Upon completion of this installation process, you'll find a new directory called ODBC on your PC and a new program group within Windows called Microsoft ODBC. Open the group (if it's not opened already) and you'll find an icon that looks like Figure 1. At this point, you're on your way to configuring your database for ODBC access.

Database Configuration and ODBC

In order to access data from a server, you must make ODBC aware of the specific files that contain the data. The ODBC Administrator utility provides a configuration function that allows you to add and configure files you plan to access through ODBC. The following material describes this process.

Bring up the ODBC Administrator by double-clicking on its icon. In ODBC parlance, a file (physical or logical) is called a data source. The example in this article uses physical file QIWS/QCUSTCDT, which should be on every AS/400 that has PC Support/400 installed. ODBC refers to this file (data source) as a data source name (DSN). The Microsoft ODBC Administrator window appears as shown in 2.

Bring up the ODBC Administrator by double-clicking on its icon. In ODBC parlance, a file (physical or logical) is called a data source. The example in this article uses physical file QIWS/QCUSTCDT, which should be on every AS/400 that has PC Support/400 installed. ODBC refers to this file (data source) as a data source name (DSN). The Microsoft ODBC Administrator window appears as shown in Figure 2.

As you install ODBC, you'll see an entry in the Installed Drivers box. Click on the AS/400 PC Support ODBC Driver entry in this box. Then click on the Add New Name button and fill out the ODBC Driver Setup window as shown in 3. Use the default local location name displayed by the OS/400 Display Network Attributes (DSPNETA) command in place of MyAS400.

As you install ODBC, you'll see an entry in the Installed Drivers box. Click on the AS/400 PC Support ODBC Driver entry in this box. Then click on the Add New Name button and fill out the ODBC Driver Setup window as shown in Figure 3. Use the default local location name displayed by the OS/400 Display Network Attributes (DSPNETA) command in place of MyAS400.

The library/owner/file format specified in the Default Library prompt doesn't conform to the traditional library/file format on the AS/400. However, it's important to follow this syntax correctly; otherwise, subsequent requests for an ODBC connection will fail. Use QIWS for the library, since this is where the file resides. Use the Display Object Authority (DSPOBJAUT) command to determine the owner of the file (probably QSECOFR). Use QCUSTCDT for the file name. Now, as soon as you hit the OK button, your PC is ready for ODBC communications.

You can repeat this process to put in other data sources. For example, if five of your users each need access to 10 files on your AS/400, you'll have to repeat the aforementioned process 50 times. As an alternative, you could configure one PC manually and copy the data sources to the other four PCs. The data sources are stored in the ODBC.INI file within the Windows subdirectory on the PC.

The Sample Application

Our sample application consists of a screen (a form in Visual Basic terminology) that displays the customer's last name, first initial, and customer number from the QCUSTCDT file in library QIWS. (You may recall that we defined this file as a data source to ODBC in the previous section.) 4 illustrates this form.

Our sample application consists of a screen (a form in Visual Basic terminology) that displays the customer's last name, first initial, and customer number from the QCUSTCDT file in library QIWS. (You may recall that we defined this file as a data source to ODBC in the previous section.) Figure 4 illustrates this form.

Through the use of buttons, the user can connect to the file and retrieve the first, next, previous, or last record. These buttons, as well as the text box to the left of the buttons, are referred to as controls. In event-driven programming, a control typically has associated code that performs an action. (For anyone who is unfamiliar with event-driven GUI programming, "Visual Development Tools for RPG," MC, May 1994, covers many of the basic concepts.)

In 4, we have labeled each control of our form with a letter that relates to a portion of the Visual Basic program code in 5. For simplicity, we have listed all of the code associated with the form in one place. The code for each control is actually created separately as you build a form in Visual Basic. The code that performs an action for a control is defined within a Sub procedure. As you can see, 5 includes a number of Sub/End Sub groups.

In Figure 4, we have labeled each control of our form with a letter that relates to a portion of the Visual Basic program code in Figure 5. For simplicity, we have listed all of the code associated with the form in one place. The code for each control is actually created separately as you build a form in Visual Basic. The code that performs an action for a control is defined within a Sub procedure. As you can see, Figure 5 includes a number of Sub/End Sub groups.

Once the form in 4 is displayed, the user can click on the Connect button. This step, in a real application, should be done during the initialization phase. The first message, "Connecting to database," will appear.

Once the form in Figure 4 is displayed, the user can click on the Connect button. This step, in a real application, should be done during the initialization phase. The first message, "Connecting to database," will appear.

At this point, let's take a look under the hood. The code that executes at the onset of the program is labeled A in 5.

At this point, let's take a look under the hood. The code that executes at the onset of the program is labeled A in Figure 5.

The Dim statements define global variables. In this example, they define "db" as your database, "ds" as a view of the database, and "readout" as the string that contains the data record you're interested in.

Since these Visual Basic terms may be unfamiliar to AS/400 programmers, let's translate them into standard AS/400 terminology. A physical or logical file that is updatable is called a dynaset; and one that is not updatable is called a snapshot. You're using dynasets here.

The code inside the Connect button is shown as label B in 5. In the third statement, "QCUSTCDT" is hard-coded as the data source name. Had the next statement (the one commented out with a single, preceding quote) been used, the user would be prompted with a list of all preconfigured ODBC data sources.

The code inside the Connect button is shown as label B in Figure 5. In the third statement, "QCUSTCDT" is hard-coded as the data source name. Had the next statement (the one commented out with a single, preceding quote) been used, the user would be prompted with a list of all preconfigured ODBC data sources.

The task is to specify that all records from file QCUSTCDT are made available. This is accomplished in the fifth statement of section B's code with the SQL statement:

"select * from QCUSTCDT"

If you want to retrieve the first record, click on the First Record button. The first record appears in the text box (see lable G in 4).

If you want to retrieve the first record, click on the First Record button. The first record appears in the text box (see lable G in Figure 4).

The code associated with the First Record button is shown as label C in 5. The MoveFirst line forces the file pointer to the first record of the dynaset. In RPG parlance, the equivalent is SETLL with *LOVAL used as the search argument.

The code associated with the First Record button is shown as label C in Figure 5. The MoveFirst line forces the file pointer to the first record of the dynaset. In RPG parlance, the equivalent is SETLL with *LOVAL used as the search argument.

After the MoveFirst, we concatenate three fields: LSTNAM, INIT, and CUSNUM. We use the variable readout to store the result and then ask the text box to display it (using the SetFocus method). For the Next, Previous, and Last buttons (see labels D, E, and F in 5), we use the MoveNext, MovePrevious, and MoveLast methods in lieu of MoveFirst. The MoveNext and MovePrevious methods are equivalent to READ and READP in RPG.

After the MoveFirst, we concatenate three fields: LSTNAM, INIT, and CUSNUM. We use the variable readout to store the result and then ask the text box to display it (using the SetFocus method). For the Next, Previous, and Last buttons (see labels D, E, and F in Figure 5), we use the MoveNext, MovePrevious, and MoveLast methods in lieu of MoveFirst. The MoveNext and MovePrevious methods are equivalent to READ and READP in RPG.

I hope this sounds simple enough for you to try. The Visual Basic program presented here is shorter than a DDS/RPG program with similar functionality.

For the sake of illustration and readability, the Visual Basic code is not robust, because we've stripped out all the error traps. For example, once the user is at the beginning of file (BOF), he shouldn't be able to click the Previous Record button. Similarly, while at end of file (EOF), he shouldn't have access to the Next Record button. You can easily implement these rules by inserting code like this:

btnPrev.Enabled = False btnNext.Enabled = False

The code to check for EOF and BOF looks like the following:

If ds.BOF Then ..... If ds.EOF Then .....

What About Subfiles?

The use of subfiles is extremely popular in the AS/400 world; the GUI equivalent is a list box. Several varieties of list boxes exist: combo boxes, dropdown list boxes, and plain list boxes (e.g., file/directory listings). Filling a list box is much the same as filling a subfile. You have to read (or MoveNext in Visual Basic) enough records to allow the user to scroll up or down for selection. Side-by-side subfiles, which are sources of headaches and divorces, can be replaced by two list boxes in Visual Basic. What could be simpler and more user-friendly?

One thing that's missing (if you haven't noticed already) is the ability to run a query against more than one ODBC data source. With that capability you'd have tremendous power at your fingertips. For example, you'd be able to run a query that does a join between a mainframe Oracle data source, an AS/400 data source, and a PC database file created with Ashton-Tate's dBASE IV software! This capability would allow your application to access and act on data from sources that are fully transparent to it. One other point to keep in mind: at this stage no details have yet been released on how ODBC performs in a complex, multiuser environment. Perhaps you can contribute some information on this by using ODBC in your environment.

Go the Distance

Event-driven, GUI-based, client/server applications-like the one described in this article-are created every day. Some are more sophisticated than others, of course. Many are in daily production; some are used for on-demand jobs.

This article is intended to furnish the AS/400 programmer with information about an easy-to-use tool for building AS/400-oriented, client/server applications utilizing ODBC. RPG programmers should not find Visual Basic difficult to learn. In fact, it should be an exciting experience. I hope I've convinced you to take a hard look at AS/400 client/server programming with Visual Basic and ODBC.

Ignatius Wong is an MIS manager in Kingston, Ontario. He's a former services specialist with IBM.


Creating Client/Server Applications with ODBS

Figure 1 The ODBC program group

 UNABLE TO REPRODUCE GRAPHICS 
Creating Client/Server Applications with ODBS

Figure 2 The ODBC Administrator main dialog box

 UNABLE TO REPRODUCE GRAPHICS 
Creating Client/Server Applications with ODBS

Figure 3 Configuring an ODBC data source

 UNABLE TO REPRODUCE GRAPHICS 
Creating Client/Server Applications with ODBS

Figure 4 ODBC test form

 UNABLE TO REPRODUCE GRAPHICS 
Creating Client/Server Applications with ODBS

Figure 5 Visual Basic code for test form.

 Section A: Dim db As Database Dim ds As Snapshot Dim readout As String Section B: Sub btnConnect_Click () Print "Connecting to database ...." Set db = OpenDatabase("QCUSTCDT", False, True) 'Set db = OpenDatabase("", False, False, "ODBC") Set ds = db.CreateSnapshot("select * from QCUSTCDT") Print "Connected" End Sub Section C: Sub btnFirst_Click () ds.MoveFirst readout = ds.Fields("LSTNAM") & "," & ds.Fields("INIT") & "," & ds.Fields("CUSNUM") txtRecord.SetFocus End Sub Section D: Sub btnNext_Click () ds.MoveNext readout = ds.Fields("LSTNAM") & "," & ds.Fields("INIT") & "," & ds.Fields("CUSNUM") txtRecord.SetFocus End Sub Section E: Sub btnPrev_Click () ds.MovePrevious readout = ds.Fields("LSTNAM") & "," & ds.Fields("INIT") & "," & ds.Fields("CUSNUM") txtRecord.SetFocus End Sub Section F: Sub btnLast_Click () ds.MoveLast readout = ds.Fields("LSTNAM") & "," & ds.Fields("INIT") & "," & ds.Fields("CUSNUM") txtRecord.SetFocus End Sub Section G: Sub txtRecord_GotFocus () txtRecord.Text = readout End Sub 
BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 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:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic 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:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT 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.

  • SB HelpSystems ROBOT GenericCan 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:

  • FORTRA 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:

  • FORTRAManaging 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:

  • FORTRAThe 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:

  • FORTRAFor 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:

  • LANSA 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.

  • LANSAWhen 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:

  • LANSASupply 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:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic 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.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: