Jochem, I tried to make an example using the INSERT .. RETURNING syntax directly in a cfquery to make the example clearer, but i couldn't get it to work with straight SQL (didn't try things like anon pl/sql block). Do you have such an example you could share with us?
Aaron, >What type of advantages does an SP in oracle(or whatever) have >over queries for basic DB inserts? SPs don't have any benefits over queries specifically for inserts, but they have more general advantages that come into play here as well, like the fact that they "automagically" use bind variables (unless you use dynamic sql), etc. Contrary to popular belief, simple SPs are not markedly faster than simple SQL statements, assuming several things (like either correct use of cfqueryparam or repeating the same query over and over so it's exec plan is read from the cache). The real benefits are in things like code organization - keeping your sql separate from your CFML, the fact that other developers on a team are less likely to change a SP than a block of SQL in a cfm file - and even less likely to just copy and paste it somewhere else if they need "something kinda like that". Also, in oracle, the db can warn you if your sp's become invalid, for example as a consequence of modifying a table, whereas with queries you'll discover that error at runtime. You can use sp's to enforce quite clever database security practices due to what's called the "definer rights" model (by default, sp's in oracle execute with the privs of the user who wrote them, not the user who's running them). Once you get into more complex db manipulations - the kind of things where you'd have several cfqueries in a row - then sp's offer the possibility to do all kinds of stuff in a single trip to the db, but then you're heading towards putting business logic in the db... not necessarily a bad thing, but something to keep in mind. I started using sp's because i had to, but now i think i would use them for anything but the very simplest of projects. /t >-----Original Message----- >Subject: Get New Record ID in ORACLE >From: Jochem van Dieten <[EMAIL PROTECTED]> >Date: Tue, 07 Jun 2005 10:33:57 +0200 >Thread: >http://www.houseoffusion.com/cf_lists/index.cfm/method=messages >&threadid=40571&forumid=4#208766 > >James Holmes wrote: >> For one they get around the problem of getting the last id >inserted into >> the table as per some of the discussion in the MySQL thread >yesterday, >> without needing to use serializable transactions. > >But for that you can also use the INSERT .. RETURNING .. directly. > >Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208770 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
