re-writing a stored procedure as a coldfusion document.
Hi,
I was wondering if someone can help me please - i have the task of re-writing out database stored procedures as coldfusion cfc's. THing is i haven't really got much experience with coldfusion and was hoping that some kind person can help me out.
I have enclosed one of our stored procedures.
Appreciated any and all help.
Thanks
Andy
-- STORED PROCEDURE --
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
GO
Software/Hardware used:
Software/Hardware used:
ASKED:
April 26, 2005 7:36 AM
UPDATED:
April 26, 2005 3:52 PM
Answer Wiki:
my advice: don't
stored procedures are the most efficient way of managing database data
rewriting them as cfcs will buy you a world of hurt
To see all answers submitted to the Answer Wiki: View Answer History.
Thank-you for the speedy reply.
Unfortunatly No isn’t an option as the manager has decided that he wants our software to go cross-platform (to mysql) and wants to get rid of stored procedures.
So i have to do it.
Can anyone please help?
Thanks again
Andy
good luck, i know how difficult it is working for a PHB
i think the decision to destroy all the hard work (not to mention superior performance) of your existing stored procs is ludicrous, but of course you can’t say that to your boss with impunity, can you
you might mention that long before you have completed your CFCs, MySQL version 5 (which supports stored procedures) will be in production status
If you can’t say no, then re-write a set of stored procedures for each platform supported (SQLServer, Oracle, DB2, MySQL …) and use the data layer properly. This not only will be more efficient, it will be more secure as re-writing to code exposes your data to sql injection attacks and just plain having hackers able to manipulate data.
Also, it won’t be any more time consuming, pl/sql isn’t that much of a leap from tsql as coldfusion will be. View this job from the data viewpoint, not the business layer.
I would argue with your manager about how to support cross-platform correctly. Moving the data layer to the business layer in general has been shown to be the wrong way by so many architects it’s really dumb to think of doing it in today’s environment.
tom mallard
software design – analysis
industrial design – products
Hi ,
Just an update ive been told its got to be ready by next week . Thing is am not a programmer (wish i was)-i drew the short straw.
And was kind of hoping some one can help/show me what to do/how to do it.
All help is very much appreciated.
Thanks
Andy
andy, no offence, but you’re not going to make it
you’re not a programmer? in a WEEK?
that’s a, um, wildly unreasonable expectation
anyone who knows anything about both coldfusion CFCs and SQL Server stored procedures will tell you that a non-programmer MIGHT be able to do it, but in a YEAR… MAYBE
should the worst happen and your job be terminated, you can do one of two things — shrug it off and be glad you got outta there, or sue for breach of employment contract (you cannot be asked to do the impossible)
Since you are required to go to any database your best option has already been stated. You need to create a data layer that your cold fusion communicates with. The data layer will have different rountines for the different database engines, allowing you to use the most efficient method available on each. In this manner you could use stored procedures (when available) or dynamic SQL.
One reason you can push for this design strategy is SQL Infusion. Allowing your application to write your SQL Code is tedious and risky. If your boss thinks he is going to save money by going to an open systems database engine because it costs little or nothing to deploy he needs to be made aware that it is going to cost him a LOT more money to develop code that is secure (especially MYSQL). I am not saying MYSQL can’t do the job. It’s just a lot harder to secure.
Your middle tier code can do the securing a LOT easier and you can create re-usable components and routines to do a lot of SQL Injection protection.
Additionally, a middle tier protects you from having to change presentation code because of changes to schema, etc. Keep this in your data layer.
Cheers,
Ben
btaylor@sswug.org