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: (more…)

Did you know the formula of love? It’s
x^2+(y-sqrt(x^2))^2=1
Plotting this will result in a nicely hearth-shaped figure. The ultimate geek gift for his geek girlfriend!
I reproduced this plot using two methods: ColdFusion cfchart/cfchartseries/cfchartdata tags and a jQuery plotting library, called flot.

1. ColdFusion code

cflove.cfm (tested both on ColdFusion 8 and ColdFusion 9)

<cfchart format="flash" xaxistitle="X" yaxistitle="Y">
	<cfchartseries type="scatter">
		<cfloop index="x" from="-1" to="1" step="0.02">
			<cfset y1 = sqr(x^2)-sqr(1-x^2)>
			<cfchartdata item="#x#" value="#y1#">
		</cfloop>
	</cfchartseries>
	<cfchartseries type="scatter">
		<cfloop index="x" from="-1" to="1" step="0.02">
			<cfset y2 = sqr(x^2)+sqr(1-x^2)>
			<cfchartdata item="#x#" value="#y2#">
		</cfloop>
	</cfchartseries>
</cfchart>

You will notice that the formula of love is composed by two curves:
-the upper part of the hearth: y = sqrt(x^2) – sqrt(1-x^2)
-the lower part of the hearth: y = sqrt(x^2) + sqrt(1-x^2)
The values of x are between -1 and 1.

Go ahead and load this file on your ColdFusion server. It will nicely draw the following image: (more…)

Say you have a cfquery statement which does a simple insert in a table in your database:

<cfquery name="query_name" result="result_name">
INSERT INTO mytable(id,name) VALUES(1,'foo')
</cfquery>

This post presents two very useful and least documented features of the cfquery tag.

1. How to get the newly inserted ID

The ColdFusion 9 documentation offers several solutions depending on your database:
(more…)