Re: Stored procs (was Top 100 ColdFusion Programmers)
I would not replace Oracle or SQL Server and pink slip the DBA's just yet and I would not use it to store 9 GB. However I think developers should have to access to all available solutions. Right now I am doing things off the web with coldfusion that would not be possible if I did not break the rules of the known universe first. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251338 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Isn't there a limit on the DB size it can use anyway?! This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Dan Plesse To: CF-Talk Sent: Tue Aug 29 07:59:06 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) I would not replace Oracle or SQL Server and pink slip the DBA's just yet and I would not use it to store 9 GB. However I think developers should have to access to all available solutions. Right now I am doing things off the web with coldfusion that would not be possible if I did not break the rules of the known universe first. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251339 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Likewise, I'm just talking horses for courses. Our DB infrastructure is used across: 1) Our entire web apps environment, which hosts hundreds of CF sites 2) Our Student (hundreds of thousands of people), Staff (thousands of people) and Finance (billions of dollars of transactions) systems. 3) Various other systems as necessary (e.g. GIS research, employing Oracle's spatial tools) As for my web apps; for example, I use Oracle for all of our full-text indexing (instead of Verity), because I can mix fully featured queries (e.g. for security roles) with complex search results (like search context highlighted return text) all in the one query. It also means that the search collection is in the same place as the data, so we can have two CF servers in front of the DB without worrying about how to keep a collection in sync. On 8/29/06, Denny Valliant [EMAIL PROTECTED] wrote: I'd be interested to hear what kinds of things you're utilizing that heavy metal for, James. Are you doing standard PL/SQL stuff that is plain old Oracle centric, or things that are only useful when you've got that level of power behind them? I'm spacing some of my wow, that's how hotmail does it?!?-type memories. And probably asking from lack thereof. :-) Again, I too have really enjoyed this thread, and didn't mean to put any emotional spin on it. Injecting ideas was more my aim, but I'm not a stellar convey-er-of-whatever-it-is-through-typing. Too emotional, come off as being personal vs. logical... yeesh! :-) -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251345 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Well, rebellion creates new ideas. As I said before, I would need to see more information on how to implement other database approaches. If 9GB is a threshold, that covers a lot of applications that are very specific tool sets that do not have to have 10,000 concurrent users using it. A lot of intranets reports and tools are meant for less than 1000 people. The embeded databse would be interesting for very specific implementations. I probably could not use them for Flex, but for vanilla CF for very specific purposes, it could be an interesting approach. Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251362 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
On 8/29/06, Teddy Payne [EMAIL PROTECTED] wrote: Well, rebellion creates new ideas. As I said before, I would need to see more information on how to implement other database approaches. If 9GB is a I was hoping Reactor would be a silver bullet... Ranging from an embedded db to oracle is pretty rough though. :-) The embeded databse would be interesting for very specific implementations. I probably could not use them for Flex, but for vanilla CF for very specific purposes, it could be an interesting approach. I was thinking Flex (or Flash in general) was a perfect candidate... not with CF, of course, but for distributed apps. I'm late for a meeting, and I was bowing out, so I won't go into depth, but in my state we're faced with the challenge of supporting people who don't have broadband, or always on connections. I have always wanted a cross-platform, sometimes connected app that could bridge the gap. Use a enterprise type DB for the mother- ship, and little embedded dealies that phone home periodically to sync up. Java seems like a natural, as does Flash, for a pretty UI, so... Just ideas, probably not very viable ones, but I like them nonetheless! ;-) James, thanks for the info! Horses for courses is cool for school. Or maybe a better rhyme hrm... Yeah, if I had the time... :-P :D ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251393 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Not true, for small queries etc, a simple inline SQL block in a cfquery is as fast. Only in some circumstances will you see huge performance gains using an SP. This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Teddy Payne To: CF-Talk Sent: Mon Aug 28 01:42:25 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) I am not sure that I am seeing a valid argument to have ad hoc queries in CF. Even for small queries, the execution time will typically always be faster executed from a databse like MS SQl, Oracle ..etc than from the CF server. As for contractors waiting for the copy of the stored procedure, the DBA should has assigned the roles to the contractors to have read access to the database so they can view the SPs. Plus, you can each cotnractor copy the databse locally and test locally before asking for the DBA to commit a solution. For the deployment of the stored procedures, ths can be achieved pretty easily with generated scripts. It is not uncommon to just copy the sql necessary to update, backup and deploy all of your stored procedures very quickly. Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251205 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
No, you do not, not really. This is not how SQL Server works. You are still using inline compiled on demand SQL. This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Zaphod Beeblebrox To: CF-Talk Sent: Mon Aug 28 01:49:32 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) I run almost all my queries with queryparam so they're all precompiled anyway. When I do a trace, I see that sqlserver is calling that query as an sp after the first call. Therefore, I get the benefits of speed of the sp with the ease of deployment with cf. On 8/27/06, Teddy Payne [EMAIL PROTECTED] wrote: Even for small queries, the execution time will typically always be faster executed from a databse like MS SQl, Oracle ..etc than from the CF server. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251206 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
an addage is the an ability for other applications to use your logic when using SP. I am not sure why there seems to be a recurring issue with changing an SP when a simply F5 in QA works a charm! This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Dave Watts To: CF-Talk Sent: Mon Aug 28 02:38:21 2006 Subject: RE: Stored procs (was Top 100 ColdFusion Programmers) While on that subject, a lot of people insist that everythig should be done with SP's wherever possible. While this is indeed a good idea for long/complex queries that will see vastly improved performance and speed, but I think it is wrong to do it just for the sake of it, and to put basic select or other small queries etc into SP's. I wouldn't go so far as to insist, but I do strongly recommend it. Not for speed, primarily - using prepared statements may be as fast in many cases - but not just for the sake of it either. Using stored procedures allows you to logically segment data access code from your application in a useful way, and allows the application to be secured a bit more - you can in many cases essentially remove the ability to run arbitrary SQL from your application. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251207 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
I would love to test out my pure java embedded db in CF solution against any SP from Oracle or any of the other DBs. Just give me a dataset and I will knock one right out of the park. Write to me offline and I will set you up (for free) and you can test it out yourself. No more DBA's and pia SP in the way anymore. Dan ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251208 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
If I have a cluster of CF boxes, can they share the same embedded DB? On 8/28/06, Dan Plesse [EMAIL PROTECTED] wrote: I would love to test out my pure java embedded db in CF solution against any SP from Oracle or any of the other DBs. Just give me a dataset and I will knock one right out of the park. Write to me offline and I will set you up (for free) and you can test it out yourself. No more DBA's and pia SP in the way anymore. Dan ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251210 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Dan, I think in all honesty the only person to use this your solution would be you. Do you really think it is going to compete in the commercial DB market? Do you think that it would ever replace the need for Oracle/MSSQL? This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Dan Plesse To: CF-Talk Sent: Mon Aug 28 10:18:10 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) I would love to test out my pure java embedded db in CF solution against any SP from Oracle or any of the other DBs. Just give me a dataset and I will knock one right out of the park. Write to me offline and I will set you up (for free) and you can test it out yourself. No more DBA's and pia SP in the way anymore. Dan ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251211 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Well, there has been a strong aversion to my original thread and some agreement with various aspects. Let me add that I thank everyone for their comments. My usage of stored procedures is my personal style of coding. I consider stored procedures a good way to abstract my data code and enforce code reuseability. I find it easier to share a stored procedure with another developer than a ColdFusion template. The other developer just needs to do a cfstoredproc with the appropriate parameters and does not have to get lost in understanding the query originally set up. cfprocparam is just as effect as cfqueryparam and offers an excellent way to have multiple returned results with cfprocresult, where I can assign resultset numbers to names of a query. Now, I am aware that you could have three separate queries to achieve the same thing, but from within CF, I do not have a way to debug the SQL as quickly than I would have a tool like Query Analyzer. Now, I also know that you can copy and paste from your CF templates in Query analyzer, test the query and paste the code back to my template. I do not consider myself a purist, but I would choose to have all of SQL being managed by my database abstractly and only have my CF code display results. Another consideration, I have stored procedures that can be executed from CF and now can be shared with more advanced database operations. I use SQl Server a great deal and with the SQL execute feature of DTS, I can have the same stored procedures that I use for my CF being used by the RDBMS. For me, this is logical to have the queries available for my database and not just for the CF. CF doesn't have a way to share SQL code easily with databases that I have seen. I enjoy topics about ORM models as well. I am subscribed to the Reactor for ColdFusion mailing list as I like the approach of abstraction my queries even more into an object definition. My custom gateway methods use stored procedures to keep my personal style consistent. If you would like to share SQL concepts with me that I can apply with stored procedures, please let me know. I enjoy finding ways to structure my queries more effectively. This is a good thread. I enjoy the contrarian comments. I see most of the comments as people justifying their own methods that work for them. I suggest keep doing what works for you as I am sure that you are getting the results that you are looking for. I find that this is a style thread and that no one is arguing that stored procedures cannot achieve great results. Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251235 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Then my sql server trace logs must be lying. On 8/28/06, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED] wrote: No, you do not, not really. This is not how SQL Server works. You are still using inline compiled on demand SQL. -- I took a walk around the world to ease my troubled mind I left my body lying somewhere in the sands of time. I watched the world flow to the dark side of the moon. I feel there's nothing I can do. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251240 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
No, they are correct, you are just reading them incorrectly (in as far as what they are doing) This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Zaphod Beeblebrox To: CF-Talk Sent: Mon Aug 28 14:57:21 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) Then my sql server trace logs must be lying. On 8/28/06, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED] wrote: No, you do not, not really. This is not how SQL Server works. You are still using inline compiled on demand SQL. -- I took a walk around the world to ease my troubled mind I left my body lying somewhere in the sands of time. I watched the world flow to the dark side of the moon. I feel there's nothing I can do. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251242 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Neil, I am not sure you see the overall point. Both the in-line compiled queries and stored procedures have good results. Most of the issues that I read were about maintenance of code. I am not sure telling people that they are incorrect is the best way to share technological debates, but rather to show examples is a better approach. I will stop my thread contribution here because I feel that I do not want a thread based upon who is right or wrong when both solutions work effectively. Good luck, Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251243 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
I am also not sure what you mean? I am all for SP work - like you! There is no correct way, use what fits your needs and style. This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Teddy Payne To: CF-Talk Sent: Mon Aug 28 15:22:17 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) Neil, I am not sure you see the overall point. Both the in-line compiled queries and stored procedures have good results. Most of the issues that I read were about maintenance of code. I am not sure telling people that they are incorrect is the best way to share technological debates, but rather to show examples is a better approach. I will stop my thread contribution here because I feel that I do not want a thread based upon who is right or wrong when both solutions work effectively. Good luck, Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251247 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
I must have misread somewhere. I apologize for misinterpreting the thread. Cheers, Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251259 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Dan Plesse wrote: I would love to test out my pure java embedded db in CF solution against any SP from Oracle or any of the other DBs. I have a hard time believing that it can scale and perform: http://db.lcs.mit.edu/madden/html/javapaper.pdf Perhaps you can elaborate on the design of your solution (in another thread). Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251262 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
I would love to test out my pure java embedded db in CF solution against any SP from Oracle or any of the other DBs. Just give me a dataset and I will knock one right out of the park. Write to me offline and I will set you up (for free) and you can test it out yourself. No more DBA's and pia SP in the way anymore. Embedded databases are wildly inappropriate for web applications. How do you keep them secure? How do you maintain their integrity? You can't. Sure, individual queries may well run faster, but if all we cared about was speed, we wouldn't use RDBMSs. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251268 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
It should. Why don't you try it and find out for yourself? On 8/28/06, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED] wrote: Dan, I think in all honesty the only person to use this your solution would be you. Do you really think it is going to compete in the commercial DB market? Do you think that it would ever replace the need for Oracle/MSSQL? This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Dan Plesse To: CF-Talk Sent: Mon Aug 28 10:18:10 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) I would love to test out my pure java embedded db in CF solution against any SP from Oracle or any of the other DBs. Just give me a dataset and I will knock one right out of the park. Write to me offline and I will set you up (for free) and you can test it out yourself. No more DBA's and pia SP in the way anymore. Dan ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251280 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Nah, I will place my faith in Bill and Co. :-) This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Dan Plesse To: CF-Talk Sent: Mon Aug 28 19:35:06 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) It should. Why don't you try it and find out for yourself? On 8/28/06, Robertson-Ravo, Neil (RX) [EMAIL PROTECTED] wrote: Dan, I think in all honesty the only person to use this your solution would be you. Do you really think it is going to compete in the commercial DB market? Do you think that it would ever replace the need for Oracle/MSSQL? This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Dan Plesse To: CF-Talk Sent: Mon Aug 28 10:18:10 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) I would love to test out my pure java embedded db in CF solution against any SP from Oracle or any of the other DBs. Just give me a dataset and I will knock one right out of the park. Write to me offline and I will set you up (for free) and you can test it out yourself. No more DBA's and pia SP in the way anymore. Dan ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251282 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
I really doubt that your solution can outperform the DBA vendors. There's a lot of engineering that goes into those solutions, and it's usually not something that one man can develop. Even if your solution performs better for small datasets, can it handle working with large ones? Can your solution efficiently return a result for a data search in a 1gb table? 10gb? 100gb? Granted web applications tend to deal with smaller data tables, but I doubt that your solution would outperform SQL 2005 express even on a moderate size table (1 mil records, 1gb). Russ -Original Message- From: Dan Plesse [mailto:[EMAIL PROTECTED] Sent: Monday, August 28, 2006 2:35 PM To: CF-Talk Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) It should. Why don't you try it and find out for yourself? On 8/28/06, Robertson-Ravo, Neil (RX) Neil.Robertson- [EMAIL PROTECTED] wrote: Dan, I think in all honesty the only person to use this your solution would be you. Do you really think it is going to compete in the commercial DB market? Do you think that it would ever replace the need for Oracle/MSSQL? This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Dan Plesse To: CF-Talk Sent: Mon Aug 28 10:18:10 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) I would love to test out my pure java embedded db in CF solution against any SP from Oracle or any of the other DBs. Just give me a dataset and I will knock one right out of the park. Write to me offline and I will set you up (for free) and you can test it out yourself. No more DBA's and pia SP in the way anymore. Dan ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251283 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
Not to mention that there's strength in numbers. Why would someone want to switch when they can find numerous online resources to issues / problems they're having with more widely adopted DBs? How can they be sure that you'll continue to support / upgrade this solution? I know that I'd feel uncomfortable having my stuff sitting in a proprietary solution... !k -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Monday, August 28, 2006 11:16 AM To: CF-Talk Subject: RE: Stored procs (was Top 100 ColdFusion Programmers) I would love to test out my pure java embedded db in CF solution against any SP from Oracle or any of the other DBs. Just give me a dataset and I will knock one right out of the park. Write to me offline and I will set you up (for free) and you can test it out yourself. No more DBA's and pia SP in the way anymore. Embedded databases are wildly inappropriate for web applications. How do you keep them secure? How do you maintain their integrity? You can't. Sure, individual queries may well run faster, but if all we cared about was speed, we wouldn't use RDBMSs. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251284 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
YES if you use the webserver object Its embedded but it has remote connectivity. If you start the webserver inside a jws container you might also get object persistence benefits. I had trouble adding the driver at runtime, so I take this to mean that the context might be off the CF map. Maybe it runs on the same level as CF. I am wildly guessing maybe someone else could shed some light on this subject. I find it hard to believe that all this stuff is free and open and no one has tested this before. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251290 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Kevin Aebig wrote: Not to mention that there's strength in numbers. Why would someone want to switch when they can find numerous online resources to issues / problems they're having with more widely adopted DBs? How can they be sure that you'll continue to support / upgrade this solution? Escrow licence on the source. I know that I'd feel uncomfortable having my stuff sitting in a proprietary solution... So you don't use MS SQL Server, Oracle and DB/2 either? Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251296 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Things to consider with stored procs: Plus: More logic is in the DB than in CF Con: More logic is in the DB than in CF If you want to switch Data providers, you have a major task- CF and the DB are hard-linked sorta, if that makes sense. And I think that there is a beliefe that stored procs can't be poorly written, which is incorrect. But they do make diagnosing from within the database easier, which is where Teddy seems to get the most reward. The all around anser of you use what's best for the task is just too easy though... but it's what you have to do, so... eh... All I have to say is that the questions that the Pragmatic Programmer asks, are difficult ones, and involve being able to swap parts out indescriminately, which is REALLY hard to code for, as easy as the concept seems. 'Cuz it takes it out of the domain of what the language is you're using to develope, even. E.g.: switching from CF to PHP, or ASP, or Java or C##... Man, how could you do that? Eh. Dang. Meeting. Well, I'll comment on hsqldb later, but I think it's pretty interesting, and I wonder why no CFers seem to be utilizing it as well. Derby? Anyone using Derby? :den On 8/28/06, Teddy Payne [EMAIL PROTECTED] wrote: Well, there has been a strong aversion to my original thread and some agreement with various aspects. Let me add that I thank everyone for their comments. My usage of stored procedures is my personal style of coding. I consider stored procedures a good way to abstract my data code and enforce code reuseability. I find it easier to share a stored procedure with another developer than a ColdFusion template. The other developer just needs to do a cfstoredproc with the appropriate parameters and does not have to get lost in understanding the query originally set up. cfprocparam is just as effect as cfqueryparam and offers an excellent way to have multiple returned results with cfprocresult, where I can assign resultset numbers to names of a query. Now, I am aware that you could have three separate queries to achieve the same thing, but from within CF, I do not have a way to debug the SQL as quickly than I would have a tool like Query Analyzer. Now, I also know that you can copy and paste from your CF templates in Query analyzer, test the query and paste the code back to my template. I do not consider myself a purist, but I would choose to have all of SQL being managed by my database abstractly and only have my CF code display results. Another consideration, I have stored procedures that can be executed from CF and now can be shared with more advanced database operations. I use SQl Server a great deal and with the SQL execute feature of DTS, I can have the same stored procedures that I use for my CF being used by the RDBMS. For me, this is logical to have the queries available for my database and not just for the CF. CF doesn't have a way to share SQL code easily with databases that I have seen. I enjoy topics about ORM models as well. I am subscribed to the Reactor for ColdFusion mailing list as I like the approach of abstraction my queries even more into an object definition. My custom gateway methods use stored procedures to keep my personal style consistent. If you would like to share SQL concepts with me that I can apply with stored procedures, please let me know. I enjoy finding ways to structure my queries more effectively. This is a good thread. I enjoy the contrarian comments. I see most of the comments as people justifying their own methods that work for them. I suggest keep doing what works for you as I am sure that you are getting the results that you are looking for. I find that this is a style thread and that no one is arguing that stored procedures cannot achieve great results. Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251299 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
I find it hard to believe that all this stuff is free and open and no one has tested this before. For the same effect, you could just use PointBase, which ships with JRun/CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251304 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
It should. Why don't you try it and find out for yourself? I can only speak for myself, but I don't use Oracle and MS SQL Server because they're faster. I use them because they're reliable, can be secured and managed from outside of my application, can support multiple separate applications, and can scale. An embedded database is a toy in a web application. There are plenty of great environments for embedded databases, like my cell phone. This isn't one of them. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251305 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
This is an important point that seems to have been missed in this thread. From the perspective of creating secure applications you can do much more to limit access to your data through stored procedures and actually using the access control systems that are built into the RDBMS of your choice. In Oracle and now in DB/2 we have actually been using DB side user accounts to enforce table and even row level grants against data. This is enforced through the DB itself, and the application only has to know it either got the data back, or it got an error back, and how to respond to each. Someone else mentioned coding so that the application doesn't need to know what it is receiving its information from. I don't think this is as pertinent to this conversation. Encapsulating your SP calls in CFCs gives you the ability to quickly change to a different means of data storage (LDAP, XML, whatever). -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Sunday, August 27, 2006 9:38 PM To: CF-Talk Subject: RE: Stored procs (was Top 100 ColdFusion Programmers) While on that subject, a lot of people insist that everythig should be done with SP's wherever possible. While this is indeed a good idea for long/complex queries that will see vastly improved performance and speed, but I think it is wrong to do it just for the sake of it, and to put basic select or other small queries etc into SP's. I wouldn't go so far as to insist, but I do strongly recommend it. Not for speed, primarily - using prepared statements may be as fast in many cases - but not just for the sake of it either. Using stored procedures allows you to logically segment data access code from your application in a useful way, and allows the application to be secured a bit more - you can in many cases essentially remove the ability to run arbitrary SQL from your application. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251306 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
Escrow licence on the source. If I wanted to support someone else's work, than I might as well just make my own. I know that I'd feel uncomfortable having my stuff sitting in a proprietary solution... So you don't use MS SQL Server, Oracle and DB/2 either? It's pretty obvious that MSSQL, Oracle and DB2 aren't open-source. At the same time though, they also work under the same core principals and work generally under the same features. I don't care about the low-level details of how they work, I just want to be sure that it's going to be around for more than a few years... !k -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Monday, August 28, 2006 1:03 PM To: CF-Talk Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) Kevin Aebig wrote: Not to mention that there's strength in numbers. Why would someone want to switch when they can find numerous online resources to issues / problems they're having with more widely adopted DBs? How can they be sure that you'll continue to support / upgrade this solution? Escrow licence on the source. I know that I'd feel uncomfortable having my stuff sitting in a proprietary solution... So you don't use MS SQL Server, Oracle and DB/2 either? Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251314 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Kevin Aebig wrote: Escrow licence on the source. If I wanted to support someone else's work, than I might as well just make my own. Yet if the work is Bill's or Larry's, you want to support it by paying a license? It's pretty obvious that MSSQL, Oracle and DB2 aren't open-source. At the same time though, they also work under the same core principals and work generally under the same features. I don't care about the low-level details of how they work, I just want to be sure that it's going to be around for more than a few years... So it has got nothing to do with being a proprietary solution? Don't get me wrong, I very much doubt that Dan's embedded database offers any added value over established offerings and I share your concern over his ability to provide support and an upgrade path. But that has nothing to do with being propietary. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251319 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
If I wanted to support someone else's work, than I might as well just make my own. Yet if the work is Bill's or Larry's, you want to support it by paying a license? By support someone else's work I mean fix someone elses mistakes when they decide to pack up and run, leaving me watching my software. I'm not only buying a license of usage, but the reputation that their software has earned through years of upgrades and testing... Plus I don't want to get sued. =] So it has got nothing to do with being a proprietary solution? You're talking about a proprietary code-base and I'm talking about a proprietary concept. I personally don't believe this concept has the added value to out-perform or take market share away from the proven vendors. Other than that I think we're agreeing... which is kind of confusing. =] Cheers, !k -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Monday, August 28, 2006 3:17 PM To: CF-Talk Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) Kevin Aebig wrote: Escrow licence on the source. If I wanted to support someone else's work, than I might as well just make my own. Yet if the work is Bill's or Larry's, you want to support it by paying a license? It's pretty obvious that MSSQL, Oracle and DB2 aren't open-source. At the same time though, they also work under the same core principals and work generally under the same features. I don't care about the low-level details of how they work, I just want to be sure that it's going to be around for more than a few years... So it has got nothing to do with being a proprietary solution? Don't get me wrong, I very much doubt that Dan's embedded database offers any added value over established offerings and I share your concern over his ability to provide support and an upgrade path. But that has nothing to do with being propietary. Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251321 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
It's not my solution, that's why it's free and open. It would be nice if
someone could run benchmarks on the different kinds
of things you can create and use. Even the two different kinds protocols the
server object uses could be tested.
I did run and test Derby and your welcome to that code as well Denny!
cool pointbase!
cfset test = createObject(java, java.lang.Class)
cfset jdbcDriver = test.forName(com.pointbase.jdbc.jdbcUniversalDriver
).newInstance()
cfdump var=#jdbcDriver#
cfdump var=#jdbcDriver.getMinorVersion()# 2
cfdump var=#jdbcDriver.jdbcCompliant()# YES
Can you do jdbc:pointbase:server://localhost:1/sample,new ??
cfdump
var=#jdbcDriver.acceptsURL(jdbc:pointbase:server://localhost:1/)#
YES
public class com.pointbase.jdbc.jdbcUniversalDriver
extends java.lang.Object
implementsjava.sql.Driver, com.pointbase.jdbc.jdbcInfoDriver
{
/*** CONSTRUCTORS ***/
public com.pointbase.jdbc.jdbcUniversalDriver()
/*** METHODS ***/
public java.lang.String getURL()
public java.util.Properties getProperties()
public synchronized java.sql.Connection connect(java.lang.String,
java.util.Properties)
throws java.sql.SQLException
public synchronized int getMajorVersion()
public synchronized boolean acceptsURL(java.lang.String)
throws java.sql.SQLException
public int getMinorVersion()
public synchronized [Ljava.sql.DriverPropertyInfo;
getPropertyInfo(java.lang.String, java.util.Properties)
throws java.sql.SQLException
public boolean jdbcCompliant()
~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251323
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
I see a lot of comments on supporting the software vendors for Oracle and MS SQL Server. These solutions attract organizations and developers because of the level of documentation, training and certification offerings. Unless you plan to create independent solutions that are for very specific solutions, then a mainstream software vendor helps provide a toolset that is not perfect, but extremely feature rich. You can agree to disagree on the usage of the databases, but their size and effect cannot be ignored. Most people from the CF community are probably not looking to create a Java API to connect to a database management system even if it is for free. MySQL is free and offers scaleable solutions that commercial entities take advantage of. ColdFusion recognizes the value of MySQL and provides a method to connect jConnect 3.0 with CFMX 7.02. I think the average developer would choose MySQL over an embeded solution. I see too many developers caught up in supporting this person versus that person. I have clients from all walks of technology so I cannot discriminate supporting technoloies if I plan to provide a valueable service to my market base. Is open source preferred? I would happily say yes. Will I use the large software vendors products? I would happily say yes as well. If you feel that you are being assimilated by the Borg, then please feel free to rebel. If you want to use all solutions possible to weigh the value of a solution from multiple platforms, then learn more about each product and be more objective on appreciating the value of each solution. If people knew more about embeded databses and were willing to spend the time from the CF community, then I suspect there will be more blog posts about the topic. I have not seen much on this topic. If someone is willing to start a blog series that would explain the benfit and a simple API to integrate the solutions, then I would gladly read. For now, accessing Access, MS SQL, MySQL and Oracle is enough for me on my plate. I have more programs to connect to managed systems that I do development tools. Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251330 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
On 8/28/06, Dan Plesse [EMAIL PROTECTED] wrote: It's not my solution, that's why it's free and open. It would be nice if someone could run benchmarks on the different kinds of things you can create and use. Even the two different kinds protocols the server object uses could be tested. All the benchmarks I saw put HSQLDB pretty far up there... speed wise. I don't think it would be a very good data-warehouse... the largest object size seems to be around a megabyte... it does handle up to 8 gig databases now though... that's pretty big. Relatively speaking... I did run and test Derby and your welcome to that code as well Denny! Of course you did Dan. :-P You're the man! Thanks for sharing!! You know, open source is really the ONLY software you can run without having to worry about it packing up and leaving. It's impossible; the blue-print is right there. Real proprietary code (or concepts??) is where you could get left high and dry. Logically. I'm not saying MS will fold in the next year or so, but from a theoretical perspective, at least with an open architecture, nothing is hidden from you, there is nothing to be taken away. I don't know WHAT ms is doing behind the scenes. Sure I could do all the nifty stuff, reverse-engineering and whatever, but... what a crap load of work for something you can't even use once you figure out. Sell to the blackmarket, maybe, but... um... bleh. As for Oracle and MS-SQL... well, maybe you get what you pay for... maybe... there are some interesting stats for various databases that make me wonder if the big O (sorry, that's pl SLASH sql ;) is really worth as much as a pretty awesome car. You're paying for way more than just the raw meat you need to get the job done, I'd wager. Lawyers, PR folks... but that's probably a bigger reason than the actual power of the DB... to use it, I mean. Maybe I'm just eating the sweet, sweet candy that is Open databases, and it's smoke and mirrors... I sure haven't tested them against [a properly configured, optimized, whathaveyou] oracle... they do work tho... I mean, we're loading a bunch of data into RAM anyways, when you get down to it. Cache is king, right? I can't help but think that you can get around any bottleneck... I personally find it funny that there are times where it is literally faster to drive a truck full of data cross-country than to do it in the aether. At least a little funny... same concept here though... plenty of times I've just popped a drive out o one thing and popped it into another... that's way faster... eh... Data-warehousing is different though, if I'm thinking of the right kind of data-warehousing (Lets you search through tera's of info pretty quick?) Guess you'd need some clustering to get that going good in something smaller. Ha! And that's one of the things I was thinking about when Dave said the Web isn't a place for fast little db's. Seems alot like the raging argument (for years and years, mind) concerning distributed applications and server applications. Or these thin clients and whatnot. 'Seen it go round and round, since the days of yore. Yin is better... No, Yang is the Only Way! No, wait, Yang-Yin! Hrm... close... close... :-) Probably pretty core to the human condition, really... Individualistic, yet social, alone in a sea of people, brain's in two pieces, etc., etc.. We try and try to pull it all together, ABSOLUTELY together, which is what screws us. If we would just realize it's already roughly there... And roughly is good enough... Which brings us back to the SP idea. And implicit and explicit and whatnot. Info in one place, or near by, at least. Why we have to know SQL as well as CF. All that jazz! Doesn't really matter. There are ways of mitigating bad stuff, propagating good stuff... doesn't really matter the forum or media. I wouldn't shy away from something just because it's different though*. Personally, I have, for years been watching these little databases, and distributed computing/synchronization, and the big databases, and how they can all get along. I have to say the little fast guys are going to play a part, if I can guess even a little. Did I mention I just got a new Cell phone? Can you BELIEVE the stuff that's out there now? Sheesh. Amazing. Simply amazing. In 2 years! Leaps and bounds. Leaps and bounds! (my old cell was @ 2yrs). It makes my nipples hard. And it makes me curious about how I can code for all of it. At once. LOL. I might be getting a little absolute there... Anyways, I think the most valid argument for doing stuff in the DB is, obviously, for doing stuff in the DB. I'm just not too sure Oracle is where I want to spend my time. Did you know homecheese wants to start a super database (along with the Prez) of everyone, with chips whatnot so we can stop these terrerristts? Semi-scary, and not a reason to not use Oracle, but still... And MS-SQL is insanely easy. I mean, you drag and drop relationships!
Re: Stored procs (was Top 100 ColdFusion Programmers)
On 8/29/06, Denny Valliant [EMAIL PROTECTED] wrote: I don't think it would be a very good data-warehouse... the largest object size seems to be around a megabyte... it does handle up to 8 gig databases now though... that's pretty big. Relatively speaking... Well, Oracle's BLOBs can be up to 4 gigs each, which should provide some perspective. As for Oracle and MS-SQL... well, maybe you get what you pay for... maybe... there are some interesting stats for various databases that make me wonder if the big O (sorry, that's pl SLASH sql ;) is really worth as much as a pretty awesome car. You're paying for way more than just the raw meat you need to get the job done, I'd wager. For some more perspective, our Oracle environment lives on a Sun E20K (http://www.sun.com/servers/highend/sunfire_e20k/index.xml) which, according to that link, start at US$452000 ish. This is why I make use of as many Oracle features as I can - to hell with portability, we paid for it so I'm using it. -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251333 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Sorry to reply to myself, but that BLOB size limit is for Oracle 8 and 9 - in 10G Oracle can support a BLOB or CLOB of somewhere between 8 terabytes and 128 terabytes, depending on the DB block size. Yes, terabytes. On 8/29/06, James Holmes [EMAIL PROTECTED] wrote: On 8/29/06, Denny Valliant [EMAIL PROTECTED] wrote: I don't think it would be a very good data-warehouse... the largest object size seems to be around a megabyte... it does handle up to 8 gig databases now though... that's pretty big. Relatively speaking... Well, Oracle's BLOBs can be up to 4 gigs each, which should provide some perspective. As for Oracle and MS-SQL... well, maybe you get what you pay for... maybe... there are some interesting stats for various databases that make me wonder if the big O (sorry, that's pl SLASH sql ;) is really worth as much as a pretty awesome car. You're paying for way more than just the raw meat you need to get the job done, I'd wager. For some more perspective, our Oracle environment lives on a Sun E20K (http://www.sun.com/servers/highend/sunfire_e20k/index.xml) which, according to that link, start at US$452000 ish. This is why I make use of as many Oracle features as I can - to hell with portability, we paid for it so I'm using it. -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251334 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
I did a project where sp's were used almost exclusively. That was a major pia whenever it came to moving the app from dev to production. Usually, you can role an update to production by just copying over the cfml. Changes in sp's required you to either increment the sp name, or take the app down while you rolled out the sp change. Either way, it was a pia. On 8/27/06, Snake [EMAIL PROTECTED] wrote: While on that subject, a lot of people insist that everythig should be done with SP's wherever possible. While this is indeed a good idea for long/complex queries that will see vastly improved performance and speed, but I think it is wrong to do it just for the sake of it, and to put basic select or other small queries etc into SP's. One of the primary reasons being that where contractors are involved, they will often have access to the CFML only and not the database server, and thus cannot access the stored proc, which can seriously slow down development time while they wait for the DBA to send them a copy of the SP, and then change it, send it back to the DBA, wait for him to apply it etc. Also it is in general a pain if you need to do quick fixes or updates on code in general. Usually it is easy to gain access to the code via FTP from wherever you are as this is generally an open port, but not to the DB server, as you will either need remote access to port 1433 or remote desktop access. neither of which may be obtained quickly or easily as they should/would be blocked from the outside world via a firewall. Just my 2p Snake -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: 27 August 2006 13:42 To: CF-Talk Subject: Re: Top 100 ColdFusion Programmers Again, and the point has been echoed by James, is that the all-rounder should know both. I am not saying that SPs etc (not that has anything to do with you level of SQL per se) are what you should know, we all know that ColdFusion can do a lot with loops and SQL but if you have no choice but to use SQL in a job, you will be stuffed if all you know SELECT. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251193 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Take the app down? Increment? I am not sure what server you were on but none of that is necessary. This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Zaphod Beeblebrox To: CF-Talk Sent: Sun Aug 27 23:29:16 2006 Subject: Re: Stored procs (was Top 100 ColdFusion Programmers) I did a project where sp's were used almost exclusively. That was a major pia whenever it came to moving the app from dev to production. Usually, you can role an update to production by just copying over the cfml. Changes in sp's required you to either increment the sp name, or take the app down while you rolled out the sp change. Either way, it was a pia. On 8/27/06, Snake [EMAIL PROTECTED] wrote: While on that subject, a lot of people insist that everythig should be done with SP's wherever possible. While this is indeed a good idea for long/complex queries that will see vastly improved performance and speed, but I think it is wrong to do it just for the sake of it, and to put basic select or other small queries etc into SP's. One of the primary reasons being that where contractors are involved, they will often have access to the CFML only and not the database server, and thus cannot access the stored proc, which can seriously slow down development time while they wait for the DBA to send them a copy of the SP, and then change it, send it back to the DBA, wait for him to apply it etc. Also it is in general a pain if you need to do quick fixes or updates on code in general. Usually it is easy to gain access to the code via FTP from wherever you are as this is generally an open port, but not to the DB server, as you will either need remote access to port 1433 or remote desktop access. neither of which may be obtained quickly or easily as they should/would be blocked from the outside world via a firewall. Just my 2p Snake -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: 27 August 2006 13:42 To: CF-Talk Subject: Re: Top 100 ColdFusion Programmers Again, and the point has been echoed by James, is that the all-rounder should know both. I am not saying that SPs etc (not that has anything to do with you level of SQL per se) are what you should know, we all know that ColdFusion can do a lot with loops and SQL but if you have no choice but to use SQL in a job, you will be stuffed if all you know SELECT. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251195 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Always depend on who the contract is being done for though. Most of the places I have worked for always give us(the contractors) basically full access to the DB in development and no access to production except special cases where they give simple select access. I have done work for some government agencies that required all queries to be within SPs and would imagine other places have similar requirements. On 8/27/06, Snake [EMAIL PROTECTED] wrote: While on that subject, a lot of people insist that everythig should be done with SP's wherever possible. While this is indeed a good idea for long/complex queries that will see vastly improved performance and speed, but I think it is wrong to do it just for the sake of it, and to put basic select or other small queries etc into SP's. One of the primary reasons being that where contractors are involved, they will often have access to the CFML only and not the database server, and thus cannot access the stored proc, which can seriously slow down development time while they wait for the DBA to send them a copy of the SP, and then change it, send it back to the DBA, wait for him to apply it etc. Also it is in general a pain if you need to do quick fixes or updates on code in general. Usually it is easy to gain access to the code via FTP from wherever you are as this is generally an open port, but not to the DB server, as you will either need remote access to port 1433 or remote desktop access. neither of which may be obtained quickly or easily as they should/would be blocked from the outside world via a firewall. Just my 2p Snake -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: 27 August 2006 13:42 To: CF-Talk Subject: Re: Top 100 ColdFusion Programmers Again, and the point has been echoed by James, is that the all-rounder should know both. I am not saying that SPs etc (not that has anything to do with you level of SQL per se) are what you should know, we all know that ColdFusion can do a lot with loops and SQL but if you have no choice but to use SQL in a job, you will be stuffed if all you know SELECT. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251196 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
I used'em in an app just to gain the ability to DO it. Sucked bigtime! Whenever I needed to change some SQL for updates and such, it was a major PITA! Sorry, but I think all that logic belongs in the app itself. Will - Award Winning Author and Database Expert ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251197 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
I am not sure that I am seeing a valid argument to have ad hoc queries in CF. Even for small queries, the execution time will typically always be faster executed from a databse like MS SQl, Oracle ..etc than from the CF server. As for contractors waiting for the copy of the stored procedure, the DBA should has assigned the roles to the contractors to have read access to the database so they can view the SPs. Plus, you can each cotnractor copy the databse locally and test locally before asking for the DBA to commit a solution. For the deployment of the stored procedures, ths can be achieved pretty easily with generated scripts. It is not uncommon to just copy the sql necessary to update, backup and deploy all of your stored procedures very quickly. Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251198 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
I run almost all my queries with queryparam so they're all precompiled anyway. When I do a trace, I see that sqlserver is calling that query as an sp after the first call. Therefore, I get the benefits of speed of the sp with the ease of deployment with cf. On 8/27/06, Teddy Payne [EMAIL PROTECTED] wrote: Even for small queries, the execution time will typically always be faster executed from a databse like MS SQl, Oracle ..etc than from the CF server. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251199 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
When we move from development to production, we have to provide SQL scripts to setup all tables, SPs, views, and so on. Our DBAs are just there to make sure the boxes stay up and running, they do not even run those SQL scripts we provide. The scripts are ran usually by the web server admins. On 8/27/06, Teddy Payne [EMAIL PROTECTED] wrote: For the deployment of the stored procedures, ths can be achieved pretty easily with generated scripts. It is not uncommon to just copy the sql necessary to update, backup and deploy all of your stored procedures very quickly. Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251200 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
While on that subject, a lot of people insist that everythig should be done with SP's wherever possible. While this is indeed a good idea for long/complex queries that will see vastly improved performance and speed, but I think it is wrong to do it just for the sake of it, and to put basic select or other small queries etc into SP's. I wouldn't go so far as to insist, but I do strongly recommend it. Not for speed, primarily - using prepared statements may be as fast in many cases - but not just for the sake of it either. Using stored procedures allows you to logically segment data access code from your application in a useful way, and allows the application to be secured a bit more - you can in many cases essentially remove the ability to run arbitrary SQL from your application. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251201 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Stored procs (was Top 100 ColdFusion Programmers)
Interesting topic, eh? As long as the SP doesn't get recompiled, the SP should be somewhat faster than plain CF query. SP coded in T-SQL or the powerful PL/SQL can save you lots of time doing the same thing in CF too. But when it goes to deployment, it will take more steps for just to upgrade your code. But i guess this is much depends on the hardware/system architecture also. Consider this: 1. Having 3 CF server running on cluster vs 1 DB server. Which will be the most fast/resource optimized? Put all queries and process in CF machines or in 1 DB? IMHO, should be better to put them in CF machines. 2. Having 1 CF server vs 3 DB running on cluster/grid (not a standby-failover). Should be better on the DB now... Well, i think sometime SP is overlooked, ppl wants to move query to sp. But process-tuning instead of SQL tuning may be the better alternative. Rizal At 07:42 AM 28/08/2006, you wrote: I am not sure that I am seeing a valid argument to have ad hoc queries in CF. Even for small queries, the execution time will typically always be faster executed from a databse like MS SQl, Oracle ..etc than from the CF server. As for contractors waiting for the copy of the stored procedure, the DBA should has assigned the roles to the contractors to have read access to the database so they can view the SPs. Plus, you can each cotnractor copy the databse locally and test locally before asking for the DBA to commit a solution. For the deployment of the stored procedures, ths can be achieved pretty easily with generated scripts. It is not uncommon to just copy the sql necessary to update, backup and deploy all of your stored procedures very quickly. Teddy ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251202 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Stored procs (was Top 100 ColdFusion Programmers)
There are other concerns for using stored proc's other than just performance and security. We decided to enforce a stored procedure only policy because we were experiencing issues with our cf servers related to database operations. In addition we noticed a very number of database calls per page (using cfstat server statistics collected over time.) Because of this we decided we needed more eyes taking a harder look at database operations, and the best way *that we found* to do that was to make sure all SQL was in the database, and easily accessible by the DBA's. In our environment, developers don't have access to production SQL servers at all. SQL changes must go through the DBA's. They also don't have direct access to the production CF servers, but can publish to the production servers at will. However accept for a code review to get on to the production CF servers, there is no oversight of CF after a code review, (unless an application starts misbehaving.) Additionally to this, we had been recommending and urging developers for years to use only stored procedures, but we didn't really make a dent. So because of all of these factors, when we rolled out a new environment for CF 7, we made stored proc's only part of the rules of using the new systems. We don't have the having to ask the DBA's to make every change problem because we have a development SQL server with databases to which developers have owner access. They still have to ask for changes in production, but that's a constraint of our environment, and the DBA's are fairly responsive to changes during the working day. Has this solved our problems? Well to some degree, yes. We don't have random, hard-to-diagnose problems due to database operations anymore. From time to time, when we do have issues with our database connections, I have found them to be much easier to troubleshoot on the MS SQL side, than on the CF side. The developers are less happy about it, and I understand that, but it was something we needed to do. The important thing we did though was make sure that many of the disadvantages of doing this we mitigated. Like all things it's a trade off, and these decisions should be made in response to the actual conditions in play, as opposed to some philosophical or religious reasons. Terrence Ryan Senior Systems Programmer Wharton Computing and Information Technology E-mail: [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:251204 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
