RE: new next primary key value in Oracle
Hi, everyone. I have been doing something for years that works, but feels darned silly. I have a table called WIDGETS. I have a sequence set to increment by 1 up to some ridiculous number called WIDGETS_PKSEQ. When I add a new row to WIDGETS, I put WIDGETS_PKSEQ.NEXTVAL into WIDGETS.pkWIDG, the primary key for the table. Often, immediately after creating a new row in WIDGETS, I want to create a row in another table that references that new row in WIDGETS: for example, ORDERS.fkORD_pkWIDG refers to WIDGETS.pkWIDG. But to do that, I need to get that new WIDGETS.pkWIDG value, which has never actually appeared on the ColdFusion side of the transaction; it's all in Oracle. So, I do this ridiculous thing where I generate a random (and safely unique value), write it to some row in WIDGETS on the new row creation, look it up to get the new pk, then overwrite that row with the appropriate value it should have had in the first place, and then continue, now that ColdFusion has the new pk value known. There's got to be a better way! I know I am missing something easy. Any thoughts? Matthieu When I have run into this, having to put a incremental key value into several tables, what I have done is select the WIDGETS_PKSEQ.NEXTVAL and then use that value in all my inserts. Somewhat like this off the top of my head example. cfquery name=nextKeyVal ... SELECT WIDGETS_PKSEQ.NEXTVAL AS KEY FROM dual /*I believe this is the proper 'table'*/ /cfquery Then I can refer to nextKeyVal.Key in all my inserts. I wrap this all up in a cftransaction block so that if a problem occurs I hopefully don't end up with 1/2 the data saved. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| 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:223057 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: new next primary key value in Oracle
You could just run the insert on one table, then select out that new ID then
use it for the other inserts. Something like:
cftransaction ...
cfquery ...
INSERT INTO BLAH (COLA) VALUES ('YES')/cfquery
cfquery ...
SELECT MAX(ID) AS NEWID FROM BLAH /cfquery
cfquery ...
INSERT INTO BLAH2 (COLA, COLB) VALUES ('NO', #QueryName.NewID#)
/cftransaction
With that I assume a trigger is done to get the new value out of the
sequence and it puts it into Blah.ID during the insert.
On 11/3/05, Ian Skinner [EMAIL PROTECTED] wrote:
Hi, everyone. I have been doing something for years that works, but feels
darned silly. I have a table called WIDGETS. I have a sequence set to
increment by 1 up to some ridiculous number called WIDGETS_PKSEQ. When I add
a new row to WIDGETS, I put WIDGETS_PKSEQ.NEXTVAL into WIDGETS.pkWIDG, the
primary key for the table. Often, immediately after creating a new row in
WIDGETS, I want to create a row in another table that references that new
row in WIDGETS: for example, ORDERS.fkORD_pkWIDG refers to WIDGETS.pkWIDG.
But to do that, I need to get that new WIDGETS.pkWIDG value, which has
never actually appeared on the ColdFusion side of the transaction; it's all
in Oracle. So, I do this ridiculous thing where I generate a random (and
safely unique value), write it to some row in WIDGETS on the new row
creation, look it up to get the new pk, then overwrite that row with the
appropriate value it should have had in the first place, and then continue,
now that ColdFusion has the new pk value known. There's got to be a better
way! I know I am missing something easy.
Any thoughts?
Matthieu
When I have run into this, having to put a incremental key value into
several tables, what I have done is select the WIDGETS_PKSEQ.NEXTVAL and
then use that value in all my inserts. Somewhat like this off the top of my
head example.
cfquery name=nextKeyVal ...
SELECT
WIDGETS_PKSEQ.NEXTVAL AS KEY
FROM dual /*I believe this is the proper 'table'*/
/cfquery
Then I can refer to nextKeyVal.Key in all my inserts. I wrap this all up
in a cftransaction block so that if a problem occurs I hopefully don't end
up with 1/2 the data saved.
--
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org http://www.BloodSource.org
Sacramento, CA
C code. C code run. Run code run. Please!
- Cynthia Dunning
Confidentiality Notice: This message including any
attachments is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or
distribution is prohibited. If you are not the
intended recipient, please contact the sender and
delete any copies of this message.
~|
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:223058
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: new next primary key value in Oracle
DUAL?!?!!? That works, and is magic, but, again: ?!?!? I'll have to research this. I am sure there is a good reason for it being called DUAL. If not, and if this is truly arbitrary, then I am disappointed that the folks at Oracle didn't come up with some more colorful name, like SPAGHETTIFLASHLIGHT or ARNOLD. Thanks! This works wonderfully. Matthieu Ian Skinner wrote: When I have run into this, having to put a incremental key value into several tables, what I have done is select the WIDGETS_PKSEQ.NEXTVAL and then use that value in all my inserts. Somewhat like this off the top of my head example. cfquery name=nextKeyVal ... SELECT WIDGETS_PKSEQ.NEXTVAL AS KEY FROM dual /*I believe this is the proper 'table'*/ /cfquery Then I can refer to nextKeyVal.Key in all my inserts. I wrap this all up in a cftransaction block so that if a problem occurs I hopefully don't end up with 1/2 the data saved. ~| 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:223059 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: new next primary key value in Oracle
One idea, if it's possible - pass all of this information at once into a stored procedure, which would know the newly created row id know what number to use. - Matt Small -Original Message- From: Cornillon, Matthieu (Consultant) [mailto:[EMAIL PROTECTED] Sent: Thursday, November 03, 2005 1:52 PM To: CF-Talk Subject: OT: new next primary key value in Oracle Hi, everyone. I have been doing something for years that works, but feels darned silly. I have a table called WIDGETS. I have a sequence set to increment by 1 up to some ridiculous number called WIDGETS_PKSEQ. When I add a new row to WIDGETS, I put WIDGETS_PKSEQ.NEXTVAL into WIDGETS.pkWIDG, the primary key for the table. Often, immediately after creating a new row in WIDGETS, I want to create a row in another table that references that new row in WIDGETS: for example, ORDERS.fkORD_pkWIDG refers to WIDGETS.pkWIDG. But to do that, I need to get that new WIDGETS.pkWIDG value, which has never actually appeared on the ColdFusion side of the transaction; it's all in Oracle. So, I do this ridiculous thing where I generate a random (and safely unique value), write it to some row in WIDGETS on the new row creation, look it up to get the new pk, then overwrite that row with the appropriate value it should have had in the first place, and then continue, now that ColdFusion has the new pk value known. There's got to be a better way! I know I am missing something easy. Any thoughts? Matthieu ~| 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:223061 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: new next primary key value in Oracle
Aaron,
This is another good solution.
Thanks,
Matthieu
-Original Message-
From: Aaron Rouse [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 03, 2005 2:08 PM
To: CF-Talk
Subject: Re: new next primary key value in Oracle
You could just run the insert on one table, then select out that new ID
then use it for the other inserts. Something like: cftransaction ...
cfquery ... INSERT INTO BLAH (COLA) VALUES ('YES')/cfquery cfquery
SELECT MAX(ID) AS NEWID FROM BLAH /cfquery cfquery ... INSERT
INTO BLAH2 (COLA, COLB) VALUES ('NO', #QueryName.NewID#)
/cftransaction With that I assume a trigger is done to get the new
value out of the sequence and it puts it into Blah.ID during the insert.
On 11/3/05, Ian Skinner [EMAIL PROTECTED] wrote:
Hi, everyone. I have been doing something for years that works, but
feels darned silly. I have a table called WIDGETS. I have a sequence
set to increment by 1 up to some ridiculous number called
WIDGETS_PKSEQ. When I add a new row to WIDGETS, I put
WIDGETS_PKSEQ.NEXTVAL into WIDGETS.pkWIDG, the primary key for the
table. Often, immediately after creating a new row in WIDGETS, I want
to create a row in another table that references that new row in
WIDGETS: for example, ORDERS.fkORD_pkWIDG refers to WIDGETS.pkWIDG.
But to do that, I need to get that new WIDGETS.pkWIDG value, which has
never actually appeared on the ColdFusion side of the transaction;
it's all in Oracle. So, I do this ridiculous thing where I generate a
random (and safely unique value), write it to some row in WIDGETS on
the new row creation, look it up to get the new pk, then overwrite
that row with the appropriate value it should have had in the first
place, and then continue, now that ColdFusion has the new pk value
known. There's got to be a better way! I know I am missing something
easy.
Any thoughts?
Matthieu
When I have run into this, having to put a incremental key value into
several tables, what I have done is select the WIDGETS_PKSEQ.NEXTVAL
and then use that value in all my inserts. Somewhat like this off the
top of my head example.
cfquery name=nextKeyVal ...
SELECT
WIDGETS_PKSEQ.NEXTVAL AS KEY
FROM dual /*I believe this is the proper 'table'*/
/cfquery
Then I can refer to nextKeyVal.Key in all my inserts. I wrap this all
up in a cftransaction block so that if a problem occurs I hopefully
don't end up with 1/2 the data saved.
--
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org http://www.BloodSource.org
Sacramento, CA
C code. C code run. Run code run. Please!
- Cynthia Dunning
Confidentiality Notice: This message including any attachments is for
the sole use of the intended
recipient(s) and may contain confidential and privileged information.
Any unauthorized review, use, disclosure or distribution is
prohibited. If you are not the intended recipient, please contact the
sender and delete any copies of this message.
~|
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:223063
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: new next primary key value in Oracle
You're new to dual? Dual is just a wonderful little widget that acts like
a one row table, but the column is arbitrary. You can do all sorts of
stuff with it:
select sysdate from dual;
select 1 + 1 from dual;
select 'I am not a crook' from dual;
select ucase('malkjdflajldjflajdlfj') from dual;
You get the idea.
On 11/3/05, Cornillon, Matthieu (Consultant) [EMAIL PROTECTED]
wrote:
DUAL?!?!!? That works, and is magic, but, again: ?!?!? I'll have to
research this. I am sure there is a good reason for it being called
DUAL. If not, and if this is truly arbitrary, then I am disappointed
that the folks at Oracle didn't come up with some more colorful name,
like SPAGHETTIFLASHLIGHT or ARNOLD.
Thanks! This works wonderfully.
Matthieu
Ian Skinner wrote:
When I have run into this, having to put a incremental key value into
several tables, what I have done is select the WIDGETS_PKSEQ.NEXTVAL and
then use that value in all my inserts. Somewhat like this off the top
of my head example.
cfquery name=nextKeyVal ...
SELECT
WIDGETS_PKSEQ.NEXTVAL AS KEY
FROM dual /*I believe this is the proper 'table'*/
/cfquery
Then I can refer to nextKeyVal.Key in all my inserts. I wrap this all
up in a cftransaction block so that if a problem occurs I hopefully
don't end up with 1/2 the data saved.
~|
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:223065
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: new next primary key value in Oracle
Yet another good answer. Thanks, gang, for sending these in. At least one will do the trick. Matthieu Matt Small wrote: One idea, if it's possible - pass all of this information at once into a stored procedure, which would know the newly created row id know what number to use. ~| 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:223067 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: new next primary key value in Oracle
It was more of a quick solution, I'd probably do the SP route and not like that would be a long and dirty solution or anything. On 11/3/05, Cornillon, Matthieu (Consultant) [EMAIL PROTECTED] wrote: Aaron, This is another good solution. Thanks, Matthieu ~| 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:223068 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: new next primary key value in Oracle
I LOVE DUAL!
Thanks! I'll be doing more research on this.
Matthieu
Deanna Schneider wrote:
You're new to dual? Dual is just a wonderful little widget that acts
like a one row table, but the column is arbitrary. You can do all
sorts of stuff with it:
select sysdate from dual;
select 1 + 1 from dual;
select 'I am not a crook' from dual;
select ucase('malkjdflajldjflajdlfj') from dual;
You get the idea.
~|
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:223074
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: new next primary key value in Oracle
What about:
INSERT INTO Widgets()
RETURNING key AS newID
I've never tried to use it within a cfquery, but it works fine in
storedProcs. (Which, btw, I recommend you use over inline queries)
-Adam
On 11/3/05, Cornillon, Matthieu (Consultant)
[EMAIL PROTECTED] wrote:
I LOVE DUAL!
Thanks! I'll be doing more research on this.
Matthieu
Deanna Schneider wrote:
You're new to dual? Dual is just a wonderful little widget that acts
like a one row table, but the column is arbitrary. You can do all
sorts of stuff with it:
select sysdate from dual;
select 1 + 1 from dual;
select 'I am not a crook' from dual;
select ucase('malkjdflajldjflajdlfj') from dual;
You get the idea.
~|
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:223076
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: new next primary key value in Oracle
That has never worked for me within a cfquery. I have seen some on here say it worked for them so guessing it is some sort of driver issue between versions. On 11/3/05, Adrocknaphobia [EMAIL PROTECTED] wrote: What about: INSERT INTO Widgets() RETURNING key AS newID I've never tried to use it within a cfquery, but it works fine in storedProcs. (Which, btw, I recommend you use over inline queries) -Adam ~| 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:223081 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: new next primary key value in Oracle
Use DUAL. Step 1 Select WIDGETS_PKSEQ.NEXTVAL from dual Step 2 Insert into WIDGETS using WIDGETS_PKSEQ.NEXTVAL Step 3 Insert into child table of WIDGETS using value from step 1. -Original Message- From: Cornillon, Matthieu (Consultant) [mailto:[EMAIL PROTECTED] Sent: Thursday, November 03, 2005 10:52 AM To: CF-Talk Subject: OT: new next primary key value in Oracle Hi, everyone. I have been doing something for years that works, but feels darned silly. I have a table called WIDGETS. I have a sequence set to increment by 1 up to some ridiculous number called WIDGETS_PKSEQ. When I add a new row to WIDGETS, I put WIDGETS_PKSEQ.NEXTVAL into WIDGETS.pkWIDG, the primary key for the table. Often, immediately after creating a new row in WIDGETS, I want to create a row in another table that references that new row in WIDGETS: for example, ORDERS.fkORD_pkWIDG refers to WIDGETS.pkWIDG. But to do that, I need to get that new WIDGETS.pkWIDG value, which has never actually appeared on the ColdFusion side of the transaction; it's all in Oracle. So, I do this ridiculous thing where I generate a random (and safely unique value), write it to some row in WIDGETS on the new row creation, look it up to get the new pk, then overwrite that row with the appropriate value it should have had in the first place, and then continue, now that ColdFusion has the new pk value known. There's got to be a better way! I know I am missing something easy. Any thoughts? Matthieu ~| 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:223116 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
