Upcoming Events

Where the Cloud Touches Down: Simplifying Data Center Infrastructure Management

Thursday, July 25, 2013
10:00 AM PT/1:00 PM ET

In most data centers, DCIM rests on a shaky foundation of manual record keeping and scattered documentation. OpManager replaces data center documentation with a single repository for data, QRCodes for asset tracking, accurate 3D mapping of asset locations, and a configuration management database (CMDB). In this webcast, sponsored by ManageEngine, you will see how a real-world datacenter mapping stored in racktables gets imported into OpManager, which then provides a 3D visualization of where assets actually are. You'll also see how the QR Code generator helps you make the link between real assets and the monitoring world, and how the layered CMDB provides a single point of view for all your configuration data.

Register Now!

A Network Computing Webinar:
SDN First Steps

Thursday, August 8, 2013
11:00 AM PT / 2:00 PM ET

This webinar will help attendees understand the overall concept of SDN and its benefits, describe the different conceptual approaches to SDN, and examine the various technologies, both proprietary and open source, that are emerging. It will also help users decide whether SDN makes sense in their environment, and outline the first steps IT can take for testing SDN technologies.

Register Now!

More Events »

Subscribe to Newsletter

  • Keep up with all of the latest news and analysis on the fast-moving IT industry with Network Computing newsletters.
Sign Up


Data Access Via ODBC and JDBC

ODBC

ODBC is a multidatabase API for programs that use SQL statements to access data. An ODBC-based program can access heterogeneous databases without needing source code changes-one program can retrieve and store content in different vendors' databases via the ODBC interface. ODBC thus provides database-neutral delivery of both SQL and database content. Be aware, however, that you must load ODBC driver software for each vendor's database you want to access. Contrast this situation with that of the generic data access tools mentioned earlier.

ODBC Basics

Microsoft created ODBC in 1992 by extending the CLI (call level interface) from SAG (SQL Access Group, now part of X/Open). It gained acceptance over Borland's Integrated Database Application Programming Interface (IDAPI). The ANSI and ISO (International Standards Organization) adopted an updated version of that CLI as part of the SQL-92 standard, and ODBC version 3.0 aligns with that standard. ODBC drivers give applications a number of methods they can use to access databases, and these drivers also give tools the ability to retrieve and update metadata-the higher level of information in the database that specifies table names, column names, column attributes and other management data. However, because they're designed for a particular vendor's database product, vendor-specific database access middleware drivers give applications and tools an even greater ability to manage metadata within the database. ODBC is a lowest-common-denominator approach to database connectivity.

Database neutrality, multiple platform support and its adoption as a standard are the reasons data processing managers, system designers, and programmers like ODBC. The database product vendors as well as several third-party vendors give away or sell ODBC middleware drivers for a variety of operating environments and databases. ODBC is, by far, the most popular database access middleware in use today.


ODBC as a Layer of Middleware

ODBC defines the client side of database connectivity but not the server side, and ODBC middleware drivers typically rely on the underlying presence of a vendor's proprietary driver (SQL*Net, in the case of Oracle). ODBC drivers transform ODBC calls into vendor-specific access requests and responses. As a result, network administrators must install and configure not only an ODBC driver on each client but the underlying vendor-specific proprietary driver. Another result of ODBC's transforming requests and responses into vendor-specific data access calls is a lack of identity for ODBC network messages to and from the database server. If you used a protocol analyzer to capture database server network traffic, you'd find distinguishing ODBC network messages from vendor-specific driver messages difficult.

ODBC's addition of an extra layer of insulating middleware is both its strength and its weakness. ODBC presents a common, standard interface no matter what the vendor-specific middleware might look like, but ODBC consumes some memory and, in its earliest incarnations, slowed data access noticeably. Current offerings from companies like INTERSOLV, Visigenic Software, and Information Builders exhibit considerable performance improvements over previous versions and have consistent feature support across all major database products.

ODBC's network utilization depends more on the underlying vendor-specific layers of middleware, such as Oracle's SQL*Net, than on the ODBC driver itself. In particular, Oracle's layer of database access middleware beneath ODBC causes the least network traffic, while Microsoft's DB-Library connection to its SQL Server database causes the most. Network protocols can make quite a difference, too. NetBEUI is the "chattiest" protocol, IPX/SPX considerably less chatty, and TCP/IP the most frugal user of the network.

ODBC Conformance Levels

The ODBC standard specifies conformance levels for both the programming interface and SQL statements delivered to the database via ODBC. These levels are 0, 1, and 2 for the API, and minimum, core, and extended for the SQL grammar. The conformance levels for the API and the SQL grammar are independent, making it technically possible for a driver to provide level 2 API support while supplying extended-level SQL grammar support.

ODBC conformance level 0 for the API supplies basic, rudimentary support for connecting to databases, executing SQL statements, retrieving data, and committing or rolling back a transaction. Level 1 adds the ability to access part or all of a result column value (useful for long data-i.e., a large amount of data), retrieve information about driver and database capabilities, and manipulate metadata. Finally, level 3 augments ODBC with scrollable cursors, further metadata access (including information about privileges), and parameter arrays.

For SQL grammar support, the minimum SQL conformance level specifies common, basic DDL (Data Definition Language) and DML (Database Management Language) support. SQL commands such as select, update, delete, insert, create table and drop table are examples of those at the minimum SQL conformance level. The core SQL conformance level adds further DML grammar, such as alter table, create index, drop index, create view, drop view, grant and revoke. It also specifies a wider range of data types, including decimal, numeric, smallint, integer, real, float and double precision. A driver offering minimum SQL conformance must allow expressions, such as sum and min. The extended SQL conformance level says an ODBC driver must support more esoteric DML, such as for outer joins, positioned update, positioned delete, select for update and unions. It also calls for the driver to offer scalar functions, such as substring, abs, date, time and timestamp as well as data types, such as bit, tinyint, bigint, binary, varbinary, long varbinary, date, time and timestamp. Extended level drivers also allow client programs to execute stored procedures within the database.

ODBC's Usefulness

ODBC drivers provide access to more than just multiuser relational databases. ODBC drivers exist for Microsoft's Access and FoxPro, Borland's Paradox and other desktop PC data sources. Many word processors and spreadsheets have ODBC support, as does Lotus Notes. Even some document management systems have ODBC interfaces. These examples notwithstanding, probably the most common use of ODBC is in client/server applications.

A typical client/server approach has program and data files on a file server, accesses a relational database and often has a two-tier architecture. Both presentation and business logic occur on the client, and data storage occurs on the database server. In contrast, a Web-based approach puts presentation software at the client (browser), business logic on an application server and data storage on yet another server-a three-tier architecture. While not all designs are as simple as this characterization portrays, you might think of the client/server versus Web approaches in terms of two-tier versus three-tier. Client/server applications frequently rely on ODBC for database connectivity, primarily because development environments such as Visual Basic and Delphi provide easy-to-use programming templates and easy-to-understand interface descriptions. If the computer programs running on an application server in a Web-enabled environment are written in Visual Basic, Delphi, or other non-Java language, they likely also use ODBC for their database accesses. Web-enabled applications written in Java, on the other hand, almost always use JDBC.

Configuring ODBC

Application designers and programmers like ODBC's simple interface, but its Achilles' heel is the difficulty of setup and configuration. Though giving a single client computer ODBC access to a database isn't onerous-Microsoft and other vendors provide a series of graphical windows for entering configuration data-setting up a large number of client computers can be quite tedious.

Two methods exist for configuring ODBC on a client computer. The first uses the ODBC entry in the control panel of a Windows 3.1, Windows95, or Windows NT Workstation machine. The other method involves editing INI files (Windows 3.1) or registry files (Windows95 and NT) directly.

Using either method commonly requires taking all or substantially all of the following steps: An administrator assigns a name to the connection between the client and a particular database server (i.e., its DSN [Data Source Name]), identifies the database server computer and the database on the server, provides the network address of the database server and specifies the account ID and password that will authenticate the connection to the database server.

In Windows95 or NT registry files, the keys relevant to ODBC settings are similar to the following two examples:

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Pubs

The following example illustrates an ODBC.INI file for a Windows 3.1 machine that accesses a Microsoft Access data source:

[ODBC Data Sources]
MS Access Databases=Access Data (*.mdb)
MS Access 2.0 Databases=Access 2.0 for MS Office (*.mdb)

[MS Access Databases]
Driver=C:\WINDOWS\SYSTEM\SIMBA.DLL
FileType=RedISAM
SingleUser=False
UseSystemDB=False

[MS Access 2.0 Databases]
Driver=C:\WINDOWS\SYSTEM\ODBCJT16.DLL
DBQ=FLDBEHAV.MDB
DefaultDir=F:\ACCESS
FIL=Microsoft Access
JetIniPath=MSACC20.INI
UID=Admin


Print This Page


e-mail E-mail this URL
Vendor Comparisons
Network Computing’s Vendor Comparisons provide extensive details on products and services, including downloadable feature matrices. Our categories include:

Research and Reports

Network Computing: April 2013



TechWeb Careers