ColdFusion Day 28:

================

 

USING CFTRANSACTION

 

In ColdFusion, any transaction's placed between opening and closing CFTRANSACTION tags are treated as a single transaction. You place your queries within the CFTRANSACTION block. None of the changes are committed to the database until all the transactions within the block are successfully performed. A rollback will occur in all transactions if even one error occurs in the query. Listing 9.18 shows a CFTRANSACTION structure.

 

LISTING 9.18:    ch0918.cfm

        <CFTRANSACTION>    

 

            <CFQUERY NAME="InsertSupplier" DATASOURCE="sybex">

                INSERT INTO supplier (s_name, s_city, s_state, s_bb)

                VALUES ('University Supply Centre', 'Springfield', 'VA', 1)

            </CFQUERY>

 

            <CFQUERY NAME="InsertProduct" DATASOURCE="sybex">

                INSERT INTO product

                VALUES ('010','PAPER', 'Phone Log Pad', 5.00, 0.40, '100/pk', 14)

            </CFQUERY>

 

        </CFTRANSACTION>    

 

 

Two tasks are involved in the transaction. You are adding a new supplier and a new product from the new supplier. You are assigning the new supplier with a 010 supplier_id, WHICH ALREADY EXISTS. So, even though the first CFQUERY does not yield an error because the two queries are considered a transaction, if one of these queries yields an error, none of the queries in the transaction gets inserted.

 

Changing the supplier_id to a unique number will yield a successful transaction. See listing 9.19.

 

 

LISTING 9.19:    ch0919.cfm

        <CFTRANSACTION>    

 

            <CFQUERY NAME="InsertSupplier" DATASOURCE="sybex">

                INSERT INTO supplier (s_name, s_city, s_state, s_bb)

                VALUES ('University Supply Centre', 'Springfield', 'VA', 1)

            </CFQUERY>

 

            <CFQUERY NAME="InsertProduct" DATASOURCE="sybex">

                INSERT INTO product

                VALUES ('014','PAPER', 'Phone Log Pad', 5.00, 0.40, '100/pk', 14)

            </CFQUERY>

 

        </CFTRANSACTION>    

 

 

After the preceding code is executed, there will be a new supplier record and a new product record.

 

Transaction isolation provides you with control over database locking during a transaction. You can control the level of isolation by using the ISOLATION attribute of the CFTRANSACTION tag and setting it to one of the following values:

 

    *    Read_Uncommitted

    *    Read_Committed

    *    Repeatable_Read

    *    Serializable

 

These isolation types refer to locking schemes implemented by ODBC drivers. Consult the documentation for your database and ODBC driver for details of possible locking schemes.

 

CALLING STORED PROCEDURES BY USING CFSTOREDPROC

 

When SQL statements are executed on a regular basis, a stored procedure should be created. Stored procedures are compiled SQL statements that are permanently stored in the database in executable format. These compiled SQL statements are known as functions and subprograms. Stored procedures are pre-parsed, compiled, and ready to be invoked by the database user.

 

The advantages of using stored procedures are as follows:

 

    *    Response time is quicker.

    *    Statements are pre-parsed and in executable format.

    *    Stored procedures may call other procedures and functions.

    *    Stored procedures support modular programming structures.

 

ColdFusion enables you to call a stored procedure by using the CFSTOREDPROC tag. This is the ColdFusion tag used for executing stored procedures via an ODBC or native connection to a server database. You can call a stored procedure within your ColdFusion application in two ways:

 

    *    Using CFQUERY and specifying call parameters.

    *    Using CFSTOREDPROC, CFPROCPARAM, & CFPROCRESULT

 

The simplest way of calling a stored procedure is by using the CFQUERY tag. The following is an example of code calling a stored procedure:

 

        <CFQUERY NAME="ProcessProducts" DATASOURCE="sybex">

            {    call BookDB.dbo.sp_process_product(  '#p_name#'  )}

        </CFQUERY>

 

You need to use CFSTOREDPROC, CFPROCPARAM & CFPROCRESULT when calling a stored procedure that requires you to do the following:

 

    *    You need to specify input/or output parameters for your query.

    *    You need to capture the returned value.

    *    You need to select from multiple result sets in a stored procedure.

 

Stored procedures are generally found when working with Enterprise database systems such as Oracle. Through this course you will be working with Microsoft Access and will not encounter stored procedures. For more information about creating and using stored procedures, consult your database system's documentation and the ColdFusion documentation.

 

NOTE:    There is some additional information online at the knowledgebase at:    www.allaire.com    Article 8353 for Oracle and Article 924 for Sybase.