CFDBINFO - How did they do that?

I have been hearing about the impressive new cfdbinfo tag in ColdFusion 8 and got to wondering, how did they do that? As I had not installed CF8 yet, I was curious how the tag obtains the information from varying database types and more importantly if something similar would work with MX7.

I set about trying to figure it all out and in my searching came upon java's DatabaseMetaData interface. It seemed likely this is what cfdbinfo uses to obtain metadata information. So I decided to create a small test CFC and run it against an Access database. The reason I chose Access is because of all the databases I have used, MS Access seems the most reluctant to divulge metadata information.

As you can see below the first draft of the CFC yielded all kinds of good information. Not only table and column names, but even column data types and procedure names. (Yes, apparently my jdbc driver reports MS Access stored queries as procedures)

<cfset dbUtil = createObject("component", "AccessDBUtil").init("MyAccessUnicodeDSN") />
<cfset qVersion = dbUtil.getVersionInfo() />
<cfset qCatalogs = dbUtil.getCatologs() />
<cfset qProcedures = dbUtil.getProcedures() />
<cfset qTableTypes = dbUtil.getTableTypes() />
<cfset qSchemas = dbUtil.getSchemas() />
<cfset qTables = dbUtil.getTables(typeList = "Table") />
<cfset qColumns = dbUtil.getColumns(tablePattern = "Table1") />


<cfdump var="#qProcedures#"/>
<cfdump var="#qColumns#"/>
<cfdump var="#qTables#"/>
<cfdump var="#qSchemas#"/>
<cfdump var="#qTableTypes#"/>
<cfdump var="#qVersion#"/>
<cfdump var="#qCatalogs#"/>

I ran some basic tests and overall the results were impressive. Though predictably schema and catalog filters had no effect, because MS Access does not have schemas or catologs. Unfortunately the jdbc driver I am using does not allow wildcard searches on table or column names. This was disappointing since it is a nice feature, but cest la vie.

I've included the CFC code below for anyone interested. Keep in mind it is a rough draft. Now if I could find the time I would love to create a version of Ray Camden's Database Explorer that works with MX7, but that is for another day.

As always comments/corrections/suggestion are welcome

AccessDBUtil.cfc

<cfcomponent>

    <cffunction name="init" returntype="AccessDBUtil" access="public" >
        <cfargument name="jdbcDatasourceName" type="string" required="true" />
        <cfset setDatasourceName(arguments.jdbcDatasourceName)  />
        <cfreturn this  />
    </cffunction>
    
    <cffunction name="setDatasourceName" returntype="void" access="private" >
        <cfargument name="jdbcDatasourceName" type="string" required="true" />
        <cfset variables.instance.datasource = arguments.jdbcDatasourceName  />
    </cffunction>
    <cffunction name="getDatasourceName" returntype="string" access="public" >
        <cfreturn variables.instance.datasource  />
    </cffunction>
    
    <cffunction name="getCatologs" returntype="query" access="public" >
        <cfset var Local = structNew() />

        <cfset Local.connection = getConnection() />
        <cfset Local.resultset = Local.connection.getMetaData().getCatalogs()  />
        <cfset Local.query = resultsetToQuery( resultset = Local.resultset )  />
        <cfset Local.resultset.close()  />
        <cfset Local.connection.close() />

        <cfreturn Local.query   />
    </cffunction>
    
    <cffunction name="getSchemas" returntype="query" access="public" output="true" >
        <cfset var Local = structNew() />

        <cfset Local.connection = getConnection() />
        <cfset Local.resultset = Local.connection.getMetaData().getSchemas()  />
        <cfset Local.query = resultsetToQuery( resultset = Local.resultset )  />
        <cfset Local.resultset.close()  />
        <cfset Local.connection.close() />

        <cfreturn Local.query   />
    </cffunction>

    <cffunction name="getTableTypes" returntype="query" access="public" >
        <cfset var Local = structNew() />
        
        <cfset Local.connection = getConnection() />
        <cfset Local.resultset = Local.connection.getMetaData().getTableTypes()  />
        <cfset Local.query = resultsetToQuery( resultset = Local.resultset )  />
        <cfset Local.resultset.close()  />
        <cfset Local.connection.close() />
        
        <cfreturn Local.query   />
    </cffunction>

    <cffunction name="getProcedures" returntype="query" access="public" output="false" >
        <cfargument name="catalogName" type="string" required="false" hint="The name of a catolog in the database. An empty string means retrieve objects without a catalog. If not specified, the catolog is not used to narrow the search"  />
        <cfargument name="schemaPattern" type="string" required="false" default="%" hint="The name of a schema in the database. An empty string means retrieve objects without a schema. "  />
        <cfargument name="procedurePattern" type="string" required="false" default="%" />
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.isCatalogNonNull =  structKeyExists(arguments, "catalogName");
            Local.isSchemaNonNull =  structKeyExists(arguments, "schemaPattern");
            Local.connection = getConnection();

            //TODO: check if there is a way to pass java NULL's conditionally 
            if ( Local.isCatalogNonNull AND Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getProcedures( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.procedurePattern );
            }
            else if ( Local.isCatalogNonNull ) {
                Local.resultset = Local.connection.getMetaData().getProcedures( arguments.catalogName, 
                            javacast("null", ""), 
                            arguments.procedurePattern );
            }        
            else if ( Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getProcedures( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.procedurePattern );
            }        
            else {
                Local.resultset = Local.connection.getMetaData().getProcedures( javacast("null", ""), 
                            javacast("null", ""),
                            arguments.procedurePattern );
            }        

            Local.query = resultsetToQuery( resultset = Local.resultset );
            Local.resultset.close();
            Local.connection.close();
        </cfscript>

        <cfreturn Local.query   />
    </cffunction>

    
    <cffunction name="getTables" returntype="query" access="public" output="false" >
        <cfargument name="catalogName" type="string" required="false" hint="The name of a catolog in the database. An empty string means retrieve objects without a catalog. If not specified, the catolog is not used to narrow the search"  />
        <cfargument name="schemaPattern" type="string" required="false" default="%" hint="The name of a schema in the database. An empty string means retrieve objects without a schema. "  />
        <cfargument name="tablePattern" type="string" required="false" default="%" />
        <cfargument name="typeList" type="string" required="false" default="" hint="Comma delimited list of table types to include. An empty string means retrieve all types" />
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.isCatalogNonNull =  structKeyExists(arguments, "catalogName");
            Local.isTypeNonNull =  len(trim(arguments.typeList));
            Local.typeArray        = listToArray(arguments.typeList);
            Local.connection = getConnection();

            //TODO: check if there is a way to pass java NULL's conditionally 
            if ( Local.isCatalogNonNull AND Local.isTypeNonNull ) {
                Local.resultset = Local.connection.getMetaData().getTables( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            Local.typeArray);
            }
            else if ( Local.isCatalogNonNull ) {
                Local.resultset = Local.connection.getMetaData().getTables( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            javacast("null", ""));
            }        
            else if ( Local.isTypeNonNull ) {
                Local.resultset = Local.connection.getMetaData().getTables( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            Local.typeArray); 
            }        
            else {
                Local.resultset = Local.connection.getMetaData().getTables( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            javacast("null", "")); 
            }

            Local.query = resultsetToQuery( resultset = Local.resultset );
            Local.resultset.close();
            Local.connection.close();
        </cfscript>

        <cfreturn Local.query   />
    </cffunction>

    <cffunction name="getColumns" returntype="query" access="public" output="false" >
        <cfargument name="catalogName" type="string" required="false" hint="The name of a catolog in the database. An empty string means retrieve objects without a catalog. If not specified, the catolog is not used to narrow the search"  />
        <cfargument name="schemaPattern" type="string" required="false" hint="The name of a schema in the database. An empty string means retrieve objects without a schema. If not specified, the schema is not used to narrow the search"  />
        <cfargument name="tablePattern" type="string" required="false" default="%" hint="Table name to find. Wildcards are NOT supported by com.inzoom.jdbcado driver"  />
        <cfargument name="columnPattern" type="string" required="false" default="%" hint="Column name to find. Wildcards are NOT supported by com.inzoom.jdbcado driver"  />
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.isCatalogNonNull =  structKeyExists(arguments, "catalogName");
            Local.isSchemaNonNull =  structKeyExists(arguments, "schemaPattern");
            Local.connection = getConnection();

            //TODO: check if there is a way to pass NULL's conditionally 
            if ( Local.isCatalogNonNull AND Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getColumns( arguments.catalogName, 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }
            else if ( Local.isCatalogNonNull ) {
                Local.resultset = Local.connection.getMetaData().getColumns( arguments.catalogName, 
                            javacast("null", ""), 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }        
            else if ( Local.isSchemaNonNull ) {
                Local.resultset = Local.connection.getMetaData().getColumns( javacast("null", ""), 
                            arguments.schemaPattern, 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }
            else {
                Local.resultset = Local.connection.getMetaData().getColumns( javacast("null", ""), 
                            javacast("null", ""), 
                            arguments.tablePattern, 
                            arguments.columnPattern);
            }

            Local.query = resultsetToQuery( resultset = Local.resultset );
            //Local.query2 = createObject("java", "coldfusion.sql.QueryTable").init(Local.resultset);
            Local.resultset.close();
            Local.connection.close();
        </cfscript>

        <cfreturn Local.query   />
    </cffunction>
    
    <cffunction name="getVersionInfo" returntype="query" access="public" >
        <cfset var Local = structNew() />
                
        <cfscript>
            Local.query = queryNew( "DATABASE_VERSION,DATABASE_PRODUCTNAME" & 
                                ",DATABASE_MAJOR_VERSION,DATABASE_MINOR_VERSION," & 
                                ", DRIVER_VERSION,DRIVER_NAME,JDBC_MAJOR_VERSION," &
                                "JDBC_MINOR_VERSION");
                            
            Local.connection = getConnection();
            Local.meta = getConnection().getMetaData();
        
            Local.row = queryAddRow( Local.query, 1);
            Local.query["DATABASE_PRODUCTNAME"][Local.row]         = Local.meta.getDatabaseProductName();
            Local.query["DATABASE_VERSION"][Local.row]             = Local.meta.getDatabaseProductVersion();
            Local.query["DATABASE_MAJOR_VERSION"][Local.row]     = Local.meta.getDatabaseMajorVersion();
            Local.query["DATABASE_MINOR_VERSION"][Local.row]     = Local.meta.getDatabaseMinorVersion();
            Local.query["DRIVER_VERSION"][Local.row]             = Local.meta.getDriverVersion();
            Local.query["DRIVER_NAME"][Local.row]                 = Local.meta.getDriverName();
            Local.query["JDBC_MAJOR_VERSION"][Local.row]         = Local.meta.getDriverMajorVersion();
            Local.query["JDBC_MINOR_VERSION"][Local.row]         = Local.meta.getDriverMinorVersion();
    
            Local.connection.close();
        </cfscript>
        
        <cfreturn Local.query  />
    </cffunction>


    <cffunction name="getConnection" returntype="any" >
        <cfset var dsService = createObject("java", "coldfusion.server.ServiceFactory").getDataSourceService()  />
        <cfset var connection = dsService.getDataSource(getDatasourceName()).getConnection()  />
        <cfreturn connection  />
    </cffunction>
    
    <cffunction name="getResultSetColumnNames" returntype="array" access="private" hint="Returns an array of column names from the resultset metadata" >
        <cfargument name="resultsetMetadata" type="any" required="true" />
        <cfset var Local = structNew()  />
        
        <cfscript>
            Local.columnArray = arrayNew(1);
            //get number of columns in the resulset
            Local.maxColumn = arguments.resultsetMetadata.getColumnCount();
            //get the name of each column in the query and append it to the array
            for (Local.index = 1; Local.index LTE Local.MaxColumn; Local.index = Local.index + 1) {
                arrayAppend( Local.columnArray, arguments.resultsetMetadata.getColumnName( javacast("int", Local.index)) );        
            }
        </cfscript>
        
        <cfreturn Local.columnArray  />
    </cffunction>
    
    <cffunction name="resultsetToQuery" returntype="query" access="private" hint="Converts a resulset to a query object" >
        <cfargument name="resultset" type="any" required="true" />
        <cfset var Local = structNew()  />
        
        <!--- could also use "coldfusion.sql.QueryTable" to create a resulset 
        <cfset Local.query = createObject("java", "coldfusion.sql.QueryTable").init(arguments.resultset) />
        --->
        
        <cfscript>
            Local.columnNames     = getResultSetColumnNames( arguments.resultset.getMetaData() );
            Local.maxColumn     = arrayLen( Local.columnNames );
            Local.query         = queryNew( arrayToList(Local.columnNames) );
            
            while ( arguments.resultset.next() ) {
                //add one row to the query for each row in the resultset
                Local.rowIndex = queryAddRow(Local.query , 1);
                 for ( Local.colIndex = 1; Local.colIndex LTE Local.maxColumn; Local.colIndex = Local.colIndex + 1) {
                    Local.colName = Local.columnNames[Local.colIndex];
                    Local.value = arguments.resultset.getObject( Local.colName );
                    // if the returned value is not NULL
                    if ( structKeyExists(Local, "value") ) {
                        Local.query[Local.colName][Local.rowIndex] = Local.value;
                    }
                }
            }
        </cfscript>
                
        <cfreturn Local.query  />
    </cffunction>
    
</cfcomponent>