The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MySQL Help
|
Auto_inc field and stored procedure
Discuss Auto_inc field and stored procedure in the MySQL Help forum on Dev Shed. Auto_inc field and stored procedure MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
|
|
 |
|
|
|
|

Dev Shed Forums Sponsor:
|
|
|

January 4th, 2013, 05:58 PM
|
|
Contributing User
|
|
Join Date: Jul 2004
Location: Alexandria, VA
Posts: 184
  
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...
|

January 4th, 2013, 06:42 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
|
no idea
perhaps you could show the code where you tried to use LAST_INSERT_ID in the procedure?
|

January 5th, 2013, 06:52 AM
|
|
Contributing User
|
|
Join Date: Jul 2004
Location: Alexandria, VA
Posts: 184
  
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
|

January 5th, 2013, 01:34 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
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
|

January 5th, 2013, 02:51 PM
|
|
Contributing User
|
|
Join Date: Jul 2004
Location: Alexandria, VA
Posts: 184
  
Time spent in forums: 1 Day 10 h 39 m 40 sec
Reputation Power: 13
|
|
|
That was it! - thanks!
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|