[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure MySql

Posted on 2007-10-08
2
Medium Priority
?
795 Views
Last Modified: 2013-11-25
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
Comment
Question by:jmokrauer
2 Comments
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 750 total points
ID: 20038250
Where can I find a list of the stored procedures?
mysql> SHOW PROCEDURE STATUS\G
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 750 total points
ID: 20038612
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month18 days, 18 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question