17
Wed, Apr
5 New Articles

The ODBC Pre-flight

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

Brief: Every ODBC driver supports a different level of functionality. Applications that use ODBC often depend on a certain level of capability from the driver. Unfortunately, which driver is used isn't necessarily up to you. This article presents information to help you code applications to decide if a driver will work and, if so, to use that driver to its maximum potential.

Have you ever watched the activity around an airplane before you take a commercial flight? The pilot always does a walk around or pre-flight check. The purpose is to make sure that there aren't any obvious problems with the airplane that would make flight unsafe. Because the pilot is ultimately responsible for the safety of the passengers, you will always see him do this personally.

As the programmer-or pilot-of your application, you are responsible for taking reasonable measures to protect the users from problems and bugs. When you write a client/server application that uses open database connectivity (ODBC), your pre-flight checks should include the ODBC driver.

ODBC is Microsoft's standard for accessing databases. Microsoft recognized that databases are not all created equal; different databases have different capabilities. For example, not all data-bases support outer joins, which allow you to join files and still have records returned when matching records might be missing in one of the files. The trick for Microsoft was to create a standard that allows vendors to supply drivers for almost any database.

ODBC Flight Manual

When Microsoft created the ODBC standard, they knew you would run into the dilemma of varying database support and designed the standard to help you handle those situations. The ODBC standard takes the differences between databases into account by allowing different levels of conformance.

In ODBC, there are two major types of conformance. The first is ODBC application program interface (API) conformance. The ODBC Version 2.0 standard defines 56 functions divided into three groups: core, level 1, and level 2.

The second major conformance type is SQL grammar. Like API conformance, there are three levels of SQL grammar conformance: minimum, core, and extended SQL grammar. Each level of SQL grammar conformance supports additional features in four areas: SQL data definition language (DDL), SQL data manipulation language (DML), expressions, and data types. The "ODBC Conformance" sidebar explains some of the details behind each conformance type.

A pilot doesn't do the maintenance on an airplane, and that's one of the reasons for a pre-flight check. Sometimes the user selects the ODBC driver, so you need to know how to test a driver to see if it supports the functionality you need. This article also focuses on helping you understand how to write programs to handle the different conformance levels. That way, your application won't try to take off without, say, the wings attached.

Microsoft defined a couple of functions in the standard to help resolve conformance issues: SQLGetInfo and SQLGetFunctions. I'll discuss these functions and supply you with a utility program to demonstrate how to use them. The program allows you to connect to a driver and displays some of the information you should gather in your programs. Pre-flighting your ODBC driver will help you write client/server programs that crash less often, work with more ODBC drivers, and end up making your passengers (I mean users!) much happier.

Wings, Propellers, and Conformance

No matter what you decide based on your application, don't select a driver unless it supports at least API conformance level 1. Most applications (for example, Microsoft Access) and many languages require level 1 conformance, whereas most don't require level 2 conformance. As for SQL grammar conformance, I would say that core level compliance should be your minimum standard.

Conformance support isn't an absolute. If a driver supports level 1 API conformance, for example, that driver may also support many of the level 2 API functions. The driver vendor shouldn't claim level 2 conformance, although the driver may support all the level 2 functions you need. As a word of warning, the ODBC standard operates on the honor system. I've seen drivers that report API level 2 compliance, yet don't support all level 2 functions.

In some situations, a database doesn't support some of the functions in a given conformance level. The driver should mimic the ability. If a back-end database doesn't support unions, the driver can implement the union functionality in its code. The driver vendor may choose to implement functions beyond those described in the conformance levels, when the back-end databases support them. You can find out about those functions in the driver documentation.

Some Microsoft Visual Basic (VB) programmers are probably wondering what ODBC functions and SQL grammar have to do with them. You can write sophisticated VB programs that access a database using ODBC without ever calling an ODBC function or writing a single SQL statement. The same holds true for Microsoft Access.

These languages and applications use ODBC, but can insulate you from the API coding; the API calls and SQL statements are still running underneath the covers. So a VB or Access programmer needs to keep ODBC conformance levels in mind just as much as a C programmer does.

What's the payoff? By determining the conformance level, you can write programs that use the driver to its maximum potential. If a driver supports unions, you might speed up your application by using them instead of running two SQL Select statements. You might decide to require that the driver supports unions or you may choose to write your application to support both drivers that do and don't support unions. This way the applications still work in situations where unions aren't available, but give the speed advantage to users who pick drivers with union support.

ODBC Flight 2 Now Boarding at Bill's Gate 1

In the introduction, I described SQLGetInfo and SQLGetFunctions-tools you need to support any ODBC driver. The SQLGetInfo function returns a wide variety of information about a driver including conformance, the driver version, the server name, the term used natively to refer to a table (e.g., file), and whether the driver supports outer joins, to name a few.

To use the SQLGetInfo function, you call it, passing an ODBC connection handle and a value that indicates what information you want returned about the driver. Microsoft's ODBC software development kit (SDK) has C include files which contain the constants for the various information types. The include files are SQL.H and SQLEXT.H. 1 illustrates the syntax for the SQLGetInfo and SQLGetFunctions function calls.

To use the SQLGetInfo function, you call it, passing an ODBC connection handle and a value that indicates what information you want returned about the driver. Microsoft's ODBC software development kit (SDK) has C include files which contain the constants for the various information types. The include files are SQL.H and SQLEXT.H. Figure 1 illustrates the syntax for the SQLGetInfo and SQLGetFunctions function calls.

To find out what API conformance level a driver has, pass SQLGetInfo the constant SQL_ODBC_API_CONFORMANCE supplied in the SQLEXT.H include file. The function returns a 16-bit integer value that tells the program the conformance level. Requesting other information about a driver returns different types of data. For example, request the database name and the function will return a string; request information about how the driver handles transaction isolation and the function will return a 32-bit integer that tells the program if the driver supports read uncommitted, read committed, and repeatable read.

SQLGetFunctions works in a similar way. For example, pass the function a constant that represents a specific API function (e.g., SQL_API_EXECUTE). The function will return a true or false flag depending on whether or not the driver supports the function.

The program can also pass the function the constant SQL_API_ALL_FUNCTIONS. In that case the SQLGetFunctions returns an array of values that indicate whether or not the driver supports each function. The program then uses the same constants (e.g. SQL_API_EXECUTE) as an offset to the array to test whether the driver supports the function (you'll see an example of this in the utility program).

For Those of You Who Always Want to Know How an Airplane Flies

To save you some effort, I have created a utility which displays some important information about an ODBC driver. You can obtain a copy of this utility by downloading and running the self-extracting file GETINPKG.EXE from the files area of MC-BBS. Once the executable file (GETINFOC.EXE) is on your system, create a Windows program item for the program. Then double click the icon to execute the application.

Once you run the program, it displays a dialog box with a list of installed ODBC drivers (See 2). Double click on one of the drivers listed and the program prompts for any additional information needed to attach to the ODBC driver, such as user name and password. 3 shows the connection window for the AS/400 ODBC driver from HiT Software.

Once you run the program, it displays a dialog box with a list of installed ODBC drivers (See Figure 2). Double click on one of the drivers listed and the program prompts for any additional information needed to attach to the ODBC driver, such as user name and password. Figure 3 shows the connection window for the AS/400 ODBC driver from HiT Software.

Once the program attaches to a driver, the application will query the driver for certain parameters and display them in a window (see 4). When you want to exit the program, double click the button in the upper left hand corner of the window or choose the File menu and select Exit.

Once the program attaches to a driver, the application will query the driver for certain parameters and display them in a window (see Figure 4). When you want to exit the program, double click the button in the upper left hand corner of the window or choose the File menu and select Exit.

I wrote the utility using Microsoft Visual C++ Version 1.5. In spite of appearances, it is a Windows program. Normally in a Windows C program, an experienced Windows programmer would expect to see a WinMain function, but I used a feature in Visual C++ called QuickWin. QuickWin creates a Windows program without having to write the supporting code. I wanted to demonstrate how to use the APIs, not how to write a Windows program.

If you choose to key the program in, you should call the project GETINFOC and link it to the ODBC.LIB file. The ODBC SDK and the Windows SDK-which you can get from Microsoft-have some of the include files that you will need. Visual C++ supplies the STDLIB.H and STDIO.H include files you need.

Let's Lift the Cowlings Off Those Engines and Take a Peek Inside

Now I'm going to walk you through the code. The first thing you'll see in 5 is five #include compiler directives. For the program to compile correctly, it needs some of the constant and function declarations in those include files. You will also see a series of variable declarations; the include files have definitions of all of the variable types used.

Now I'm going to walk you through the code. The first thing you'll see in Figure 5 is five #include compiler directives. For the program to compile correctly, it needs some of the constant and function declarations in those include files. You will also see a series of variable declarations; the include files have definitions of all of the variable types used.

First, the program makes three malloc function calls. Malloc is an ANSI C function that allocates memory. I use this function because I need to allocate a specific amount of string memory for later use.

Then the program uses the Windows API FindWindow function. The purpose of the FindWindow function is to find a window and return the handle to that window. The handle is a way of uniquely referencing a specific window-kind of like an address-and one of the ODBC functions that I use later will need the handle of the window as one of its parameters.

At this point, the program starts the process of actually connecting to an ODBC driver. There are three steps involved: allocating the environment handle using the SQLAllocEnv function, allocating a connection handle using the SQLAllocConnect function, and connecting to a driver using either the SQLConnect function or the SQLDriverConnect function. Each of the functions has an equivalent function to disconnect from the driver or free the handle: SQLDisconnect, SQLFreeConnect, and SQLFreeEnv. See 6 for a chart detailing the relationships of these function calls.

At this point, the program starts the process of actually connecting to an ODBC driver. There are three steps involved: allocating the environment handle using the SQLAllocEnv function, allocating a connection handle using the SQLAllocConnect function, and connecting to a driver using either the SQLConnect function or the SQLDriverConnect function. Each of the functions has an equivalent function to disconnect from the driver or free the handle: SQLDisconnect, SQLFreeConnect, and SQLFreeEnv. See Figure 6 for a chart detailing the relationships of these function calls.

I used the function SQLDriverConnect-the function to connect to an ODBC driver-that needs the window handle. Passing the function a null value for the connect string tells the driver manager that the program wants the user to select an ODBC driver. The program automatically displays a series of dialog boxes that allow the user to select an installed driver and connect to the database. (For an explanation of drivers, driver managers, and other related issues, see chapter 1 of the Microsoft ODBC 2.0 Programmer's Reference manual.)

After the program successfully connects to a driver, it calls the SQLGetInfo function four times, returning the name of the driver (the dynamic link library (DLL) file name), the driver version, the API conformance level, and the SQL grammar conformance level.

If the return code indicates that the program was able to get the driver name and driver version, it prints those to the window. If the program was able to get the API conformance level, it finds out what level the driver is and prints a text description. The program does the same thing for SQL grammar conformance.

If the driver is API function core level or level 1 compliant, the program calls the function SQLGetFunctions. The program has the function return an array with each element being a true or false value indicating whether the driver supports the corresponding API. I had the program test the functions SQLDrivers, SQLTable-Privileges, and SQLBrowseConnect, which are API level 2 functions. If the driver supports any of the functions, it prints a line for each one it supports.

At this point, the program is done and just needs to clean up after itself. The program calls the SQLDisconnect function to disconnect from the driver. It then calls the SQLFreeConnect and SQLFreeEnv functions to free the memory associated with the connection handle and the environment handle. The program also deallocates the string storage (using the free function) allocated earlier in the program, when it called the malloc function several times. The program ends at the return statement.

Have Many Safe and Happy Flights

The best place to go for more information on this topic is the Microsoft ODBC 2.0 Programmer's Reference manual. It contains a complete list of all the functions and SQL grammar you can test for using the SQLGetFunctions and SQLGetInfo functions. It also discusses additional considerations concerning compliance.

You've seen a C language example of how to use these two powerful functions, but the information applies to most other ODBC capable languages. The program can be translated to Visual Basic, for example. For additional information on translating C function calls to VB, see "PC Support Windows APIs," MC, November 1994.

It's surprising how easy it is to prevent many of the problems you run into when using ODBC. By adding just a few lines of code to your application, you can ensure that the people using the applications you create will be happy to fly the friendly skies of Information Systems Airlines.

Jim Hoopes is a senior technical editor for Midrange Computing.

Reference Microsoft ODBC 2.0 Programmer's Reference.

ODBC Conformance I'm going to discuss API conformance first. If your driver doesn't support the core API functions, you don't really have an ODBC driver, you have a proprietary driver. The core APIs comprise 22 of the 56 API functions and include allocate environment (SQLAllocEnv), execute an SQL statement (SQLExecute), and get a result row (SQLFetch).

Level 1 functions comprise 16 more of the 56 API functions and level 2 functions make up the remaining 18. Examples of level 1 functions include get information about supported data types (SQLGetTypeInfo), get the column names in a table (SQLColumns), and get a list of tables (SQLTables). Examples of level 2 functions include get a list of the installed drivers (SQLDrivers), return the native SQL statement (SQLNativeSql), and get a list of columns that make up the primary key for a table (SQLPrimaryKey).

For a vendor to claim conformance to a given API level, it's driver must support all functions in that level. Even if a driver supports 99 percent of the functions in level 2, it is still not a level 2 driver. The driver must also support all the functions in the previous conformance levels. If a driver vendor claims level 1 conformance, according to Microsoft's standard the driver must also support all core functions. Each API conformance level builds on the previous level.

The second type of conformance is SQL grammar. The minimum SQL grammar conformance level supports, for example, the CREATE and DROP TABLE data definition language (DDL) statements. The data manipulation language (DML) support includes statements like simple SELECT, INSERT, and UPDATE. The expressions supported include simple arithmetic and logical expressions. The data types supported include character (CHAR) and variable character (VARCHAR).

For a driver to be core SQL grammar compliant, it must support all of the minimum SQL grammar and data types. Additional DDL support includes ALTER TABLE, CREATE INDEX, and GRANT. The DML support includes the full SELECT statement. The expressions supported at the core level include sub-queries and functions like average (AVG), and maximum value (MAX), among others. The variable types supported include INTEGER, FLOAT, and REAL.

At the extended SQL grammar level, the driver must support all minimum and core level SQL grammar and data types. The additional DML support includes unions and positioned DELETEs, SELECTs, and UPDATEs. Supported expressions include date, time, and timestamp literals, and functions like absolute value (ABS) and substring (SUBSTRING). The data types supported include BIT, BINARY, and TIMESTAMP.


The ODBC Pre-flight

Figure 1 SQLGetInfo and SQLGetFunctions Syntax

 RtnCode=SQLGetInfo(HDBC, UWORD, PTR, SWORD, SWORD FAR *); HDBC: ODBC Connection Handle UWORD: ODBC Information Type PTR: Pointer To The Value Returned SWORD: Maximum Length Of The Return Value SWORD FAR *: The Number Of Bytes Returned By The Function RtnCode=SQLGetFunctions(HDBC, UWORD, UWORD FAR *); HDBC: ODBC Connection Handle UWORD: ODBC Function UWORD FAR *: TRUE or FALSE return value 
The ODBC Pre-flight

Figure 2 Select Driver Dialog Box

 UNABLE TO REPRODUCE GRAPHICS 
The ODBC Pre-flight

Figure 3 Sample ODBC Connection Window

 UNABLE TO REPRODUCE GRAPHICS 
The ODBC Pre-flight

Figure 4 The ODBC Driver Information Window

 UNABLE TO REPRODUCE GRAPHICS 
The ODBC Pre-flight

Figure 5 GETINFOC.C Program

 All components for this utility are contained in the self-extracting file: GTINFPKG.EXE This file can be found in the download area of the MC-BBS. 
The ODBC Pre-flight

Figure 6 ODBC Connection Function Calls

 UNABLE TO REPRODUCE GRAPHICS 
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: