Connecting to a database

Developers used to code the data and presentation into the same file. There was no "data layer" for developing software; it was all mixed on the same page. This made the pages longer and there was much more to work through to figure out bugs or page enhancements. It was pretty much what we call "information overload". CFCs change that by encapsulating the data as a separate layer. Then the interface serves as a simpler way to push and pull data to and from your database.

Note

If you haven't set up your database, then refer to Appendix A and set that part of your environment up for this section first. If you are not skilled with databases, then we have suggestions in Appendix B. You don't need that as we write the ones that you need in the code examples of this book.

Here we will create another version of our product.cfc object class. Let's name it product_2.cfc so we can keep things separate in case you want to go back and compare them later. Let's take a look at the starting version of this data-oriented object. If you are using development tools such as CFBuilder, DreamWeaver, or CFEclipse, then there are tools for creating CFCs and data-based CFCs also. We suggest that you start here just for the sake of understanding. This also makes it easier for you to move from one developer coding tool to another without having to master the tool before you can become productive.

<cfcomponent output="false">
<cffunction name="init" access="public" output="false">
<cfscript>
variables.attributes = structNew();
variables.attributes.name = "";
variables.attributes.description = "";
variables.attributes.price = 0;
</cfscript>
<cfreturn this />
</cffunction>
<!--- Getter and Setter Methods --->
<cffunction name="get_name" access="public" output="false">
<cfreturn variables.attributes.name />
</cffunction>
<cffunction name="set_name" access="public" output="false">
<cfargument name="name" type="any" required="true" />
<cfset variables.attributes.name = arguments.name />
<cfreturn />
</cffunction>
<cffunction name="get_description" access="public" output="false">
<cfreturn variables.attributes.description />
</cffunction>
<cffunction name="set_description" access="public" output="false">
<cfargument name="description" type="any" required="true" />
<cfset variables.attributes.description = arguments.description />
<cfreturn />
</cffunction>
<cffunction name="get_price" access="public" output="false">
<cfreturn variables.attributes.price />
</cffunction>
<cffunction name="set_price" access="public" output="false">
<cfargument name="price" required="true" />
<cfset variables.attributes.price = arguments.price />
<cfreturn />
</cffunction>
<!--- Data Methods --->
<cffunction name="getRecordset" access="public" output="false">
<cfargument name="where" required="false" />
<!--- TODO: Implement Method --->
<cfreturn />
</cffunction>
<cffunction name="getRecord" access="public" output="false">
<cfargument name="id" required="false" />
<!--- TODO: Implement Method --->
<cfreturn />
</cffunction>
</cfcomponent>

You should be able to tell that the setter and getter section of this object class is the same as the first one we built. When building CFCs, it is a good thing to take common functions within an object class and group them together. It is always smart to organize your code. So we have the same init function for now and the same getters and setters. At the bottom of the CFC code, you will see a section for data management. We are going to start with the getRecordset method. One thing you will notice is the name of the data source. (See Appendix A if you have not set this up yet.) We are using a Data Source Name (DSN) called cfb after the title of this book. We are adding the highlighted section of code to this method:

<cffunction name="getRecordset" access="public" output="false">
<cfargument name="where" default="" />
<cfscript> var rsReturn = ""; </cfscript> <cfquery datasource="cfb" name="rsReturn"> SELECT id, name, description, price FROM product </cfquery> <cfreturn rsReturn/> 
</cffunction>

We will step through these additions one item at a time. First we will look at the rsReturn variable. It could be declared in a<cfset> or inside a<cfscript></cfscript> code segment. We will talk about scope in more detail later. For now just consider this a 100 percent of the time rule. When we create any variables inside a CFC method, you "must" put the word var and a space between that word and the variable that you are declaring. If you do not do that, you will find you get errors when the method or variable is used multiple times within the CFC. So again, you must add var to your variables to create what are called "thread-safe variables".

Note

We are using the variable rsReturn to hold the recordset returned by the query. To be a thread-safe variable, rsReturn must be declared with var. However, we do not need to specify that it is a query.

Next, we are introduced to our first database tag in ColdFusion. This simple approach to connecting to the data sources in ColdFusion is by far as simple as it can get. This is one area that other platforms have still not matched. The select SQL statement is used to pull back any records found in that request. This can be in a single table or in a cross selection of multiple, related tables. Learning SQL is outside the scope of what we are focusing on in this book, so again see Appendix B for suggestions to extend that area of knowledge later. Right now, just use the queries we have prewritten for you. You should list the fields you are pulling back in the select section of the SQL statement as you see in the previous example. Use the datasource attribute to name the database data source name created on your system. The name attribute is where the recordset will be stored. This is also referred to as a query set in ColdFusion. We will also use the more universal term recordset interchangeably for query results.