Using ColdFusion MX with Databases

208 Part II ✦ Using ColdFusion MX with Databases Listing 10-2 (continued) RAISERROR 50001 ‘The OrderItem could not be inserted.’ ROLLBACK TRANSACTION RETURN END UPDATE InventoryItem SET AvailableToSell = AvailableToSell - 10 WHERE ItemNumber = ‘CAS30-BLK’ IF @@ERROR != 0 BEGIN RAISERROR 50002 ‘The InventoryItem could not be updated.’ ROLLBACK TRANSACTION RETURN END COMMIT TRANSACTION The syntax is a little different, but the principles are very similar, aren’t they? It’s really just a matter of learning both methods for implementing transactions and then controlling them as close to the database server as your application enables you to do so. A good example of when you need to control a transaction within ColdFusion is whenever you are passing multiple rows of data from ColdFusion to the database server, and you want to encapsulate all those queries into a single transaction. Listing 10-3 illustrates this example. Listing 10-3: Inserting multiple rows within a single transaction <cfquery name=”InsertOrderItems” datasource=”CFMXBible”> INSERT INTO OrderItem ( SalesOrderID, ItemNumber, Description, UnitPrice, Quantity ) VALUES ( #Val(arSalesOrderID[i])#, ‘#Trim(arItemNumber[i])#’, ‘#Trim(arDescription[i])#’, #Val(arUnitPrice[i])#, #Val(arQuantity[i])# 13546228 ch10.F 1/30/03 10:50 AM Page 208 209Chapter 10 ✦ Using Advanced Database Techniques ) You currently have no easy and effective way to directly send multidimensional data from ColdFusion to a database server in a single statement. Until such a method exists, you must loop over ColdFusion arrays or structures and call CFQUERY once for each unit of data to be stored in the database. CFTRANSACTION and exception handling The default behavior of the CFTRANSACTION tag is such that if you do not explicitly command it to commit or rollback, it does so implicitly for you. This is the technique shown in Listings 10-1 and 10-3. Many ColdFusion developers are used to coding that way, but if you rely on the implicit behavior that CFTRANSACTION automatically commits and rolls back for you, stop doing so right now. In our own tests, ColdFusion MX slows to a crawl if CFTRANSACTION tags are not explicitly coded with BEGIN, COMMIT, and ROLLBACK commands. To make sure that you are committing only if everything works correctly and rolling back only if it doesn’t, you should be very aware of CFTRANSACTION’s behavior with respect to exception handling and also how to correctly nest CFTRANSACTION and CFTRY tags. The best practice for coding CFTRANSACTION is as follows: 1. CFSET a flag variable TRUE. 2. Begin a CFTRANSACTION. 3. Open a CFTRY block. 4. Code any database queries you need. 5. Test for exceptions with CFCATCH blocks as necessary. 6. Within any and all CFCATCH blocks that would indicate a failure of any part of the trans- action, CFSET the flag FALSE. 7. Close the CFTRY block. 8. Test the flag: Commit the transaction if TRUE and roll it back if FALSE. 9. Close the CFTRANSACTION. Listing 10-4 rewrites Listing 10-1 to incorporate CFTRANSACTION best practices. Listing 10-4: Combining CFTRANSACTION with CFTRY and CFCATCH <cfquery name=”InsertOrderItem” datasource=”CFMXBible”> INSERT INTO OrderItem ( Continued 13546228 ch10.F 1/30/03 10:50 AM Page 209 210 Part II ✦ Using ColdFusion MX with Databases Listing 10-4 (continued) SalesOrderID, ItemNumber, Description, UnitPrice, Quantity ) VALUES ( 1, ‘CAS30-BLK’, ‘30-Minute Cassette, Black Case’, 1.05, 10 ) <!--- If an error occurs after the first query, control immediately falls to CFCATCH ---> <cfquery name=”UpdateInventory” datasource=”CFMXBible”> UPDATE InventoryItem SET AvailableToSell = AvailableToSell - 10 WHERE ItemNumber = ‘CAS30-BLK’ As soon as any one of the queries throws an exception, program flow falls immediately to the applicable CFCATCH block, which then takes control and sets the OKtoCommit flag to FALSE. This circumvents any attempts to execute any other queries in the transaction. After the CFTRY block, the flag is tested and the entire transaction is either committed to disk or rolled back as if nothing ever happened. If you’re upgrading from an earlier version of ColdFusion Server, go right now and run the Find command on all your code for CFTRANSACTION, inspect your code, and determine whether you need to shore up your CFTRANSACTION tags. 13546228 ch10.F 1/30/03 10:50 AM Page 210 211Chapter 10 ✦ Using Advanced Database Techniques Transaction isolation Transaction isolation is one of the most misunderstood concepts of transactions, possibly because it forces you to think in terms of multiple users executing various multitable transac- tions over time, and that isn’t easy. If you don’t quite get it at first, that’s okay — don’t be so hard on yourself. Transaction isolation is the degree to which the effects of one transaction are isolated from those of other transactions that are also trying to execute at the same time. Isolating the effects of one transaction from those of another is controlled through the database server’s data-locking mechanisms, and these are, in turn, controlled through the SET TRANSACTION ISOLATION LEVEL command. Transaction isolation is a balancing act between concurrency and consistency. In other words, “how many people can bang on it at once” versus “how accurate is the data at any given point in time.” At one extreme, you can have everyone accessing data simultaneously (high concurrency) but at the cost of them working with data that may still be undergoing change as they access it (low consistency). At the other extreme, you can have each transaction absolutely separated from every other transaction such that everything appears to have been performed on a single-user machine (high consistency) but at the cost of considerably slowing down multi-user access (low concurrency). The key to a high performance database is executing each transaction with the minimum amount of locking possible to enable sufficiently consistent data. Doing so enables your database to operate with the highest possibly concurrency while affording it the data consistency that it requires. All in all, you have the following four levels of transaction isolation: ✦ Read Uncommitted ✦ Read Committed ✦ Repeatable Read ✦ Serializable Various database servers have various degrees of support for transaction isolation: Some support all four levels, some support none, and most serious database products support only two or three, so carefully read the documentation for both your database product and its driver before you attempt to set a transaction’s isolation level. The following sections describe what each of these levels mean. Read Uncommitted Read Uncommitted is the least-used level of isolation, because it is the least safe of all. Remember that all data operations (selects, inserts, updates, deletes) take place in the database server’s RAM and not on its disk. After data is requested, the database engine first looks for it in RAM — the data is possibly already there from another user’s request — and if the data isn’t there, it retrieves the necessary data from disk and loads it into RAM for use. If the data is being modified, after the database server sees that its operations in RAM are successful, it becomes “committed” to writing such modifications to disk, where they become permanent changes. Between the time that data is being modified in RAM and the time that the database server is committed to writing such modifications to disk, the data is said to be in an uncommitted state. If you set the isolation level to Read Uncommitted and then perform a query of data 13546228 ch10.F 1/30/03 10:50 AM Page 211 212 Part II ✦ Using ColdFusion MX with Databases that is in the process of being updated by another user, the result set contains data exactly as it appears with the other user’s modifications at that moment — even if those modifications have not yet been written to disk. If the original transaction rolls back, its modifications never really existed, and therefore the second transaction’s read of its uncommitted data was “dirty.” This is what the term dirty read means. Read Uncommitted can perform dirty reads because it has nothing at all to do with locks of any kind. At this level of isolation, locks are neither set by a transaction nor are they honored if set by another transaction. If you query the database to give you a set of data, it returns to you whatever values are in that data set at the very moment that it is requested, regardless of anything else that’s going on at the time. Read Uncommitted has the highest concurrency and the lowest consistency. It is set as follows: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED go BEGIN TRANSACTION . . . Oracle doesn’t support the Read Uncommitted isolation level. Read Committed If you take one step up in the consistency department, you take one corresponding step down in the concurrency department, and you end up at the Read Committed isolation level. The difference between Read Uncommitted and Read Committed is that Read Committed sees only data that has been committed to being written out to disk — in other words, locks placed on the data modified by the first user’s transaction are honored by the second user’s transaction, so data read by the second transaction is “clean” rather than “dirty.” Although Read Committed doesn’t permit dirty reads, it does permit what are called nonrepeatable reads, which can also become a problem. If you have a transaction that reads a set of data, performs some operation, and then reads the same set of data, for example, another user’s transaction could have modified your set of data in between the two times that it was read, thereby causing the second read to appear different from the first. In other words, the read was nonrepeatable. Read Committed is the default level of isolation for most database products and offers a good combination of concurrency and consistency for most applications. It is set as follows: SET TRANSACTION ISOLATION LEVEL READ COMMITTED go BEGIN TRANSACTION . . . Repeatable Read If your isolation level is set to Repeatable Read, no one else’s transactions can affect the consistency between one read of a data set at the beginning of the transaction and another read toward its end. By data set, we are talking about exactly the same collection of rows — not the results of a repeat of the SELECT statement that produced those rows. You see, a repeat of the SELECT statement may return rows that satisfy the WHERE clause that were inserted mid-transaction by another user. Such newly inserted rows are called phantom rows and are permitted under the Repeatable Read isolation level, because Repeatable Read preserves only the exact collection of rows originally read at the beginning of the transaction. Note 13546228 ch10.F 1/30/03 10:50 AM Page 212 213Chapter 10 ✦ Using Advanced Database Techniques Repeatable Read involves more locks on data across transactions, so although it increases data consistency, concurrency takes a performance hit. It is set as follows: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ go BEGIN TRANSACTION . . . Oracle doesn’t support the Repeatable Read isolation level. Serializable Now you come to the most isolated level of all. Serializable means that all transactions operate as if they were executed in series, or one after the other. In other words, the Serializable isolation level reduces your big, expensive, multi-user database server to a single-user machine so that the effects of one user’s operations are totally unaffected by those of others. That’s because each user, in effect, hogs the entire database while he is using it and doesn’t give it up until he finishes. The Serializable isolation level is the only one that prevents phantom rows, but you should ask yourself whether your transactions really care about this. As you can imagine, the Serializable level inflicts a serious and sometimes deadly hit to the multi-user performance of your system and, as such, should be used only if you have an absolutely compelling reason for it. The Serializable isolation level is set as follows: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE go BEGIN TRANSACTION . . . The big picture of transaction isolation Having a chart that plots isolation level against behavior may be useful, so check out the following table. Transaction isolation levels versus behaviors Isolation Level Dirty Read Nonrepeatable Read Phantom Read Uncommitted X X X Read Committed X X Repeatable Read X Serializable With all this about transaction isolation level said, how do you choose the appropriate isolation level for your transactions? By carefully studying the code in your transactions and considering their minimum consistency needs with respect to every other possible transaction that could be occurring simultaneously in the system. It’s not a simple task, but the following list gives you the basics: Note 13546228 ch10.F 1/30/03 10:50 AM Page 213 214 Part II ✦ Using ColdFusion MX with Databases 1. Start with your default transaction isolation level set to Read Committed. 2. Examine your first transaction’s code for the tables that it reads from and writes to. 3. If you don’t care whether the data being read is dirty, if the transaction’s modifications could take place anyway despite other concurrently running transactions, and if no further tests require a higher level of isolation, you can reduce the isolation level to Read Uncommitted for that transaction. 4. If you read from the same table twice during the same transaction and whether the first and second reads are different makes a difference to you, increase your isolation level to Repeatable Read on that transaction. 5. If you have any transactions that must not see others rows that are inserted while your transaction is executing, increase the isolation level of that transaction to Serializable. 6. Examine the rest of the transactions in your system by using this technique. Remember that you must individually consider each of your transactions in parallel with all other database operations that could possibly occur at the same time with multiple users — doing so is what makes fine-tuning transaction isolation such a difficult task. By the way, many developers just go as far as Step 1 and leave it at that. But if you’re going to use your database in a high-transaction throughput system, it serves you well to take the hours necessary to carefully analyze your transactions for their optimum isolation level and dial them in accordingly. Views As we state in Chapter 9, no matter how many tables a relational join traverses, regardless of whether it contains a GROUP BY clause (or anything else for that matter), all query result sets manifest themselves as one or more rows that contain an identical collection of one or more columns. So, in a way, query results are virtual tables based on underlying physical tables of data. Now imagine if you could take a query statement and define it as a formal database object that could be accessed just as a table can. Well, you can — that is what is called a view. Listing 10-5 defines a database view. Listing 10-5: Defining a database view CREATE VIEW vwEmployee AS SELECT SSN, CompanyID, Firstname, Lastname, DateOfBirth FROM Employee 13546228 ch10.F 1/30/03 10:50 AM Page 214 215Chapter 10 ✦ Using Advanced Database Techniques The boldfaced code in Listing 10-5 is just a standard SELECT statement; the rest of the listing simply encapsulates that SELECT statement as a formal, reusable database object — a view — with a formal name. Now, whenever you want to see all employees without their salaries, you can simply perform the following: SELECT * FROM vwEmployee Similarly, you can perform more elaborate queries on a view, as if it was a table, as follows: SELECT SSN, Firstname + ‘ ‘ + Lastname AS Fullname, DateOfBirth FROM vwEmployee WHERE DateOfBirth > ‘01/01/1960’ ORDER BY DateOfBirth DESC In fact, views can be filtered, sorted, and joined just as any physical table can. Horizontal and vertical masking Views typically hide elements of data so that what is returned exposes only the data that is needed. The elements being hid can be specific columns of a table, rows in that table that do not satisfy a WHERE clause, or a combination of both. Basically, a view masks unneeded data from your application. These masks can be either vertical or horizontal. A vertical mask shields specific columns of a table from returning and is simply the defined collection of underlying table columns that your view contains. A horizontal mask shields specific rows of a table from returning and is simply the WHERE clause that supplies the filtering criteria for the view. You can combine both vertical and horizontal masking in the same view. Listing 10-6, for example, returns only the SalesOrderID, SaleDate, and OrderTotal columns of only those sales orders with a Status of 20. Listing 10-6: A view that combines both vertical and horizontal masking CREATE VIEW vwFinishedSalesOrder AS SELECT SalesOrderID, SaleDate, Total FROM SalesOrder WHERE Status = 20 13546228 ch10.F 1/30/03 10:50 AM Page 215 216 Part II ✦ Using ColdFusion MX with Databases Relational views Views don’t stop at a single table. In fact, views are often used to simplify complicated relational joins across multiple tables so that the application developer doesn’t need to concern himself with such complexities and can instead concentrate on simply displaying relevant data. Listing 10-7 defines such a relational view. Listing 10-7: Defining a view across multiple joined tables CREATE VIEW vwEmployeeCompany AS SELECT e.SSN, e.Firstname + ‘ ‘ + e.Lastname AS Fullname, e.DateOfBirth, c.CompanyName FROM Employee e INNER JOIN Company c ON e.CompanyID = c.CompanyID The ColdFusion developer now can select all employees born earlier than 1960, hide the salary column, and display the name of the company for which each works, all by simply doing the following: SELECT * FROM vwEmployeeCompany Similarly, suppose that you tried to perform a SELECT against this view that included the Salary column, as follows: SELECT SSN, Fullname, DateOfBirth, CompanyName FROM VwEmployeeCompany WHERE Salary > 100000 It would fail, because although the Salary column is a part of the underlying Employee table, it is not a part of the vwEmployeeCompany view being queried. Precompiling queries So why define views at all? Why not just CFINCLUDE a common CFQUERY call wherever that query is needed? Isn’t that just as good? No, it isn’t. Not by a long shot. You see, if you send a query to your database server, the server goes through the complexities and processing expense of parsing the syntax with which the query is defined, optimizing the query for best performance, and compiling an execution plan. This very expensive process is what happens almost every time that you send a plain query to your database — just so that it can run the query! 13546228 ch10.F 1/30/03 10:50 AM Page 216 217Chapter 10 ✦ Using Advanced Database Techniques If you define a view, your database performs the same parse/optimize/compile process on the query for which the view is defined. If you call the view, the database server knows that an appropriate execution plan already exists and executes it directly, thereby eliminating virtually all the overhead necessary to execute the query and, in the bargain, increasing performance. You realize the biggest increases in performance by defining views on very complex relational queries, because these queries are the most difficult and time-consuming to parse and optimize. Enhancing security Another benefit that views afford you is the capability to lock down security on your database. Because a view is a formal database object, you can grant user and group privileges to it just as you would a table or a stored procedure. If fact, to denying direct access to physical tables and granting them only on views and stored procedures is a good practice overall — that way, you are absolutely certain that ColdFusion developers touch only the data that you want them to access. Caveats Views are great for easily selecting only that data that you want to access, but they can also be used to insert and update data. This can be both a help and a hindrance, depending on the view. Suppose that your view is a simple, single-table view containing all the NOT NULL columns of a table, as shown in Figure 10-1. Figure 10-1: A simple view containing all critical columns of a single table. In such a case, you can easily insert and update data through this view, as shown in Listing 10-8. Listing 10-8: Examples of inserting and updating through a simple view INSERT INTO vwInventoryItem ( ItemNumber, Description, UnitPrice, AvailableToSell ) VALUES ( ‘CAS30-BLK’, Continued InventoryItem.ItemNumber (vPK) InventoryItem.Description InventoryItem.UnitPrice InventoryItem.AvailableToSell InventoryItem.ReorderLevel InventoryItem.ReorderQuantity ItemNumber VARCHAR(15) NOT NULL Description VARCHAR(40) NOT NULL UnitPrice NUMERIC(12,2) NOT NULL AvailableToSell INTEGER NOT NULL ReorderLevel INTEGER NOT NULL ReorderQuantity INTEGER NOT NULL Comments VARCHAR(200) NULL InventoryItem vwInventoryItem 13546228 ch10.F 1/30/03 10:50 AM Page 217 218 Part II ✦ Using ColdFusion MX with Databases Listing 10-8 (continued) ‘30-Minute Cassette, Black Case’, 1.05, 100 ) UPDATE vwInventoryItem SET Description = ‘30-Minute Cassette, Black Case’, UnitPrice = 1.25, AvailableToSell = 90 WHERE ItemNumber = ‘CAS30-BLK’ But if the view does not contain all the critical (that is, NOT NULL) columns from its underlying table, you cannot insert new rows into the underlying table through that view, because you cannot supply all the data necessary to create a valid row in that table. The exception to this rule are NOT NULL columns for which default values are defined; these columns are automatically assigned their default values on insert if they’re not part of the view. You can update existing rows through such a view as long as you are setting the values only of those columns defined in the view — but that’s it. Problems appear if you attempt to insert, update, or delete by using relational views, because the action that you think you are performing isn’t always what’s going to happen — and the results can destroy your production data. In some cases, you throw an error because the database can’t figure out what you’re trying to accomplish. Our suggestion is that you never attempt to modify data by using relational views and instead define specific views and/or stored procedures for data modification or have application developers insert, update, and delete from the physical tables themselves. If you are interested in performing inserts, updates, and deletes from relational or complicated views, read your database product’s documentation regarding “Instead-Of Triggers,” which replace the code in the triggering statement with code of your own. Although we do touch on Instead-Of Triggers in Chapter 11, a discussion of using them to resolve relational view data modification problems is beyond the scope of this book. Stored Procedures After you first learned ColdFusion, you performed every call to your database by using a CFQUERY call. CFQUERY is simple and straightforward, so it is popular with developers who just want to get the job done, but another method is more scalable and flexible for manipulating your data: stored procedures. What is a stored procedure? At its most basic level, a stored procedure takes the SQL logic that you would normally write into a CFQUERY call and stores it directly in the database server. This may not sound like a big deal, and it may not even seem that useful at first, but the added efficiency of stored procedures can spell a huge performance gain for your ColdFusion applications. 13546228 ch10.F 1/30/03 10:50 AM Page 218 219Chapter 10 ✦ Using Advanced Database Techniques Whenever ColdFusion Server sends a CFQUERY call to your database server, the SQL logic in that CFQUERY call is pulled apart, or parsed, and translated into internal machine language instructions that your database server can directly understand; then these instructions are put through a process that determines the most efficient methods for executing them against the database. After the most efficient methods are decided on, they are assembled into an execution plan that is compiled and executed against the database. With some exceptions, this process happens every time that you send a CFQUERY call to your database server. That’s a lot of work to do just to get ready to execute your SQL logic. A stored procedure eliminates the need to repeat such drudgery every time that it is executed because it stores the compiled execution plan on the database server, where it can be called directly by an outside program — such as your ColdFusion application. And after this procedure is placed into the server’s memory, it remains there until it is pushed out by some other process that needs to run. So if you have sufficient memory, your stored procedure most likely stays cached in memory; this means that your database server doesn’t even need to retrieve the stored procedure object from disk to execute it, resulting in the fastest possible execution of SQL logic. Nice theory, but you need to take a look at the nuts and bolts of how it’s done. Listing 10-9 shows a typical CFQUERY call. Listing 10-9: A typical CFQUERY call <cfquery name=”GetCompanies” datasource=”CFMXBible”> SELECT CompanyName, ZipCode FROM Company WHERE State = ‘#Trim(FORM.State)#’ ORDER BY ZipCode ASC To create a stored procedure in Microsoft SQL Server from the SQL logic inside this CFQUERY call, you add the following code (Listing 10-10) and execute it against the database server (by using Query Analyzer or some other batch processing utility). Listing 10-10: Creating a stored procedure from the SQL inside Listing 10-9 CREATE PROCEDURE sp_GetCompanies ( @State CHAR(2) ) AS SELECT Continued 13546228 ch10.F 1/30/03 10:50 AM Page 219 220 Part II ✦ Using ColdFusion MX with Databases Listing 10-10 (continued) CompanyName, ZipCode FROM Company WHERE State = @State ORDER BY ZipCode ASC RETURN After you execute Listing 10-10 against the database, you have a precompiled stored procedure object, sp_GetCompanies, that you can call from your ColdFusion application. To call this stored procedure from ColdFusion, you use a CFSTOREDPROC tag in place of the CFQUERY tag, and you supply the parameter used by the WHERE clause through the CFPROCPARAM tag. This process is shown in Listing 10-11. Listing 10-11: Calling the stored procedure created in Listing 10-10 <cfstoredproc procedure=”sp_GetCompanies” datasource=”CFMXBible”> <cfprocparam type=”In” cfsqltype=”CF_SQL_CHAR” dbvarname=”@State” value=”#Trim(FORM.State)#” maxlength=”2” null=”No”> The database server receives this parameter from the CFPROCPARAM tags and supplies it to the stored procedure. You may also notice that, in Listing 10-12, you have a third tag involved named CFPROCRESULT. CFPROCRESULT binds the result set returned from the stored procedure to a named ColdFusion query object that can be CFOUTPUT just as the result of running your original CFQUERY call can in Listing 10-9. 13546228 ch10.F 1/30/03 10:50 AM Page 220 221Chapter 10 ✦ Using Advanced Database Techniques Listing 10-12: Stored procedure result sets are identical to CFQUERY result sets Company Name Zip Code #CompanyName# #ZipCode# Listing 10-12 is a very simple stored procedure that takes a single input parameter and returns a single result set. Listing 10-13 is a stored procedure that takes two input parameters and returns two result sets. See the section “Input parameters” for more details. Listing 10-13: A stored procedure that returns two result sets CREATE PROCEDURE sp_GetCompaniesEmployees ( @State Char(2), @Name Char(1) ) AS BEGIN SELECT CompanyName, ZipCode FROM Company WHERE State = @State ORDER BY ZipCode ASC SELECT Firstname, Lastname FROM Employee WHERE Lastname LIKE @Name + ‘%’ ORDER BY Lastname ASC, Firstname ASC END 13546228 ch10.F 1/30/03 10:50 AM Page 221 222 Part II ✦ Using ColdFusion MX with Databases Listing 10-13 is an easy extension of Listing 10-10. You just add another input parameter and another result set. Whenever multiple parameters are sent to a stored procedure, they are sent in the order that they appear within the CFSTOREDPROC tag. Listing 10-14 shows how you call Listing 10-13 from ColdFusion. Listing 10-14: Calling the stored procedure from Listing 10-13 <cfstoredproc procedure=”sp_GetCompaniesEmployees” datasource=”CFMXBible”> <cfprocparam type=”In” cfsqltype=”CF_SQL_CHAR” dbvarname=”@State” value=”#Trim(FORM.State)#” maxlength=”2” null=”No”> <cfprocparam type=”In” cfsqltype=”CF_SQL_CHAR” dbvarname=”@Name” value=”#Trim(FORM.Name)#” maxlength=”1” null=”No”> Again, Listing 10-14 is an easy extension of what you’ve already done in Listing 10-11: You just supply another input parameter and return a second result set that you can use as you can any other ColdFusion query object, as shown in Listing 10-15. Listing 10-15: Using multiple result sets from a stored procedure. Companies Company Name Zip Code #CompanyName# #ZipCode# 13546228 ch10.F 1/30/03 10:50 AM Page 222 223Chapter 10 ✦ Using Advanced Database Techniques Employees Firstname Lastname #Firstname# #Lastname# That’s the big picture of stored procedures. The following sections delve into the details. The three components of calling a stored procedure As you saw earlier in the section “What is a stored procedure?” the following three tags are associated with calling a stored procedure: 1. CFSTOREDPROC, which specifies the stored procedure being called. 2. CFPROCPARAM, which enables the flow of parameters between ColdFusion and the stored procedure. 3. CFPROCRESULT, which enables ColdFusion to use result sets returned from the stored procedure. We cover each of these in detail by using stored procedures written for SQL Server 2000, because it is a good platform for learning stored procedures. After you’re familiar with how stored procedures are implemented in SQL Server 2000, we follow up by showing how sp_GetCompaniesEmployees is adapted to run on the Oracle 9i platform so that you can see the critical differences between them — and you see plenty. CFSTOREDPROC To make ColdFusion call a stored procedure and use any result sets it returns, you must tell the CFSTOREDPROC tag the following four things: ✦ What database contains the stored procedure. ✦ The name of the stored procedure to be executed. ✦ How big the database server should make the blocks of data that it returns to ColdFusion Server. ✦ Whether you want ColdFusion to have access to the stored procedure’s Return Code. Now take a look at the following typical example of database connection parameters in the CFSTOREDPROC tag: <cfstoredproc procedure=”sp_SomeStoredProcedure” datasource=”CFMXBible” blockfactor=”10” returncode=”Yes”> After you are pointing to the right datasource, you specify the name of the stored procedure to execute through the procedure parameter, as follows: 13546228 ch10.F 1/30/03 10:50 AM Page 223 224 Part II ✦ Using ColdFusion MX with Databases <cfstoredproc procedure=”sp_SomeStoredProcedure” datasource=”CFMXBible” blockfactor=”10” returncode=”Yes”> You may need to prefix the stored procedure object with the owner’s name, followed by a period. BLOCKFACTOR controls how many rows at a time are returned from the database server to ColdFusion server. The range of values is from 1 to 100. Most developers never touch the BLOCKFACTOR parameter, because its default value of 1 is perfectly serviceable in most cases. If you increase the value of BLOCKFACTOR, you increase the size of each block of data transmitted between your database server and ColdFusion server and similarly decrease the number of fetches required to service the stored procedure request. This sounds pretty enticing, because fewer fetches mean less time wasted in mechanical overhead, and processing larger blocks of data is more efficient than processing smaller blocks of data, but you face a tradeoff if you do so. First, depending on the size of the individual rows returned from your stored procedure, you may run into memory issues with a large BLOCKFACTOR value. Your application may also suf- fer under heavy multi-user load with a large BLOCKFACTOR value. The optimum BLOCKFACTOR value is the largest setting that your application can consistently handle, considering memory constraints and multi-user load under production conditions. The following, for instance, code shows how to specify 10 rows at a time to be fetched from the database: <cfstoredproc procedure=”sp_SomeStoredProcedure” datasource=”CFMXBible” blockfactor=”10” returncode=”Yes”> If you have a good load-testing tool, such as Empirix e-TEST Suite, start your stored procedure with a BLOCKFACTOR of 1, perform load tests on the section of your application that makes use of the stored procedure, and then increase BLOCKFACTOR in increments of 10 between each load test until your Performance Per User graph begins to plateau. Finally, you can control whether your stored procedure passes its Return Code back to ColdFusion server by using the following code: <cfstoredproc procedure=”sp_SomeStoredProcedure” datasource=”CFMXBible” blockfactor=”10” returncode=”Yes”> The Return Code is programmatically set within the stored procedure so that it can be used to communicate any number of things, such as the status of the stored procedure’s execution, whether one option is taken as opposed to another, and so on. Although it is called a Return Code — which is exactly what it is — it is referred to by ColdFusion server as a Status Code — specifically, CFSTOREDPROC.StatusCode. See the section “Using the Return Code,” later in this chapter, to see how you can increase the flexibility of your ColdFusion apps. CFPROCPARAM After you have your call from ColdFusion to your stored procedure established, you need to establish the interface between your ColdFusion variables and the arguments used by your stored procedure. Note 13546228 ch10.F 1/30/03 10:50 AM Page 224 225Chapter 10 ✦ Using Advanced Database Techniques Understand first, however, that not all stored procedures take arguments. Listing 10-16, for example, creates a stored procedure that simply lists all companies in Georgia. Listing 10-16: A simple stored procedure CREATE PROCEDURE sp_GetGeorgiaCompanies AS SELECT CompanyName, ZipCode FROM Company WHERE State = ‘GA’ ORDER BY ZipCode ASC RETURN Now all that you need to do is to call the stored procedure, as shown in Listing 10-17. Listing 10-17: Call the simple stored procedure <cfstoredproc procedure=”sp_GetGeorgiaCompanies” datasource=”CFMXBible”> Input parameters Listing 10-16 doesn’t require any values to be passed to it, but most stored procedures require one or more input parameters to be supplied by ColdFusion. An input parameter is a value that is passed to a function or procedure. If you adapt Listing 10-16 to use an input parameter in place of the hard coded GA value, for example, you end up with Listing 10-18 (which is identical to Listing 10-10). Listing 10-18: Adapting sp_GetGeorgiaCompanies to accept an argument CREATE PROCEDURE sp_GetCompanies ( @State CHAR(2) ) AS SELECT Continued 13546228 ch10.F 1/30/03 10:50 AM Page 225 226 Part II ✦ Using ColdFusion MX with Databases Listing 10-18 (continued) CompanyName, ZipCode FROM Company WHERE State = @State ORDER BY ZipCode ASC RETURN The code in Listing 10-19 used to call the new stored procedure in Listing 10-18 is almost identical to the stored procedure call in Listing 10-17, except for adding a single CFPROCPARAM tag to supply the state abbreviation to the stored procedure. You should recognize this call as the same call in Listing 10-11. Listing 10-19: Calling sp_GetCompanies from ColdFusion <cfstoredproc procedure=”sp_GetCompanies” datasource=”CFMXBible”> <cfprocparam type=”In” cfsqltype=”CF_SQL_CHAR” dbvarname=”@State” value=”#Trim(FORM.State)#” maxlength=”2” null=”No”> Here’s a basic breakdown of what you’re seeing. ColdFusion takes the value from the input form that posted to this template and supplies that value to the stored procedure through CFPROCPARAM’s VALUE attribute. The value is formally defined as being of the database’s CHAR data type by specifying CFSQLTYPE=”CF_SQL_CHAR”, and its maximum length is limited to 2 via the MAXLENGTH attribute. If the parameter had been a NUMERIC data type, SCALE would be used to specify the number of decimal places to which the number is accurate. MAXLENGTH is used to specify the overall length of string data types, but in some cases, MAXLENGTH can also be used to limit the size of a numeric parameter being fed to a stored procedure and, thereby, prevent out-of-range errors from being thrown, as shown in Figure 10-2. Because you are not supplying this stored procedure parameter as a NULL value, you specify NULL=”No”. If you had wanted to supply a NULL value in this stored procedure parameter, you would have specified NULL=”Yes”, and the VALUE attribute of the CFPROCPARAM tag would have been ignored. 13546228 ch10.F 1/30/03 10:50 AM Page 226 227Chapter 10 ✦ Using Advanced Database Techniques The TYPE attribute specifies whether the parameter is being supplied to the stored procedure, received from the stored procedure, or both. In this case, you are just supplying the parameter to the stored procedure, so the value of the TYPE attribute is IN— it is being sent from ColdFusion into the stored procedure. Figure 10-2: Visualizing the communication between ColdFusion input parameters and their corresponding stored procedure parameters. And that’s basically how you pass parameters from ColdFusion to a stored procedure. Just think of it as a more formal way to supply ColdFusion values to a query, where the strings supplied by ColdFusion are formally bound to specific data types native to your database server and are given specific size limits within which to fit. After you know how to pass individual ColdFusion values to a stored procedure by using input parameters, you can move on to passing individual values from a stored procedure back to ColdFusion server by using output parameters. Output parameters You’re probably used to receiving values back from stored procedures as multirow result sets, but how should you handle receiving values that are not associated with table rows? For that situation, you use output parameters that, unlike input parameters that send values from ColdFusion to stored procedures, receive values from stored procedures and bind them to ColdFusion variables so that they can be used by ColdFusion applications. Listing 10-20 shows a stored procedure that calculates the average salary of all employees and returns the result as a single output parameter: Listing 10-20: A stored procedure that returns a single value in an output parameter CREATE PROCEDURE sp_GetAvgSalary ( @AverageSalary Numeric(12,2) OUTPUT ) AS Continued <cfstoredproc procedure="sp_GetCompanies" datasource="CFMXBible"> <cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="@State" value="#Trim(FORM.State)#" maxlength="2" null="No"> <cfprocresult name="GetCompanies" resultset="1"> CREATE PROCEDURE sp_GetCompanies( @State CHAR(2) ) AS SELECT CompanyName, ZipCode FROM Company WHERE State = @State ORDER BY ZipCode ASC RETURN 13546228 ch10.F 1/30/03 10:50 AM Page 227 228 Part II ✦ Using ColdFusion MX with Databases Listing 10-20 (continued) SELECT @AverageSalary = Avg(Salary) FROM Employee RETURN The OUTPUT qualifier next to a parameter is what instructs the stored procedure to expose that parameter to the calling program (in this case, your ColdFusion application) after it has finished executing. Listing 10-21 shows the ColdFusion code that executes the stored procedure in Listing 10-20 and displays the output parameter. Listing 10-21: Calling the stored procedure from Listing 10-20 and displaying the output parameter that it returns <cfstoredproc procedure=”sp_GetAvgSalary” datasource=”CFMXBible”> <cfprocparam type=”Out” cfsqltype=”CF_SQL_NUMERIC” variable=”AverageSalary” dbvarname=”@AverageSalary” scale=”2” null=”No”> #AverageSalary# The output parameter is exposed in ColdFusion as a simple local variable that is not associated with the stored procedure that created it. It is as if you had created the local variable by using a simple CFSET call. You are not restricted to only one output parameter; you can have as many output parameters as you want. Figure 10-3 shows what the communications between ColdFusion and the stored procedure look like. InOut parameters An InOut parameter can be sent to a stored procedure containing one value and then returned to the calling program containing a different value. Say, for example, that you have a stored proce- dure that receives a gift certificate coupon code and an amount due (for a sales order, inventory item, or any other monetary amount), and if the gift certificate if still valid, the Amount Due value is decreased by the amount of the certificate and is returned in the same parameter to the calling ColdFusion template. If the coupon doesn’t exist (@@ROWCOUNT != 1), then the Amount Due remains unchanged. Listing 10-22 contains a perfect application of an InOut parameter. 13546228 ch10.F 1/30/03 10:50 AM Page 228 229Chapter 10 ✦ Using Advanced Database Techniques Listing 10-22: Calling a stored procedure that contains an InOut parameter CREATE PROCEDURE sp_GetAmountDue ( @CouponCode Varchar(16), @AmountDue Numeric(12,2) OUTPUT ) AS DECLARE @Redeemed Bit, @PercentDiscount Numeric(12,2) SELECT @Redeemed = Redeemed, @PercentDiscount = PercentDiscount FROM Coupon WHERE CouponCode = @CouponCode IF (@@ROWCOUNT != 1) BEGIN RETURN -- Coupon doesn’t exist; no price change END IF (@Redeemed = 1) BEGIN RETURN -- Coupon already redeemed; no price change END ELSE BEGIN SELECT @AmountDue = @AmountDue * (1 - @PercentDiscount / 100) END RETURN Figure 10-3: Visualizing the communication between ColdFusion output parameters and their corresponding stored procedure parameters. <cfstoredproc procedure="sp_GetAvgSalary" datasource="CFMXBible"> <cfprocparam type="Out" cfsqltype="CF_SQL_NUMERIC" variable="AverageSalary" dbvarname="@AverageSalary" scale="2" null="No"> #AverageSalary# CREATE PROCEDURE sp_GetAvgSalary( @AverageSalary Numeric(12,2) OUTPUT ) AS SELECT @AverageSalary = Avg(Salary) FROM Employee RETURN 13546228 ch10.F 1/30/03 10:50 AM Page 229 230 Part II ✦ Using ColdFusion MX with Databases Listing 10-23 shows the ColdFusion code that executes the stored procedure and then displays the InOut parameter. Listing 10-23: Calling the stored procedure from Listing 10-22 and displaying the InOut parameter <cfstoredproc procedure=”sp_GetAmountDue” datasource=”CFMXBible”> <cfprocparam type=”In” cfsqltype=”CF_SQL_VARCHAR” dbvarname=”@CouponCode” value=”#Trim(FORM.CouponCode)#” maxlength=”16” null=”No”> <cfprocparam type=”InOut” cfsqltype=”CF_SQL_NUMERIC” dbvarname=”@AmountDue” value=”#Val(FORM.AmountDue)#” variable=”AmountDue” scale=”2” null=”No”> Figure 10-4 shows a conceptual diagram of how InOut parameters communicate with stored procedures. Although both SQL Server and Oracle support InOut type CFPROCPARAMs, only Oracle supports IN OUT qualifiers in the stored procedure definition itself. To use InOut parameters with SQL Server stored procedures, specify InOut as the CFPROCPARAM type and use the OUTPUT qualifier next to the names of the parameters in the stored procedure itself. For Oracle stored procedures, specify InOut as the CFPROCPARAM type and use the IN OUT qualifier next to the names of the parameters in the stored procedure itself. Passing parameters by position rather than by name Various documentation has mentioned that parameters may be passed to stored procedures in any order and that the binding that you specify by using DBVARNAME correctly maps Value attributes to their corresponding database variables, but this is not the case in practice. You should always pass parameters to stored procedures in exactly the same order as they appear in the stored procedure’s interface; otherwise, ColdFusion throws an exception. CFPROCRESULT Many, but not all, database servers can return result sets from stored procedures. For those that can, CFPROCRESULT binds those result sets to ColdFusion queries, which makes them available for use in your ColdFusion applications just as if they were returned from a CFQUERY call. And although some database servers can return multiple result sets from a single CFSTOREDPROC call, the driver with which ColdFusion accesses the database must support such a capability as well. For Oracle, you need a Type 4 JDBC Driver, such as the one that ships standard with ColdFusion MX Enterprise, or the free OIC driver from Oracle if you’re running ColdFusion MX Professional. Note 13546228 ch10.F 1/30/03 10:50 AM Page 230 231Chapter 10 ✦ Using Advanced Database Techniques Figure 10-4: Visualizing the communication between ColdFusion InOut parameters and their corresponding stored procedure parameters. Look back for a moment at Listing 10-17. You expected back only one result set from this stored procedure, and you bound that result set to a ColdFusion query object named GetGeorgiaCompanies by using the following CFPROCRESULT tag: This tag is instructing ColdFusion Server to take the first result set returned from the stored procedure and bind it to a ColdFusion query object named GetGeorgiaCompanies. After this tag executes, your ColdFusion application can use that GetGeorgiaCompanies query object just as if it came from a CFQUERY call. CFPROCRESULT binds result sets to ColdFusion query objects in the order in which they are created within the stored procedure. Mapping result sets in the stored procedure to query objects in ColdFusion may seem an obvious and trivial task — just number them in the order they appear, top to bottom — but this task can become confusing unless you follow the rules, as follows. < cfstoredproc procedure= "sp_GetAmountDue" datasource="CFMXBibIe"> <cfprocparam type="In" cfsqItype="CF_SQL_VARCHAR" "dbvarname= "@ CouponCode" vaIue= "#Trim( FORM.CouponCode)#" maxIength="16" nuII="No"> <cfprocparam type="InOut" cfsqltype="CF_SQL_NUMERIC "dbvarname= "@Amount Due" value="#VaI(FORM.Amount Due)#" variable="AmountDue" scaIe="2" nuIl="No"> CREATE PROCEDURE sp_GetAmountDue ( @ CouponCode Varchar(16), @AmountDue Numeric(12,2) OUTPUT ) AS DECLARE @Redeemed Bit, @PercentDiscount Numeric(12,2) SELECT @Redeemed = Redeemed, @ Percent Discount = PercentDiscount FROM Coupon WHERE CouponCode = @CouponCode IF (@@ROWCOUNT != 1) BEGIN RETURN END IF (@Redeemed = 1) BEGIN RETURN END ELSE BEGIN SELECT @AmountDue = @AmountDue * (1 - @PercentDiscount/ 100) END RETURN ↑ 13546228 ch10.F 1/30/03 10:50 AM Page 231 232 Part II ✦ Using ColdFusion MX with Databases In Transact/SQL, the SQL language used to write SQL Server stored procedures, variables are modified by using the same SELECT statement used to produce result sets. Modifying a variable does not count as a result set, so in reading through your stored procedure and enumerating the result sets, pass up any SELECT statements that simply create variables or change their values. By the same token, don’t confuse the SELECT of a single column from a single row as anything other than a full-fledged result set — just as if it was 20 columns wide and a thousand rows deep. Figure 10-5 shows an example of correctly enumerated result sets among variable operations. Remember: You never “skip over” anything in a stored procedure in enumerating its result sets; you should never leave any gaps in the enumeration sequence. Figure 10-5: If a SELECT statement produces output, it is an enumerated result set, regardless of its dimensions. Oracle stored procedures and packages In this section, you adapt Listing 10-13 to produce exactly the same results in Oracle. As you soon see, the differences are significant. This is Resultset 1 because it is the first SELECT statement that produces output This is not a ResultSet because it does not produce output This is Resultset 2 because it is the second SELECT statement that produces output This is not a Resultset because it does not produce output This is Resultset 3 because it is the third SELECT statement that produces output SELECT CompanyName, ZipCode FROM Company WHERE State = @State ORDER BY ZipCode ASC SELECT @myVar = 125000 SELECT @myVar AS aliasForColdFusionUse SELECT @yourVar = @myVar * 1.5 SELECT Firstname, L