RE: retrieve ID from db insert

[RADEMAKERS Tanguy]

Two other good resources:

http://www.jlcomp.demon.co.uk/
Jonathan Lewis' site -check out the oracle FAQs

http://www.psoug.org/reference
which has some great examples for more advanced db features

and, of course, the oracle docco, esp. the Concepts guide,
Application Developer's Guide - Fundamentals, and the various reference
guides to SQL, PL/SQL and (for when things go wrong) Error Messages. 
 

>-Original Message-----
>Subject: retrieve ID from db insert
>From: Aaron Rouse <[EMAIL PROTECTED]>
>Date: Wed, 13 Jul 2005 06:09:37 -0500
>Thread: 
>http://www.houseoffusion.com/cf_lists/index.cfm/method=messages
>&threadid=41103&forumid=4#211728
>
>I forgot to mention I have used their Ask Tom a few times and 
>with good 
>success. I usually end up finding something similar to my 
>needs in there and 
>try to apply it and if I get stumped then post to it, not sure 
>if I have 
>ever done a new thread in Ask Tom.


~|
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:211731
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

Re: retrieve ID from db insert

[Aaron Rouse]

I forgot to mention I have used their Ask Tom a few times and with good 
success. I usually end up finding something similar to my needs in there and 
try to apply it and if I get stumped then post to it, not sure if I have 
ever done a new thread in Ask Tom.

On 7/12/05, daniel kessler <[EMAIL PROTECTED]> wrote:
> 
> hey that's great! thanks. I looked it over and it seems very useful, at 
> least for me.
> I appreciate moving my Oracle questions over to Oracle.
> 
> >I have had very good luck here:
> > http://forums.oracle.com/forums/forum.jsp?forum=75
> > My experience everywhere else, even here at work has been the holier 
> than
> >though DBAs you described. I even got that experience from one here at 
> work
> >that I helped with by sending some LDAP/Oracle integration I had worked 
> on
> >which I thought was rather surprising.
> >
> > On 7/12/05, Deanna Schneider <[EMAIL PROTECTED]> wrote:
> 
> 

~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211728
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

Re: retrieve ID from db insert

[daniel kessler]

hey that's great!  thanks.  I looked it over and it seems very useful, at least 
for me.
I appreciate moving my Oracle questions over to Oracle.

>I have had very good luck here:
> http://forums.oracle.com/forums/forum.jsp?forum=75
> My experience everywhere else, even here at work has been the holier than 
>though DBAs you described. I even got that experience from one here at work 
>that I helped with by sending some LDAP/Oracle integration I had worked on 
>which I thought was rather surprising.
>
> On 7/12/05, Deanna Schneider <[EMAIL PROTECTED]> wrote:

~|
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:211709
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

Re: retrieve ID from db insert

[Aaron Rouse]

True although I work with people who convert Excel "databases" over to 
Oracle, but of course they use Access as a jump to there. At the end of the 
"day" they have a really nice Oracle "Spreadsheet"

On 7/12/05, Jochem van Dieten <[EMAIL PROTECTED]> wrote: 
> 
> 
> 
> ISTM it usually is the other way around: Excel is the worlds most
> used 'database' :)
> 
> 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:211693
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

Re: retrieve ID from db insert

[Jochem van Dieten]

RADEMAKERS Tanguy wrote:
> 
> And it's not restricted to Oracle - there are doubtless plenty of people
> out there using SQLServer as if it was a big excel spreadsheet.

ISTM it usually is the other way around: Excel is the worlds most 
used 'database' :)

Jochem

~|
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:211690
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

RE: retrieve ID from db insert

[RADEMAKERS Tanguy]

>-Original Message-
>Subject: retrieve ID from db insert
>From: Aaron Rouse <[EMAIL PROTECTED]>
>Date: Tue, 12 Jul 2005 14:40:27 -0500

[snip]

> I often wonder how many CFM developers that work with Oracle use and 
>understand all the features that all that money was spent on. And if
they do 
>not understand the features well enough, what could have been intended
to 
>make their life/jobs easier could quickly make it harder but that is
just 
>part of life and anything out there.

It's not restricted to CFM developers - if anything, web developers are
more likely to be comfortable with a greater number of different
technologies than their mainstream counterparts. A lot of them
high-falutin' object cowboys are downright afraid of databases. Then
again, if i wrote SQL like that i would be too ;> 

And it's not restricted to Oracle - there are doubtless plenty of people
out there using SQLServer as if it was a big excel spreadsheet.

~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211688
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

Re: retrieve ID from db insert

[Aaron Rouse]

I have had very good luck here:
 http://forums.oracle.com/forums/forum.jsp?forum=75
 My experience everywhere else, even here at work has been the holier than 
though DBAs you described. I even got that experience from one here at work 
that I helped with by sending some LDAP/Oracle integration I had worked on 
which I thought was rather surprising.

 On 7/12/05, Deanna Schneider <[EMAIL PROTECTED]> wrote: 
> 
> You've found good oracle forums? Care to share? All I've found are a
> lot of DBA's that don't want to answer code questions. I think that's
> one of the biggest problems with the oracle community - there's a lot
> of holier than thou DBA's out there.
> 
> I'm fortunate to have a pretty approachable DBA that actually helps
> answer our questions. But, I know there are reams of things I don' t
> know, particularly now that we're moving to 10g from 8i. I haven't
> even begun to learn what new goodies there are.
> 
> I've used some of those analytics- they are way cool, aren't they?
> 
>


~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211681
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

Re: retrieve ID from db insert

[Deanna Schneider]

You've found good oracle forums? Care to share? All I've found are a
lot of DBA's that don't want to answer code questions. I think that's
one of the biggest problems with the oracle community - there's a lot
of holier than thou DBA's out there.

I'm fortunate to have a pretty approachable DBA that actually helps
answer our questions. But, I know there are reams of things I don' t
know, particularly now that we're moving to 10g from 8i. I haven't
even begun to learn what new goodies there are.

I've used some of those analytics- they are way cool, aren't they?

On 7/12/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> Not sure what the coolest thing I have learned in it lately was, just been
> too busy with other things to remember much more than my name. I think maybe
> it was the use of pipelined functions to deal with some lists of data being
> sent in. Although I have written a few SPs in the past few months to help
> out with ref integrity checking/messaging that hopefully will help this
> group out here.
>  My only beef with Oracle has always been the general attitude of people
> when seeking help. Although their forums tend to have a good group of people
> in them. Maybe I just have been spoiled with out CFM people are.
> 
>  On 7/12/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote:
> >
> > ditto...heck we got a couple folks still using font tags...geesh.
> >
> > coolest thing I learned in Oracle in the past few months is the use of
> > aggregate functions as analytics via PARTION..OVER. Way kewl.
> >
> > DK
> >
> >
> 
> 
> 

~|
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:211680
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

Re: retrieve ID from db insert

[Aaron Rouse]

Not sure what the coolest thing I have learned in it lately was, just been 
too busy with other things to remember much more than my name. I think maybe 
it was the use of pipelined functions to deal with some lists of data being 
sent in. Although I have written a few SPs in the past few months to help 
out with ref integrity checking/messaging that hopefully will help this 
group out here.
 My only beef with Oracle has always been the general attitude of people 
when seeking help. Although their forums tend to have a good group of people 
in them. Maybe I just have been spoiled with out CFM people are.

 On 7/12/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote: 
> 
> ditto...heck we got a couple folks still using font tags...geesh.
> 
> coolest thing I learned in Oracle in the past few months is the use of
> aggregate functions as analytics via PARTION..OVER. Way kewl.
> 
> DK
> 
>


~|
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:211678
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

Re: retrieve ID from db insert

[Douglas Knudsen]

ditto...heck we got a couple folks still using font tags...geesh.

coolest thing I learned in Oracle in the past few months is the use of 
aggregate functions as analytics via PARTION..OVER. Way kewl. 

DK

On 7/12/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> 
> Guess it depends on your definition of "life easier" and ones 
> understanding
> of features, after all someone with a limited view would think using
> triggers to plug in sequence values is using "cool" oracle features.
> Only reason I state that is I can think of a lot of "developers" here at
> this company that if you were to try to get them to get the nextval of the
> sequence and then insert it you would get deer in the headlight looks.
> Actually not too sure any of then use the transaction tag either and bet 
> if
> any do they put no attributes on it.
> I often wonder how many CFM developers that work with Oracle use and
> understand all the features that all that money was spent on. And if they 
> do
> not understand the features well enough, what could have been intended to
> make their life/jobs easier could quickly make it harder but that is just
> part of life and anything out there.
> On 7/12/05, RADEMAKERS Tanguy <[EMAIL PROTECTED]> wrote:
> >
> >
> > My advice: oracle cost a lot of money, so get your money's worth: use
> > oracle specific features when they make your life easier. It's what
> > they're for.
> >
> >
> >
> 
> 
> 

~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211676
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

Re: retrieve ID from db insert

[Aaron Rouse]

Guess it depends on your definition of "life easier" and ones understanding 
of features, after all someone with a limited view would think using 
triggers to plug in sequence values is using "cool" oracle features. 
 Only reason I state that is I can think of a lot of "developers" here at 
this company that if you were to try to get them to get the nextval of the 
sequence and then insert it you would get deer in the headlight looks. 
Actually not too sure any of then use the transaction tag either and bet if 
any do they put no attributes on it.
 I often wonder how many CFM developers that work with Oracle use and 
understand all the features that all that money was spent on. And if they do 
not understand the features well enough, what could have been intended to 
make their life/jobs easier could quickly make it harder but that is just 
part of life and anything out there.
 On 7/12/05, RADEMAKERS Tanguy <[EMAIL PROTECTED]> wrote: 
> 
> 
> My advice: oracle cost a lot of money, so get your money's worth: use
> oracle specific features when they make your life easier. It's what
> they're for.
> 
> 
>


~|
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:211671
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

RE: retrieve ID from db insert

[RADEMAKERS Tanguy]

As i see it, the benefit of this approach is that it will be more
portable across different databases (as long as they understand
transactions). The down side is that if you want this to work you will
have to use  - "repeatable_read"
won't cut it if another transaction has finished in the mean time.
Unfortunately, some other products implement this level of isolation by
locking the whole table - which means that only one thread at a time
will be able to insert into this table. 

My advice: oracle cost a lot of money, so get your money's worth: use
oracle specific features when they make your life easier. It's what
they're for.


>-----Original Message-----
Subject: retrieve ID from db insert
>From: Aaron Rouse <[EMAIL PROTECTED]>
>Date: Tue, 12 Jul 2005 11:10:05 -0500
>Thread: 
>http://www.houseoffusion.com/cf_lists/index.cfm/method=messages
>&threadid=41103&forumid=4#211639
>
>We appear to just be going around in circles on this one. If 
>you have a 
>query that selects the sequence next value then you do not need a 
>CFTRANSACTION but you do have to remember to always select the 
>next value 
>since I'd assume this uses no trigger to handle it on any 
>other inserts? 
>What do you do for mass inserts from another table, guess just 
>a little 
>addition to the SQL and really no biggie on that one.
> Or you take the approach of never inserting anything manually 
>into the "ID" 
>column and just little a trigger/sequence handle it. That means using 
>CFTRANSACTION so that after you insert it you do the SELECT 
>MAX(ID) AS NEWID 
> Since there seems to be no adverse reason to using 
>CFTRANSACTION unless 
>in some of the already noted situations which seem more out of 
>the norm than 
>the norm, then this method just to me seems like an easier approach.

~|
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:211666
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

Re: retrieve ID from db insert

[Aaron Rouse]

We appear to just be going around in circles on this one. If you have a 
query that selects the sequence next value then you do not need a 
CFTRANSACTION but you do have to remember to always select the next value 
since I'd assume this uses no trigger to handle it on any other inserts? 
What do you do for mass inserts from another table, guess just a little 
addition to the SQL and really no biggie on that one.
 Or you take the approach of never inserting anything manually into the "ID" 
column and just little a trigger/sequence handle it. That means using 
CFTRANSACTION so that after you insert it you do the SELECT MAX(ID) AS NEWID 
 Since there seems to be no adverse reason to using CFTRANSACTION unless 
in some of the already noted situations which seem more out of the norm than 
the norm, then this method just to me seems like an easier approach.

 On 7/12/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote: 
> 
> The two query approach secures the id from the sequence though. No other
> thread, request, etc can get the same sequence value.
> 
> Now, if you were to manually build and control a numeric sequence ala 
> SELECT
> Max(id) + 1 as newid FROM tblname, then you would have to worry about this
> issue and use a transaction or lock orsomething. But with Oracle's 
> sequence,
> no need to worry.
> 
> DK
> 
> On 7/12/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> >
> > Yes, but isn't the point in doing the CFTRANSACTION in the way I am
> > describing to ensure that you get the ID of what you just inserted in
> > order
> > to pass it back to the website? More than likely to pass it back with a
> > URL
> > for telling the user hey this is where you go to view status or to edit
> > what
> > you just put in our database. I thought the point in doing the
> > CFTRANSACTION
> > for this was not so that you can do a rollback on the sequence(which we
> > all
> > agree can not happen anyway) but was to ensure when things are being
> > inserted by multiple people and around the same times that each person
> > gets
> > the ID that belongs to them.
> >
> > On 7/12/05, Douglas Knudsen
> >
> > ok. as I mentioned earlier in the this thread, a rollback does not 
> effect
> > a
> > sequence. So wrapping a transaction around these too queries is no
> > different
> > really then wrapping it around the single insert.
> >
> > DK
> >
> >
> >
> >
> >
> >
> 
> 

~|
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:211639
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

Re: retrieve ID from db insert

[Douglas Knudsen]

The two query approach secures the id from the sequence though. No other 
thread, request, etc can get the same sequence value. 

Now, if you were to manually build and control a numeric sequence ala SELECT 
Max(id) + 1 as newid FROM tblname, then you would have to worry about this 
issue and use a transaction or lock orsomething. But with Oracle's sequence, 
no need to worry. 

DK

On 7/12/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> 
> Yes, but isn't the point in doing the CFTRANSACTION in the way I am
> describing to ensure that you get the ID of what you just inserted in 
> order
> to pass it back to the website? More than likely to pass it back with a 
> URL
> for telling the user hey this is where you go to view status or to edit 
> what
> you just put in our database. I thought the point in doing the 
> CFTRANSACTION
> for this was not so that you can do a rollback on the sequence(which we 
> all
> agree can not happen anyway) but was to ensure when things are being
> inserted by multiple people and around the same times that each person 
> gets
> the ID that belongs to them.
> 
> On 7/12/05, Douglas Knudsen
> 
> ok. as I mentioned earlier in the this thread, a rollback does not effect 
> a
> sequence. So wrapping a transaction around these too queries is no 
> different
> really then wrapping it around the single insert.
> 
> DK
> 
> 
> 
> 
> 
> 

~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211637
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

Re: retrieve ID from db insert

[Aaron Rouse]

Yes, but isn't the point in doing the CFTRANSACTION in the way I am 
describing to ensure that you get the ID of what you just inserted in order 
to pass it back to the website? More than likely to pass it back with a URL 
for telling the user hey this is where you go to view status or to edit what 
you just put in our database. I thought the point in doing the CFTRANSACTION 
for this was not so that you can do a rollback on the sequence(which we all 
agree can not happen anyway) but was to ensure when things are being 
inserted by multiple people and around the same times that each person gets 
the ID that belongs to them.

On 7/12/05, Douglas Knudsen 

ok. as I mentioned earlier in the this thread, a rollback does not effect a
sequence. So wrapping a transaction around these too queries is no different
really then wrapping it around the single insert.

DK





~|
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:211630
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

Re: retrieve ID from db insert

[Douglas Knudsen]

On 7/11/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> 
> On 7/11/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote:
> >
> > On 7/11/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> > >
> > > How many times would it really matter if it did? I have seen sometimes
> > > where
> > > a "new app" would start off at say ID value of 143 just because of all
> > the
> > > testing and not resetting the sequence never seemed to be an issue
> > though.
> >
> >
> > I suppose if the seq value has some real meaning, yo umight need to fuss
> > around with a way to ensure things are 'in sequence' so to speak.
> > Typically
> > I could care less, the sequence is usually a PK and has no real meaning 
> to
> > the end user/gui. IIRC, there is no guarantee a numerical sequence in
> > Oracle
> > is always one apart. You could get 1,2,3,4,10,11,20. That is what I've
> > been
> > told at least.
> 
> 
> When I have noticed this happening it usually ended up the trigger was 
> setup
> with some caching. That is not to say it may or does happen in other
> situations. I too could really care less what the ID value is, not sure 
> how
> many people out there really would care since never ran into a customer 
> who
> did but I am sure there are many out there that do.
> 
> 
> > If your SQL has only one insert, why use a transaction? Vacuously its
> > considered a transaction I suppose. Further, if a single SQL fails in 
> CF,
> > a
> > commit is never issued for it. I have always understood transactions to 
> be
> > a
> > group of SQL statements that you want to execute in concert.
> 
> 
> I was referring to a single insert statement followed by a single select
> statement to grad the ID of that inserted record, not just one insert and
> nothing more.


ok. as I mentioned earlier in the this thread, a rollback does not effect a 
sequence. So wrapping a transaction around these too queries is no different 
really then wrapping it around the single insert.

DK



~|
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:211618
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

Re: retrieve ID from db insert

[Aaron Rouse]

On 7/11/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote:
> 
> On 7/11/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> >
> > How many times would it really matter if it did? I have seen sometimes
> > where
> > a "new app" would start off at say ID value of 143 just because of all 
> the
> > testing and not resetting the sequence never seemed to be an issue 
> though.
> 
> 
> I suppose if the seq value has some real meaning, yo umight need to fuss
> around with a way to ensure things are 'in sequence' so to speak. 
> Typically
> I could care less, the sequence is usually a PK and has no real meaning to
> the end user/gui. IIRC, there is no guarantee a numerical sequence in 
> Oracle
> is always one apart. You could get 1,2,3,4,10,11,20. That is what I've 
> been
> told at least.


When I have noticed this happening it usually ended up the trigger was setup 
with some caching. That is not to say it may or does happen in other 
situations. I too could really care less what the ID value is, not sure how 
many people out there really would care since never ran into a customer who 
did but I am sure there are many out there that do.


> If your SQL has only one insert, why use a transaction? Vacuously its
> considered a transaction I suppose. Further, if a single SQL fails in CF, 
> a
> commit is never issued for it. I have always understood transactions to be 
> a
> group of SQL statements that you want to execute in concert.


I was referring to a single insert statement followed by a single select 
statement to grad the ID of that inserted record, not just one insert and 
nothing more.


~|
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:211595
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

Re: retrieve ID from db insert

[Douglas Knudsen]

On 7/11/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> 
> How many times would it really matter if it did? I have seen sometimes 
> where
> a "new app" would start off at say ID value of 143 just because of all the
> testing and not resetting the sequence never seemed to be an issue though.


I suppose if the seq value has some real meaning, yo umight need to fuss 
around with a way to ensure things are 'in sequence' so to speak. Typically 
I could care less, the sequence is usually a PK and has no real meaning to 
the end user/gui. IIRC, there is no guarantee a numerical sequence in Oracle 
is always one apart. You could get 1,2,3,4,10,11,20. That is what I've been 
told at least.



Guess I just look at it differently, because the way I look at it is if the
> CFTRANSACTION is not going to hurt anything to be used then why the heck 
> not
> use it around even something so simple as the basic insert vs lots of
> inserts/updates. Seems like the only difference is one way saves you from
> typing the opening/closing transaction tags. But if less typing were the
> goal then I'd say the SP would lead to the least amount of typing, at 
> least
> within the CFM files.



If your SQL has only one insert, why use a transaction? Vacuously its 
considered a transaction I suppose. Further, if a single SQL fails in CF, a 
commit is never issued for it. I have always understood transactions to be a 
group of SQL statements that you want to execute in concert.


On 7/11/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote:
> >
> >
> > note a rollback does not effect sequences.
> >
> > DK
> >
> >
> 
> 
> 

~|
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:211594
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

Re: retrieve ID from db insert

[S . Isaac Dealey]

Well that's not the only reason to avoid cftransaction where it's not
needed... You can't nest cftransactions, so if for some (who knows)
reason you found yourself needing to cfinclude two separate templates
within a cftransaction (or different method calls or whatever) you'd
get an error if the included template (or method call) has its own
cftransaction. Most of the time it's not an issue, and usually when it
does it's not a large issue, but it's something to consider.

> How many times would it really matter if it did? I have
> seen sometimes where
> a "new app" would start off at say ID value of 143 just
> because of all the
> testing and not resetting the sequence never seemed to be
> an issue though.
>  Guess I just look at it differently, because the way I
>  look at it is if the
> CFTRANSACTION is not going to hurt anything to be used
> then why the heck not
> use it around even something so simple as the basic insert
> vs lots of
> inserts/updates. Seems like the only difference is one way
> saves you from
> typing the opening/closing transaction tags. But if less
> typing were the
> goal then I'd say the SP would lead to the least amount of
> typing, at least
> within the CFM files.

>  On 7/11/05, Douglas Knudsen <[EMAIL PROTECTED]>
>  wrote:
>>
>>
>> note a rollback does not effect sequences.


s. isaac dealey   954.522.6080
new epoch : isn't it time for a change?

add features without fixtures with
the onTap open source framework

http://www.fusiontap.com
http://coldfusion.sys-con.com/author/4806Dealey.htm




~|
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:211589
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

Re: retrieve ID from db insert

[Aaron Rouse]

How many times would it really matter if it did? I have seen sometimes where 
a "new app" would start off at say ID value of 143 just because of all the 
testing and not resetting the sequence never seemed to be an issue though.
 Guess I just look at it differently, because the way I look at it is if the 
CFTRANSACTION is not going to hurt anything to be used then why the heck not 
use it around even something so simple as the basic insert vs lots of 
inserts/updates. Seems like the only difference is one way saves you from 
typing the opening/closing transaction tags. But if less typing were the 
goal then I'd say the SP would lead to the least amount of typing, at least 
within the CFM files.

 On 7/11/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote: 
> 
> 
> note a rollback does not effect sequences.
> 
> DK
> 
>


~|
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:211584
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

Re: retrieve ID from db insert

[Douglas Knudsen]

i see no reason to use it in Daniels case. once you get the next value from 
a sequence in Oracle, no other request will get that id. 

Now, if Daniel needed to perform several inserts, updates, etc in one 
request based on this id, then yes, by all means use cftransaction. In his 
case he had only one.

note a rollback does not effect sequences.

DK

On 7/11/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> 
> Is there a reason to want to avoid using cftransaction?
> 
> On 7/11/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote:
> >
> > and you do not need cftansaction either. once query 1 executes, you have
> > the
> > id in hand. if another request came in it would get a differnt id. 
> simply
> > based on the uniqueness of the sequence.
> >
> > DK
> >
> >
> 
> 
> 

~|
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:211581
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

Re: retrieve ID from db insert

[Aaron Rouse]

Is there a reason to want to avoid using cftransaction?

On 7/11/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote: 
> 
> and you do not need cftansaction either. once query 1 executes, you have 
> the
> id in hand. if another request came in it would get a differnt id. simply
> based on the uniqueness of the sequence.
> 
> DK
> 
>


~|
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:211563
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

Re: retrieve ID from db insert

[Douglas Knudsen]

and you do not need cftansaction either. once query 1 executes, you have the 
id in hand. if another request came in it would get a differnt id. simply 
based on the uniqueness of the sequence.

DK

On 7/11/05, Greg Morphis <[EMAIL PROTECTED]> wrote:
> 
> Like Doug suggested, I've found the best way to do this is 2 queries..
> First
> 
> select game_id.nextval as id from dual
> 
> 
> Then store that value in a session variable or however you want to.
> Then you can use that to insert into different queries.
> 
> 
> 
> On 7/11/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> > I have yet to see a way of doing this in Oracle/CFQUERY that works in 
> all
> > situations. Actually I have yet to see it work, but have heard of it 
> working
> > which is why I put "situations"
> >
> > On 7/11/05, Russ <[EMAIL PROTECTED]> wrote:
> > >
> > > Why not just do something like this (sql server)
> > >
> > > Insert into table (cols) values (values)
> > > select @@IDENTITY as maxid
> > >
> > >
> >
> >
> >
> 
> 

~|
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:211561
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

RE: retrieve ID from db insert

[Russ]

How would the NOCOUNT setting affect the @@IDENTITY being returned?  Doesn't
seem like it from the sql docs:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
set-set_3ed0.asp

SET NOCOUNT
Stops the message indicating the number of rows affected by a Transact-SQL
statement from being returned as part of the results.

Syntax
SET NOCOUNT { ON | OFF } 

Remarks
When SET NOCOUNT is ON, the count (indicating the number of rows affected by
a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the
count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client
for each statement in a stored procedure. When using the utilities provided
with MicrosoftR SQL ServerT to execute queries, the results prevent "nn rows
affected" from being displayed at the end Transact-SQL statements such as
SELECT, INSERT, UPDATE, and DELETE.

For stored procedures that contain several statements that do not return
much actual data, this can provide a significant performance boost because
network traffic is greatly reduced.

The setting of SET NOCOUNT is set at execute or run time and not at parse
time.


-Original Message-
From: Mark A Kruger [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 11, 2005 1:22 PM
To: CF-Talk
Subject: RE: retrieve ID from db insert

Sometimes you need to fiddle with the "NOCOUNT" setting to make it work
reliably.  It depends on how the SQL server is configured.

SET NOCOUNT ON

   Insert into table (cols) values (values)
   select @@IDENTITY as maxid

SET NOCOUNT OFF

This is especially true if you are updating more than 1 statement inside the
SQL block.

-mk

-Original Message-
From: Aaron Rouse [mailto:[EMAIL PROTECTED]
Sent: Monday, July 11, 2005 12:07 PM
To: CF-Talk
Subject: Re: retrieve ID from db insert


I always just use triggers for when inserting, but it seemed like the worry
here was to know the ID after the insert to I'd guess display to the user.
How would a trigger help for that need?

On 7/11/05, Eddie Awad <[EMAIL PROTECTED]> wrote:
>
>
> Of course, you could also just create a "when-insert" trigger on the
> table to automatically populate the id column so you do not have to
> worry about it in your "insert" statements.
> --
> Eddie Awad.
> http://awads.net/
>
>






~|
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:211560
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

RE: retrieve ID from db insert

[Mark A Kruger]

Sometimes you need to fiddle with the "NOCOUNT" setting to make it work
reliably.  It depends on how the SQL server is configured.

SET NOCOUNT ON

   Insert into table (cols) values (values)
   select @@IDENTITY as maxid

SET NOCOUNT OFF

This is especially true if you are updating more than 1 statement inside the
SQL block.

-mk

-Original Message-
From: Aaron Rouse [mailto:[EMAIL PROTECTED]
Sent: Monday, July 11, 2005 12:07 PM
To: CF-Talk
Subject: Re: retrieve ID from db insert


I always just use triggers for when inserting, but it seemed like the worry
here was to know the ID after the insert to I'd guess display to the user.
How would a trigger help for that need?

On 7/11/05, Eddie Awad <[EMAIL PROTECTED]> wrote:
>
>
> Of course, you could also just create a "when-insert" trigger on the
> table to automatically populate the id column so you do not have to
> worry about it in your "insert" statements.
> --
> Eddie Awad.
> http://awads.net/
>
>




~|
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:211558
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

Re: retrieve ID from db insert

[Eddie Awad]

On 7/11/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> I always just use triggers for when inserting, but it seemed like the worry
> here was to know the ID after the insert to I'd guess display to the user.
> How would a trigger help for that need?

The trigger won't help for that need. I was just stating an idea
because that's how I would do it. And by the way, I forgot to put a
"commit" after the "insert" in the example procedure in my previous
e-mail.
-- 
Eddie Awad.
http://awads.net/

~|
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:211557
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

Re: retrieve ID from db insert

[Aaron Rouse]

I always just use triggers for when inserting, but it seemed like the worry 
here was to know the ID after the insert to I'd guess display to the user. 
How would a trigger help for that need?

On 7/11/05, Eddie Awad <[EMAIL PROTECTED]> wrote: 
> 
> 
> Of course, you could also just create a "when-insert" trigger on the
> table to automatically populate the id column so you do not have to
> worry about it in your "insert" statements.
> --
> Eddie Awad.
> http://awads.net/
> 
>


~|
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:211554
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

Re: retrieve ID from db insert

[Aaron Rouse]

That syntax is invalid for Oracle.
 As far as SQL Server, I believe it is fine but I have watched a couple of 
debates on it via this mailing list so as with all things best to just 
research over taking someones word. :)

 On 7/11/05, Russ <[EMAIL PROTECTED]> wrote: 
> 
> This does work properly in SQL Server, right? I mean we haven't seen any
> issues, but I'd hate to think that we have a bug in our code...
> 
> Does the same syntax work in oracle? What kind of situations could cause
> this to not work properly?
> 
> Russ
> 
>


~|
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:211553
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

Re: retrieve ID from db insert

[Greg Morphis]

Like Doug suggested, I've found the best way to do this is 2 queries.. 
First 

select game_id.nextval as id from dual


Then store that value in a session variable or however you want to.
Then you can use that to insert into different queries.



On 7/11/05, Aaron Rouse <[EMAIL PROTECTED]> wrote:
> I have yet to see a way of doing this in Oracle/CFQUERY that works in all
> situations. Actually I have yet to see it work, but have heard of it working
> which is why I put "situations"
> 
> On 7/11/05, Russ <[EMAIL PROTECTED]> wrote:
> >
> > Why not just do something like this (sql server)
> >
> > Insert into table (cols) values (values)
> > select @@IDENTITY as maxid
> >
> >
> 
> 
> 

~|
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:211551
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

Re: retrieve ID from db insert

[Eddie Awad]

> I don't know SPs yet, but that's an interesting idea.  I'll have to look into 
> that when I get time.

Well, it's simple. Here is a working example of how you would do it in
Oracle using a stored procedure:

create table t (id number, colname varchar2(50))
/
create sequence t_seq
start with  1
increment by  1
minvalue  1
maxvalue  
/
create or replace procedure insert_into_t 
   (colname_in in t.colname%type, 
   id_out out t.id%type)
as
begin
   insert into t 
  (id, colname) 
   values 
  (t_seq.nextval, colname_in)
   returning id into id_out;
end;
/

Then in ColdFusion:






#id_out#

Of course, you could also just create a "when-insert" trigger on the
table to automatically populate the id column so you do not have to
worry about it in your "insert" statements.
-- 
Eddie Awad.
http://awads.net/

~|
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:211550
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

RE: retrieve ID from db insert

[Russ]

This does work properly in SQL Server, right?  I mean we haven't seen any
issues, but I'd hate to think that we have a bug in our code...

Does the same syntax work in oracle? What kind of situations could cause
this to not work properly?

Russ 

-Original Message-
From: Aaron Rouse [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 11, 2005 11:45 AM
To: CF-Talk
Subject: Re: retrieve ID from db insert

I have yet to see a way of doing this in Oracle/CFQUERY that works in all 
situations. Actually I have yet to see it work, but have heard of it working

which is why I put "situations"

On 7/11/05, Russ <[EMAIL PROTECTED]> wrote: 
> 
> Why not just do something like this (sql server)
> 
> Insert into table (cols) values (values)
> select @@IDENTITY as maxid
> 
>




~|
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:211549
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

Re: retrieve ID from db insert

[Aaron Rouse]

I have yet to see a way of doing this in Oracle/CFQUERY that works in all 
situations. Actually I have yet to see it work, but have heard of it working 
which is why I put "situations"

On 7/11/05, Russ <[EMAIL PROTECTED]> wrote: 
> 
> Why not just do something like this (sql server)
> 
> Insert into table (cols) values (values)
> select @@IDENTITY as maxid
> 
>


~|
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:211542
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

RE: retrieve ID from db insert

[Russ]

Why not just do something like this (sql server)

Insert into table (cols) values (values)
select @@IDENTITY as maxid

-Original Message-
From: daniel kessler [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 11, 2005 11:32 AM
To: CF-Talk
Subject: Re: retrieve ID from db insert

I don't know SPs yet, but that's an interesting idea.  I'll have to look
into that when I get time.

>Or if you wanted to do it all in one "tag" you could just put it into a SP 
>and run it that way. There are of course various views as to the added 
>advantage of doing this, you can swearch through the list archives for all 
>of those. :)



~|
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:211541
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

Re: retrieve ID from db insert

[daniel kessler]

I don't know SPs yet, but that's an interesting idea.  I'll have to look into 
that when I get time.

>Or if you wanted to do it all in one "tag" you could just put it into a SP 
>and run it that way. There are of course various views as to the added 
>advantage of doing this, you can swearch through the list archives for all 
>of those. :)

~|
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:211539
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

Re: retrieve ID from db insert

[daniel kessler]

thanks everyone for your help!

>search the archives, this has been discussed several times here. In short, 
>do two queries. query 1, get the next val in your sequence. now you have 
>your id in hand. query 2, do your insert using the id. do whatever you want 
>with the id.

~|
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:211537
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

Re: retrieve ID from db insert

[Aaron Rouse]

Or if you wanted to do it all in one "tag" you could just put it into a SP 
and run it that way. There are of course various views as to the added 
advantage of doing this, you can swearch through the list archives for all 
of those. :)

On 7/11/05, daniel kessler <[EMAIL PROTECTED]> wrote: 
> 
> ok, this actually answers my question of how to assure that the 2nd query 
> actually returns the correct id and not allow another to be inserted until I 
> have it. That was my worry.
> 
> Thanks!
> 
> >In reference to the first email, you would do a CFTRANSACTION and not a
> >CFLOCK.
> 
>


~|
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:211536
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

Re: retrieve ID from db insert

[daniel kessler]

ok, this actually answers my question of how to assure that the 2nd query 
actually returns the correct id and not allow another to be inserted until I 
have it.  That was my worry.

Thanks!

>In reference to the first email, you would do a CFTRANSACTION and not a 
>CFLOCK.

~|
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:211535
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

Re: retrieve ID from db insert

[Aaron Rouse]

In reference to the first email, you would do a CFTRANSACTION and not a 
CFLOCK.

On 7/11/05, Douglas Knudsen <[EMAIL PROTECTED]> wrote: 
> 
> search the archives, this has been discussed several times here. In short,
> do two queries. query 1, get the next val in your sequence. now you have
> your id in hand. query 2, do your insert using the id. do whatever you 
> want
> with the id.
> 
> DK
> 
>


~|
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:211532
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

Re: retrieve ID from db insert

[Douglas Knudsen]

search the archives, this has been discussed several times here. In short, 
do two queries. query 1, get the next val in your sequence. now you have 
your id in hand. query 2, do your insert using the id. do whatever you want 
with the id.

DK

On 7/11/05, Daniel Kessler <[EMAIL PROTECTED]> wrote:
> 
> I create a new game and insert it into my Oracle database. From
> there I want to have the game ID and put it into a cookie. I've used
> NEXTVAL AND CURVAL for inserting the value into the db, but not for
> any external use, like my insert into a cookie. I guess I can do a
> CFLock and then do a query after the insert for the last inserted
> item, but is that the best way?
> 
> Here's the insert:
> 
> 
> INSERT INTO fsnep_food_store_game_info
> (
> gi_id,login_id,
> date_added,date_modified,
> grocery_list,budget,
> month,num_people,
> num_weeks
> )
> VALUES
> (
> unique_food_store_game_info_s.NEXTVAL,#cookie.fsnep_login#,
> SYSDATE,SYSDATE,
>  cfsqltype="cf_sql_varchar">, cfsqltype="cf_sql_varchar">,
>  cfsqltype="cf_sql_varchar">, value="#form.the_num_people#" cfsqltype="cf_sql_varchar">,
> 
> 
> )
> 
> 
> 
> 
> thanks for any help.
> 
> --
> Daniel Kessler
> 
> Department of Public and Community Health
> University of Maryland
> Suite 2387 Valley Drive
> College Park, MD 20742-2611
> 301-405-2545 Phone
> www.phi.umd.edu 
> 
> 

~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211530
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

retrieve ID from db insert

[Daniel Kessler]

I create a new game and insert it into my Oracle database.  From 
there I want to have the game ID and put it into a cookie. I've used 
NEXTVAL AND CURVAL for inserting the value into the db, but not for 
any external use, like my insert into a cookie.  I guess I can do a 
CFLock and then do a query after the insert for the last inserted 
item, but is that the best way?

Here's the insert:


  INSERT INTO fsnep_food_store_game_info
 (
 gi_id,login_id,
 date_added,date_modified,
 grocery_list,budget,
 month,num_people,
 num_weeks
 )
  VALUES
 (
 unique_food_store_game_info_s.NEXTVAL,#cookie.fsnep_login#,
 SYSDATE,SYSDATE,
 ,,
 ,,
 
 
 )




thanks for any help.

-- 
Daniel Kessler

Department of Public and Community Health
University of Maryland
Suite 2387 Valley Drive
College Park, MD  20742-2611
301-405-2545 Phone
www.phi.umd.edu

~|
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:211529
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