Re: [KCFusion] deletes on database?
Hey why is it every time I post something it generates an avalanche of activity. :-) Its like you guys forget this thing exist. So (and anybody can field this one) I was studying how to write stored procedures in SQL Server last night I was like..gee. thats a lot of freaking work. So what would you say was the most appropriate time to use stored procedures vs. just putting it on the page. A. P.S. As a matter of fact Misty, no, I don't have a jobI'm a consultant! - Original Message - From: Misty Woodward [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 10:59 AM Subject: RE: [KCFusion] deletes on database? In one of the projects I am working on now, they use Oracle stored functions only for database calls. When I first started doing it, I hated it. Honestly, I still hate it. From a development standpoint, as far as time goes, i would say it takes longer than just writing it directly into CF. When Im coding in CF I just write out my CF query and move on. When writing Stored Functions your 8 line query turns into a huge function where you have to declare varaibles, check for variabls, create the function, create your IN Variables, write in your return values, etc. The part that Stored Functions shine in, is with code re-use. The function I wrote is to be used across 3 systems. Which means, I only have to change it in one place and I can manage all 3 sites with it. Misty -- Original Message -- From: Glenn Crocker [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 12 Sep 2002 10:50:12 -0500 MessageI generally don't do any JOINs in my CF code, instead using queries/views to accomplish them. (Sometimes, when a parameter needs to be way inside a JOIN, I'll put one in CF.) I haven't done the full-on stored procedure architecture, but most of my projects are just one or two developers, so it's not a big team thing with lots of turnover. -glenn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ryan Hartwich Sent: Wednesday, September 11, 2002 10:28 PM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] deletes on database? Adaryl, Somewhere I interviewed or spoke with someone about this topic. It was my understanding that the individual coders being employed were generally not allowed to do any form of insert/update/delete into the database through their code. They were however permitted to write Select statements to tables and/or views. The coders were given a set of API calls utilizing XML services internally to do the direct DB manipulation. The idea was to force data integrity and consistency by only allowing data to be modified through approved prebuilt modules. A permutation of this would be to not permit inline DB calls in your CF code and to call all DB statements via stored procedures. I have heard this speeds up development, improves reusability, and quality. I'm a bit skeptical of this however. I have spent a few years writing code as the primary developer and write my SQL code directly inside of my CF pages. At least for small development teams with ad-hoc design standards that change frequently I think the extra overhead of standardizing and separating the layers adds significant complexity. However, I would love to hear from those who have used this method in large, formal design groups. It may be the way to go. Ryan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Adaryl Wakefield Sent: Wednesday, September 11, 2002 10:10 PM To: [EMAIL PROTECTED] Subject: [KCFusion] deletes on database? I once saw a job announcement that said something to the effect. ..no deletes on the database will be allowed. All deletes are done via XML... At the time I just raised my eyebrow and went on but now I'm kinda curious if anybody can shed some light on what exactly they meant. I only have an academic understanding of XML and a small one at that. A. __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
RE: [KCFusion] deletes on database?
I generally use stored procedures unless a cached query would be appropriate. (i.e. populating dropdowns that don't change often) -Original Message- From: Adaryl Wakefield [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 12:59 PM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] deletes on database? Hey why is it every time I post something it generates an avalanche of activity. :-) Its like you guys forget this thing exist. So (and anybody can field this one) I was studying how to write stored procedures in SQL Server last night I was like..gee. thats a lot of freaking work. So what would you say was the most appropriate time to use stored procedures vs. just putting it on the page. A. P.S. As a matter of fact Misty, no, I don't have a jobI'm a consultant! - Original Message - From: Misty Woodward [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 10:59 AM Subject: RE: [KCFusion] deletes on database? In one of the projects I am working on now, they use Oracle stored functions only for database calls. When I first started doing it, I hated it. Honestly, I still hate it. From a development standpoint, as far as time goes, i would say it takes longer than just writing it directly into CF. When Im coding in CF I just write out my CF query and move on. When writing Stored Functions your 8 line query turns into a huge function where you have to declare varaibles, check for variabls, create the function, create your IN Variables, write in your return values, etc. The part that Stored Functions shine in, is with code re-use. The function I wrote is to be used across 3 systems. Which means, I only have to change it in one place and I can manage all 3 sites with it. Misty -- Original Message -- From: Glenn Crocker [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 12 Sep 2002 10:50:12 -0500 MessageI generally don't do any JOINs in my CF code, instead using queries/views to accomplish them. (Sometimes, when a parameter needs to be way inside a JOIN, I'll put one in CF.) I haven't done the full-on stored procedure architecture, but most of my projects are just one or two developers, so it's not a big team thing with lots of turnover. -glenn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ryan Hartwich Sent: Wednesday, September 11, 2002 10:28 PM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] deletes on database? Adaryl, Somewhere I interviewed or spoke with someone about this topic. It was my understanding that the individual coders being employed were generally not allowed to do any form of insert/update/delete into the database through their code. They were however permitted to write Select statements to tables and/or views. The coders were given a set of API calls utilizing XML services internally to do the direct DB manipulation. The idea was to force data integrity and consistency by only allowing data to be modified through approved prebuilt modules. A permutation of this would be to not permit inline DB calls in your CF code and to call all DB statements via stored procedures. I have heard this speeds up development, improves reusability, and quality. I'm a bit skeptical of this however. I have spent a few years writing code as the primary developer and write my SQL code directly inside of my CF pages. At least for small development teams with ad-hoc design standards that change frequently I think the extra overhead of standardizing and separating the layers adds significant complexity. However, I would love to hear from those who have used this method in large, formal design groups. It may be the way to go. Ryan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Adaryl Wakefield Sent: Wednesday, September 11, 2002 10:10 PM To: [EMAIL PROTECTED] Subject: [KCFusion] deletes on database? I once saw a job announcement that said something to the effect. ..no deletes on the database will be allowed. All deletes are done via XML... At the time I just raised my eyebrow and went on but now I'm kinda curious if anybody can shed some light on what exactly they meant. I only have an academic understanding of XML and a small one at that. A. __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives
Re: [KCFusion] deletes on database?
Well I know some that use stored procedures for everything. For me, I try to use them only when things get complex. For instance, Im writing an application that goes into the database checks who a shipper is based on a mailstop, based on that it does another query, takes the result of that query and goes into another database pulls out the next tracking number then calls another Oracle function within it. So you can see the more jumping around i have do, the more it makes sense to do it on the Oracle box instead. The other reason regardless of what makes sense, is some companies force you to put everything into stored procedures or functions no matter what. There are a lot of companies now moving to not letting CF programmers write SQL statements and instead having database guys do it because they are more efficient in writing SQL (ya I dont buy that either) but it is thought to be better. Make the database better by letting the people who specialize in it write the queries. This makes sense I suppose but its a big increaser of development time. Ive literally sat here twiddling my thumbs for hours waiting on my DB guy to come back and load my oracle function for me to test it. it sucks. But thats company processes at work! :) Misty -- Original Message -- From: Adaryl Wakefield [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 12 Sep 2002 12:58:32 -0500 Hey why is it every time I post something it generates an avalanche of activity. :-) Its like you guys forget this thing exist. So (and anybody can field this one) I was studying how to write stored procedures in SQL Server last night I was like..gee. thats a lot of freaking work. So what would you say was the most appropriate time to use stored procedures vs. just putting it on the page. A. P.S. As a matter of fact Misty, no, I don't have a jobI'm a consultant! - Original Message - From: Misty Woodward [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 10:59 AM Subject: RE: [KCFusion] deletes on database? In one of the projects I am working on now, they use Oracle stored functions only for database calls. When I first started doing it, I hated it. Honestly, I still hate it. From a development standpoint, as far as time goes, i would say it takes longer than just writing it directly into CF. When Im coding in CF I just write out my CF query and move on. When writing Stored Functions your 8 line query turns into a huge function where you have to declare varaibles, check for variabls, create the function, create your IN Variables, write in your return values, etc. The part that Stored Functions shine in, is with code re-use. The function I wrote is to be used across 3 systems. Which means, I only have to change it in one place and I can manage all 3 sites with it. Misty -- Original Message -- From: Glenn Crocker [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 12 Sep 2002 10:50:12 -0500 MessageI generally don't do any JOINs in my CF code, instead using queries/views to accomplish them. (Sometimes, when a parameter needs to be way inside a JOIN, I'll put one in CF.) I haven't done the full-on stored procedure architecture, but most of my projects are just one or two developers, so it's not a big team thing with lots of turnover. -glenn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ryan Hartwich Sent: Wednesday, September 11, 2002 10:28 PM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] deletes on database? Adaryl, Somewhere I interviewed or spoke with someone about this topic. It was my understanding that the individual coders being employed were generally not allowed to do any form of insert/update/delete into the database through their code. They were however permitted to write Select statements to tables and/or views. The coders were given a set of API calls utilizing XML services internally to do the direct DB manipulation. The idea was to force data integrity and consistency by only allowing data to be modified through approved prebuilt modules. A permutation of this would be to not permit inline DB calls in your CF code and to call all DB statements via stored procedures. I have heard this speeds up development, improves reusability, and quality. I'm a bit skeptical of this however. I have spent a few years writing code as the primary developer and write my SQL code directly inside of my CF pages. At least for small development teams with ad-hoc design standards that change frequently I think the extra overhead of standardizing and separating the layers adds significant complexity. However, I would love to hear from those who have used this method in large, formal design groups. It may be the way to go. Ryan -Original Message- From: [EMAIL PROTECTED] [mailto
Re: [KCFusion] deletes on database?
I (personally) find using stored procedures instead of simple queries to be a nuisance for anything but large, cross-platform projects. Instead of replacing simple queries with stored procedures, you can just use cfqueryparam to get the same performance benefit (query plan reuse) in SQL Server or Oracle. --Daryl Banttari Macromedia - Original Message - From: Adaryl Wakefield [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 12:58 PM Subject: Re: [KCFusion] deletes on database? Hey why is it every time I post something it generates an avalanche of activity. :-) Its like you guys forget this thing exist. So (and anybody can field this one) I was studying how to write stored procedures in SQL Server last night I was like..gee. thats a lot of freaking work. So what would you say was the most appropriate time to use stored procedures vs. just putting it on the page. A. P.S. As a matter of fact Misty, no, I don't have a jobI'm a consultant! - Original Message - From: Misty Woodward [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 10:59 AM Subject: RE: [KCFusion] deletes on database? In one of the projects I am working on now, they use Oracle stored functions only for database calls. When I first started doing it, I hated it. Honestly, I still hate it. From a development standpoint, as far as time goes, i would say it takes longer than just writing it directly into CF. When Im coding in CF I just write out my CF query and move on. When writing Stored Functions your 8 line query turns into a huge function where you have to declare varaibles, check for variabls, create the function, create your IN Variables, write in your return values, etc. The part that Stored Functions shine in, is with code re-use. The function I wrote is to be used across 3 systems. Which means, I only have to change it in one place and I can manage all 3 sites with it. Misty -- Original Message -- From: Glenn Crocker [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 12 Sep 2002 10:50:12 -0500 MessageI generally don't do any JOINs in my CF code, instead using queries/views to accomplish them. (Sometimes, when a parameter needs to be way inside a JOIN, I'll put one in CF.) I haven't done the full-on stored procedure architecture, but most of my projects are just one or two developers, so it's not a big team thing with lots of turnover. -glenn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ryan Hartwich Sent: Wednesday, September 11, 2002 10:28 PM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] deletes on database? Adaryl, Somewhere I interviewed or spoke with someone about this topic. It was my understanding that the individual coders being employed were generally not allowed to do any form of insert/update/delete into the database through their code. They were however permitted to write Select statements to tables and/or views. The coders were given a set of API calls utilizing XML services internally to do the direct DB manipulation. The idea was to force data integrity and consistency by only allowing data to be modified through approved prebuilt modules. A permutation of this would be to not permit inline DB calls in your CF code and to call all DB statements via stored procedures. I have heard this speeds up development, improves reusability, and quality. I'm a bit skeptical of this however. I have spent a few years writing code as the primary developer and write my SQL code directly inside of my CF pages. At least for small development teams with ad-hoc design standards that change frequently I think the extra overhead of standardizing and separating the layers adds significant complexity. However, I would love to hear from those who have used this method in large, formal design groups. It may be the way to go. Ryan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Adaryl Wakefield Sent: Wednesday, September 11, 2002 10:10 PM To: [EMAIL PROTECTED] Subject: [KCFusion] deletes on database? I once saw a job announcement that said something to the effect. ..no deletes on the database will be allowed. All deletes are done via XML... At the time I just raised my eyebrow and went on but now I'm kinda curious if anybody can shed some light on what exactly they meant. I only have an academic understanding of XML and a small one at that. A. __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto
RE: [KCFusion] deletes on database?
Ha!! Some of the CF developers on my team are better SQL writers than some DBAs I know.!! We also use stored proc for complex situations like Misty mentioned. -Betty -Original Message- From: Misty Woodward [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 1:35 PM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] deletes on database? Well I know some that use stored procedures for everything. For me, I try to use them only when things get complex. For instance, Im writing an application that goes into the database checks who a shipper is based on a mailstop, based on that it does another query, takes the result of that query and goes into another database pulls out the next tracking number then calls another Oracle function within it. So you can see the more jumping around i have do, the more it makes sense to do it on the Oracle box instead. The other reason regardless of what makes sense, is some companies force you to put everything into stored procedures or functions no matter what. There are a lot of companies now moving to not letting CF programmers write SQL statements and instead having database guys do it because they are more efficient in writing SQL (ya I dont buy that either) but it is thought to be better. Make the database better by letting the people who specialize in it write the queries. This makes sense I suppose but its a big increaser of development time. Ive literally sat here twiddling my thumbs for hours waiting on my DB guy to come back and load my oracle function for me to test it. it sucks. But thats company processes at work! :) Misty -- Original Message -- From: Adaryl Wakefield [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 12 Sep 2002 12:58:32 -0500 Hey why is it every time I post something it generates an avalanche of activity. :-) Its like you guys forget this thing exist. So (and anybody can field this one) I was studying how to write stored procedures in SQL Server last night I was like..gee. thats a lot of freaking work. So what would you say was the most appropriate time to use stored procedures vs. just putting it on the page. A. P.S. As a matter of fact Misty, no, I don't have a jobI'm a consultant! - Original Message - From: Misty Woodward [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 10:59 AM Subject: RE: [KCFusion] deletes on database? In one of the projects I am working on now, they use Oracle stored functions only for database calls. When I first started doing it, I hated it. Honestly, I still hate it. From a development standpoint, as far as time goes, i would say it takes longer than just writing it directly into CF. When Im coding in CF I just write out my CF query and move on. When writing Stored Functions your 8 line query turns into a huge function where you have to declare varaibles, check for variabls, create the function, create your IN Variables, write in your return values, etc. The part that Stored Functions shine in, is with code re-use. The function I wrote is to be used across 3 systems. Which means, I only have to change it in one place and I can manage all 3 sites with it. Misty -- Original Message -- From: Glenn Crocker [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 12 Sep 2002 10:50:12 -0500 MessageI generally don't do any JOINs in my CF code, instead using queries/views to accomplish them. (Sometimes, when a parameter needs to be way inside a JOIN, I'll put one in CF.) I haven't done the full-on stored procedure architecture, but most of my projects are just one or two developers, so it's not a big team thing with lots of turnover. -glenn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ryan Hartwich Sent: Wednesday, September 11, 2002 10:28 PM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] deletes on database? Adaryl, Somewhere I interviewed or spoke with someone about this topic. It was my understanding that the individual coders being employed were generally not allowed to do any form of insert/update/delete into the database through their code. They were however permitted to write Select statements to tables and/or views. The coders were given a set of API calls utilizing XML services internally to do the direct DB manipulation. The idea was to force data integrity and consistency by only allowing data to be modified through approved prebuilt modules. A permutation of this would be to not permit inline DB calls in your CF code and to call all DB statements via stored procedures. I have heard this speeds up development, improves reusability, and quality. I'm a bit skeptical of this however. I have spent a few years writing code as the primary developer and write my SQL code directly inside of my CF pages. At least for small development teams with ad-hoc
Re: [KCFusion] deletes on database?
Folks, One of the chief reasons for using stored procedures is to make applications more secure. Girish Daryl Banttari [EMAIL PROTECTED]To: [EMAIL PROTECTED] cc: Sent by: Subject: Re: [KCFusion] deletes on database? CF-List-owner@kcf usion.org 09/12/02 01:33 PM Please respond to CF-List I (personally) find using stored procedures instead of simple queries to be a nuisance for anything but large, cross-platform projects. Instead of replacing simple queries with stored procedures, you can just use cfqueryparam to get the same performance benefit (query plan reuse) in SQL Server or Oracle. --Daryl Banttari Macromedia - Original Message - From: Adaryl Wakefield [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 12:58 PM Subject: Re: [KCFusion] deletes on database? Hey why is it every time I post something it generates an avalanche of activity. :-) Its like you guys forget this thing exist. So (and anybody can field this one) I was studying how to write stored procedures in SQL Server last night I was like..gee. thats a lot of freaking work. So what would you say was the most appropriate time to use stored procedures vs. just putting it on the page. A. P.S. As a matter of fact Misty, no, I don't have a jobI'm a consultant! - Original Message - From: Misty Woodward [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 10:59 AM Subject: RE: [KCFusion] deletes on database? In one of the projects I am working on now, they use Oracle stored functions only for database calls. When I first started doing it, I hated it. Honestly, I still hate it. From a development standpoint, as far as time goes, i would say it takes longer than just writing it directly into CF. When Im coding in CF I just write out my CF query and move on. When writing Stored Functions your 8 line query turns into a huge function where you have to declare varaibles, check for variabls, create the function, create your IN Variables, write in your return values, etc. The part that Stored Functions shine in, is with code re-use. The function I wrote is to be used across 3 systems. Which means, I only have to change it in one place and I can manage all 3 sites with it. Misty -- Original Message -- From: Glenn Crocker [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 12 Sep 2002 10:50:12 -0500 MessageI generally don't do any JOINs in my CF code, instead using queries/views to accomplish them. (Sometimes, when a parameter needs to be way inside a JOIN, I'll put one in CF.) I haven't done the full-on stored procedure architecture, but most of my projects are just one or two developers, so it's not a big team thing with lots of turnover. -glenn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ryan Hartwich Sent: Wednesday, September 11, 2002 10:28 PM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] deletes on database? Adaryl, Somewhere I interviewed or spoke with someone about this topic. It was my understanding that the individual coders being employed were generally not allowed to do any
Re: [KCFusion] deletes on database?
See that i knew. I have been diving though my libary all day for a better explantion though. Care to elaborate if you have time? The only security issue that i am aware of is the url attack which can be defended with cfqueryparam. A. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 2:55 PM Subject: Re: [KCFusion] deletes on database? Folks, One of the chief reasons for using stored procedures is to make applications more secure. Girish Daryl Banttari [EMAIL PROTECTED]To: [EMAIL PROTECTED] cc: Sent by: Subject: Re: [KCFusion] deletes on database? CF-List-owner@kcf usion.org 09/12/02 01:33 PM Please respond to CF-List I (personally) find using stored procedures instead of simple queries to be a nuisance for anything but large, cross-platform projects. Instead of replacing simple queries with stored procedures, you can just use cfqueryparam to get the same performance benefit (query plan reuse) in SQL Server or Oracle. --Daryl Banttari Macromedia - Original Message - From: Adaryl Wakefield [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 12:58 PM Subject: Re: [KCFusion] deletes on database? Hey why is it every time I post something it generates an avalanche of activity. :-) Its like you guys forget this thing exist. So (and anybody can field this one) I was studying how to write stored procedures in SQL Server last night I was like..gee. thats a lot of freaking work. So what would you say was the most appropriate time to use stored procedures vs. just putting it on the page. A. P.S. As a matter of fact Misty, no, I don't have a jobI'm a consultant! - Original Message - From: Misty Woodward [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 10:59 AM Subject: RE: [KCFusion] deletes on database? In one of the projects I am working on now, they use Oracle stored functions only for database calls. When I first started doing it, I hated it. Honestly, I still hate it. From a development standpoint, as far as time goes, i would say it takes longer than just writing it directly into CF. When Im coding in CF I just write out my CF query and move on. When writing Stored Functions your 8 line query turns into a huge function where you have to declare varaibles, check for variabls, create the function, create your IN Variables, write in your return values, etc. The part that Stored Functions shine in, is with code re-use. The function I wrote is to be used across 3 systems. Which means, I only have to change it in one place and I can manage all 3 sites with it. Misty -- Original Message -- From: Glenn Crocker [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Thu, 12 Sep 2002 10:50:12 -0500 MessageI generally don't do any JOINs in my CF code, instead using queries/views to accomplish them. (Sometimes, when a parameter needs to be way inside a JOIN, I'll put one in CF.) I haven't done the full-on stored procedure architecture, but most of my projects are just one or two developers, so it's not a big team thing with lots of turnover. -glenn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ryan Hartwich Sent: Wednesday, September 11, 2002 10:28 PM To: [EMAIL PROTECTED] Subject: RE: [KCFusion] deletes on database? Adaryl, Somewhere I interviewed or spoke with someone about this topic. It was my understanding that the individual coders being employed were generally not allowed to do any form of insert/update/delete into the database through their code. They were however permitted to write Select statements to tables and/or views. The coders were given a set of API calls utilizing XML services internally to do the direct DB manipulation. The idea was to force data integrity and consistency by only allowing data to be modified through approved prebuilt modules. A permutation of this would be to not permit inline DB calls in your CF code and to call all DB statements via stored procedures. I have heard this speeds up development, improves reusability, and quality. I'm a bit skeptical of this however. I have spent a few years writing code as the primary developer and write my SQL code directly inside of my CF pages. At least for small development teams with ad-hoc design standards that change frequently I think the extra overhead of standardizing and separating the layers adds
Re: [KCFusion] deletes on database?
Adaryl, In short, by hiding (or not letting have access to) the details of manipulations/information that is being done inside of the stored procedure you have prevented its possible misuse (misuse can mean lot of issues such as leaking of secure information, database corruption etc.). Girish Adaryl Wakefield To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: Sent by: Subject: Re: [KCFusion] deletes on database? CF-List-owner@kcf usion.org 09/12/02 03:28 PM Please respond to CF-List See that i knew. I have been diving though my libary all day for a better explantion though. Care to elaborate if you have time? The only security issue that i am aware of is the url attack which can be defended with cfqueryparam. A. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 2:55 PM Subject: Re: [KCFusion] deletes on database? Folks, One of the chief reasons for using stored procedures is to make applications more secure. Girish Daryl Banttari [EMAIL PROTECTED]To: [EMAIL PROTECTED] cc: Sent by: Subject: Re: [KCFusion] deletes on database? CF-List-owner@kcf usion.org 09/12/02 01:33 PM Please respond to CF-List I (personally) find using stored procedures instead of simple queries to be a nuisance for anything but large, cross-platform projects. Instead of replacing simple queries with stored procedures, you can just use cfqueryparam to get the same performance benefit (query plan reuse) in SQL Server or Oracle. --Daryl Banttari Macromedia - Original Message - From: Adaryl Wakefield [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 12:58 PM Subject: Re: [KCFusion] deletes on database? Hey why is it every time I post something it generates an avalanche of activity. :-) Its like you guys forget this thing exist. So (and anybody can field this one) I was studying how to write stored procedures in SQL Server last night I was like..gee. thats a lot of freaking work. So what would you say was the most appropriate time to use stored procedures vs. just putting it on the page. A. P.S. As a matter of fact Misty, no, I don't have a jobI'm a consultant! - Original Message - From: Misty Woodward [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 10:59 AM Subject: RE: [KCFusion] deletes on database? In one of the projects I am working on now, they use Oracle stored functions only for database calls. When I first started doing it, I hated it. Honestly, I still hate it. From a development standpoint, as far as time goes, i would say it takes longer than just writing it directly into CF. When Im coding in CF I just write out my CF query and move on. When writing Stored Functions your 8 line query turns into a huge function where you have to declare varaibles, check for variabls, create the function, create your IN Variables, write in your return values, etc. The part
Re: [KCFusion] deletes on database?
Adaryl, Here's my $.02 Chapter 32 (Working With Stored Procedures), page 1057, in the book Web Application Construction Kit Fourth Edition (Ben Forta and Nate Weiss), there is a pretty good description about why performance may be enhanced by using stored procedures: Depending on the situation, using stored procedures can often cause an application to perform better. For two basic reasons, the use of stored procedures will speed your application. First, most database systems do some type of precompilation of the stored procedure so that it runs more quickly when it is actually used. For instance, Microsoft SQL Server makes all its performance-optimizing decisions (such as what indexes and which join algorithms to use) the first time a stored procedure is run. Subsequent executions of the stored procedure do not need to be parsed and analyzed, which causes the procedure to be run somewhat more quickly than if you executed its SQL statements in an ad hoc fassion every time. Generally, the more steps the procedure represents, the more of a difference this precompilation makes. Oracle servers do something very similar. Second, if you compare the idea of having one stored procedure verses several CFQUERY and CFIF tags in a template, the stored procedure approach is often more efficient because less communication is necessary between ColdFusion and the database server. Ok - enough stuff from a book. There are dozens of things to consider when deciding upon whether or not to use stored procedures or query's directly in the cf code. One can make an argument that once a stored procedure is written against a table, everyone can re-use that stored procedure. If the company is big enough to have both a SQL team and front end team, then the duties can be broken down easier. Our entire application is written using CFSTOREDPROC calls exclusively, and all of the stored procedures are done in MS SQL Server. There is no CFQUERY tags. Some of our searches are accessing tables in excess of 18 million records. One advantage from a security standpoint is that we do not have to grant any access to the database other than the EXECUTE privilege on the stored procedure. No generic insert, update, delete rights are given to individual users for the tables. So in the event that someone did gain access to your database they could still just execute stored procedures. If all the rights were there they could do select, insert, delete statements directly against the tables. So database integrity and security are greatly enhanced. Chris --- Adaryl Wakefield [EMAIL PROTECTED] wrote: See that i knew. I have been diving though my libary all day for a better explantion though. Care to elaborate if you have time? The only security issue that i am aware of is the url attack which can be defended with cfqueryparam. A. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 2:55 PM Subject: Re: [KCFusion] deletes on database? Folks, One of the chief reasons for using stored procedures is to make applications more secure. Girish Daryl Banttari [EMAIL PROTECTED]To: [EMAIL PROTECTED] cc: Sent by: Subject: Re: [KCFusion] deletes on database? CF-List-owner@kcf usion.org 09/12/02 01:33 PM Please respond to CF-List I (personally) find using stored procedures instead of simple queries to be a nuisance for anything but large, cross-platform projects. Instead of replacing simple queries with stored procedures, you can just use cfqueryparam to get the same performance benefit (query plan reuse) in SQL Server or Oracle. --Daryl Banttari Macromedia - Original Message - From: Adaryl Wakefield [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 12:58 PM Subject: Re: [KCFusion] deletes on database? Hey why is it every time I post something it generates an avalanche of activity. :-) Its like you guys forget this thing exist. So (and anybody can field this one) I was studying how to write stored procedures in SQL Server last night I was like..gee. thats a lot of freaking work. So what would you say was the most appropriate time to use stored procedures vs. just putting it on the page. A. P.S. As a matter of fact Misty, no, I don't have a jobI'm a consultant! - Original Message - From: Misty Woodward [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 10:59 AM Subject: RE: [KCFusion] deletes on database? In one of the projects I am working on now, they use Oracle stored functions only for database
[KCFusion] deletes on database?
I once saw a job announcement that said something to the effect. "..no deletes on the database will be allowed. All deletes are done via XML..." At the time I just raised my eyebrow and went on but now I'm kinda curious if anybody can shed some light on what exactly they meant. I only have an academic understanding of XML and a small one at that. A.
RE: [KCFusion] deletes on database?
Title: Message Adaryl, Somewhere I interviewed or spoke with someone about this topic. It was my understanding that the individual coders being employed were generally not allowed to do any form of insert/update/delete into the database through their code. They were however permitted to write Select statements to tables and/or views. The coders were given a set of API calls utilizing XML services internally to do the direct DB manipulation. The idea was to force data integrity and consistency by only allowing data to be modified through approved prebuilt modules. A permutation of this would be to not permit inline DB calls in your CF code and to call all DB statements via stored procedures. I have heard this speeds up development, improves reusability, and quality. I'm a bit skeptical of this however. I have spent a few years writing code as the primary developer and write my SQL code directly inside of my CF pages. At least for small development teams with ad-hoc design standards that change frequently I think the extra overhead of standardizing and separating the layers adds significant complexity. However, I would love to hear from those who have used this method in large, formal design groups. It may be the way to go. Ryan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Adaryl WakefieldSent: Wednesday, September 11, 2002 10:10 PMTo: [EMAIL PROTECTED]Subject: [KCFusion] deletes on database? I once saw a job announcement that said something to the effect. "..no deletes on the database will be allowed. All deletes are done via XML..." At the time I just raised my eyebrow and went on but now I'm kinda curious if anybody can shed some light on what exactly they meant. I only have an academic understanding of XML and a small one at that. A.
