MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesMySQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 4th, 2013, 05:58 PM
brouse brouse is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Location: Alexandria, VA
Posts: 184 brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Day 10 h 39 m 40 sec
Reputation Power: 13
Auto_inc field and stored procedure

I have a stored procedure which adds a record to a table with an auto_inc field if the passed in ID is 0 (and updates it if it is > 0). The insert works correctly; the auto_inc field populates correctly (automatically).

The problem is that I would like to return the ID value (generated in the auto_inc field). I've tried several ways, including LAST_INSERT_ID(), but it does not get the value. Once the stored procedure completes, I can get the value, but I cannot while execution is still within the stored procedure.

I assume this is some latency or data commit problem, perhaps a setting or other command/code I need to do? Or is this working "as designed", and I need to shell out to a sub stored procedure to make this work the way I want?

Thanks...

Reply With Quote
  #2  
Old January 4th, 2013, 06:42 PM
r937's Avatar
r937 r937 is online now
SQL Consultant
Click here for more information.
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,355 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m
Reputation Power: 4140
no idea

perhaps you could show the code where you tried to use LAST_INSERT_ID in the procedure?
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Reply With Quote
  #3  
Old January 5th, 2013, 06:52 AM
brouse brouse is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Location: Alexandria, VA
Posts: 184 brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Day 10 h 39 m 40 sec
Reputation Power: 13
Code:
CREATE PROCEDURE `sp_SaveComment`(IN nCommentID int, IN nUserID INT, IN dCreateDate datetime, IN sContent text)
BEGIN
if nCommentID = 0 then
    Insert into comments(UserID, CommentDate, Content) Values(nUserID, dCreateDate, sContent);

    /* NOTE: CommentID is an AUTO_INC field. Following line does not get the new CommentID value
    Select nCommentID = LAST_INSERT_ID(); /*CommentID from comments order by CommentID Desc Limit 1;*/

    insert into KeywordProcess(CommentID, Action) Values (nCommentID, 'A');
else
    Update comments set Content = sContent Where CommentID = nCommentID;
    insert into KeywordProcess(CommentID, Action) Values (nCommentID, 'U');
end if;

select nCommentID as 'CommentID';

END

Reply With Quote
  #4  
Old January 5th, 2013, 01:34 PM
r937's Avatar
r937 r937 is online now
SQL Consultant
Click here for more information.
 
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,355 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m
Reputation Power: 4140
it's been a long time since i worked with stored procs, but i think the following is wrong --
Code:
Select nCommentID = LAST_INSERT_ID(); 

try this instead --
Code:
Select LAST_INSERT_ID() INTO nCommentID 

Reply With Quote
  #5  
Old January 5th, 2013, 02:51 PM
brouse brouse is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Location: Alexandria, VA
Posts: 184 brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Day 10 h 39 m 40 sec
Reputation Power: 13
That was it! - thanks!

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMySQL Help > Auto_inc field and stored procedure

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap