Connectivity
with the
BASIS ODBC DRIVERÖ

---

A White Paper

December 15, 1995

ELUSIVE TIME MANAGEMENT

What problem does Microsoft's Open Database Connectivity API address for the busy end-user?

Lack of Time!

It's as simple as that. Until the ODBC API became an accepted industry standard, overworked end-users were faced with one big problem: how to access their data. Historically, developers built database applications that accessed a single data source. As companies grew, so did their data sources. Developers and end-users were faced with multiple databases across multiple platforms with no easy way to consolidate the disparate data. Enterprise-wide information was virtually impossible to get at quickly. And in corporate scenarios, time is money.

In the past, busy corporate executives and overburdened clerks had to learn, in detail, how to use three or four different reporting tools, one for each in-house database.

ODBC changes all that. In today's down-sized corporations, no one has the time for labor-intensive reporting. ODBC gives users transparent access to their corporate databases and lets them use their favorite reporting tool to do it. The focus is on pulling information, not learning another reporting tool. Most users are comfortable with one spreadsheet, report writer, or database. With ODBC, they can quickly build their own reports and save time.

With ODBC technology, MIS staffs no longer have to train and maintain four or five reporting tools.

With ODBC, the developer no longer has to build a front-end tool for each DBMS or port across multiple platforms. ODBC lets you concentrate on the logic of your application, not the finer points of the user interface. This means more profit for you.

• WHAT IS ODBC?
• THE ODBC ARCHITECTURE
• ODBC SPECIFICS
• ODBC TIERS
• THE BASIS ODBC DRIVER 16-bit
• CLIENT/SERVER
• ODBC/SQL SUPPORT
• REQUIREMENTS
• FREQUENTLY ASKED QUESTIONS
• CONCLUSION

ODBC is a single common API that hides the differences between DBMS from the end-user. Front-end applications do not need to be highly customized. Instead they call a single ODBC API, and send requests to the DBMS through the platform-independent ODBC Driver.

ODBC is supported by more than 70 DBMS vendors on Windows iterations, OS/2, Macintosh, and major UNIX platforms. ODBC is also supported by several hundred client applications.

The ODBC standard was created by Microsoft and other database developers who based its specifications on the Call Level Interface of the X/Open SQL Access Group. The aim was to overcome the proprietary interfaces which various DBMS utilized so that the possibility of an open system could reach fruition.

One standard that did exist was Structured Query Language (SQL). This is a standard programming language for relational database applications. Though SQL was intended to be a standard language for developers, many database applications created their own versions of SQL. In developing ODBC, the involved parties settled on a common syntax of SQL in order to speak with the DBMS and then followed with a standard for communicating that language to the DBMS.

ODBC is a communication standard which passes SQL syntax commands to databases. It can access relational, flat, and legacy databases across multiple platforms. And since ODBC is an ôopenö API, it isolates the end-user from any changes or upgrades to the underlying data source. For example, if the MIS department upgrades a DOS data source to a Windows data source, a drastic change in the query API is usually required. But if ODBC is supported, the end-user will never know that there has been the alteration in data sources.

The base is the data source (DBMS). The data source can be a flat-file database, a relational database, or a legacy database.

The second level, the ODBC driver, is installed on each PC that will be making ODBC compliant queries. It translates all queries into a language that the data source will understand and once a query is made, translates this proprietary information back into an ODBC (SQL) compliant result set understood by the client application. The ODBC driver also immediately processes updates, deletes, and inserts if the data source and driver offer rollback/commit functionality.

Above the ODBC driver is the ODBC driver manager. The manager essentially maps a data source to an ODBC driver so that the end-user can connect front-end applications to available ODBC drivers installed on each PC. Among its other functions is to provide parameter and sequence validation for ODBC calls and process ODBC initialization calls. There is a single ODBC manager for each platform. In Windows, a special ODBC DLL provides ODBC capabilities. On other systems, the ODBC driver vendor must include a copy of the ODBC manager.

At the interface level is the ODBC-compliant query tool. The end-user makes queries, pulls information and sends it back to the database from here. This client application can be a report writer, spreadsheet, database, or other ODBC application.

The API and SQL Grammar

• Core API must include the following: Allocate resources, connect to data sources, prepare and execute statements, retrieve data from a result set, commit or rollback execution, and retrieve error information.
• Level 1 API must include Core functionality and the following: retrieve information about data source capabilities, retrieve catalog information, set statement and connection options, and retrieve or set all or part of parameter or column values.
• Level 2 API must include Core and Level 1 functionality and the following: ability to browse connection information, send and retrieve arrays of parameter values, describe those parameter values, retrieve DBMS native SQL, and call a translation DLL.

• Minimum SQL Grammar includes the following: CREATE and DROP TABLE, basic SELECT, INSERT, UPDATE, and DELETE.
• Core SQL Grammar includes Minimum plus the following: CREATE and DROP INDEX, CREATE and DROP VIEW, GRANT and REVOKE, full SELECT, subqueries, and aggregate functions.
• Extended SQL Grammar includes Minimum and Core plus the following: outer joins, positioned UPDATE and DELETE, SELECT FOR UPDATE, and UNION.

The above grammar is not the end of ODBC SQL options. Using SQL pass-through capabilities, you can execute statements that conform to the SQL dialect of the particular DBMS.

There are two basic tier systems in the ODBC API, each of which attempts to address the two basic DBMS scenarios in the client/server world.

1. The single-tier system: In this scenario, the ODBC driver accesses the data source directly. The driver itself acts as the relational database engine processing the SQL statements coming from the querying application. Typically this scenario is utilized because the underlying data source is not relational nor does it have any native understanding of SQL statements. This means that data processing takes place on the client, not the server.
2. The multi-tier system: This scenario works in a similar manner to the single-tier system. The ODBC driver still connects directly to the data source; however, it acts more as a conduit. The DBMS speaks a native SQL, and the ODBC driver translates standard SQL statements from the client application into DBMS-native SQL. The data source processes the SQL statements and returns the results to the ODBC Driver which re-translates the native SQL information into standard SQL results for the querying tool to review. All processing takes place on the server, not the client as in the single-tier system.

BASIS is leveraging ODBC technology in two ways. First, the new release of BB^X«, called Visual PRO/5Ö, incorporates the ability to access foreign data via its own ODBC capabilities. Visual PRO/5 is not limited to accessing only BB^X data. Using its new SQL commands or its Flat File Interface you can access any data source where an ODBC driver is available. With Visual PRO/5's new capabilities, you, the developer, no longer need to create multiple client applications to access each of the proprietary DBMS your clients possess. Visual PRO/5 is the tool to make your development job easier.

The BASIS ODBC Driver addresses the problem of assimilating data. If you are accessing legacy BB^X data, you do not need to worry about creating the front-end tools to get at that data. You can focus your time on developing the logic of the application and spend more time selling instead of building user interfaces. Currently the market is saturated with effective reporting tools. More than 140 vendors support ODBC in over 200 applications.

You are not limited to producing only Windows applications. The BASIS ODBC Driver comes client/server ready. You can access your legacy data that resides on a UNIX server by providing the server path in your data definition and setting up a PRO/5 Data ServerÖ on the UNIX side.

In this scenario we use the powerful GUI capabilities of Windows to exploit the data storage and integrity of UNIX. Of course if your data resides on the Windows side, you have immediate access to it without the need for a PRO/5 Data Server.

The old solution to this problem was to down-load application data to the Windows system and then laboriously convert the data to an appropriate third-party tool format. This introduced a host of new problems: data conversion, data consistency, network traffic, and integration issues. Leaving your data on the UNIX host and accessing it with the BASIS ODBC Driver eliminates all of these problems and provides up-to-the-minute information to the Windows workstation. You'll never have to examine day-old data again.

In order to give your query tools sophisticated access to BB^X data, the BASIS ODBC Driver has been developed in accordance with the Level 1 API and supports MINIMUM ODBC grammar. The BASIS ODBC Driver supports:

• CREATE TABLE & DROP TABLE
• SELECT
• INSERT
• UPDATE & DELETE

The BASIS ODBC Driver also includes a READ-ONLY toggle which allows the developer to give READ access to some data sources and READ/WRITE access to other data sources.

In addition, you may monitor events in your ODBC setup by including a LOG file. Just type a path and file name for the LOG file. You can review the LOG file with any text editor.

Running in auto-commit mode, the Driver instantly acts upon updates, inserts, and deletes.

In order for the BASIS ODBC Driver to run, you need to install a copy of the driver on each PC. Also, data files must be normalized in a BASIS Data Dictionary which holds a central description for BB^X tables in the database.

The BASIS ODBC Driver supports Windows 3.1x, Windows for Workgroups, Windows 95, and Windows NT. The Driver also comes client ready. In order to communicate between your Windows applications and BB^X data on UNIX-OS platforms with a PRO/5 Server, just type a username in the USERID section of the driver setup.

FREQUENTLY ASKED QUESTIONS
	Do I need PRO/5 to run this?
	No, but you will need BBX or PRO/5 data files and a BASIS Data Dictionary.
	How is it sold and where is it loaded?
	One copy of the BASIS ODBC Driver must be licensed and installed for each workstation that will connect with ODBC-compliant data sources.
	How do I access my data on a UNIX machine?
	The BASIS ODBC Driver comes client-ready. This means that you can access any UNIX-based PRO/5 application by setting up a PRO/5 Data Server.
	Is it both READ & WRITE?
	Yes, but there is a READ-ONLY toggle so you can determine which privileges your users will have.
	Isn't ODBC slow?
	The initial ODBC 1.0 API was incomplete and many ODBC drivers were not optimally tuned. The ODBC 2.0 API is much tighter and the speed loss compared to native calls is negligible.
	Isn't ODBC the lowest common denominator, therefore not as effective as native calls?
	ODBC provides 3 levels of SQL grammar. This gives your applications as much or more functionality as native calls to a database. Plus, you can use any ODBC compliant front-end application to connect to many different databases, in effect, making you more productive since you don't have to learn multiple query-tool interfaces.

The BASIS ODBC Driver is the solution to your end-users time management problems and your support headaches. Busy executives and overburdened clerks no longer have to learn numerous tools for one job. The BASIS ODBC Driver lets companies maximize their software and their investment.

Once you set up your end users with ODBC drivers, they can spend their time evaluating their data in their favorite tool, not trying to find it. And you, the developer, can perfect and improve your applications with minimal support costs as users utilize client applications they already understand.

Armed with the BASIS ODBC Driver, you open up your existing BB^X data to sophisticated information retrieval and exploitation. There are no limitations to BB^X data access with the BASIS ODBC Driver. You can easily access data with off-the-shelf productivity tools and that is what open systems is all about: it is your data and you should be able to retrieve and manipulate it using the best tool for the job. Now, you can!