#ColdFusion Queries
To interact with a database with CFML first you need to setup the data source in the administrator site.
####----TestTable-----
id | myDataAlfa | myDataInt |
---|---|---|
1 | Alfa | 1 |
2 | Delta | 4 |
- Basic query
<cfquery name="firstQ" datasource="tsdata.ts24">
SELECT * FROM TestTable
</cfquery>
- Display the query to see what info you get from the database
<cfdump var="#firstQ#" />
- Output the info in your query This will display only one row of the table
<cfoutput>
<p><i>myDataAlfa: </i>#firstQ.myDataAlfa# <i>myDataInt: </i>#firstQ.myDataInt#</p>
</cfoutput>
- Loop through the data
<cfoutput>
<cfloop query="#firstQ#">
<p><i>myDataAlfa: </i>#firstQ.myDataAlfa# <i>myDataInt: </i>#firstQ.myDataInt#</p>
</cfloop>
</cfoutput>
- Extra info about the table
<cfoutput>
<p>#firstQ.columnlist#</p>
<p>#firstQ.recordcount#</p>
</cfoutput>
- Query Output Grouping (like in the tutorial, the c fart gallery)
<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>
- Query Param The param is use to avoid sql injections and validate data entered by the user
<cfquery name="myQuery" datasource="cfartgallery">
SELECT * FROM artists
WHERE firstname = <cfqueryparam value="#form.name#" cfsqltype="cf_sql_varchar" />
</cfquery>
The <cfqueryparam>
is getting the value
from a variable defined before for the form.
cfsqltype
is validating the value to be cf_sql_varchar
what it means that the value has to be a varchar
- Query Caching
<cfquery name="myQuery" datasource="cfartgallery" cachedwithin="#createTimespan(0,1,0,0)#">
SELECT * FROM artists
</cfquery>
This is one of the way to cache a query result. In this example the chache will last for 1 hour, if the query doesn't change.
- Dynamic Queries The query is generated based on the info passed to it and the decisions made by the statements that you put inside the 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>
This is a pretty heinous way to build a query, is better to generate a variable and then use it to execute the query like this:
<cfset query = 'SELECT firstname, lastname, email FROM artists' />
<cfif structkeyExists(form, 'firstname') and len(form.firstname)>
<cfset query = query & "WHERE firstname = " & "#<cfqueryparam value="#form.firstname#" cfsqltype="cf_sql_varchar" />#" />
</cfif>
<cfquery name="myQuery" datasource="cfartgallery">
#query#
</cfquery>
- Limiting Result Counts
<cfquery name="myQuery" datasource="cfartgallery" maxrows="100">
SELECT firstname, lastname, email FROM artists
</cfquery>
maxrows is like adding to the query LIMIT and a number so it will only retrive 100 rows of the entire table.
- Inset and Indentity Retrival Sometimes when inserting a new record you will need the last record inserted. You could get it doing after the insert a SELECT query to get the last row inserted but CF has a better way to do this
<cfquery result="qryResult" datasource="cfartgallery">
INSERT INTO TableTest
(myDataAlfa, myDataInt)
VALUES
(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="Gamma" />,
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="5" />,)
</cfquery>
<cfdump var="#qryResult#" />
When this code is executed the new row inserted would be available in the variable qryResult
. It will keep the whole object created.
#Queries in CFScript
- Basic query
<cfscritp>
myQry = new Query();
myQry.setDatasource("cfartgallery");
myQry.setSQL("SELECT firstname, lastname, email FROM artists");
myQuery = myQry.execute();
writeDump(myQuery.getResult());
writeDump(myQuery.getPrefix());
</cfscript>
- Concatenated methods
<cfscript>
myQueryResult = new Query(sql="SELECT firstname, lastname, email FROM artists", datasource="cfartgallery").execute().getResult();
writeDump(myQueryResult);
</cfscript>
- Using Query Params
<cfscript>
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());
</cfscript>
FYI: "CF_SQL_INT" now throws an "invalid attribute" error with CF2016u17+, CF2018 and CF2021.