• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

Stored Procedure MySql

I have a table called 'capital' in mysql.  I am subtracting 10 from a field called 'pendingCapitalAvailable'.  To accomplish this, I am considering using a called procedure that I will call from Matlab.  I need to make sure the table is locked until I finish the procedure.

Below is the code I wrote to create the procedure, it does not work and tells me to look at the manual to figure it out.  Can you tell me what is wrong with it?

create procedure lockedUpdateOfCapital(IN capitalUsed INT)

BEGIN$

START TRANSACTION;
UPDATE capital SET pendingCapitalAvailable = pendingCapitalAvailable - capitalUsed;
UPDATE capital SET marginAvailable = marginAvailable - capitalUsed;
commit

END$

I assume to execute it I would type 'call lockedUpdateOfCapital(102)' in the query browser.  Where can I find a list of the stored procedures?

Server info:
MySQL 5.0.45-community-nt via TCP/IP
MySQL Client Version 5.1.11
InnoDB tables but I could use MYISAM tables if necessary
0
jmokrauer
Asked:
jmokrauer
2 Solutions
 
SQL_SERVER_DBACommented:
Where can I find a list of the stored procedures?
mysql> SHOW PROCEDURE STATUS\G
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just a side node:
your procedure code does not seem to use any WHERE clause on the table???


now, how do you try to compile the procedure? I ask, because I don't see the DELIMITER statement ...

next, you could update the table in 1 update statement (assuming that either you have indeed no where clause, or the same where clause...)
that would also avoid that you needed an explicit transaction.

note that, you missed a ; after the commit...


 DELIMITER $

create procedure lockedUpdateOfCapital(IN capitalUsed INT)

BEGIN$

START TRANSACTION;
UPDATE `capital` SET pendingCapitalAvailable = pendingCapitalAvailable - capitalUsed
                 , marginAvailable = marginAvailable - capitalUsed;
commit;

END;
$

 delimiter ;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now