CF and Stored Procedures

Document Sample
CF and Stored Procedures Powered By Docstoc
					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”
 Multiple users accessing the same data
     User1                        User2
             Read from database

             Update record
                                          Update record
Database Locks
 Blocking access for all other users
            User1              User2
     Begin Transaction
             Read Value
     Lock
                            Waiting
            Update Value

     Commit Transaction
                            Begin Transaction

                                  Read Value
                           Lock
                                  Update Value

                           Commit Transaction
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
 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)
   <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
   < 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
 Transaction
  A logical unit of work.
 Begin the transaction
 Commit the transaction or rollback
  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
 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
 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
 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
 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
 Less traffic, simple web page
  CFTRSACTION
 Heavy traffic, complicated web pages
  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,@P ayment TypeCode,
                          null,getdate(),@NextbContactID,null, @Token, 'N',null,null)


 IF @@ERROR <>0
 ROLLBACK TRANSACTION
 ELSE
 COMMIT TRANSACTION
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
 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
 Type:
  IN/OUT/INOUT
 CFSQLTYPE 17 total
     CF_SQL_INTEGER
     CF_SQL_CHAR
     CF_SQL_VARCHAR
     CF_SQL_DATE
     CF_SQL_MONEY
CFPROCPARAM
 DBVARNAME
 Start with @
 @NextInvoiceID
 VARIABLE
 CF variable name
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
 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
                            <cfprocparam type="in"
@Amount smallmoney,                    cfsqltype="CF_SQL_MONEY"
@DiscAmt smallmoney,            value="#attributes.Total_Due#"
                                dbvarname="@Amount">
@DiscCode char(3),          <cfprocparam ***
                                dbvarname="@DiscAmt">
@PaymentTypeCode char(2),
                            <cfprocparam ***
@Token varchar(30),             dbvarname="@DiscCode">
                            <cfprocparam ***
@NextContactID int,
                                dbvarname="@PaymentTypeCod
@NextInvoiceID int output       e">
                            <cfprocparam *** dbvarname="@Token">
                            <cfprocparam ***
                                dbvarname="@NextContactID">
                            <cfprocparam ***
                                dbvarname="@NextInvoiceID">
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
   <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
 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
  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
Q&A
 Lwang@alpi.com

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:3
posted:11/14/2011
language:English
pages:28