Dr. Dobb's Sourcebook May/June 1997
As I write this, some truths from my early career are evident today. Donald Knuth is writing a volume of The Art of Computer Programming. George Foreman is a top-ranked heavyweight fighter. Mick Jagger, Eric Clapton, and Tina Turner perform before huge audiences. IBM is by far the largest company in the computer industry.
Plus ça change, plus c'est la meme chose.
Are we to assume from this state of affairs that the operative words we use to describe database programming will always start with constants? The French say, "The more things change, the more they remain the same." Is that a reasonable mindset for today's database developer?
If you have written SQL programs using C or C++, then you are probably familiar with using header files and including constants. Examples include SQLTYPES.H used with Informix ESQL and CSTYPES.H used with Sybase Open Client CT-Library. For programming with Oracle Call Interface, you probably have a header file including prototypes for oopen(), oclose(), ologon(), and other functions. Will information remain constant so that programs compiled with those headers will be good for another generation? Consider the header shown in Example 1.
If we limit our view to the first three lines, it appears the header is as valid today as it was a few decades ago. The final line, however, reminds us that, because not everything is constant, we will need to recompile any source code using that header. Is this example representative of the situation we will find when writing programs that access databases? Consider a few illustrations. A header created to define Paradox 3.0 data types is out of date for today's Paradox. A program that enumerates, in source code, a list of the functions in the Open Database Connectivity (ODBC) API is facing obsolescence as ODBC 3.0 supersedes earlier versions.
With respect to today's database-management systems (DBMS), you can say that types, APIs, and features are not constant. Because the DBMS marketplace is fiercely competitive, vendors must constantly develop new versions with added capabilities. The recent flurry of activity centered on the Web is another example of the pressure to add features to SQL products. The interest in Internet technology caused database companies to focus on universal connectivity, rich data types, and delivering HTML to web browsers. The good news is that the Web promotes connectivity. The bad news is that it emphasizes a "one size fits all" mentality that is not the most realistic mindset for programming with today's DBMS tools, even with multidatabase APIs.
Consider the situation of SQL databases and stored procedures, also known as persistent SQL modules. Procedures are optimized SQL scripts or modules stored in the database. They reside at the server and are accessible to clients. Although stored procedures are likely to be part of a future SQL3 standard, they are not defined by today's SQL standard. This means that support for stored procedures has been subject to change and variance in vendor implementations, even across generations of the same product. For example, procedures were not a part of Watcom SQL 3.2 but they are available with its successors, Watcom 4.0 and Sybase SQL Anywhere. If you have applications that originally used Watcom 3.2, you have probably gone through at least one round of code changes related to SQL procedures. Those revisions are unlikely to be your last changes because the race is on for SQL vendors to use Java as a stored procedure language.
The invariant nature of DBMS products and versions means that developers must continually update database programs to keep up with new types, API revisions, and new features. Skilled developers often try to minimize the amount of source code that is subject to change by writing programs at a level of abstraction that treats the database as a "black box."
The venerable black box is a solution for dealing with inconstancy and implementation differences. Because it is a useful abstraction that isolates code affected by implementation variances, it is often a solution for writing software that must operate on multiple platforms. The UNIX kernel and Windows NT Hardware Abstraction Layer are classic examples of the black box concept. Most of the code in these operating systems is portable because it treats the hardware-specific logic as a black box. Virtual machines, such as Java's, are a variation of the same theme, so Java classes are hardware-independent and portable. Programmers use Java APIs with a virtual machine -- a black box having uniform behavior across platforms. (Java implementations are still imperfect but, in time, Java should satisfy black box purists.)
Database programmers often view ODBC and Java Database Connectivity (JDBC) as APIs for a virtual database. JDBC and ODBC operate on a variety of databases and they are available for a variety of operating systems. Even though ODBC and JDBC are multidatabase and multiplatform, they are not the equal of Java in the extent to which they implement black box behavior. The difference between Java and database APIs is that a database programmer can not expect uniform behavior across servers and engines.
The problems of using portable SQL and creating logic that works on multiple platforms is not unlike the current situation with web browsers and HTML authoring. You must evaluate the tradeoff between using standards with maximum portability or using extensions that deliver capabilities at the price of sacrificing portability. One advantage of JDBC and ODBC is that they are multidatabase APIs, so they include features to permit your program to adapt to its environment and make choices about using features.
Except for very simple applications, an ODBC or JDBC program should not be written as though the DBMS is a black box having defined behavior. Loadable database drivers permit JDBC and ODBC programs to connect to a variety of SQL databases but neither API guarantees consistent behavior from the drivers, network transport, and SQL engine. For example, ODBC provides a function to set a connection option (ODBC 2.0) or attribute (ODBC 3.0) that enables asynchronous processing. Asynchronous mode means that a client application exhibits nonblocking behavior, permitting a user to do other work while waiting for a query to complete. Setting that option does not mean that your program will automatically operate in asynchronous mode with every DBMS and database to which it connects. Some network libraries do not support asynchronous processing, and calling the ODBC function won't overcome this limitation.
One of the misconceptions that I try to correct in my database programming workshops is that JDBC and ODBC do not mandate features. The erroneous expectation of some developers is that a program using ODBC or JDBC is guaranteed certain behavior by the DBMS. For example, because ODBC 2.0 defined constants for 19 types, some developers assumed that they could use those types with every ODBC driver and DBMS. The reality is that a driver and DBMS must support only a character data type in order to conform to the minimum ODBC SQL grammar.
Instead of mandating features, the ODBC and JDBC APIs report what features are available from a DBMS and its driver. ODBC includes functions and JDBC includes methods that give your program the ability to determine, after connecting to a database, what its capabilities are. Developers can use run-time interrogation techniques, or introspection, to write programs that adapt to the currently available SQL dialect, types, API functions, and other capabilities. Because your program does this at execution time instead of compile time, it might not require changes to work with new DBMS versions. There is no guarantee that you can insulate your program completely from DBMS updates, but for many changes, you won't have to rewrite code. Adaptive programming is also useful for writing applications that operate with a heterogeneous mix of databases and drivers. Crystal Reports and Microsoft Access are examples of applications that use adaptive logic to operate as a client of a variety of SQL servers.
ODBC is a call-level interface that supports introspection with functions that report features (SQLGetInfo), types (SQLGetTypeInfo), procedures (SQLProcedures), API (SQLGetFunctions), and other information about the database. JDBC consists of interfaces and classes that permit a developer to use objects while accessing SQL databases. To access data with either API, a developer uses SQL statements and works with result sets. It should be no surprise that JDBC uses objects such as connections (java.sql.connection) and statements (java.sql.statement). ODBC does not provide the objects and classes, but it uses handles to keep track of connections and statements, and information related to each.
JDBC includes classes that provide metadata or catalog information about the database and query result sets. The database metadata class (java.sql.DatabaseMetaData) encapsulates much of the data that compares to the values returned from calling ODBC functions such as SQLGetTypeInfo and SQLGetInfo. In simple terms, it is this class that provides much of the information about how one DBMS differs from another. JDBC metadata methods can return integers, strings, Boolean values, and other types. JDBC methods, like ODBC functions, can return metadata as result sets. ODBC and JDBC programs often use a similar programming paradigm to process data requests and metadata requests.
Developers writing multidatabase code should not overlook the fact that types are not constant. This is true if one compares product to product, or even different versions of the same product. Given this fact, we can see that programs using hard-coded lists of types can become obsolete. Instead of coding and compiling programs to use a static list of types, consider using ODBC and JDBC APIs that support execution-time reporting of type information. The JDBC DatabaseMetaData class provides type information in a manner similar to the ODBC SQLGetTypeInfo function. Both APIs return type information as a result set with columns that describe a variety of information about types. One of the most important columns is the type name (TYPE_NAME), because this is the name you must use in SQL statements. ODBC and JDBC understand a defined list of SQL data types including character, binary, date, decimal, double, float, integer, numeric, real, time, timestamp, long varchar, and others. The result set identifies which of those SQL data types is available when your program connects to the current driver. The result set returns the maximum precision of the data type, its minimum scale, maximum scale, and whether scale is applicable for a type. The result set also indicates whether a data type accepts a null value, whether it is case sensitive or a money data type, and how the type is usable in SQL WHERE clauses. Other information indicates whether the type is autoincrementing and whether the type is signed, unsigned, or signs are not applicable for the type.
You may be wondering why it is necessary or even useful for an API to provide type information at execution time. There are a variety of reasons that come under the headings of flexibility and avoiding obsolescence. Database products change and new releases of a product often add a richer variety of types. If you create constants to define types at compile time, you will find that your code is likely to require recompilation and relinking when a new release of your DBMS adds types. You will encounter a similar situation if you want to use your source code with heterogeneous databases supporting a variety of types.
Another scenario is the need to write a program that creates tables for different databases or prompts the user for the structure of a table to create. To build the SQL CREATE TABLE statement, your program needs the name to use for the type of each column in the table. The type name must be one that is valid for the SQL dialect of the database to which it is connected. One typical technique is to display a listbox and permit the user to choose which type to use for each column. To ensure that the type list you display is not out of date, you should construct the list at run time instead of defining the types in your source code. To dynamically create the type list, you'll find it useful to understand the ODBC SQLGetTypeInfo function or the JDBC getTypeInfo method.
Listing One is an example program (TYPEINFO.JAVA) that demonstrates the use of the getTypeInfo method of the JDBC DatabaseMetaData class. Because TYPEINFO is a Java application, it includes a main class. You execute TYPEINFO by using the Java Virtual Machine, as opposed to using an applet that a browser executes. The command line that I am currently using to invoke the Java Virtual Machine to execute TYPEINFO is java -classpathE:\java\jdbc\classes;E:\lib;E:\lib\java\lib\classes.zip; GetTypes. TYPEINFO uses the JDBC-ODBC Bridge (jointly developed by Sun and Intersolv) to connect to ODBC data sources, then instantiates a result set that will contain the database metadata. It then sits in a loop getting rows of information about the types supported by the driver connecting to the jdbc:odbc:SSPer URL. When you run TYPEINFO, it reports the names and SQL types that a database provides. TYPEINFO connects to an ODBC data source (SSPer) configured on my network to use a Microsoft SQL Server database. If you run TYPEINFO and connect to an SQL server, you will typically see a longer list of types than you will see from a desktop product such as dBASE. Running TYPEINFO and connecting to SQL Server produces a type list that includes bit, image, timestamp, date/time, and money data types.
In a perfect world, the black box technique operates like an electrical outlet. You plug in to a receptacle and your program works without requiring any additional intelligence. If you need solutions for writing interoperable SQL programs you will find that ODBC and JDBC are useful technology. They do not deliver 100 percent of the promise of the black box and they don't produce a black box effect that is comparable to the Java APIs and the Java Virtual Machine. However, ODBC and JDBC do include functions and methods, respectively, that are useful for writing portable database code.
DDJ
/****************************************************************************
* FILE NAME: typeinfo.java TITLE: Get available types for DBMS
* AUTHOR: K. E. North II, Ken North Computing
* from Database Magic with Java (Prentice-Hall, 1997)
* Copyright (c) Kendall E. North II, 1997. All rights reserved. Reproduction
* or translation of this work beyond that permitted in Section 117 of the
* United States Copyright Act without express written permission of the
* copyright owner is unlawful.
* The purchaser may make backup copies for his/her own use only and not for
* distribution or resale. The Author and Publisher assume no responsibility
* errors, omissions or damages caused by the use of these programs or from
* use of the information contained herein.
****************************************************************************/
import java.sql.*;
class TypeInfo {
public static void main(String argv[]) {
try {
// load the JDBC-ODBC bridge
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
// specify the ODBC data source's URL
String url = "jdbc:odbc:SSPer";
// connect to data source
Connection con = DriverManager.getConnection(url,"North","Ken");
// get DB metadata (types) for this connection
DatabaseMetaData dbmeta = con.getMetaData();
ResultSet rs = dbmeta.getTypeInfo();
System.out.println("Class is TypeInfo");
// traverse through types
System.out.println("Type information:");
while (rs.next())
{
// get type result set information
String typeName = rs.getString("TYPE_NAME");
short SQLType = rs.getShort("DATA_TYPE");
int Precision = rs.getInt("PRECISION");
// print values
System.out.print (" Type Name=" + typeName);
System.out.print (" SQL Type=" + SQLType);
System.out.print (" Precision=" + Precision);
System.out.print(" \n");
}
// close connection
con.close();
} catch (java.lang.Exception ex)
{
ex.printStackTrace();
}
}
}