Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Transaction wont Rollback in SP

    Hi,

    I have the following stored procedure:

    Code:
    CREATE PROCEDURE jobseeker_add(IN inTitle VARCHAR(50), 
                                   IN inFname VARCHAR(50),
                                   IN inLname VARCHAR(50),
    			       IN inJobTitle VARCHAR(100),
    			       IN inAddLine1 VARCHAR(100),
    			       IN inAddLine2 VARCHAR(100), 
    			       IN inCity VARCHAR(50),
    			       IN inState VARCHAR(50),
    			       IN inZip VARCHAR(50),
    			       IN inCountryId INT,
    			       IN inPhone VARCHAR(50),
    			       IN inMobile VARCHAR(50),
    			       IN inEmail VARCHAR(100),
    			       IN inDob DATE,
    			       IN inCvProfile TEXT)
    
    BEGIN
    
    DECLARE jobseekerId INT;
    
    START TRANSACTION;
        
    		# Insert a new record into jobseeker and obtain the new jobseeker ID
    		INSERT INTO tbl_jobseekers (js_title, js_fname, js_lname, js_job_title, js_addressline1, js_addressline2,js_city, js_state, js_zipcode, js_country_id, js_phone, js_mobile, js_email, js_dob) 
    		VALUES (inTitle, inFname, inLname, inJobTitle, inAddLine1, inAddLine2 ,inCity ,inState, inZip, inCountryId,inPhone, inMobile, inEmail, inDob);
    		
    		# Obtain the new jobseeker ID
    		SELECT LAST_INSERT_ID() INTO jobseekerId;
    
                    # insert jobseeker cv
    		INSERT INTO tbl_jobseeker_cv (jobseeker_id, cv) VALUES (jobseekerId, inCvProfile );
    		
    		
    		#return jobseeker id
    		SELECT jobseekerId;
    		
    COMMIT;
    END$$
    I want the transaction to rollback if any insert fails. Unfortunatley this doest seem to be the case. A jobseeker record is still created even if the jobseeker cv insert fails? Any idea why this is happening and how do you make it so that it rolls back with a message I can display within my PHP code?

    Any help would be appreciated.

  2. #2
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Hey Ozzii, I don't have the answer but came to post the exact same question. I'm also having an issue so I will be watching this thread. Good luck.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by ozzii View Post
    I want the transaction to rollback if any insert fails. Unfortunatley this doest seem to be the case.
    Are you are sure that tbl_jobseekers is using a storage engine that supports transactions?

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Hey Shammat, I'm looking at his code and I'm wondering if the LAST_INSERT_ID needs to be assigned to a variable first. What say ye?

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by shammat View Post
    Are you are sure that tbl_jobseekers is using a storage engine that supports transactions?
    Yes the table uses innoDB.

    To Frunkie - I found the following thread I posted a while back with a useful reply from a kind user. However am having some unsual behaviour with it lately - it was working a while back but not any more.

    Am using a slighlty modified version of the stored procedure which uses a select statment as the last statement. so if the transaction was successfull i should have a 1 returned back but it doesnt seem to return the last select statment in my php code. See below

    Code:
    Listing 1. STORED PROCEDURE:
    
    create procedure sp_answer(IN name VARCHAR(20))
    begin
    declare exit handler for not found rollback;
    declare exit handler for sqlwarning rollback;
    declare exit handler for sqlexception rollback;
    
    
    start transaction
    
    -- insert into ...
    -- update ...
    -- any other DDL operation
    
    -- select 1
    commit;
    
    end
    the original post is here: http://www.dbforums.com/mysql/164091...procedure.html

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    ozzii,

    I've been at this for the better part of the day now and I am noticing that that in order to use procedures with PHP that you will have to use mysqli instead of mysql. While this may not be directly related to your immediate problem, you will certainly be in for a rough ride. When I find a workable solution, I'll post back. Also, if you have mastered this mysqli approach, I'd love to see a snippet of code.

    Thanks.. Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •