In the good old days, all the data you needed to access was on one computer, and chances are the logo on that computer had three blue letters-IBM. Those days are gone and most of those who said, "You're always safe buying IBM," have been retired or are on display in the Museum of Science and Industry in Chicago. It's a brave new world.
Developing client/server applications often involves accessing data from a number of different systems. Most systems, with the exception of the AS/400, use SQL as the native database interface. The problem is that almost every system has a different SQL dialect. Something as common as an SQL SELECT statement might be different between a Microsoft SQL Server database and an Oracle database. For an application to work across different systems, it has to know which type of system it's talking to, and send the appropriate SQL syntax for that system. That's too complex.
The open database connectivity (ODBC) standard is an answer to the problem of accessing data from systems with different SQL dialects, and even systems where SQL is not the native database access language. The purpose of this article is to give you an overview of ODBC and discuss its implementation vis-?-vis the AS/400.
If you're even pondering the move to client/server development, how your applications will access the database is clearly one of the core issues, and ODBC is one method that you should keep in mind. This article will give you an understanding of the fundamentals of ODBC. That way, as you begin to evaluate and use ODBC, you will be better prepared to make the decision whether to use it and to deal with the problems that may occur if you do.
In the Beginning...
ODBC is a standard defined by Microsoft. Instead of having as many dialects of SQL as there are systems, they created a single standard way to access a database from a Windows program. Strictly speaking, ODBC could just as easily be implemented on a Macintosh or under OS/2, but that hasn't happened yet.
An application that uses ODBC is built on three layers. They are the driver manager, the individual ODBC driver, and the data sources (see 1). The highest layer of ODBC is the driver manager. Microsoft supplies the driver manager for each Windows operating system (e.g., Windows NT) it ships. The driver manager is one of the few pieces of software supplied as a part of the ODBC standard itself.
An application that uses ODBC is built on three layers. They are the driver manager, the individual ODBC driver, and the data sources (see Figure 1). The highest layer of ODBC is the driver manager. Microsoft supplies the driver manager for each Windows operating system (e.g., Windows NT) it ships. The driver manager is one of the few pieces of software supplied as a part of the ODBC standard itself.
The driver manager (ODBC.DLL) is a dynamic link library that serves several purposes. The main one is the interface between your application and the drivers that you use. The driver manager loads drivers, handles some of the ODBC initialization functions, and validates the syntax and sequence of ODBC calls.
The next layer in ODBC is the in-dividual driver. A driver that you choose is also a DLL. For example, the name of the file for IBM's AS/400 ODBC driver is EHNODBC.DLL. The driver is where the bulk of the work takes place. The driver starts the conversation with the database, translates the ODBC calls that your application makes into the native SQL dialect, and returns the results.
Drivers come from different vendors, and frequently that's not Microsoft. Microsoft provides drivers for Microsoft SQL Server and Microsoft Access, but not for the AS/400. Drivers for DB2/400 come from vendors that include IBM, HiT Software, and Execusoft.
There are two types of drivers, single-tier and multiple-tier. A single-tier driver works directly with the database. If your application sends a request to update a record to a single-tier driver, that driver will actually update the record. A multiple-tier driver passes the request on to a server for the database system; it's that server that actually updates the record. As a result, the ODBC drivers that work with the AS/400 database will be multiple- tier drivers.
The lowest layer in the structure of ODBC is the data source, which is where the data resides. It can be on the PC or another system. If the data source is on another system, it isn't the responsibility of the ODBC driver to provide communications support, although each driver may support certain types of connections. For example, an AS/400 ODBC driver might support both the Client Access/400 router and NetSoft's NS/Router. Router support is something you need to consider when you're kicking the tires on the different drivers that are available.
You configure ODBC using the ODBC administrator program (ODBCADM.EXE). This is another piece of software that Microsoft supplies. In some cases, when you install a driver, the installation program will create the administration program's icon in a separate Windows program group. Otherwise, you will find the ODBC administration program icon in the Windows Control Panel window.
When you install an ODBC driver, before you can use the driver, you will need to set up one or more data source names (DSNs) as shown in 2. This assigns a unique name to a specific database connection. You can use a single driver to connect to more than one database, but you will configure different DSNs for them. For example, if you have more than one AS/400 in your network, you would create two different DSNs. You can name each one to match the name of the system it connects to. In your programs, the DSN you will use refers to a specific database.
When you install an ODBC driver, before you can use the driver, you will need to set up one or more data source names (DSNs) as shown in Figure 2. This assigns a unique name to a specific database connection. You can use a single driver to connect to more than one database, but you will configure different DSNs for them. For example, if you have more than one AS/400 in your network, you would create two different DSNs. You can name each one to match the name of the system it connects to. In your programs, the DSN you will use refers to a specific database.
All of the data created when you configure ODBC data sources is stored in a file called ODBC.INI. You might want to take a look at this file, but I strongly recommend against changing it by any means other than the ODBC administration program. Changing ODBC.INI with something like Notepad or the PrivateProfile Windows APIs is a quick way to trash your ODBC configuration. In this file, you can see specifically which DLL is being used for a certain driver. This can help when you're trying to figure out what version of a DLL you are using.
Using ODBC
If you go to your local bookstore and purchase the Microsoft ODBC 2.0 Programmer's Reference and SDK Guide, almost half of the "Programmer's Reference" section is taken up by a single chapter, the ODBC Function Reference chapter. This is where all of the API calls for ODBC are defined.
No matter how you use ODBC, whether it's from C++, Visual Basic, PowerBuilder, or an end-user application, the API calls are going on under the covers. Having a general knowledge of how the API calls are happening, even if you aren't directly using them, will help you when you are having a problem. It will tell you if your problem is with the driver, or if it is happening before the driver is even called. It can be a big time saver.
Microsoft groups the APIs into several types. There are APIs that connect to a data source, APIs that set and retrieve driver options, and APIs that submit SQL requests, to name a few. Microsoft also groups the APIs by conformance level. (For more information on conformance levels, see "ODBC Pre-flight," MC, January 1995.)
I've always thought of the ODBC APIs as a very formalized dance. There is a specific way that you are supposed to do everything. For example, to connect to an ODBC driver, you need to call the following ODBC functions in order:
1. SQLAllocEnv 2. SQLAllocConnect 3. SQLConnect or SQLDriverConnect
The driver manager handles these three APIs. The first two involve setting up memory and handles (or addresses) to allow the connection to happen. The third function actually connects to a specific driver. SQLConnect is used to connect to a specific data source and SQLDriverConnect can bring up a list of the configured data sources so the user can select one.
To run an SQL statement to retrieve data, there is a different set of dance steps you have to take. They involve the use of the following functions:
1. SQLAllocStmt 2. SQLPrepare 3. SQLBindParameter 4. SQLExecute
The SQLAllocStmt allocates a statement handle, SQLPrepare gets an SQL statement ready for execution, SQLBindParameter sets up a buffer for a returned value, and SQLExecute runs the statement on the database server. As you can see, there is a very structured approach to the use of the APIs, and it is documented in the "Programmer's Reference" section of the Microsoft ODBC Reference.
Language Considerations
You will most commonly see the ODBC API functions used in C programs. Other languages, like Microsoft's Visual Basic (VB) and Powersoft's PowerBuilder, have their own interface to ODBC. You can still use the API functions from languages like these, but they have a better way.
One way to access ODBC from a VB program is to use a data control. A data control makes a data source available to a VB program. VB calls a control's parameter a property. In the Connect property for a data control, you can specify a DSN. VB also allows you to pass other parameters that are driver specific. For example, you can pass a user ID and password to log on to the database. In many cases, if you don't pass those two elements, the driver will prompt you for the user ID and password. Here is an example of a connect string in VB:
DSN=MCPGMR;uid=CSLOGIN;pwd=CSPWD
You can see an example of using a data control in3 and 4. 3 shows the parameters set to connect to an ODBC driver. 4 shows a text box being linked to the data control and a specific field in the file. That way the data in the file is shown without writing any code.
You can see an example of using a data control in Figures 3 and 4. Figure 3 shows the parameters set to connect to an ODBC driver. Figure 4 shows a text box being linked to the data control and a specific field in the file. That way the data in the file is shown without writing any code.
VB's data controls are one way of connecting to a database using an ODBC driver. VB also has a programmatic way of accomplishing the same thing. In the Professional Edition, there are a number of objects, properties, and methods included for accessing databases. They extend the ability of VB to access databases beyond the capability of a data control. Microsoft documents them in the Visual Basic Professional Features Book 2: Data Access Guide.
5 shows one way to code access to an ODBC database. The Open Database method connects to a driver. The Create Dynaset method opens a specific file (in ODBC terms, a connection that allows updates to one or more files is a dynaset). Then the program moves to the first record in the dynaset and sets what the text box will display to the value in the field Title.
Figure 5 shows one way to code access to an ODBC database. The Open Database method connects to a driver. The Create Dynaset method opens a specific file (in ODBC terms, a connection that allows updates to one or more files is a dynaset). Then the program moves to the first record in the dynaset and sets what the text box will display to the value in the field Title.
Other languages, like PowerBuilder and SQLWindows, have their own interface to ODBC. They all accomplish the same tasks of being able to connect to a specific database and select which files and fields you need access to in your application.
You can also use ODBC from certain applications. One example is Microsoft Office. Included in Office is Microsoft Query, which allows you access to ODBC databases. When you use Excel, there is a Data menu; from that menu there is a Get External Data option. This option calls Query and allows you to develop a query against an ODBC database. Once you are satisfied with the data you see in Query, there is a menu option to return that data to Excel. There is another option in Excel that allows you to refresh the data using the query you defined.
AS/400 ODBC Specifics
Starting with OS/400 V2R3, IBM supplied an ODBC driver with Client Access/400. The driver is available by downloading a set of PTFs or, as of V3R1, is included with Client Access/400.
One of the first questions many people ask is, "If an ODBC driver is included with V3R1, why should I pay for someone else's driver?" There are two primary answers-support for all of the ODBC functions you need and performance. The driver IBM is shipping right now is an API level 1 driver and supports minimum SQL grammar. That may work for you, but you may find that you need or want a higher level of support. It depends on the applications you use or the features you decide to support if you are writing your own applications.
The performance issue can be a significant one. The driver vendor has a lot of influence over how well the driver performs. When I first started looking at ODBC drivers, and found there were a few available, I expected them to have similar performance. My testing showed otherwise.
Driver performance not only varies from vendor to vendor, it also varies based on how you are using the driver. Some drivers perform read-only SELECTs very fast, but are quite slow when doing an update.
One reason for the differences in performance is simply the different programming skill levels of the people developing the driver. Another difference is that some vendors don't use the Client Access transfer function and remote SQL; they write their own data transfers, which can dramatically improve performance. The moral of this story is that you really need to test drivers in your production environment.
As I said, most ODBC drivers for DB2/400 use the transfer function and/or the remote SQL function of Client Access/400. This has been part of the reason for the complaints people have had about the speed of ODBC. Because the speed of Client Access/400 is being dramatically improved in V3R1, there should be significant improvements in the speed of the ODBC drivers that use those functions. So after you install V3R1, you may want to re-evaluate ODBC drivers.
One common problem when using an ODBC driver against DB2/400 is a lack of support for multiple-member physical files. Some drivers support them and others don't. Because the concept of multiple-member physical files isn't common on other systems, this isn't something that the ODBC standard supports. However, the standard specifically allows extensions in this kind of situation. It's up to the driver vendor to implement them.
Where To Go From Here
If you're a developer, one of the most important tools is the ODBC software development kit (SDK). The SDK has a series of tools, Windows help files, and include files to aid in the development of applications using ODBC. One tool in particular, called ODBC Test, or Gator, can pass a driver all of the API functions that the driver supports. It's particularly helpful in figuring out the steps involved in the ODBC dance. The Microsoft ODBC 2.0 Programmer's Reference and SDK Guide is the official bible of ODBC. Microsoft includes the manual with the SDK, or you can purchase it separately at many bookstores. In terms of languages for learning ODBC, I've had the best experiences with Visual Basic Professional Edition. You can try calling the ODBC APIs, use a data control and bound controls, and use data access objects, comparing the performance of each one of those. VB is one of the quickest ways to prototype ODBC applications.
As far as the future of ODBC is concerned, it probably lies in object linking and embedding (OLE). OLE is Microsoft's object technology. Instead of using ODBC directly, the driver vendors may ship OLE-aware drivers. Our applications wouldn't get the data using the ODBC APIs but, rather, using the OLE APIs. In spite of the fact that OLE is probably going to supplant ODBC as the method we use to get data from different databases, I would still spend the time to learn and develop applications with ODBC. The primary reason is because it will be a while before vendors create OLE-aware data controls, so for the time being you still need to use ODBC.
Another reason is that OLE is quite slow right now. As Microsoft more tightly integrates the support for OLE in Windows 95, the speed should improve; Windows 95 is scheduled for release this year, but chances are good that you won't be implementing it the day it's released. So the investment you make in learning ODBC will have benefits for quite a while. If you are using VB's data controls or the equivalent in another language, you should be able to convert your programs from ODBC- to OLE-aware data controls with little effort.
You have the power in your hands right now to access nearly any database out there using a single common interface. No longer do you have to tell your users, "Sorry, that data is on another system." ODBC opens the door to our brave new world.
Jim Hoopes is a senior technical editor for Midrange Computing.
REFERENCE Microsoft ODBC 2.0 Programmer's Reference and SDK Guide (ISBN 1-55615-658-8).
ODBC Overview
Figure 1 ODBC High Level Structure Diagram
UNABLE TO REPRODUCE GRAPHICS
ODBC Overview
Figure 2 Configuring an ODBC Data Source
UNABLE TO REPRODUCE GRAPHICS
ODBC Overview
Figure 3 Setting the Properties of a Data Control
UNABLE TO REPRODUCE GRAPHICS
ODBC Overview
Figure 4 Linking a Text Box to the Data Control
UNABLE TO REPRODUCE GRAPHICS
ODBC Overview
Figure 5 VB Code to Connect to an ODBC Database
UNABLE TO REPRODUCE GRAPHICS
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