aaargh - sorry, keep forgetting to turn off my digital signature. i think the server kicks the mail in that case? otherwise... sorry ;))
>-----Original Message----- >From: RADEMAKERS Tanguy >Sent: Tuesday, June 07, 2005 6:33 PM >To: '[email protected]' >Subject: RE: Get New Record ID in ORACLE > >-----Original Message----- >Subject: Get New Record ID in ORACLE >From: Adrocknaphobia <[EMAIL PROTECTED]> >Date: Tue, 7 Jun 2005 10:51:35 -0400 >Thread: >http://www.houseoffusion.com/cf_lists/index.cfm/method=messages >&threadid=40571&forumid=4#208800 > >1. Performance (maybe not as much on insert, but updates an queries >definitely faster). When running queries in Oracle, a plan is made on >how to efficiently retrieve the data. The PL/SQL is precompiled and an >action plan is made to optimize performance. That's not a completely fair or accurate point: the second execution of a stored procedure will be much faster than the second execution of a <cfquery> assuming that the cfquery doesn't use bind variables and is being run with different parameters. Otherwise, the db will reuse the stored query plan and the time difference won't be *that* great. The reason i want to point this out is that a lot of people think "SP's are complicated but they're fast", so they tend to see them as a kind of optimization technique... and then they get disappointed when switching to SP's doesn't magically "deliver" much faster execution time. If all you're after is speed and scalability then a) spend some time learning how to write correct sql and b) use cfqueryparam. >2. Transaction and Locking. A procedure is an implied transaction >block that the _database_ manages. If your database supports the >ability for transaction and locking, CF should really never be used. >If you have multiple SQL statements to execute, when in a procedure, >it one fails then the actions are not commited to the database. > True enough, but then again that's what cftransaction is for. You could counter with savepoints and autonomous transactions but then we're really getting into edge cases. > >3. Security. Security. Security. This is by far the biggest and most >important aspect of procedures in my opinion. I can't sleep easy at >night knowing an application's SQL statements (a roadmap diagraming >your database) is stored in open text files on your web server (which >is the server most commonly comprimised). > >Additionaly, with procedures and packages you can start to leverage >the role based permission of your database. For instance, the only >actions a user is allowed to perform in the database is to execute a >certain set of methods. When the SQL is stored in CF, then the user >must have permission to select, insert, update & delete on every table >in your database. (Unless you want to get into the enormous task of >specifying each table/privelage seperately) +1. The number of people who connect their web application to their database as a user with dba privs is amazing. There are two aspects to sql injection attacks: people putting junk data into your tables... and people damaging your database (dropping tables, deleting records, etc). In most cases, the user your web app connects as has no business being allowed to drop or create objects. SP's are a great way to grant explicit object level privs to the web app user without having to even use the db privs system: they can only do those things you gave them a proc to do. > >4. Inline SQL functions. The ability to call custom functions >within a query. yessssss, but: you could write functions in pl/sql and call them from within a query in a cfquery block. > >These are the first 4 that benefits that come to mind, but there are >surely more. PL/SQL is a very very very simple language. You can read >Oracle Press PL/SQL book in a weekend and become an expert. > >-Adam My favorite stored proc feature is that when i change a database table i immediately know which procs need to be updated at the press of a button, without having to test my whole web app. My second favorite feature is that i know where all my sql is. To be more explicit on the book end: check out the book "Learning Oracle PL/SQL" from O'Reilly and hold off on the two other fuerenstein books - you can easily read that in a weekend and it will cover everything almost everybody needs to know for a long long time. /t ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208841 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
