A beginner's guide to using stored procedures with ColdFusion
Modified
2 August 2004
Requirements
Prerequisite knowledge
CFQUERY tag
User Level
Beginning
Required products
- ColdFusion (Download trial)
Tutorials and sample files
- beg_storedproc_files.zip (4 KB)
Additional Requirements
Microsoft SQL Server 2000 (or any database server that supports stored procedures)
The scenario: You are a ColdFusion developer and your boss has asked you to create a simple web-based employee directory using ColdFusion to access stored procedures that the database administration group of your company created. For security reasons, you can only use stored procedures, not direct queries, to access the data from a sensitive personnel database. How do you succeed at developing the application?
This tutorial will help you understand stored procedure basics. It will also help you figure out what questions to ask your database administrators about the database and define the stored procedures you'd like them to create for you, based on your application needs. You'll build a simple web-based employee directory using ColdFusion tags to access stored procedures.
Background and Benefits of Stored Procedures
Stored procedures are (typically small) programs that run in a language that is specific to a particular database server. The programming language varies from database server to database server, and is designed to extend SQL to offer additional functionality that’s not available through standard SQL syntax. Stored procedures offer a number of compelling benefits for enterprise-level applications: security, performance, and business logic abstraction. This article will help you understand how to use ColdFusion as a presentation layer for a set of existing stored procedures.
Security: Stored procedures offer the ability to restrict inputs and results. With stored procedures, your database administrator can limit access to sensitive information.
Performance: Database management systems have two halves: a query optimizer and an execution engine. The query optimizer reads a query and performs a lot of CPU-intensive work to parse the query and determine which indexes and join methods would be best to use for the tables in a query. The query plan created by the query optimizer is then passed to the execution engine, which follows the query plan and gathers the specified data, perform joins, and sort results. When using a stored procedure, the database can remember, or cache the query plan, which bypasses much of the work involved in carrying out queries.
Business logic abstraction: If multiple systems access the same database, then any change in the database structure can result in considerable work to rewrite all systems that use the tables affected by the change. By moving the business logic into stored procedures, a change in the underlying table structure can be masked by a rewrite of the stored procedure, so that it returns the correct data for the same input, even though the table structure has been altered.
The Scenario
As I mentioned in the introduction, in this scenario, you must create a simple web-based employee directory using ColdFusion to access stored procedures that the database administration group of your company created. For security reasons, you must use stored procedures, not direct queries, to access the data from a sensitive personnel database.
For this application, you will create a simple search form to request and match the last name and/or first name, region, or extension number to the company’s employees. If the request finds more than one match, the application displays a list of matching results; from that list, a user can select a name and see the employee details, including a list of territories assigned to that employee.
Setting Up the Database
This tutorial uses the Northwind example database installed by default with Microsoft SQL Server 2000. Use the following steps to set up the data source and import the stored procedures for this tutorial.
- In ColdFusion, create a data source named Directory that connects to the Northwind example database. Verify that the connection succeeds.
- Open the SQL Server Management Console. Import and run procedures.sql to create the stored procedures in the Northwind example database.
Data Requirements
First, determine which pages to create and their data requirements. In this scenario, after reviewing the customer requirements and then meeting with the database administrators about the application, the plan is to create three files that provide the following data requirements:
| Page Name | Purpose | Data Requirements | Procedure Name |
|---|---|---|---|
| index.cfm | Search Form |
|
|
| search.cfm | Results List |
|
|
| detail.cfm | Employee Detail |
|
|
You can use this table as you plan your own application. You fill out the Page Name, Purpose, and most importantly, the Data Requirements (for each page). Your database administrator might give you a list of stored procedures available to you (the Procedure Name column). If not, bring this list to your meeting with the database administrator, and ask her which stored procedures can fulfill the data requirements for each page you need to create.
Next, you may have to ask your database administrators to create some stored procedures. In asking your administrators to create procedures, you must also specify the inputs and outputs for the procedure.
| Procedure Name | Inputs | Outputs |
|---|---|---|
| spListRegions | None | ResultSet with RegionID and RegionDescription |
| SpSearchByName | FirstName (varchar) LastName (varchar) |
ResultSet with EmployeeID, Title, Lastname, Firstname, Extension, City, Region, and Country |
| spSearchByPhone | PhoneNumber (varchar) | ResultSet with EmployeeID, Title, Lastname, Firstname, Extension, City, Region, and Country |
| spSearchByRegionID | RegionID (int) | ResultSet with EmployeeID, Title, Lastname, Firstname, Extension, City, Region, and Country |
| spEmployeeExists | EmployeeID (int) | IsExisting (int) |
| spGetEmployee | EmployeeID (int) |
|
Using ColdFusion To Access Stored Procedures
The file index.cfm (the complete code is in the ZIP file in the Requirements area) calls a simple stored procedure that has no inputs, and returns a result set with a list of regions. The call to the cfstoredproc tag looks like the following:
<!--- This retrieves the resultset that lists regions --->
<cfstoredproc procedure="spListRegions" datasource="#request.datasource#">
<!--- There are no input parameters to this stored procedure, --->
<!--- so there are no cfprocparam tags. --->
<!--- One resultset is returned called "qEmployees" --->
<cfprocresult name="qRegions">
</cfstoredproc>
In the above code snippet, to display the result set, you use the cfprocresult tag. You use it much like you use a result set from a cfquery tag:
<select name="ffRegionID">
<option value="">(Select)</option>
<cfoutput query="qRegions">
<option value="#regionID#">#RegionDescription#</option>
</cfoutput>
</select>
The file search.cfm, has some examples of inputs submitted to stored procedures. In the following example, you pass the RegionID from the index.cfm page to the stored procedure using the cfprocparam tag.
<!--- When the user selects a region, use spSearchByRegionID --->
<cfstoredproc procedure="spSearchByRegionID" datasource="#request.datasource#">
<!--- there is one input parameter, RegionID --->
<cfprocparam value="#ffRegionID#" cfsqltype="CF_SQL_INTEGER">
<!--- One resultset is returned called "qEmployees" --->
<cfprocresult name="qEmployees">
</cfstoredproc>
Another procedure call in search.cfm has two inputs: lastname and firstname. Note that in ColdFusion, passed inputs to stored procedures are based on a matched position, not by "name" attribute values.
In the following example, the first parameter is @FirstName and the second parameter is @LastName. In short, you must pass the first name as the first parameter and pass the last name as the second parameter:
<!--- When the user enters a firstname or lastname, use spSearchByName --->
<cfstoredproc procedure="spSearchByName" datasource="#request.datasource#">
<!--- There are two input parameters: --->
<!--- the first is FirstName, the second is LastName --->
<cfprocparam value="#ffFirstName#" cfsqltype="CF_SQL_VARCHAR">
<cfprocparam value="#ffLastName#" cfsqltype="CF_SQL_VARCHAR">
<!--- One resultset is returned called "qEmployees" --->
<cfprocresult name="qEmployees">
</cfstoredproc>
Some procedures can return values through the parameters provided. In detail.cfm, the procedure has one "in" parameter, and one "out" parameter. The "out" parameter in this example returns a one when an employee record exists, and a zero if it doesn’t exist:
<cfstoredproc procedure="spEmployeeExists" datasource="#request.datasource#">
<cfprocparam value="#ffEmployeeID#" cfsqltype="CF_SQL_INTEGER" type="In">
<cfprocparam variable="isEmployeeReal" cfsqltype="CF_SQL_INTEGER" type="Out">
</cfstoredproc>
With this particular stored procedure, if the employee record exists, the variable isEmployeeReal returns a value of one; if not, then the variable returns a value of zero. Notice that when type="in" in a cfprocparam tag, you use the value attribute to specify a value to pass in, but when type="out", the variable attribute specifies the name of the ColdFusion variable that will hold the value that the stored procedure returns.
Other stored procedures can return multiple "out" parameters, or even have "in" parameters that are also "out" parameters, which are called "inout" parameters. When using "inout" parameters, however, you must use the value and variable attributes of the cfstoredproc tag.
The following is a case where a stored procedure returns more than one set of results. Note the resultset attribute.
<cfstoredproc procedure="spGetEmployee" datasource="#request.datasource#">
<!--- There is one input parameter, EmployeeID --->
<cfprocparam value="#ffEmployeeID#" cfsqltype="CF_SQL_INTEGER">
<!--- Two resultsets are returned --->
<!--- the first is the employee data called qEmployee --->
<cfprocresult name="qEmployee" resultset="1">
<!--- The second is the list of territories, which you name qTerritories --->
<cfprocresult name="qTerritories" resultset="2">
</cfstoredproc>
You must give the two result sets two different names, as in the example.
Result Sets and Oracle
Oracle doesn't return result sets from stored procedures by the conventional means, but rather through a special type of "out" parameter, called a reference cursor. The drivers supplied for use in Java systems (called Java DataBase Connectivity drivers, or JDBC drivers) do not return reference cursors as JDBC "ResultSet" objects using the standard JDBC getResultSet() method.
The JDBC drivers for Oracle databases that Macromedia supplies (which are different than the Oracle JDBC drivers from Oracle) return reference cursors as result sets through the JDBC standard getResultSet() method; however, for this to work correctly, three conditions are required:
- You must return the reference cursor "out" parameters after all of the non-reference-cursor parameters.
- You must define the parameters as "out" parameters, not "inout" parameters.
- You must use the
cfprocresulttag instead of using thecfprocparamtag to retrieve reference cursors.
For more information on reference cursors and the cfstoredproc tag, see ColdFusion MX: Calling stored procedures which return reference cursors in Oracle 8.1 and higher (TechNote 18963).
Where To Go from Here
For more information on how to create stored procedures for your database, see your database vendor's documentation, as there is no standard syntax for stored procedures. Even though SQL has a standards-based syntax, veteran programmers will note that there are substantial differences in even basic SQL from one database to another. In fact, the SQL standard does not even provide a uniform syntax for expressing dates and times, which can make the creation of applications that work with multiple database vendors problematic. Note that using the cfqueryparam tag or the using stored procedures written for each database vendor can help.
Tutorials & Samples |
