Transaction within a procedure not working

Frunkie · #1 (**permalink**) 02-05-10, 11:48

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;

r937 · #2 (**permalink**) 02-05-10, 12:14

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)

Frunkie · #3 (**permalink**) 02-05-10, 12:35

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.

r937 · #4 (**permalink**) 02-05-10, 12:47

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...

Frunkie · #5 (**permalink**) 02-05-10, 13:14

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.

Frunkie · #6 (**permalink**) 02-05-10, 13:24

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.

r937 · #7 (**permalink**) 02-05-10, 16:06

i'm glad you're no longer stuck

sorry if i sounded a bit abrupt

carry on

Internet Services

Web Development

Digital Marketing

Consumer Tech