One of the key parts of any application is its ability to do CRUD (Create, Read, Update, and Delete) operations. ColdFusion has the ability to read a variety of data sources with minimal effort; there is built in support for over ten different databases. Among the supported databases, you will find MS SQL, MySql, Oracle, DB2, and Sybase. There are slight sql syntax differences when interacting with the database; however, the same code can be used against different databases without any changes.
Note: This chapter will not teach you how to write sql. However, it will show you how to take the sql skills you have and incorporate them into your code. Alternatively, if you are using ColdFusion Builder, you can use the query builder to assist you with query creation.
A data source needs to be configured in the administrator. The data source contains all the connection information that ColdFusion needs to communicate with your database. Rather than providing all that information with each database call, ColdFusion allows you to defined a data source, providing it a name, and use the name as a reference for all your queries.
If you installed the samples when you installed ColdFusion, then there are some for you to play with already. Once you have the datasource created, you can start to do CRUD operations.
- Login to ColdFusion Administrator.
- On the left menu under "Data & Services," select "Data Sources".
- Under "Add New Data Source", enter a name (this will be the name you will use in your code to reference the data source) and select a driver type. The type must match the database type you are using.
- Click "Add" to submit the form.
- The next screen will vary depending on data source type. The following steps are based on selecting "MySQL (4/5)" as a type.
- Enter database name (name of your database on your server).
- Enter Server (IP address or domain name of server).
- Port is defaulted but change it if necessary.
- Enter username (this is the login that will be tied to all database actions from your application).
- Enter Password.
- Enter a description if necessary.
- This is not a requirement, but you can click on "Advanced Settings" and make any adjustments there.
- Click "Submit" to save.
- The data source will validate; if there are any errors they will be reported on screen.
- If necessary, correct any errors until the data source validates upon save.
<cfquery name="myQuery" datasource="cfartgallery">
SELECT * FROM artists
</cfquery>
The query result is stored in the variable with the same name as they query name. To see the result of the query, you can just dump the result.
<cfdump var="#myQuery#" />
The dump will contain information on caching status, execution time, actual sql statement, and the entire result set. Don't let the dump confuse you; the result is not stored in a "resultset" variable. That is just how cfdump shows it.
There is other information about the query that cfdump does not display that are part of the myquery object, such as columnlist and recordcount. These can be referenced by using myquery.columnlist or myquery.recordcount.
The query should only return the columns you need; this speeds up the query and return processing. For example, since the output in the above example only used email, firstname, and lastname, the query should be changed from "select *" to "select email, firstname, and lastname."
While cfdump is a great way to make sure the query is working, it isn't very useful output to a user. Using cfoutput, we now gain display control and can display what we want how we want it.
<cfoutput query="myQuery">
#myquery.CurrentRow# - #myquery.email# - #myquery.firstname# - #myquery.lastname#<br />
</cfoutput>
There are times where you may wish to process the query return but not directly output it. To accomplish this, use cfloop. This tag allows you to loop over the result set and perform operations per row (and output them too). It is also useful in the event that you wish to loop over a query result and there is already an outer cfoutput in the code. The syntax looks identical to cfoutput.
<cfset artistArray = [] />
<cfloop query="myQuery">
<cfset arrayappend(artistArray, myquery.lastname & ', ' & myQuery.firstname) />
</cfloop>
It is also possible to nest cfoutput tags so you can group like data. This creates a nested loop where the outer loop is iterated once per group and the inner is looped once for each item in the group. While this sounds great on the surface, it does have a drawback: the data must be pre-sorted before the cfoutput for the grouping to work correctly. In the example below, an order by is added to the query, which makes sure that the rows come back in the correct order. The cfoutput tag now has a group attribute. The value of this attribute must always be a column name of the query. Inside the cfoutput is another cfoutput. This is the inner loop that will output once per row in the group. The outer will only output once.
<cfquery name="myQuery" datasource="cfartgallery">
SELECT * FROM art
ORDER BY issold
</cfquery>
<cfoutput Query="myQuery" group="issold">
<p>
Sold ?: #YesNoFormat(myQuery.issold)#:<br />
<blockquote>
<cfoutput>
#myQuery.artname#: #DollarFormat(myQuery.price)#<br />
</cfoutput>
</blockquote>
</p>
<hr />
</cfoutput>
One of the biggest attacks against a system is sql injection. However, an easy way to prevent it is to use cfqueryparam. This sets parameters for the query input, thus making injection nearly impossible. cfqueryparam should be used anywhere you have query input. cfqueryparam is not exclusive to select where clauses; it should also be used for inserts, updates, or any query that has dynamic input.
cfqueryparam is also useful in doing data length validation as well as data type. For example, if your queryparam is set as an integer and a string is passed, an error will be thrown. The same is true when a string is longer than the set max length.
Note: cfqueryparam should be used ALWAYS without exception.
Query Example:
<cfquery name="myQuery" datasource="cfartgallery">
SELECT * FROM artists
WHERE firstname = <cfqueryparam value="#form.name#" cfsqltype="cf_sql_varchar" />
</cfquery>
If you have debugging enabled, the query output in the debug info will look a little different:
myQuery (Datasource=cfartgallery, Time=1ms, Records=1) iSELECT * FROM artists WHERE firstname = ?Query Parameter Value(s) - Parameter #1(cf_sql_varchar) = Austin
Notice the "?" in the where clause? That is the parameter that was created using cfqueryparam. You can also use it to replace a list in an "in" clause:
<cfquery name="myQuery" datasource="cfartgallery">
SELECT * FROM artists
WHERE artistid IN (<cfqueryparam value="#form.ids#" list="true" />)
</cfquery>
myQuery (Datasource=cfartgallery, Time=2ms, Records=4) i
select * from ARTISTS
where artistid in (?,?,?,?)
Query Parameter Value(s) -
Parameter #1(CF_SQL_CHAR) = 1
Parameter #2(CF_SQL_CHAR) = 2
Parameter #3(CF_SQL_CHAR) = 3
Parameter #4(CF_SQL_CHAR) = 4
Query caching is very easy to implement. Caching will be covered in detail in a later chapter; for now, this chapter will just touch on one way to cache here: cachedwithin. This type of cache states that the query result will be stored and reused for the specified duration. Using the previous query example, caching the result can be done like this:
<cfquery name="myQuery" datasource="cfartgallery" cachedwithin="#createTimespan(0,1,0,0)#">
SELECT * FROM artists
</cfquery>
This would cause the query result to cache for 1 hour. Caching is based on the query syntax, not the query name. As long as the syntax doesn't change, the cache will be used. However, if you have a very dynamic query, caching can be a challenge. For example, if the below is your query, you might not want to cache it because the sql syntax can vary widely from request to request. Using cfqueryparam does not help in caching at the ColdFusion server level. However, it does assist with database query plans so there is some caching benefit.
<cfquery name="myQuery" datasource="cfartgallery" cachedwithin="#createTimespan(0,1,0,0)#">
SELECT firstname, lastname, email FROM artists
WHERE firstname = <cfqueryparam value="#form.firstname#" cfsqltype="cf_sql_varchar" />
</cfquery>
Queries are not always straightforward. There may be occurrences in which a where clause or column list needs to be decided upon at runtime. Thankfully, cfquery allows for wide range of dynamics to create the query. Think of it like generating browser output without any markup or cfoutput tags, like a paragraph of text. Knowing that, we can use a variety of ways to generate a dynamic query.
<cfquery name="myQuery" datasource="cfartgallery">
SELECT firstname, lastname, email FROM artists
WHERE 1 = 1
<cfif structkeyExists(form, 'firstname') and len(form.firstname)>
AND firstname = <cfqueryparam value="#form.firstname#" cfsqltype="cf_sql_varchar" />
</cfif>
<cfif structkeyExists(form, 'lastname') and len(form.lastname)>
AND lastname = <cfqueryparam value="#form.lastname#" cfsqltype="cf_sql_varchar" />
</cfif>
</cfquery>
<cfset query = 'SELECT firstname, lastname, email FROM artists' /><cfquery name="myQuery" datasource="cfartgallery">
#query# </cfquery>
Returning thousands of rows from a query can be very inefficient. It is good practice to only return the number of rows you are going to deal with. Reducing the result set to a defined number of rows can be done in a variety of ways: You could add a maxrows to the output; you could add a max count to the sql statement itself; or you could add a max to the cfquery tag itself. These are all valid ways to handle it, but choosing which way to handle the result can be a challenge. Adding a limiter to the cfquery tag or in the sql statement limits the rows from the databases, but the max rows on the output just reduces the output. Thus, if you query returned 10,000 rows, your output could have a max of 100, and only display the first 100.
<cfoutput query="myQuery" maxrows="100">
#myquery.CurrentRow# - #myquery.email# - #myquery.firstname# - #myquery.lastname#<br />
</cfoutput>
The issue is that there were 9,900 rows of ignored data. The better approach may be to have the database only return 100 rows, which can be done by setting a max on the initial query.
<cfquery name="myQuery" datasource="cfartgallery" maxrows="100">
SELECT firstname, lastname, email FROM artists
</cfquery>
This way is much more efficient, as less data is transmitted from database to server. It also helps speed things up as ColdFusion no longer has to deal with query data that is not being used.
When inserting records, you are going to want to know the ID of the inserted item more often than not. Typically, this is achieved by doing the insert, then doing a select and getting the highest ID in the table. While this may work, it is very problematic and may not yield the expected results. Fortunately, there is an alternative that works very well and yields the correct results. Look at the example below:
<cfquery result="qryResult" datasource="cfartgallery">
INSERT INTO art
(
artistsID, artName, description, isSold, largeImage, mediaID, price
)
VALUES
(
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="1" />,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="Test Item" />,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="Test Desc" />,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="1" />,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="img.png" />,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="1" />,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="1" />
)
</cfquery>
<cfdump var="#qryResult#" />
Notice in the above a result attribute was added and given a value of "qryResult." This variable is a dumpable structure of data. One of the parts of the data is the ID of the inserted row. It will also contain the params (if any), execution time, and the sql statement. Depending on your database, the identity variable may be different; dump the result first to see what the column is.
There is a way to run queries if you really like writing all your code in script syntax; it is slightly more complicated, but has all the same functionality that the tag based syntax has. There are many ways to implement using queries in script. The example below is just one of those ways. The one main difference is that with script based query, you actually have to tell ColdFusion to execute the query; the query doesn't just run by creating it. Also, it will be necessary to get the query result by using the getResult() function.
myQry = new Query();
myQry.setDatasource("cfartgallery");
myQry.setSQL("SELECT firstname, lastname, email FROM artists");
myQuery = myQry.execute();
writeDump(myQuery.getResult());
writeDump(myQuery.getPrefix());
Using chaining and adding params to the query initialization, the code can be reduced to a single line:
myQueryResult = new Query(sql="SELECT firstname, lastname, email FROM artists", datasource="cfartgallery").execute().getResult(); writeDump(myQueryResult);
Just like the tag based version, you can easily add query params to the query. For the script syntax you have to define a matching pair, which is the param name in the query; the added param must match. If they don't match, an error will be thrown.
myQry = new Query();
myQry.setDatasource("cfartgallery");
myQry.setSQL("SELECT artname, description FROM art WHERE issold = :sold");
myQry.addParam(name: "sold", value: "1", cfsqltype: "CF_SQL_INT");
myQuery = myQry.execute();
writeDump(myQuery.getResult());
writeDump(myQuery.getPrefix());