Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: Transaction within a procedure not working

    I want to use a proc to update a user table. The code I am using will not rollback when it fails to match on the IN name.

    If the transaction fails, I want to rollback and send out an integer value of 0. Here is my code so far. It isn't working and all I get back thought the OUT variable is integer 1 even when it fails.

    Code:
    declare exit handler for not found rollback;
    declare exit handler for sqlwarning rollback;
    declare exit handler for sqlexception rollback;
    set row = 0;
    
    START TRANSACTION;
    UPDATE user
    SET name = name
    WHERE code = code;
    COMMIT;
    
    set row = 1;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SET name = name WHERE code = code

    just what are you trying to do here? it sure looks like you're setting the value of the name column equal to the value it already has, for every row where the value of the code column is equal to itself (which is basically all rows where code is not NULL)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Code:
    UPDATE user
    SET name = 'Frunkie'
    WHERE code = '123456';
    You see a problem with the syntax here? Where inCode = '12345', set the inName equal to 'Frunkie'? This is a stored procedure so the inCode and inName are variables. I think I see what you are saying though because the name and code on both sides of the equal sign are the same. I've changed that but it still won't work. I still only get (int) 1 back out of the proc even when the inCode isn't found in the table.

    What I want here are two things. I need the affected rows returned OUT and also need the trans to rollback if it fails for any reason. Thanks for the help Rudy.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why do i get the feeling once again that i am not looking at the actual code, but a dumbed down version?

    how am i supposed to diagnose an error like "I've changed that but it still won't work."?

    what does "I need the affected rows returned OUT" mean?

    come on, Frank, you know the drill --
    your black cards can make you money
    so you hide them when you're able
    in the land of milk and honey
    you must put them on the table...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    lol.. I love that song!

    The truth is that I have changed this so many times that I don't know which way is up. It really IS the whole code and not a watered down version.

    Here is the current version though..
    Code:
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
      DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
      DECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK;
      SET row = '0';
    
      START TRANSACTION;
        UPDATE user
        SET name = inName
        WHERE code = inCode;
      COMMIT;
    
      SET row = '1';
    END

    I have another proc that inserts and uses the exact same syntax and it works great. This update won't though and I can't seem to find the problem.

    I ran just the straight DDL in a command window with a bad 'inCode' expecting to get an error but nothing happens. What I mean by this is that when I press enter in the command utility window, the DDL is submitted and I don't get an error of any kind. If I do a row_count I get back -1 which means the query failed.

    I believe that when there is no match for the inCode that there will be nothing to rollback. This I understand but I need to have either a zero or one returned back out.

    What I mean when I say that I need the affected rows returned out is that if a row has been successfully altered, I would like to have my OUT parameter tell me this by either a zero or a one. One for success and zero for fail.

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Alrighty. I think I've figured out what is happening. The syntax I have seems to be correct.

    I opened another console window and started a separate transaction, locking the tables. Then in my application, I try and modify the user data and get back a zero when it times out, which is correct.

    I kill the transaction in the console window and again, attempt to update the user with a bad code that I know won't match anything from my application and I get back a one which is incorrect.

    I believe that I need to rewrite this procedure somehow to allow for rows that are NOT matched and then return that failure code (zero) back out of the procedure. The rollback is working great.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm glad you're no longer stuck

    sorry if i sounded a bit abrupt

    carry on

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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