This fourth article in the series will introduce a few ColdFusion tags and demonstrate how to use ColdFusion to select database records. The next article will demonstrate how to insert, update and delete records.
The first article in this series demonstrated how to setup a Fedora 20 VM.
The second article discussed installing MariaDB on the VM.
The third article illustrated installing Railo and connecting to the MariaDB datasource.
To test the Railo and MariaDB installation on the Fedora 20 VM, we’ll create a small file to retrieve database records.
In a terminal window, change to the Tomcat webapp root directory, update the permissions with chmod and use nano to create a test file.
cd /opt/railo/tomcat/webapps/ sudo chmod 775 ROOT cd ROOT mkdir app cd app nano -w test.cfm <cfquery datasource="web_db" name="qEmp"> select * from employees </cfquery> <cfdump var="#qEmp#">
CTRL-O to write the file.
CTRL-X to exit nano.
Open Firefox and navigate to http://127.0.0.1:8000/app/test.cfm
If everything has gone according to plan, you should see a CFDump block containing the results of your query against the employees table. The <cfdump> tag displays the results of a query. However, the <cfoutput> tag is what you will use to display formatted results. The simplicity and versatility of the <cfquery> and <cfoutput> tags are what makes Coldfusion easy to learn and allows for rapid application development.
CFQUERY
The <cfquery> tag is one of the most powerful ColdFusion tags because it performs select, insert, update and delete operations on a database and it can select from other <cfquery> tags. The <cfquery> tag has two required attributes: datasource and name. To use <cfquery> for a query-of-queries, the dbtype attribute is required instead of the datasource. The <cfquery> tag requires a begin and end tag (e.g., <cfquery></cfquery). The <cfquery> tag cannot be nested. However, one <cfquery> can select from another <cfquery>.
CFOUTPUT
One of the key function of the <cfoutput> tag is to process and display the results of the <cfquery> tag. The <cfoutput> tag requires a begin and end tag (e.g., <cfoutput></cfoutput>). The <cfoutput> tag can only be nested under certain circumstances.
Ground Rules
For this inital example, I will focus on data retrieval and modification. In a future post, I will look at where to put <HTML>, <body> and other standard HTML tags.
Getting Started
We will reuse the test.cfm file and expand on it. You can use nano or jEdit or any text editor.
First, let’s disect what we have so far.
<cfquery datasource="web_db" name="qEmp">
The opening <cfquery> tag establishes the datasource and a name for the query. The datasource should be setup in the Administrator console and includes all the information <cfquery> will need to execute the query. The name attribute should be meaningful since it is used later in the file to display the query results. A typical naming convention suggests that the name start with a ‘q’ if the <cfquery> is selecting from the datasource. Accordingly, a ‘u’ is prefixed to the name for updates, ‘d’ for deletes and ‘i’ for inserts. Naming convetions are handy when debugging and troubleshooting code. An elaborate but useful naming convention might require
the operation type, the schema and table names and a unique description of the query.
Next, the SQL query of the <cfquery> is required.
select * from employees
Formatting is not important for processing. However, for longer SQL statements, it will be easier to troubleshoot if the entire query is not on a single line. In this example, the SQL is a simple select statement. The <cfquery> tag can execute select, insert, update and delete statements.
Then, the end </cfquery> tag is required. There is nothing special about it except that it is required.
</cfquery>
Displaying Query Results
Using the test.cfm, we’ll delete the <cfdump> and add <cfoutput>.
Query results can be displayed using the <cfoutput> tag. The easiest way to display query results is to use the query attribute in the <cfoutput> tag. Start with an opening <cfoutput> tag, add the name attribute with the <cfquery> name as the value.
<cfoutput query="qEmp">
The query attribute instructs the <cfoutput> tag to loop over the results of the query. Without the query attribute, ColdFusion will process whatever is in between the <cfoutput> start and end tags. To instruct ColdFusion to process variables, enclose the variable name in #. For our example, that would look something like this:
#namelast#, #namefirst#<br>
Adding a line break will display each name on a new line.
To finish off the test document, close the <cfoutput>.
</cfoutput>
Before we test the changes, the file should look like this.
<cfquery datasource="web_db" name="qEmp"> select * from employees </cfquery> <cfoutput query="qEmp"> #namelast#, #namefirst#<br> </cfoutput>
Finally, save the file and head over to http://127.0.0.1:8000/app/test.cfm. The results should looks something like this. It is not the most glorious web page ever created. However, it proves that your database and application tiers are working.
Playing Around
This is all fine and good for a simple list of names, but if there were more columns to display, you might want to put a table in your <cfoutput> like this:
<table> <cfoutput query="qEmp"> <tr> <td>#namelast#</td> <td>#namefirst#</td> </tr> </cfoutput> </table>
Notice that if you put the <table> tag inside the <cfoutput> you would get a table for each record. By putting the <table> outside the <cfoutput>, we can display one table with one record per row. Similarly, if you wanted to present a <ul> of names, the code would look like this:
<ul> <cfoutput query="qEmp"> <li>#namelast#, #namefirst#</li> </cfoutput> </ul>
The <cfquery> results are actually stored as an object. Different flavors of ColdFusion may provide different attributes to the object in addition to the query results. For example, Railo adds currentRow, columnList and recordCount. Therefore, if you need to display the number of records returned by a query, you can use:
<cfoutput>#qEmp.recordCount# rows returned</cfoutput>
Or, if you wanted to display a row number for each record returned, you could use currentRow like this:
<cfoutput query="qEmp"> #currentRow#. #namelast#, #namefirst#<br> </cfoutput>
Queries as objects also enables them to be stored and reused across an application so that you don’t have to return to the database for data that rarely changes. More on that later. For now, enjoy your Railo + MariaDB installation on Fedora.
Leave a Reply
You must be logged in to post a comment.