Professional Web Applications Themes

re-writting stored procedures as ,cfc - Coldfusion - Advanced Techniques

Hi, I have the unfortunat task of re-writting our sql stored procedures as a .cfc. Thing is ive never touched or even looked at coldfusion (i drew the short straw). And i was kind of hoping some kind person can help me. Ive enclosed our Simpliest stored procedure and was kind of hope someone can help me with tisone so that i have an idea of what to do for the rest. All help is very much appreciated. Thanks Andy CREATE PROCEDURE [saveAuditTrail] ( sObjectType varchar(50), sObjectStatus varchar(50), sActionCode varchar(50), UserID int, objUUID varchar(100), sAuditTrailNotes varchar(8000), bIsMajor bit, dNow datetime ...

  1. #1

    Default re-writting stored procedures as ,cfc

    Hi,

    I have the unfortunat task of re-writting our sql stored procedures as a .cfc.
    Thing is ive never touched or even looked at coldfusion (i drew the short
    straw).

    And i was kind of hoping some kind person can help me. Ive enclosed our
    Simpliest stored procedure and was kind of hope someone can help me with tisone
    so that i have an idea of what to do for the rest.

    All help is very much appreciated.

    Thanks

    Andy

    CREATE PROCEDURE [saveAuditTrail]
    (
    sObjectType varchar(50),
    sObjectStatus varchar(50),
    sActionCode varchar(50),
    UserID int,
    objUUID varchar(100),
    sAuditTrailNotes varchar(8000),
    bIsMajor bit,
    dNow datetime
    )
    AS
    declare iVersion int , iMajorVersion int
    If sActionCode in('B','C','Cc','Q','Qc','Cu','Ca','Cs') and sObjectStatus <>
    'BeingTranslated'
    begin
    set iVersion = 0
    set iMajorVersion = 0
    end
    else if sActionCode in('E','Ct') and sObjectStatus <> 'BeingTranslated'
    begin
    if bIsMajor = 1
    begin
    set iMajorVersion = (SELECT MAX(iMajorVersion) FROM tblAuditTrail WHERE uuid
    =objUUID)
    if iMajorVersion= null
    begin
    set iMajorVersion=0
    end
    set iMajorVersion=iMajorVersion + 1
    set iVersion = 0
    end
    else
    begin
    set iMajorVersion = (SELECT MAX(iMajorVersion) FROM tblAuditTrail WHERE uuid
    =objUUID)
    if iMajorVersion= null
    begin
    set iMajorVersion=0
    end
    set iVersion = (SELECT MAX(iVersion) FROM tblAuditTrail WHERE uuid =objUUID
    and iMajorVersion=iMajorVersion)
    if iVersion= null
    begin
    set iVersion=0
    end
    set iVersion=iVersion + 1

    end
    end
    else
    begin
    set iMajorVersion = (SELECT MAX(iMajorVersion) FROM tblAuditTrail WHERE uuid
    =objUUID)
    if iMajorVersion= null
    begin
    set iMajorVersion=0
    end
    set iVersion = (SELECT MAX(iVersion) FROM tblAuditTrail WHERE uuid =objUUID
    and iMajorVersion=iMajorVersion)
    if iVersion= null
    begin
    set iVersion=0
    end
    end
    if len(sAuditTrailNotes) =0
    begin
    INSERT INTO tblAuditTrail(dEventdate, uuid, iVersion, iMajorVersion,
    sObjectType, sObjectStatus, sActionCode, iActionedBy)
    VALUES (dNow, objUUID, iVersion, iMajorVersion, sObjectType,
    sObjectStatus, sActionCode,UserID)
    end
    else
    begin
    INSERT INTO tblAuditTrail(dEventDate, uuid, iVersion, iMajorVersion,
    sObjectType, sObjectStatus, sActionCode, iActionedBy, sAuditNotes)
    VALUES (dNow, objUUID, iVersion, iMajorVersion, sObjectType,
    sObjectStatus, sActionCode,UserID, sAuditTrailNotes)
    end
    --select iMajorVersion as iMajorVersion, iVersion as iLatestVersion
    --select rtrim(CAST(iMajorVersion AS char(50))) +'.'+ ltrim(CAST(iVersion AS
    char(50))) as sLatestVersion


    Anderson11983 Guest

  2. #2

    Default Re: re-writting stored procedures as ,cfc

    Here's a sample. Note carefule use of the var keyword inside functions. These
    create variables in the LOCAL scope only and help to enforce encapsulation by
    ensuring that the value can only be accessed in the calling template unless it
    is returned from a function. It's also worth bearing in mind that a bug in
    coldfusion also allows VARIABLE scoped variables to leak to the calling
    template so use the UNNAMED scope if you are looking to implement instance
    variables. This isn't a problem for the CFC below.

    Good Luck

    Peter (aka lad4bear)


    <cfcomponent displayname="EmployeeGateway" hint="EmployeeGateway">




    <cffunction name="InsertEmployee" access="public" returntype="numeric"
    output="false">
    <cfargument name="firstname" type="string" required="true">
    <cfargument name="lastname" type="string" required="true">
    <cfargument name="deptid" type="numeric" required="true">
    <cfargument name="startdate" type="date" required="true">
    <cfargument name="salary" type="numeric" required="true">
    <cfargument name="contract" type="boolean" required="true">

    <cfset var uniqueid = "" />

    <cftry>
    <cfstoredproc procedure="insertemployee" datasource="CompanyInformation"
    returncode="no">
    <cfprocparam value="#arguments.firstname#" type="in"
    cfsqltype="cf_sql_varchar" null="no">
    <cfprocparam value="#arguments.lastname#" type="in"
    cfsqltype="cf_sql_varchar" null="no">
    <cfprocparam value="#arguments.deptid#" type="in"
    cfsqltype="cf_sql_integer" null="no">
    <cfprocparam value="#arguments.startdate#" type="in"
    cfsqltype="cf_sql_timestamp" null="no">
    <cfprocparam value="#arguments.salary#" type="in"
    cfsqltype="cf_sql_money" null="no">
    <cfprocparam value="#arguments.contract#" type="in"
    cfsqltype="cf_sql_varchar" null="no">

    <cfprocparam variable="#uniqueid#" type="out" cfsqltype="cf_sql_integer"
    null="no">
    </cfstoredproc>

    <cfcatch type="database">
    <cfrethrow />
    </cfcatch>
    </cftry>

    <cfreturn uniqueid />
    </cffunction>




    <cffunction name="SelectEmployee" access="public" returntype="query"
    output="false">
    <cfargument name="employeeid" type="numeric" required="true">

    <cfset var results = "" />

    <cftry>
    <cfstoredproc procedure="selectemployee" datasource="CompanyInformation"
    returncode="no">
    <cfprocparam value=#arguments.employeeid# type="in"
    cfsqltype="cf_sql_integer" null="no" />

    <cfprocresult name="results" />
    </cfstoredproc>

    <cfcatch type="database">
    <cfrethrow />
    </cfcatch>
    </cftry>

    <cfreturn results />
    </cffunction>




    <cffunction name="UpdateEmployee" access="public" returntype="boolean"
    output="false">
    <cfargument name="employeeid" type="numeric" required="true">
    <cfargument name="firstname" type="string" required="true">
    <cfargument name="lastname" type="string" required="true">
    <cfargument name="deptid" type="numeric" required="true">
    <cfargument name="startdate" type="date" required="true">
    <cfargument name="salary" type="numeric" required="true">
    <cfargument name="contract" type="boolean" required="true">

    <cftry>
    <cfstoredproc procedure="updateemployee" datasource="CompanyInformation"
    returncode="no">
    <cfprocparam value=#arguments.employeeid# type="in"
    cfsqltype="cf_sql_integer" null="no">
    <cfprocparam value=#arguments.firstname# type="in"
    cfsqltype="cf_sql_varchar" null="no">
    <cfprocparam value=#arguments.lastname# type="in"
    cfsqltype="cf_sql_varchar" null="no">
    <cfprocparam value=#arguments.deptid# type="in"
    cfsqltype="cf_sql_integer" null="no">
    <cfprocparam value=#arguments.startdate# type="in"
    cfsqltype="cf_sql_timestamp" null="no">
    <cfprocparam value=#arguments.salary# type="in" cfsqltype="cf_sql_money"
    null="no">
    <cfprocparam value=#arguments.contract# type="in"
    cfsqltype="cf_sql_varchar" null="no">
    </cfstoredproc>

    <cfcatch type="database">
    <cfrethrow />
    </cfcatch>
    </cftry>

    <cfreturn true />
    </cffunction>




    <cffunction name="DeleteEmployee" access="public" returntype="boolean"
    output="false">
    <cfargument name="employeeid" type="numeric" required="true">


    <cftry>
    <cfstoredproc procedure="deleteemployee" datasource="CompanyInformation"
    returncode="no">
    <cfprocparam value=#arguments.employeeid# type="in"
    cfsqltype="cf_sql_integer" null="no" />
    <cfprocresult name="employeedata" />
    </cfstoredproc>

    <cfcatch type="database">
    <cfrethrow />
    </cfcatch>
    </cftry>

    <cfreturn true />
    </cffunction>




    <cffunction name="FindEmployees" access="public" returntype="query"
    output="false">
    <cfargument name="firstname" type="string" required="false" default="">
    <cfargument name="lastname" type="string" required="false" default="">
    <cfargument name="deptid" type="string" required="false" default="">
    <cfargument name="startdate" type="string" required="false" default="">
    <cfargument name="salary" type="string" required="false" default="">
    <cfargument name="contract" type="string" required="false" default="">

    <cfset var results = "" />

    <cftry>
    <cfstoredproc procedure="findemployees" datasource="CompanyInformation"
    returncode="no">
    <cfprocparam value="#arguments.firstname#" type="in"
    cfsqltype="cf_sql_varchar" null="no">
    <cfprocparam value="#arguments.lastname#" type="in"
    cfsqltype="cf_sql_varchar" null="no">
    <cfprocparam value="#arguments.deptid#" type="in"
    cfsqltype="cf_sql_varchar" null="no">
    <cfprocparam value="#arguments.startdate#" type="in"
    cfsqltype="cf_sql_varchar" null="no">
    <cfprocparam value="#arguments.salary#" type="in"
    cfsqltype="cf_sql_varchar" null="no">
    <cfprocparam value="#arguments.contract#" type="in"
    cfsqltype="cf_sql_varchar" null="no">

    <cfprocresult name="results" />
    </cfstoredproc>

    <cfcatch type="database">
    <cfrethrow />
    </cfcatch>
    </cftry>

    <cfreturn results />
    </cffunction>




    </cfcomponent>


    lad4bear Guest

  3. #3

    Default Re: re-writting stored procedures as ,cfc

    Hi Peter,

    Thanks for your help - its very much appreciated. Thing is i forgot to mention
    that i have been told that we are not using stored pocedures at all because the
    target database is going to be mysql (even though it supports stored procedures)

    Can you help ?

    Thanks again its much appreciated

    Thanks

    Andy

    Anderson11983 Guest

  4. #4

    Default Re: re-writting stored procedures as ,cfc

    With CFC's, you can invoke them as an object, or just include them in a CFM
    file as a library (the latter would seem to do you suffice, I think, no?).
    Peter gave you the basic syntax for the CFC and the functions (with parameter
    calls), so just replace his stored procedure calls with CFQUERY tags, and you
    should be all set. Reply if you need more information.

    David

    davidmedifit Guest

  5. #5

    Default Re: re-writting stored procedures as ,cfc

    Hi there,

    I'm not sure that MySql does support Stored Procedures but rather an
    approximation of them. Although I must admit its been a while since I checked
    this so my information may now be out of date.

    As david said replace my stored procedure tags with <cfquery> tags instead.
    I'd advise you to use <cfqueryparam> tags also as this allows for slightly
    better performance and more importantly better security. There are caveats
    though, try not to use 'SELECT * FROM' in your query statements as it can
    errors if your schema changes. You can avoid this problem by select each of the
    columns you require. i.e SELECT [FirstName], [LastName], [Age] FROM etc

    One thought though. If your sequel is already written you could probably
    automate the development of you CFC's. Thats definately the route I'd go. There
    is no way on Gods earth I'd craft them all by hand

    Cheers, Peter (aka lad4bear)



    lad4bear Guest

  6. #6

    Default Re: re-writting stored procedures as ,cfc

    there's a discussion going on about DAO right now on the [url]http://www.cfczone.org/listserv.cfm[/url] that you might benefit from if you're going ahead w/this.


    PaulH Guest

  7. #7

    Default Re: re-writting stored procedures as ,cfc

    Hey Everyone,

    Thank-you very much for all your help and support its much appreiated. One of
    developers said i should go through the route of usinga <CFMODULE>. but to be
    be brutally honest its all foreign to me as ive never touched or even looked at
    coldfsion until now.

    I know the following code is in a sql sp format - i just wanted to ask if
    Peter's code is the correct way for defining the datatypes and lengths?

    Im really confused?

    <CFQUERY name="makeSP_saveAuditTrail" datasource="#attributes.datasource#">
    CREATE PROCEDURE [saveAuditTrail]
    (
    sObjectType varchar(50),
    sObjectStatus varchar(50),
    sActionCode varchar(50),

    Thanks for your help again everyone - its really appreciated.

    Thanks

    Andy

    Anderson11983 Guest

Similar Threads

  1. Stored Procedures
    By Anthony M. Davis in forum Coldfusion - Advanced Techniques
    Replies: 28
    Last Post: February 9th, 03:17 PM
  2. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  3. New to ASP and Stored Procedures
    By John Berman in forum ASP Database
    Replies: 6
    Last Post: February 28th, 01:37 AM
  4. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  5. stored procedures in access
    By Bob Barrows in forum ASP Database
    Replies: 0
    Last Post: July 25th, 06:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139