Trying to create a transaction in phpmyadmin

Question

Trying to create a transaction in phpmyadmin using the routine panel. I want to do an insert and an update:

START TRANSACTION;

INSERT INTO inventoryitems (item, quantity, userid)
VALUES(item, quantity, userid);

UPDATE users
SET cash = cash - (quantity * unitbuyprice);

COMMIT;

You can see the create/edit routine panel in the screen shot below:

Below is the error I get:

The following query has failed: "CREATE DEFINER=root@localhost PROCEDURE InsertInventoryItem(IN item VARCHAR(255), IN quantity INT, IN userid INT, IN unitbuyprice INT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER START TRANSACTION; INSERT INTO inventoryitems (item, quantity, userid) VALUES(item, quantity, userid); UPDATE users SET cash = cash - (quantity * unitbuyprice); COMMIT;" MySQL said: #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 'INSERT INTO inventoryitems (item, quantity, userid) VALUES(item, quantity, user' at line 3

If I remove the Start Transaction, Commit and either the insert or update then the procedure is fine. IE just a single statement works fine but multiple statements always gives an error.

What am I missing when I want to include multiple statements in a procedure. I have tried with and without the semi colon delimiter.

This stuff just works with MS SQL. I have created Procedures with hundreds of statements inside before.

Cheers for the Help in advance.

spencer7593 spencer7593 · Accepted Answer · 2017-05-20T14:56:28

I suggest you add BEGIN and END.

Also note:

A local variable should not have the same name as a table column. If an SQL statement ... contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.

Reference: https://dev.mysql.com/doc/refman/5.7/en/local-variable-scope.html

If we implement control of transaction within the context of a stored program, we should probably also handle an error condition, and issue the rollback within the stored program. (Personally, I adhere to the school of thought that believes we should handle transaction context outside of the stored procedure.)

The procedure definition would look something like this:

DELIMITER $$

CREATE DEFINER=root@localhost PROCEDURE InsertInventoryItem(
  IN as_item         VARCHAR(255),
  IN ai_quantity     INT,
  IN ai_userid       INT,
  IN ai_unitbuyprice INT
) 
BEGIN

  -- handle error conditions by issuing a ROLLBACK and exiting
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    EXIT PROCEDURE;
  END;

  START TRANSACTION ;

  INSERT INTO inventoryitems (item, quantity, userid)
  VALUES (as_item, ai_quantity, ai_userid) ;

  UPDATE users u
  SET u.cash = u.cash - (ai_quantity * ai_unitbuyprice)
  WHERE u.userid = ai_userid ;

  COMMIT ;

END$$

DELIMITER ;

--

Note that the update will assign a NULL to cash if either ai_quantity or ai_unitbuyprice is NULL. And we probably want a WHERE clause to limit the rows that will be updated. (Without the WHERE clause, the UPDATE statement will update all rows in the table.)

That's what the statements would look like if I wanted to create the procedure from a normal client, such as the mysql command line, or SQLyog.

MySQL syntax is significantly different than Transact-SQL (Microsoft SQL Server). We just have to deal with that.

As far as "this stuff just works with MS SQL", in all fairness, we should be careful to not conflate MySQL itself with the trouble prone idiot-syncracies of the phpMyAdmin client.

Trying to create a transaction in phpmyadmin

1 Answers