Using JDBC metadata, it is now possible to analyze databases and automate database tasks with ColdFusion MX in ways that were problematic or at best difficult in the past. Although Java can be rather intimidating, you don't need to be a Java expert (or a database expert) to use these tools.

"Metadata" is all the information about how your data is stored (such as table names, column names, data types and sizes) as opposed to the data itself (such as names of people and meeting or appointment dates). All databases must store this information to function and many databases expose metadata (usually via SQL), although with each database making its own decisions about how the data is exposed, writing database-agnostic tools using SQL could be an arduous, frustrating, and often fruitless task.

Prior to ColdFusion MX the popular means of connecting to databases involved the Open Database Connectivity (ODBC) standard, which used an API implemented in the operating system to connect applications to their databases for both executing SQL and retrieving metadata. While metadata is available through ODBC, it remains difficult to implement using C++ and typically via the recently deprecated COM standard. With the introduction of CFMX the supported means of connecting to databases became the Java Database Connectivity (JDBC) standard, which includes a similarly robust API for retrieving metadata that is much more accessible. (Connection to ODBC datasources is still supported, although it requires the use of a JDBC-ODBC bridge driver.) The additional abstraction provided by Java and the new JDBC standard allows us to use a common syntax for all our metadata tasks with any database supporting the standard and easily implemented in simple CFML tags or functions.

For example, you might create a function or a CFC method to fetch all the column names, data types, sizes, and nullability of columns in a specified database table. This is just one example of a number of JDBC-driven features I've implemented in the onTap Framework (described in "Features Without Fixtures" in CFDJ, Vol. 6, Issue 4), and is merely scratching the surface of the JDBC metadata potential.

Before You Leap
Before you make the decision to leverage JDBC metadata in your applications, there are a number of things to consider.

First, the technique described in this article requires use of the coldfusion.server.ServiceFactory object. You may recognize this object from some other essays, articles, and mailing lists. This is one of the key objects the ColdFusion server uses internally to manage its processes and has been the subject of much discussion since the release of ColdFusion MX. Because this class is not a documented feature of CFML or the ColdFusion server, Macromedia won't provide support for any CF software using it, and won't guarantee the availability of these features in the future. This is the primary concern with regard to using JDBC directly in your CF applications as described in this article, and may be the deciding factor in determining if you want to use these features in a specific project. Although these features are undocumented and not supported by Macromedia, it's important to note that JDBC is a well-defined standard and the ColdFusion Server Administrator application uses these same features for managing the server's DSNs. While this doesn't guarantee that these features will not change it is likely that the same functionality will be reproducible with any future versions of the ColdFusion Server.

Because ODBC includes no specifications for metadata, the techniques described in this article are not compatible with the ColdFusion Server's ODBC-Bridge drivers.

Unfortunately I also have some bad news for users of Microsoft Access databases. The MS Access database doesn't conform to the standards for JDBC, requiring the use of ODBC to make connections to Access even when the ODBC-Bridge drivers are not being used. Because the ODBC Bridge driver included with ColdFusion MX doesn't implement all the JDBC metadata features, the available features when using MS Access are particularly limited. I have been able to retrieve a list of supported table-types in an Access database, but not tables or columns. Metadata can be retrieved from Access by querying its hidden tables (which also provides rather limited information) or by implementing a separate COM solution - although this was recently deprecated by Microsoft so providing common syntax for this may be quite a challenge.

Finally because this technique requires creating a connection to your database directly through Java (bypassing the ColdFusion Server), you need to include the database name (in addition to the DSN), username, and password of your database in your application code. Generally speaking this should be a trivial consideration, although some developers may dislike it as a matter of style partly because some of this information must also be included in the ColdFusion Administrator, increasing the number of independent locations where the information is stored and must be updated when changed. Using this information in the application may be similar to using DSN-less database connections with ColdFusion 5 and a connection-string, although this feature is not implemented in CFMX.

Java and Databases: An Introduction
If you don't have much experience with Java and you're thinking this article might be a bit over your head, don't worry. Although Java can be rather intimidating, you don't need to be a Java expert (or a database expert) to use these tools.

For the purpose of this article it's important that you understand the distinction between a Data Source Name (DSN), a Relational Database Management System (RDBMS), and a catalog or database. An RDBMS is a database server such as SQL Server, Oracle, Sybase or PostgreSQL. Each RDBMS installation may (and almost always does) contain multiple individual databases. In RDBMS parlance individual databases are often known as "catalogs" (called "databases" in SQL Server, and "schemas" in Oracle). By contrast the DSN is the name given to a connection pointing to an individual database or catalog (within a specific RDBMS installation) in the ColdFusion Server Administrator or J2EE application server. A DSN is often the same as the name of the database or catalog within the RDBMS, although the two are not related and not required to match. It is also important to note that catalogs or databases also include what are known as "schemas." A schema is simply a codified collection of tables, views, stored procedures, and other items within an individual database. Usually an individual database will contain or use only one schema (for instance, with Microsoft SQL Server all data is usually stored in a "dbo" schema).

Beyond this database knowledge, these terms may also be helpful in understanding the Java involved in this process:

• OO or OOP (Object-Oriented Programming): This article will not focus on OOP at large (a subject on which many entire books have been written). All you need to know is that OOP is the philosophy of Java and the origin of the terms Class, Object, Method, and Interface.
• Class: A class is a blueprint (or template) for creating objects. An object is a complex data type containing both variables (also called "properties") and functions (also called "methods"). Class functionality in ColdFusion is provided by ColdFusion Components (CFCs).
• Object: An object (sometimes called an "instance") is a variable created using a class as its blueprint. Thus all objects of a specific class have the same properties (variables) and methods (functions) although the properties of individual objects or instances may have different values. Objects are created in CFML using the cfobject tag or the CreateObject function.
• Method: A method is a function within a class and its objects. Methods in CFCs are often referred to as "CFC functions".
• Interface: A public definition of properties and methods required by certain classes. In simple terms an interface contains a list of properties and methods that certain classes must contain. A more in-depth understanding of interfaces is not necessary for this article.

Getting Connected, Getting Results
Although CFML included simple tools for executing SQL queries from its earliest days, connecting to a database to do anything other than manipulate data has never been addressed. Simplified native tools to do this in CFML (like the cfquery tag) do not yet and may never exist, so arguably the best solution for retrieving database metadata lies in Java. To begin our journey into the land of JDBC we must start by creating a Java object (a process sometimes referred to as "instantiating a class"). Create a new ColdFusion template called jdbc.cfm. Place the following lines of code in this new template and execute it:

<cfscript>
dss = CreateObject("java",
"coldfusion.server.ServiceFactory");
dss = dss.getDataSourceService();
</cfscript>
<cfdump var="#dss#">

The output of the cfdump tag should look like Figure 1. This is how the ColdFusion server's cfdump tag displays a Java object. At the top you'll see "object of coldfusion.sql.Executive". This tells you that the variable dumped (dss) is an object and that the object uses the coldfusion.sql.Executive class as its blueprint. To retrieve metadata from your database, you'll need to use the dss object you've just created to create a metadata object by adding this code to your jdbc.cfm template:

<cfscript>
conn = dss.getDataSource("yourDSN");
conn = conn.getConnection("username","password");
mdata = conn.getMetaData();
</cfscript>
<cfdump var="#mdata#">

In this code, yourDSN is the name of your data source as specified in the ColdFusion Server Administrator. Username and password are the username and password required to access the database. Although you can omit username and password when executing queries using cfquery (when the username and password are stored in the ColdFusion Server Administrator DSN definition), you can't here. The username and password of your database must be used in the getConnection() method, otherwise your Java object will not connect to your database. The mdata variable you've created is an Object that now displays a class name of macromedia.jdbc.base.BaseDatabaseMetaData.

At this point you might want to open a new browser and view the documentation for JDBC on the Sun Microsystems Web site: java.sun.com/j2se/1.4.2/docs/api/java/sql/package-summary.html

At the top of this page, you should see the words "Package java.sql". Don't worry; you won't need to learn everything there is to know about Java or JDBC. Suffice to say that everything that is JDBC can be found in this java.sql package. The term package is merely used to describe a collection of related classes and interfaces. Below this title you should see a table labeled "Interface Summary." In the interface summary you'll notice an interface named DatabaseMetaData. Because the mdata object is a DatabaseMetaData object, any properties and methods found in the DatabaseMetaData interface will also be available in the mdata object.

When you select the DatabaseMetaData link you'll see a page with some general information about the interface, followed by a "Field Summary" table (these are the interface properties) and a "Method Summary" table (these are the interface functions). In the Method Summary you'll notice a number of methods beginning with the word "get," such as getCatalogs and getColumns. These "getter" methods are often referred to as "accessors" and are used to fetch information from the object. In this case these accessor methods are used to fetch metadata information from the RDBMS.

Have a look at the getCatalogs method (see also Figure 3). You'll notice that there is a brief description of the method beneath the name, and the name is linked to a more thorough description of the method further down on the same page. To the left of the linked method name you'll notice a link labeled "ResultSet". The column to the left of the method names displays the type of data returned by each of the methods (with a link to documentation for the specified class or interface if applicable). The functions you will likely find most useful when working with JDBC will return ResultSet objects. A ResultSet is the Java equivalent of a ColdFusion query variable created by the cfquery tag or a number of other tags and functions such as cfdirectory and cfpop.

Returning to our jdbc.cfm template, let's add the following code and execute the template:

<cfset rsCatalog = mdata.getCatalogs()>
<cfdump var="#rsCatalog#">

You Call This Results?
I have stated that a ResultSet is the Java equivalent of a ColdFusion query variable and that the method mdata.getCatalogs() will return a Java ResultSet object. Something is happening here, however, because when you display the new rsCatalog variable (a ResultSet object) using the cfdump tag, it produces something quite different than what is displayed when you use the same tag to display a query. You might expect the display to resemble Figure 4, but instead it resembles Figure 1.

One of the great advantages of CFML is it simplicity. CFML masks much of the complexity of Java to make our work easier. To this end the ColdFusion Server uses another proprietary class (coldfusion.sql.QueryTable) to provide the much simpler cfoutput, cfloop, and cfdump functionality we all know and love. This functionality is not included in Java and therefore isn't a part of the ResultSet object. Thus you need to manually convert the ResultSet object into a query. To make this easier I've created a function to perform this task. The ColdFusion code is shown in Listing 1.

Place this function at the top of your jdbc.cfm template, then add this code to the bottom of your template:

<cfset qCatalog = rstoquery(rscatalog)>
<cfdump var="#qCatalog#">

The output of this new variable should resemble Figure 4 and might be much more like what you expected to see from the previous cfdump tag output. With this function we can fetch queries from our metadata object using any of the ResultSet methods of the java.sql.DatabaseMetaData interface.

Returning to the documentation for the DatabaseMetaData interface at java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html we see several other useful methods such as getColumns(), getTables(), getPrimaryKeys(), getKeysImported(), getKeysExported(), getProcedures() and getProcedureColumns(). Other methods such as getSuperTables() and getTablePrivileges() might be useful only in certain specialized applications. Let's test our rsToQuery() function with the getColumns() method. Add this code to the bottom of your template:

<cfset rsColumns =
   mdata.getColumns("yourDB",
   "%","yourTable","%")>
<cfset qColumns = rstoquery(rscolumns)>
<cfdump var="#qColumns#">

As a reminder, "yourDB" in the code above should be the name of your database within your RDBMS, which may or may not be the same as your DSN within the ColdFusion Server Administrator (see Figure 2). The variable yourDB specifies the catalog (or database) from which you want to retrieve columns. The remaining three arguments of this method are schemaPattern, tableNamePattern and columnNamePattern. At this point you might notice some similarity between a SQL query and the getColumns function. The percent (%) symbol is used in both cases as a wild-card character. This provides very flexible and granular control over what data is returned by this function. You'll find that any of the arguments of the DatabaseMetaData methods ending with the word "pattern" behave in this manner, providing the same functionality for getTables() or getProcedures(). For now don't worry about the other methods; simply choose the name of a table you know is in your database to place in the tableNamePattern argument in place of the string "yourTable".

The cfdump output from this new qColumns variable shows some information you might find quite useful in developing applications, although these results are subtly deceptive. You might notice that several of the columns appear to be duplicated in some manner, such as the is_nullable and nullable columns. While these two columns can likely be used interchangeably without worrying about the results, the column data-type information requires further scrutiny.

The new query contains a "type_name" column containing the data types of the columns in your table. There are also three additional columns, "data_type", "source_data_type" and "sql_data_type" two of which appear to be numeric values, while "source_data_type" contain only empty strings with most databases. Unfortunately none of these four values will be particularly useful when attempting to automate something like the implementation of cfqueryparam tags. This is what the Java documentation has to say about these columns:

- DATA_TYPE int => SQL type from java.sql.Types
- TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified
- SQL_DATA_TYPE int => unused
- SOURCE_DATA_TYPE short => source type of a distinct type or user-generated Ref type, SQL type from java.sql.Types (null if DATA_TYPE isn't DISTINCT or user-generated REF)

From this documentation we can guess that the sql_data_type column was used in a previous version of JDBC but has since been deprecated. The source_data_type column is closely related to user-defined data_types and as such will not be populated unless user-defined types (UDTs) are included in the schema. Most databases don't contain any UDTs, resulting in empty string values. The type_name column is also problematic because it is data-source dependent, meaning the values in this column will vary from one RDBMS to the next. This includes values such as "smalldatetime" and "int identity" for MS SQL Server and possibly Sybase, while these values are meaningless in an Oracle or PostgreSQL database or within the standards for JDBC, ODBC, or even SQL.

This leaves us with the data_type column, which appears to be an integer. Obviously an integer value of 12 or 93 is also unhelpful in automating something like the cfqueryparam tag, so we need to translate these integer values to a useful string value. The solution to this problem isn't very intuitive, although it is described in the documentation for the java.sql.Types interface. I've created another function to make this process easier. You can see the code for that in Listing 2.

Now that you have this function, you can use it to convert the values in your data_type column from integers to string values matching the appropriate SQL standard data-type names. These string values will make more sense and likely be more useful in your development. Add the jdbcType() function from Listing 2 to the top of your jdbc.cfm template and then add the following code to the bottom:

<cfloop query="qColumns">
   <cfset qColumns.data_type = jdbcType(qColumns.data_type)>
</cfloop>
<cfdump var="#qColumns#">

When you execute this template again, the results of the final cfdump tag output should display most of the information you might want about your table's columns in a friendly format you can use for your own development.

Comparing Notes
When working with the DataBaseMetaData object, be careful not to omit any of the arguments of its methods. Unlike many CFML functions, none of the arguments for these methods are optional. If you specify a method with the wrong number of arguments or with the wrong type of arguments (passing an array instead of a string for instance), the ColdFusion server will produce an error such as "The selected method [nameOfMethod] was not found." This can be confusing to those of us not familiar with Java when the documentation insists that the object does have a method with the specified name, and even more so when other scripts successfully implement the same method.

Concerning the data type of method arguments: when the Java documentation includes a pair of brackets [] to the right of an argument's data type, the expected argument should be an array of items of the specified type. For instance, "String[]" must be an array of strings and "int[]" must be an array of integers. This should not be an issue with most of the java.sql methods although the DatabaseMetaData.getTables() method's last argument is described in the documentation as "String[] types" (where "types" is the name of the argument). This can be easily accommodated by using something like listToArray("TABLE,VIEW") in your call to the getTables() method.

Finally, because the metadata in your database design isn't likely to change very often and because the ResultSet objects returned by the DatabaseMetaData methods must be converted to queries via a processor-intensive manual routine, it's a good idea to use the ColdFusion Server's persistent scopes (I recommend the server or application scope) to store the metadata for your database. To reduce server load in production, any metadata transaction involving conversion of a Java ResultSet object to a query should be given a consistent variable naming convention and retrieved from this cache instead of using JDBC if the desired data exists in the cache. For example: column data for your "tMembers" table might be stored in the variable server.dba.yourDSN.yourDB.dbo.tMembers. columns. If the structure of your tMembers table changes, you can restart the ColdFusion Server service or delete the server.dba.yourDSN.yourDB.dbo.tMembers variable to force the application to renew the data the next time it is requested.

Summary
Unfortunately a more thorough examination of the possibilities of using JDBC metadata in CF applications is beyond the scope of this article. After reading this far, however, you should be able to use Java in CFML to connect to your database, retrieve metadata ResultSets, and convert them to queries. With this knowledge and the JDBC documentation at your disposal, finding ways to use these features should be easy. I've implemented a number of features not discussed in this article in the onTap framework (www.turnkey.to/ontap). These include but aren't limited to alternatives to the cfinsert and cfupdate tags, automated query filtering using form and URL variables, and an alternative to the cfstoredproc tag which makes stored procedures as easy to use as cfinsert and cfupdate. Please download the framework to experiment with these features. I look forward to seeing how others use JDBC.

• Figure 1
• Figure 2
• Figure 3
• Figure 4
• Source Code