first ooops: > RETURNING ID > INTO newid;
should be > RETURNING GI_ID > INTO newid; (obviously) /t >-----Original Message----- >From: RADEMAKERS Tanguy >Sent: Monday, July 11, 2005 5:19 PM >To: 'cf-talk@houseoffusion.com' >Subject: RE: CF-Talk: Digest every hour > >Hello, > >1) Do two queries in a CFTRANSACTION (not CFLOCK) - one to >insert and one to get the value of the new ID. > >2) Write a stored procedure that returns the value of the >inserted id (see the SQL guide for the syntax of RETURNING INTO clause) > >start with this: > >CREATE OR REPLACE PROCEDURE insert_game ( > fsnep_login IN VARCHAR2, > g_list IN VARCHAR2, > budget IN VARCHAR2, > the_month IN VARCHAR2, > the_num_people IN VARCHAR2, > the_weeks IN VARCHAR2, > newid OUT INT >) >AS >BEGIN > 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, >fsnep_login, SYSDATE, > SYSDATE, g_list, budget, the_month, the_num_people, > the_weeks > ) > RETURNING ID > INTO newid; >EXCEPTION > WHEN OTHERS > THEN > ROLLBACK; > RAISE; >END; >/ > >call it like this: ><cfstoredproc procedure="insert_game" >datasource="[your_datasource_name]"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" >dbvarname="fsnep_login" value="#cookie.fsnep_login#" null="No"> > <cfprocparam type="In" cfsqltype="CF_SQL_NUMERIC" >dbvarname="g_list" value="#form.g_list#" null="No"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" >dbvarname="budget" value="#form.budget#" null="No"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" >dbvarname="the_month" value="#form.the_month#" null="No"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" >dbvarname="the_num_people" value="#form.the_num_people#" null="No"> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" >dbvarname="the_weeks" value="#form.the_weeks#" null="No"> > <cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" >dbvarname="newid" variable="newid"> ></cfstoredproc> > >and then do: > ><cfcookie name="fsnep_bargain_hunt_gi_id" value="#newid#"> > > >/t > >ps: none of the above is tested. > >>-----Original Message----- >>Subject: retrieve ID from db insert >>From: Daniel Kessler <[EMAIL PROTECTED]> >>Date: Mon, 11 Jul 2005 10:47:21 -0400 >>Thread: >>http://www.houseoffusion.com/cf_lists/index.cfm/method=messages >>&threadid=41103&forumid=4#211529 >> >>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: >> >><CFQUERY NAME="add_item" DATASOURCE="esmart"> >> 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, >> <cfqueryparam value="#form.g_list#" >>cfsqltype="cf_sql_varchar">,<cfqueryparam value="#form.budget#" >>cfsqltype="cf_sql_varchar">, >> <cfqueryparam value="#form.the_month#" >>cfsqltype="cf_sql_varchar">,<cfqueryparam >>value="#form.the_num_people#" cfsqltype="cf_sql_varchar">, >> <cfqueryparam value="#form.the_weeks#" >>cfsqltype="cf_sql_varchar"> >> <!--- <cfcookie name="fsnep_bargain_hunt_gi_id" >>value="#unique_food_store_game_info_s.CURVAL#"> ---> >> ) >> >></CFQUERY> >> >> >>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:211538 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
