Everything CFDBINFO Never Told You About Your Database (Connection Metadata)

I was doing some debugging and came across the nifty java.sql.DatbaseMetaData class. It is an interface implemented by the various database drivers and it provides you with a ton of information about both your database and driver capabilities. While you can glean some of the information from <cfdbinfo>, DatbaseMetaData tells you so much more.


To use it, you need to open a database connection first. If you have full server access, the simplest method is using the internal ServiceFactory. But you could just as easily open your own jdbc connection. Then call one of the 100+ methods to discover things you may have always wondered about. For example:

• What database or driver version you are using?
  
• What is the maximum number of concurrent connections?
  
• What is the maximum number of tables in an ORDER BY?
  
• What happens when you concatenate a NULL value with a non-NULL value?

Apparently, the DatabaseMetaData class can tell you that. While I would not take all of these values "as gospel", they do provide some solid guidelines.

<cfscript>
myDSN = "MSSQL_2005";

// open a connection to the datasource
factory = createObject("java", "coldfusion.server.ServiceFactory");
datasource = factory.getDataSourceService().getDataSource( myDSN );
connection = datasource.getConnection();
// extract the database metadata
metadata = connection.getMetaData();

results = {};

// Display version information
results["getJDBCMajorVersion()"] = metadata.getJDBCMajorVersion();
results["getJDBCMinorVersion()"] = metadata.getJDBCMinorVersion();
results["getDriverMinorVersion()"] = metadata.getDriverMinorVersion();
results["getDriverName()"] = metadata.getDriverName();
results["getDriverVersion()"] = metadata.getDriverVersion();
results["getDatabaseMajorVersion()"] = metadata.getDatabaseMajorVersion();
results["getDatabaseMinorVersion()"] = metadata.getDatabaseMinorVersion();
results["getDatabaseProductName()"] = metadata.getDatabaseProductName();
results["getDatabaseProductVersion()"] = metadata.getDatabaseProductVersion();

// cleanup
connection.close();
</cfscript>

<cfdump var="#results#" label="[#myDSN#] java.sql.DatbaseMetaData" />

As there are so many functions, I used a bit of reflection to quickly dump all of the simple properties into a structure. (See the full code at the end of the entry). It was interesting to see how the metadata differed across drivers. Both in what information was exposed and the results of the core methods. Notice the sql keywords differ when using the JTDS driver and the built-in CF8 driver.

Note: Unfortunately, getSQLKeywords() only returns potential keywords that are not also ansi sql:2003 reserved words. But with a little effort you could easily create a function to list of all potential keywords.

CF8 Driver

JTDS Driver


The metadata contains all kinds of interesting information. So if you ever wondered about your database or driver capabilities, read up on java.sql.DatbaseMetaData. What it reveals could surprise you.

Full Code

<cfscript>
myDSN = "MSSQL_2005";

// open a connection to the datasource
factory = createObject("java", "coldfusion.server.ServiceFactory");
datasource = factory.getDataSourceService().getDataSource( myDSN );
connection = datasource.getConnection();
// extract the database metadata
connMetadata = connection.getMetaData();

// get all methods in the metadata class
allMethods = connMetadata.getClass().getDeclaredMethods();
Modifier = createObject("java", "java.lang.reflect.Modifier");

results = {};

// Execute all simple methods and store the results
for(x=1; x <= arrayLen(allMethods); x++)
{
    method = allMethods[x];

    // Get the parameters for the current method
    parameters = method.getParameterTypes();

    // If this is a simple method, requiring no extra information ..
    if (Modifier.isPublic(method.getModifiers()))
    {
        if (not arrayLen(parameters) AND method.getName() neq "getConnection")
        {
            try
            {
                // run the current method
                data = method.invoke( connMetadata, parameters );
                // convert resultsets to a query object first
                if ( isInstanceOf(data, "java.sql.ResultSet")) {
                    data = createObject("java", "coldfusion.sql.QueryTable").init(data);
                }
                results[method.getName()] = data;
            }
            catch(java.lang.Exception e)
            {
                results[method.getName()] = e.message;
            }
        }
    }
}

connection.close() ;
</cfscript>

<cfdump var="#results#" label="connection.getMetaData() " />