StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTrying to learn MySQL and transactions
text
Body
copied!<p>Over the last couple of days I have tried to write an Stored procedure in MySQL and I have some truble getting it to work. Hope someone here can give me some input :)</p> <p>The example I post is for asp.Net Membership provider to create a new user. I expect to send email and password to the DB and get an int return to verify that the userdeatils was written to the DB.</p> <p>I use a MySQL DB 5.1 (I think) and write the SQL to a webinterface.</p> <p>I got 2 sidequestions, can someone explain that too :): 1) I use a DELIMITER, but do not know what it does. 2) I am not sure if I have to do other things then to set autocommit = 0 to get transactions to work, or if I even have to do that. </p> <p>I know that I could have used a IF / ELSE statement instead of a transaction, but would like to do it with one to find out how it works. (I expect to use it alot later)</p> <p>The code I can not get to work:</p> <pre><code>DELIMITER // CREATE DEFINER=`websharp_dk`@`%` PROCEDURE `CreateUser`( IN _username VARCHAR(100), IN _Password VARCHAR(100)) RETURNS INT BEGIN SET autocommit = 0; DECLARE return_value INT; BEGIN TRY START TRANSACTION INSERT INTO User (Email ,Password ,Failed_Password_Count ,Creation_Date) VALUES (_username ,_Password ,0 ,Datetime.Now()) SET return_value = 1; COMMIT; END TRY BEGIN CATCH ROLLBACK SET return_value = 0; END CATCH BEGIN FINALLY RETURN return_value; END FINALLY END// DELIMITER ; </code></pre> <p>Edit: The error message I get is:</p> <h1>1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT BEGIN SET autocommit = 0; DECLARE return_value INT; ' at line 4</h1>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload