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 (7)

  1. Timothy Steck — April 24, 2020 at 23:58

    When I do this statement, everything works fine, I can query against it and get the list of tables.

    But when I do this statement,

    I get this error

    [Macromedia][SequeLink JDBC Driver][ODBC Socket]internal error: Driver does not support this function

    I have full access to the code and the webserver, I can make any changes I need to.

    Basic info:
    – Webserver: Server 2012 R2
    – ColdFusion 11
    – Access database: eescc.accdb (using Access 2016)
    – using Microsoft Access Driver v16.00.4513.1000, Date: 3/7/2017
    – Website development and testing on Windows 10 Enterprise v1909

    My end results is to retrieve only the fields with a Field’s Default Value = “xyz”

    This statement gives me the Column names but from the CFDUMP results it doesn’t appear to give me the “Default Value” field value.

    If I could use the CFdbinfo statement with the “Columns” option that would be awesome but it gives me an error. Please help if you can. You may call if you want 541-767-2100.

    Thanks for your help in advance!

    Reply

    • Dorin Moise — April 25, 2020 at 10:45

      Timothy, your code seems to be lost in the comment. Could you please check?

      Reply

      • Timothy Steck — April 25, 2020 at 23:12

        It looks like the Snippets got lost here is the code again.
        This code works fine:

        but this code doesn’t and gives the error I listed:

      • Timothy Steck — April 25, 2020 at 23:15

        Sorry I missed another Snippit
        This also works but it doesn’t have the values of the “Default Value”

        SELECT * FROM #varTable#

  2. Timothy Steck — April 25, 2020 at 23:19

    Does like when I put in the code, I’ll change it so it will be accepts it (but the code isn’t 100% correct)
    This works (Note: replace the ** with less than and greater than, plus I left of the DSN,Username,Password because of the hash tags)
    **cfdbinfo name=”dbiTableNames” type=”tables” **

    Reply

    • Timothy Steck — April 25, 2020 at 23:23

      Same as I said above … (I’m not sure what characters it doesn’t like)
      This does not work
      **cfdbinfo name=”dbiColumnNames” type=”columns” table=”ValidCodes”**
      This also works but it doesn’t get all the info I need:
      ** cfquery name=”rsColumnNames” **
      SELECT * FROM #varTable#
      **/cfquery**

      **cfset ColumnNames = getMetaData(rsColumnNames)**

      Reply

Leave a response