re-writting stored procedures as ,cfc

[Anderson11983]

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

[lad4bear]

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>

[Anderson11983]

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

[davidmedifit]

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

[lad4bear]

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)

[PaulH]

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.

[Anderson11983]

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