1 / 28

CF and Stored Procedures

CF and Stored Procedures. Lei Wang ALP International. Main Topics. Database Locks How Cold Fusion perform DB locks Stored Procedure Stress test to compare Cftransaction and stored procedure How to use stored procedure Common Mistakes. Database Locks. “Lost update problem”

taran
Download Presentation

CF and Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CF and Stored Procedures Lei Wang ALP International

  2. Main Topics • Database Locks • How Cold Fusion perform DB locks • Stored Procedure • Stress test to compare Cftransaction and stored procedure • How to use stored procedure • Common Mistakes

  3. Database Locks • “Lost update problem” • Multiple users accessing the same data User1 User2 Read from database Update record Update record

  4. Database Locks • Blocking access for all other users User2 User1 Begin Transaction Read Value Waiting Lock Update Value Commit Transaction Begin Transaction Read Value Lock Update Value Commit Transaction

  5. Types of Locks • Table-level: supported by all database. The entire table is locked when a user is posting a transaction that utilizes a row from that table • Row-level: supported by higher-end database. Only records being utilized by a transaction are locked.

  6. How Cold Fusion perform DB Locks • Use CFTRANSACTION to group multiple queries into a single unit. CFTRANSACTION also provides commit and rollback processing. • <CFTRANSACTION> ACTION="BEGIN" or "COMMIT" or "ROLLBACK" ISOLATION="Read_Uncommitted" or "Read_Committed" or "Repeatable_Read" </CFTRANSACTION> • CFLOCK

  7. Standard usage of cftransaction(from CF Studio) • <CFTRY> <CFSET commitIt = "Yes"> <CFTRANSACTION ACTION="BEGIN"> <CFQUERY NAME='makeNewCourse' DATASOURCE='cfsnippets'> INSERT INTO Courses (Number, Descript) VALUES ('#myNumber#', '#myDescription#') </CFQUERY> <CFCATCH TYPE="DATABASE"> <CFTRANSACTION ACTION="ROLLBACK"/> <CFSET commitIt = "No"> </CFCATCH> <CFIF commitIt> <CFTRANSACTION ACTION="COMMIT"/> <CFQUERY > ……………………………………… </CFQUERY> </CFTRANSACTION> </CFTRY> Ending tag within the cftry block should be a cfcatch tag

  8. Standard usage of cftransaction • <CFTRANSACTION ACTION=“BEGIN”> <CFTRY> <CFQUERY NAME='makeNewCourse' DATASOURCE='cfsnippets'> INSERT INTO Courses (Number, Descript) VALUES ('#myNumber#', '#myDescription#') </CFQUERY> <CFQUERY > ……………………………………… </CFQUERY> <CFTRANSACTION ACTION="COMMIT"/> <CFCATCH TYPE="DATABASE"> <CFTRANSACTION ACTION="ROLLBACK"/> </CFCATCH> </CFTRY> </CFTRANSACTION>

  9. How DB tools perform Lock • Transaction A logical unit of work. • Begin the transaction • Commit the transaction or rollback transaction

  10. Standard usage of transaction • BEGIN TRANSACTION MyTransaction SELECT * FROM roysched WHERE title_id LIKE ‘Pc%’ UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%' COMMIT TRANSACTION MyTransaction

  11. Stored Procedure • A stored procedure is basically a precompiled program that is stored at the server site. • Stored procedure is not written in SQL PL/SQL Oracle Transact-SQL Sybase & SQL Server

  12. Advantage of Stored Procedure • It enables you to write and maintain a single set of code that is utilized by all current and future applications that utilize the database • It insulates the application developers from the structure of the database • It can provide better performance.

  13. Stress test • LoadRunner is used to conduct 3 levels of stress test, 10, 50, 100 users. • Internal network 100 M bps • Cold Fusion server 5 on Windows 2000 server (1.5 Ghz CPU, 512 MB memory) • SQL server 7 on the same Windows 2000 server • 5 Pentium 430 Mhz Dell machines as client

  14. Stress test results • Users cftransaction Stored Procedure 1 0.11 0.11 10 1.96 1.47 50 8.15 4.57 100 15 9

  15. What determines which tool to use • Less traffic, simple web page CFTRSACTION • Heavy traffic, complicated web pages Stored procedure

  16. How to write stored procedure • CREATE PROCEDURE Insert_invoice @Amount smallmoney,@DiscAmt smallmoney,@DiscCode char(3),@PaymentTypeCode char(2), @Token varchar(30), @NextbContactID int ,@NextInvoiceID int output • AS • BEGIN TRANSACTION select @NextInvoiceID=ISNULL(max(InvoiceID)+1,10000) from T_Invoice Insert into T_Invoice values( @NextInvoiceID,getdate(),@Amount,@DiscAmt,@DiscCode,@PaymentTypeCode, null,getdate(),@NextbContactID,null,@Token,'N',null,null) • IF @@ERROR <>0 • ROLLBACK TRANSACTION • ELSE • COMMIT TRANSACTION

  17. How to use stored procedure • Cfstoredproc tag <CFSTOREDPROC PROCEDURE="procedure name" DATASOURCE="ds_name" USERNAME="username" PASSWORD="password" DBSERVER="dbms" DBNAME="database name" BLOCKFACTOR="blocksize" PROVIDER="COMProvider" PROVIDERDSN="datasource" DEBUG="Yes/No" RETURNCODE="Yes/No">

  18. How to user Stored Procedure • Cfprocparam tag <CFPROCPARAM TYPE="IN/OUT/INOUT" VARIABLE="variable name" DBVARNAME="DB variable name" VALUE="parameter value“ CFSQLTYPE="parameter datatype“ MAXLENGTH="length" SCALE="decimal places" NULL="yes/no">

  19. CFPROCPARAM • Type: IN/OUT/INOUT • CFSQLTYPE 17 total CF_SQL_INTEGER CF_SQL_CHAR CF_SQL_VARCHAR CF_SQL_DATE CF_SQL_MONEY

  20. CFPROCPARAM • DBVARNAME Start with @ @NextInvoiceID • VARIABLE CF variable name

  21. How to use Stored Procedure <cfstoredproc datasource="#request.DSN#" procedure="Insert_Invoice"> <cfprocparam type="in" cfsqltype="CF_SQL_MONEY" value="#attributes.Total_Due#" dbvarname="@Amount"> <cfprocparam type="in" cfsqltype="CF_SQL_MONEY" value="#attributes.DiscAmt#" dbvarname="@DiscAmt"> <cfprocparam type="in" cfsqltype="CF_SQL_CHAR" value="#attributes.assumedDiscount#" dbvarname="@DiscCode"> <cfprocparam type="in" cfsqltype="CF_SQL_CHAR" value="#attributes.PaymentType#" dbvarname="@PaymentTypeCode"> <cfprocparam type="in" cfsqltype="CF_SQL_VARCHAR" value="#str_customerToken#" dbvarname="@Token"> <cfprocparam type="in" cfsqltype="CF_SQL_INTEGER" value="1" dbvarname="@NextContactID"> <cfprocparam type="out" cfsqltype="CF_SQL_INTEGER" variable="NextInvoiceID" dbvarname="@NextInvoiceID"> </cfstoredproc>

  22. Common mistakes when using Stored Procedure • Declaration order in stored procedure is different from CFPROCPARAM order Error message Operand type clash: int is incompatible with text

  23. @Amount smallmoney, @DiscAmt smallmoney, @DiscCode char(3), @PaymentTypeCode char(2), @Token varchar(30), @NextContactID int, @NextInvoiceID int output <cfprocparam type="in" cfsqltype="CF_SQL_MONEY" value="#attributes.Total_Due#" dbvarname="@Amount"> <cfprocparam *** dbvarname="@DiscAmt"> <cfprocparam *** dbvarname="@DiscCode"> <cfprocparam *** dbvarname="@PaymentTypeCode"> <cfprocparam *** dbvarname="@Token"> <cfprocparam *** dbvarname="@NextContactID"> <cfprocparam *** dbvarname="@NextInvoiceID"> Variables are not in the same order

  24. Common Mistakes • Every In variable need a value create procedure Update_Email @StudentID int, @InvoiceID int, @Email varchar(60) as Begin SELECT @InvoiceID=InvoiceID,@Email=StudentEmail FROM T_Student WHERE StudentID=@StudentID UPDATE T_Invoice SET Email=@Email WHERE InvoiceID=@InvoiceID END

  25. Every Variable need a value • <cfprocparam type="in" cfsqltype="CF_SQL_INTEGER" value="#attributes.StudentID#" dbvarname="@StudentID"> <cfprocparam type="in" cfsqltype="CF_SQL_INTEGER" value="#attributes.InvoiceID#" dbvarname="@InvoiceID"> <cfprocparam type="in" cfsqltype="CF_SQL_VARCHAR" value=“no@not.com” dbvarname="@Email">

  26. Conflict with other DB tools • CFTRANSACTION and TRIGGER Triggers are specialized stored procedures that are executed automatically when a particular event occurs and are used to enforce data integrity A lot of triggers have Rollback transaction command

  27. Trigger • CREATE TRIGGER Insert_T_Contacts ON T_Contacts FOR INSERT AS DECLARE @JustInsertedpEmail varchar(60), @ContactIDNew int, @ContactIDOld int, BEGIN SELECT @JustInsertedpEmail=PrimaryEmailAddress, @ContactIDNew=ContactID FROM INSERTED SELECT @ContactIDOld=ContactID, FROM T_Contacts WHERE PrimaryEmailAddress=@JustInsertedpEmail AND ContactID!=@ContactIDNew IF @@ROWCOUNT > 0 ROLLBACK TRANSACTION End

  28. Q & A • Lwang@alpi.com

More Related