Learning stored procedure basics in ColdFusion 8
Modified
15 October 2007
Requirements
User level
Intermediate
Required products
- ColdFusion 8 (Download trial)
Sample files
- stored_procedures.zip (49 KB)
Most ColdFusion applications center around a database. The key to creating efficient and secure ColdFusion applications is in the database integration. Stored procedures help developers accomplish both of these goals and also provide greater abstraction between the application code and database details. In this article, we’ll explore stored procedures and review a few examples using Oracle, Microsoft SQL Server (MSSQL), and MySQL.
What is a stored procedure?
A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form. Procedures can be shared between a number of programs, and provide improved performance as well as increased security.
Stored procedures can contain not only simple select, insert, update, and delete statements, but also can encapsulate procedural code. Since you compile and encapsulate stored procedures, they provide more efficient and secure data access.
Whenever you run a query statement against a database, the database performs two functions. First, it compiles the statement and, Second, it executes it. During compilation the database engine breaks the query apart, identifies the tables and relationships, determines which indexes to use and when to combine and filter records. For example, see the execution plan in Figure 1.
This execution plan shows a simple query that combines data from five tables using outer joins. With a standard query from a ColdFusion cfquery tag, the database engine recompiles the statement each time it executes the query. With a stored procedure, however, the database compiles the query plan once and reuses the compiled process. This pre-compilation can yield a significant performance advantage.
Encapsulating database code to simplify and secure database transactions
Stored procedures also help developers encapsulate database code. By placing all database access and manipulation code inside stored procedures, you create lots of little black boxes. Instead of a team of ColdFusion developers having to be familiar with the database schema, indexes, and columns, they simply need to know the names of stored procedures, which an experienced DBA can write and optimize. For example, a ColdFusion page might have a poorly written cross-tab query, as shown in Listing 1.
Listing 1. A query that performs a cross-tab style query against an Oracle database through ColdFusion.
<CFQUERY NAME="getCrossTabData" DATASOURCE="ORATEST">
SELECT
DV1.State_ID,
DV1.Data_Year,
DV1.Data_Value AS Val1,
DV2.Data_Value AS Val2
FROM
Data_Values DV1,
Data_Values DV2
WHERE
DV1.State_ID = DV2.State_ID
AND DV1.Data_Year = DV2.Data_Year
AND DV1.Data_Year BETWEEN #yearStart# AND #yearEnd#
AND DV1.Field_ID = 1000
AND DV2.Field_ID = 1001
ORDER BY
DV1.State_ID,
DV1.Data_Year
</CFQUERY>
If you instead use a stored procedure to perform the same select, it simplifies the ColdFusion code, as shown in Listing 2.
Listing 2. Using a cfstoredproc tag to perform the same cross-tab style query shown in Listing 1.
<CFSTOREDPROC PROCEDURE="pkg_Simple.Get_Values" DATASOURCE="oratst">
<CFPROCPARAM
VALUE="#yearStart#"
CFSQLTYPE="cf_sql_numeric">
<CFPROCPARAM
VALUE="#yearEnd#"
CFSQLTYPE="cf_sql_numeric">
<CFPROCRESULT
NAME="getCrossTabData">
</CFSTOREDPROC>
Encapsulating the database interaction provides a number of benefits:
- It simplifies the ColdFusion code. Instead of using a large query in your application, you simply call a stored procedure.
- It allows you to optimize SQL queries in a central location. With all SQL stored centrally in a database, you can appoint a DBA or senior developer to write or optimize queries. In this case, your DBA may choose to rewrite the above statement using only a single instance of the database table and an Oracle specific function,
DECODE, to generate the cross-tab data.
- It achieves greater security. The link between stored procedures and security is not always obvious. A stored procedure limits specific functionality based on required, type-specific input parameters. This forced parameterization ensures protection against SQL-injection attacks. Also, limiting access to the database is helps you take full advantage of the security features in stored procedures.
If you write an application so that all database interactions occur through stored procedures, you can limit the access that a specific database username and password (entered into ColdFusion Administrator for the application) has so that it can only run stored procedures. With this safeguard, anyone who gains access to your database or ColdFusion server can only perform very few tasks with such limited access.
Creating stored procedures
The section below reviews several sample stored procedures in Oracle, MSSQL, and MySQL. To follow along, download the table creation scripts from the Requirements section at the beginning of this article. These scripts create the tables and columns, and insert data.
Refer to the documentation for your database to learn how to execute the scripts. Typically, Oracle, use SQL Plus. In SQL Server, use the SQL Management Studio, and in MySQL use MySQL Command Line Client. Note that MySQL version 5 is required for stored procedure support.
At this time, also create your data sources in ColdFusion administrator. Name your Oracle data source oratst, SQL Server data source sqltst, and MySQL data source mytst.
Inserting data with stored procedures
In the first example, you'll learn to create a stored procedure that inserts new records into the Books table shown in Figure 2.
The table has four columns; three information fields, and a BookID (its primary key). The stored procedure will accept three input parameters and return one output parameter, the newly inserted BookID. See the stored procedures for both Oracle and MSSQL below in listings 3 and 4. Typically, you execute your stored procedures in SQL Plus in Oracle and Query Analyzer in SQL Server. For more information on executing queries and stored procedures, refer to the documentation for your specific database.
Listing 3. Stored procedure to insert a record into the Books table in Oracle.
CREATE OR REPLACE PROCEDURE Insert_Book (
arg_Title Books.Title%type,
arg_Price Books.Price%type,
arg_PublishDate Books.PublishDate%type,
arg_BookID OUT Books.BookID%type)
AS
num_BookID NUMBER;
BEGIN
SELECT seq_Books.NEXTVAL
INTO num_BookID
FROM DUAL;
INSERT INTO
Books (
BookID,
Title,
Price,
PublishDate )
VALUES (
num_BookID,
arg_Title,
arg_Price,
arg_PublishDate );
arg_BookID := num_BookID;
END;
/
Listing 4. Stored procedure to insert a record into the Books table in MSSQL.
CREATE PROCEDURE Insert_Book (
@arg_Title VARCHAR(255),
@arg_Price SMALLMONEY,
@arg_PublishDate DATETIME,
@arg_BookID INT OUT)
AS
BEGIN
INSERT INTO
Books (
Title,
Price,
PublishDate )
VALUES (
@arg_Title,
@arg_Price,
@arg_PublishDate );
SELECT @arg_BookID = @@IDENTITY;
END;
Listing 5. Stored procedure to insert a record into the Books table in MySQL.
DELIMITER //
CREATE PROCEDURE Insert_Book (
arg_Title VARCHAR(255),
arg_Price DECIMAL(6,2),
arg_PublishDate DATETIME,
OUT arg_BookID INT)
BEGIN
INSERT INTO
Books (
Title,
Price,
PublishDate )
SELECT
arg_Title,
arg_Price,
arg_PublishDate;
SET arg_BookID = LAST_INSERT_ID();
END;
//
DELIMITER ;
All three stored procedures perform the same function but require slightly different syntax. Oracle has a beneficial feature that allows you to specify data types for procedures so they match a field type using the following syntax: [Table Name].[Field Name]%type. MSSQL and MySQL use a simpler mechanism for inserting incremental values, which means the same procedure requires half the code for this simple routine.
While the stored procedure syntax is different, one big benefit of stored procedures is that the ColdFusion code that calls a stored procedure is exactly the same regardless of DBMS. Listing 5 shows a ColdFusion page that inserts books into the database using the stored procedure and confirm that the insert succeeded.
Listing 6. ColdFusion code inserts a book into a database and confirms the insert with the user. You can copy and paste this code into your own CFML page to see the results. Comment out the data source you are not using.
<cfset ds = "sqltst">
<!--- <cfset ds = "oratst"> --->
<!--- <cfset ds = "mytst"> --->
<!---
If submitting a new book,
insert the record and display
confirmation --->
<cfif isDefined("form.title")>
<cfstoredproc procedure="Insert_Book" datasource="#ds#">
<cfprocparam
cfsqltype="cf_sql_varchar"
value="#form.title#">
<cfprocparam
cfsqltype="cf_sql_numeric"
value="#form.price#">
<cfprocparam
cfsqltype="cf_sql_date"
value="#form.price#">
<cfprocparam
cfsqltype="cf_sql_numeric"
type="out"
variable="bookId">
</cfstoredproc>
<cfoutput>
<h3>'#form.title#' inserted into database. The ID is #bookId#.</h3>
</cfoutput>
</cfif>
<cfform action="#CGI.SCRIPT_NAME#" method="post">
<h3>Insert a new book</h3>
Title:
<cfinput
type="text"
size="20"
required="yes"
name="title"/>
<br/>
Price:
<cfinput
type="text"
size="20"
required="yes"
name="price"
validate="float" />
<br/>
Publish Date:
<cfinput
type="text"
size="5"
required="yes"
name="publishDate"
validate="date" />
<br/>
<input type="submit" value="Insert Book"/>
</cfform>
To call a stored procedure, you use two ColdFusion tags: cfstoredproc and cfprocparam. The cfstoredproc tag starts the call to the database and wraps the related tags. At a minimum, the tag requires the procedure and datasource attributes, which specify the stored procedure you wish to call and the data source to use for the call.
Use the cfprocparam tag to both pass parameters to a stored procedure and to specify variable names for output parameters. The cfsqltype attribute is always required and specifies the data type for the parameter. To input parameters, include the value attribute with the passed value. Alternatively, if you wanted to pass a null value instead, you could include the null attribute.
For the output parameter, you again use the cfprocparam tag, specifying the type attribute value as out, and provide a ColdFusion variable name in the variable attribute. While in this page, you only display the Book ID to the user, but you could use the ID to also add authors and publishers to the book record with additional programming.
Returning recordsets from stored procedures
In listings 3, 4, and 5, you created stored procedures that accepted arguments and returned simple values. Stored procedures can also return database records. The procedures in listings 7, 8, and 9 return book details based on a title search.
Listing 7. Stored procedure that searches for books in an Oracle data source.
CREATE OR REPLACE PACKAGE pkg_Search
AS
TYPE CUSTOM_REF_CURSOR IS REF CURSOR;
PROCEDURE By_Title (
arg_Title Books.Title%type,
arg_Cursor IN OUT CUSTOM_REF_CURSOR);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_Search
AS
PROCEDURE By_Title (
arg_Title Books.Title%type,
arg_Cursor IN OUT CUSTOM_REF_CURSOR)
IS
BEGIN
OPEN arg_Cursor FOR
SELECT
BookID,
Title,
Price,
PublishDate
FROM
Books
WHERE
Title LIKE '%' || arg_Title || '%';
END;
END;
/
Listing 8. Stored procedure that searches for books in a MSSQL data source.
CREATE PROCEDURE Search_By_Title (
@arg_Title VARCHAR(255))
as
BEGIN
SELECT
BookID,
Title,
Price,
PublishDate
FROM
Books
WHERE
Title LIKE '%' + @arg_Title + '%';
END;
Listing 9. Stored procedure that searches for books in a MySQL data source.
DELIMITER //
CREATE PROCEDURE Search_By_Title (
arg_Title VARCHAR(255))
BEGIN
SELECT
BookID,
Title,
Price,
PublishDate
FROM
Books
WHERE
Title LIKE CONCAT('%', arg_Title, '%');
END;
//
DELIMITER ;
Notice first in this example that the Oracle example is long for the amount of work it does. Oracle returns records to the calling application through a reference cursor. However, you cannot directly use a reference cursor (REF CURSOR) in a stored procedure; instead you must declare a custom type within a package—which is a collection of stored procedures and functions, similar to a ColdFusion component (CFC).
All Oracle packages have two parts, the package header and the package body. The header includes type declarations and stored procedure headers. The stored procedure details are then placed in the package body using the syntax shown in listing 7, above.
Again, you can use similar code to call both stored procedures, as shown in listing 10. The only difference in the call for Oracle and the other two database engines is the stored procedure name, since the Oracle procedure is a part of a package and neither MSSQL nor MySQL require packages for this task.
Listing 10. ColdFusion code to search for books using the stored procedures in listings 7, 8, and 9.
<cfset ds = "sqltst">
<cfset sp = "Search_By_Title">
<!---
<cfset ds = "mytst">
<cfset ds = "oratst">
<cfset sp = "pkg_Search.By_Title">
--->
<cfoutput>
<form action="#cgi.SCRIPT_NAME#" method="post">
<input type="text" name="criteria" size="20" />
<input type="submit" value="Search"/>
</form>
</cfoutput>
<cfif isDefined("form.criteria") AND form.criteria NEQ "">
<cfstoredproc procedure="#sp#" datasource="#ds#">
<cfprocparam
cfsqltype="cf_sql_varchar"
value="#form.criteria#">
<cfprocresult
name="searchResults">
</cfstoredproc>
<cfoutput>
<h3>Search results for '#form.criteria#'</h3>
</cfoutput>
<cfdump var="#searchResults#" label="Search Results">
</cfif>
Listing 8 provides a small form for users to enter search criteria; the form then passes the search criteria to the database. The listing uses cfdump to print the results to the screen.
The stored procedure call uses one new tag, cfprocresult, to provide a ColdFusion variable name for the recordset returned from the stored procedure. Since a stored procedure may return multiple recordsets, ColdFusion allows multiple cfprocresult tags within a single cfstoredproc tag.
Stored procedures provide a number of advantages over storing queries in ColdFusion pages. Pre-compiling SQL helps your application execute pages faster, and encapsulating SQL within a database allows you to specialize database development and increase security. Using stored procedures is simple in ColdFusion and you should consider using them for all database-centric applications.
