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.
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:11:2619
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
