cf and stored procedures
DESCRIPTION
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” - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/1.jpg)
CF and Stored Procedures
Lei Wang
ALP International
![Page 2: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/2.jpg)
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
![Page 3: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/3.jpg)
Database Locks
“Lost update problem” Multiple users accessing the same data
Read from database
Update record
Update record
User1 User2
![Page 4: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/4.jpg)
Database Locks
Blocking access for all other usersUser1 User2
Begin Transaction
Commit Transaction
Begin Transaction
Commit Transaction
LockRead Value
Update Value
Lock Read Value
Update Value
Waiting
![Page 5: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/5.jpg)
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.
![Page 6: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/6.jpg)
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
![Page 7: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/7.jpg)
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
![Page 8: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/8.jpg)
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>
![Page 9: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/9.jpg)
How DB tools perform Lock
Transaction
A logical unit of work. Begin the transaction Commit the transaction or rollback
transaction
![Page 10: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/10.jpg)
Standard usage of transaction
BEGIN TRANSACTION MyTransactionSELECT * FROM roysched
WHERE title_id LIKE ‘Pc%’
UPDATE roysched
SET royalty = royalty * 1.10
WHERE title_id LIKE 'Pc%'
COMMIT TRANSACTION MyTransaction
![Page 11: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/11.jpg)
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
![Page 12: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/12.jpg)
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.
![Page 13: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/13.jpg)
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
![Page 14: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/14.jpg)
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
![Page 15: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/15.jpg)
What determines which tool to use Less traffic, simple web page
CFTRSACTION Heavy traffic, complicated web pages
Stored procedure
![Page 16: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/16.jpg)
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
![Page 17: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/17.jpg)
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">
![Page 18: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/18.jpg)
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">
![Page 19: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/19.jpg)
CFPROCPARAM
Type:
IN/OUT/INOUT CFSQLTYPE 17 total
CF_SQL_INTEGER
CF_SQL_CHAR
CF_SQL_VARCHAR
CF_SQL_DATE
CF_SQL_MONEY
![Page 20: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/20.jpg)
CFPROCPARAM
DBVARNAMEStart with @
@NextInvoiceID
VARIABLE
CF variable name
![Page 21: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/21.jpg)
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>
![Page 22: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/22.jpg)
Common mistakes when using Stored Procedure Declaration order in stored procedure is
different from CFPROCPARAM orderError messageOperand type clash: int is incompatible with text
![Page 23: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/23.jpg)
Variables are not in the same order@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">
![Page 24: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/24.jpg)
Common Mistakes
Every In variable need a valuecreate procedure Update_Email
@StudentID int, @InvoiceID int, @Email varchar(60)asBegin
SELECT @InvoiceID=InvoiceID,@Email=StudentEmailFROM T_StudentWHERE StudentID=@StudentID
UPDATE T_Invoice
SET Email=@EmailWHERE InvoiceID=@InvoiceID
END
![Page 25: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/25.jpg)
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=“[email protected]” dbvarname="@Email">
![Page 26: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/26.jpg)
Conflict with other DB tools
CFTRANSACTION and TRIGGERTriggers are specialized stored procedures that are executed automatically when a particular event occurs and are used to enforce data integrityA lot of triggers have Rollback transaction command
![Page 27: CF and Stored Procedures](https://reader034.vdocuments.us/reader034/viewer/2022042603/56813bb8550346895da4e9ee/html5/thumbnails/27.jpg)
Trigger CREATE TRIGGER Insert_T_ContactsON T_Contacts
FOR INSERT AS DECLARE @JustInsertedpEmail varchar(60),@ContactIDNew int, @ContactIDOld int,
BEGINSELECT @JustInsertedpEmail=PrimaryEmailAddress,
@ContactIDNew=ContactIDFROM INSERTEDSELECT @ContactIDOld=ContactID,FROM T_ContactsWHERE PrimaryEmailAddress=@JustInsertedpEmailAND ContactID!=@ContactIDNewIF @@ROWCOUNT > 0ROLLBACK TRANSACTION
End