re-writing a stored procedure as a coldfusion document.

0 pts.
Tags:
ColdFusion
SQL
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
ASKED: April 26, 2005  7:36 AM
UPDATED: April 26, 2005  3:52 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Andy11983
    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
    0 pointsBadges:
    report
  • Andy11983
    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
    0 pointsBadges:
    report
  • Timallard
    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
    0 pointsBadges:
    report
  • Andy11983
    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
    0 pointsBadges:
    report
  • Andy11983
    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)
    0 pointsBadges:
    report
  • BenjiT
    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
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following