cf and stored procedures l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CF and Stored Procedures PowerPoint Presentation
Download Presentation
CF and Stored Procedures

Loading in 2 Seconds...

play fullscreen
1 / 28

CF and Stored Procedures - PowerPoint PPT Presentation


  • 123 Views
  • Uploaded on

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”

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'CF and Stored Procedures' - les


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
cf and stored procedures

CF and Stored Procedures

Lei Wang

ALP International

main topics
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
Database Locks
  • “Lost update problem”
  • Multiple users accessing the same data

User1

User2

Read from database

Update record

Update record

database locks4
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

types of locks
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.
how cold fusion perform db locks
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
standard usage of cftransaction from cf studio
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

standard usage of cftransaction
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>

how db tools perform lock
How DB tools perform Lock
  • Transaction

A logical unit of work.

  • Begin the transaction
  • Commit the transaction or rollback transaction
standard usage of transaction
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

stored procedure
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

advantage of stored procedure
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.
stress test
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
stress test results
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

what determines which tool to use
What determines which tool to use
  • Less traffic, simple web page

CFTRSACTION

  • Heavy traffic, complicated web pages

Stored procedure

how to write stored procedure
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
how to use stored procedure
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">

how to user stored procedure
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">

cfprocparam
CFPROCPARAM
  • Type:

IN/OUT/INOUT

  • CFSQLTYPE 17 total

CF_SQL_INTEGER

CF_SQL_CHAR

CF_SQL_VARCHAR

CF_SQL_DATE

CF_SQL_MONEY

cfprocparam20
CFPROCPARAM
  • DBVARNAME

Start with @

@NextInvoiceID

  • VARIABLE

CF variable name

how to use stored procedure21
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>

common mistakes when using stored procedure
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

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">

Variables are not in the same order
common mistakes
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

every variable need a value
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">

conflict with other db tools
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

trigger
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

slide28
Q & A
  • Lwang@alpi.com