ColdFusion: cfdbinfo usage

Written on 17 March 2011, 11:40pm

Tagged with: , , ,

The cfdbinfo tag, introduced back in ColdFusion 8 provides a lot of information about your database environment. The documentation page shows 7 distinct values for the ‘type’ attribute of cfdbinfo:

type=”dbnames|tables|columns|version|procedures|foreignkeys|index”
Type of information to get:
* dbnames: database name and type
* tables: name, type, and remarks
* columns: name, SQL data type, size, decimal precision, default value, maximum length in bytes of a character or integer data type column, whether nulls are allowed, ordinal position, remarks, whether the column is a primary key, whether the column is a foreign key, the table that the foreign key refers to, the key name the foreign key refers to
* version: database product name and version, driver name and version, JDBC major and minor version
* procedures: name, type, and remarks
* foreignkeys: foreign key name and table, primary key name, delete, and update rules
* index: name, column on which the index is applied, ordinal position, cardinality, whether the row represents a table statistic or an index, number of pages used by the table or index, whether the index values are unique

This post shows some simple examples for the 7 cases described above to give you a better grip on this tag.

1. Get the databases

<cfset dsn = "YOURDSN">

<cfdbinfo
    type="dbnames"
    datasource="#dsn#"
    name="dbdata">

<cfoutput>
The #dsn# data source has the following databases:<br />
</cfoutput>
<cfdump var="#dbdata#">

It’s important to note that the cfdbinfo returns in this case all the databases, not just the one defined in the data source.

2. Get the tables of a database

<cfdbinfo
    type="tables"
    datasource="#dsn#"
    name="dbdata">

<cfoutput>
The #dsn# data source has the following tables:<br />
</cfoutput>
<cfdump var="#dbdata#">

In MySQL – the cfdbinfo above returns only the tables of the database defined in the data source.
In Oracle – the cfdbinfo above returns all the tables in all the databases returned at point 1.
To filter the returned tables, you can use the following two attributes:

  • pattern: Use an underline (_) to represent a single wildcard character and a percent sign (%) to represent a wildcard of zero or more characters. If you need to escape the two special characters (_ or %) you can use the backslash. Example:
    pattern="t\_%" will return t_user, t_comment, t_post, but not tblaccount.

    The pattern attribute is not case sensitive, so using pattern="t\_c%" is equivalent to pattern="T\_C%".
  • dbname: this attribute specifies the database to be used for returning the tables. There are important difference between Oracle and MySQL regarding this attribute.
    * As stated above, in MySQL, if you omit this attribute, cfdbinfo will return only the tables of the database defined in the data source. If you use this attribute, you can specify a database name from the ones returned at point 1 above. The value of the dbname attribute is case insensitive in MySQL. If you specify a dbname that does not exists, ColdFusion will return an error.
    * In Oracle, if you omit this attribute, cfdbinfo will return all the tables in all the databases returned at point 1. If you use this attribute to specify a certain database, then you must make sure that you use the same letter case as in point 1. In Oracle, the value of the dbname attribute is case sensitive. If you specify a dbname that does not exists, the dbname attribute will be ignored.

Let’s take an example to highlight all the attributes described above and the main differences between Oracle and MySQL. You have a schema called FOO, with a table T_FOO, and a schema called BAR with a table called T_BAR. The dsn datasource uses the FOO database.

<cfdbinfo type="tables" datasource="#dsn#" name="dbdata"> 
<!--- 
Oracle: returns T_FOO, T_BAR
MySQL: returns T_FOO
--->
<cfdbinfo type="tables" datasource="#dsn#" name="dbdata" dbname="FOO"> 
<!--- 
Oracle: returns T_FOO
MySQL: returns T_FOO
--->
<cfdbinfo type="tables" datasource="#dsn#" name="dbdata" dbname="foo"> 
<!--- 
Oracle: returns T_FOO, T_BAR
MySQL: returns FOO
--->
<cfdbinfo type="tables" datasource="#dsn#" name="dbdata" dbname="BAR"> 
<!--- 
Oracle: returns T_BAR 
MySQL: returns T_BAR
--->
<cfdbinfo type="tables" datasource="#dsn#" name="dbdata" dbname="NOTEXISTING"> 
<!--- 
Oracle: returns T_FOO, T_BAR
MySQL: returns error (Datasource #dsn# could not be found.)
--->
<cfdbinfo type="tables" datasource="#dsn#" name="dbdata" dbname=""> 
<!--- 
Oracle: returns T_FOO, T_BAR
MySQL: returns error (Value for attribute dbname is invalid.)
--->

3. Get the columns of a table

<cfdbinfo
    type="columns"
    datasource="#dsn#"
    name="dbdata"
    table="t_comment">
 
<cfoutput>
The table t_comment has the following columns:<br />
</cfoutput>

<cfdump var="#dbdata#">

4. Get the database version

<cfdbinfo
    type="version"
    datasource="#dsn#"
    name="dbdata">
<cfoutput>
The #dsn# data source has the following version:
</cfoutput>
<cfdump var="#dbdata#">

This outputs the following, depending on your database:
MySQL:

Oracle:

5. Get information about the stored procedures

<cfdbinfo
    type="procedures"
    datasource="#dsn#"
    name="dbdata">

<cfoutput>
The #dsn# data source has the following stored procedures:<br />
</cfoutput>
<cfdump var="#dbdata#">

6. Get information about the foreign keys of a table

<cfdbinfo  
    type="foreignkeys" 
    datasource="#dsn#" 
    name="dbdata"
    table="t_post"
> 
<cfdump var="#dbdata#">

If you have T_POST.ID_POST and T_COMMENT.FK_ID_POST:

7. Get information about the indexes of a table

<cfdbinfo  
    type="index" 
    datasource="#dsn#" 
    name="dbdata"
    table="t_post"
> 
<cfdump var="#dbdata#">

cfdbinfo get index

What are your tips regarding this?

Comments (1)

Leave a response