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>

...Read More

Displaying multiple queries on a single sheet with POIUtility.cfc

I recently saw an interesting question on the adobe forums about POI and the POIUtility. The POIUtility is a wrapper CFC that can be used to read and write Excel files. Using the built in functions it allows you to display one query per worksheet. The poster asked if it was possible to use the POIUtility to display more than one query on a single worksheet.

I posted an example of how you might modify the POIUtility.cfc to do just that. The queries on each sheet can be displayed either horizontally or vertically. The example is barely tested, but hopefully someone out there might find it useful.

You can download POI from apache.org and the POIUtility.cfc from Ben Nadel's site.

And now a comment from left field ..

Ben Nadel is a talented guy, with some very interesting ideas and projects involving ColdFusion. A lot of good work went into the POIUtility and many programmers have found it extremely useful. But since I am posting a link on my site I have to say that personally I find many of his examples superfluous. Its like being trapped in the mind of an adolescent boy with some bizarre obsession or weird form of OCD or GTS. I will not even visit some areas of his site. But if you can get past all that, there are some interesting articles to be found.. some of which are actually about ColdFusion.

Now I fully expect some of you to disagree with me, but to each his own ;-)

...Read More