Or the simplified version of the example INSERT INTO tablefoo (id, goo) VALUES (seqname.next_val, 'soem stuff')
-------------- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning ....-----Original Message----- ....From: Douglas Knudsen [mailto:[EMAIL PROTECTED] ....Sent: Thursday, May 12, 2005 10:00 AM ....To: CF-Jobs-Talk ....Subject: Re: What makes a programmer look low level .... ....with oracle vernacular the word is sequence. Just perform a query to ....return ....the next value in the sequence, then use it in your inserts. .... SELECT seqname.next_val as newid FROM dual .... INSERT INTO tablefoo (id, goo) VALUES (newid, 'soem stuff') .... DK .... .... On 5/12/05, Aaron Rouse <[EMAIL PROTECTED]> wrote: ....> ....> That syntax is not even valid for all databases. An example I am ....thinking ....> of ....> is how to return the identity when using Oracle. I know the syntax to ....use ....> if ....> just in a SQL client, tried it with a couple of versions of DB drivers ....> with ....> CF and never had it work. Of course it could simple just be done with ....an ....> SP, ....> which is what I do anyway. ....> CFLOCKing two queries together? Wouldn't that be CFTRANSACTIONing the ....two ....> queries together? ....> Wish we had DBAs that could review our SP's to see if things could be ....> improved. :( ....> On 5/12/05, Glenn Saunders <[EMAIL PROTECTED]> wrote: ....> > ....> > ....> > This has sometimes worked in the past: ....> > ....> > declare @new_id INT ....> > insert into table (a, b) ....> > values ('#a#', '#b#' ....> > select @new_id as new_id ....> > ....> > It depends on the DB driver. ....> > ....> > I've had this sometimes work and sometimes not work. It's almost like ....> the ....> > driver (or CF) says "oh, this is an insert, you don't need any data ....> back" ....> > and throws out the data. ....> > ....> > Using a 2nd query to get the identity is not reliable without ....CFLOCKing ....> > the ....> > two queries together and it's not as efficient. ....> > ....> > One thing I wish CFQUERY could do is return multiple recordsets and ....> output ....> > parameters back. In PHP you can do this. ....> > ....> > >Just curious, not ....> > >implying anything wrong with it one bit, but what are your ....reasonings ....> for ....> > >using almost all SPs for your DB work? ....> > ....> > Because it's faster in heavy-load environments and it provides a ....central ....> > clearing house for db code that the dbas can (presumably) analyze and ....> > improve. It also lets you restrict direct access to tables, just ....> granting ....> > EXEC permissions on a proc by proc basis after proper review. ....> > ....> > ....> ....> .... .... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:11:2620 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/11 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:11 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.11 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
