MySQL Stored Procedures with detail table and transactions

Question

I have a relatively large (for me) stored procedure that I'm working on that is responsible for maintaining inventory values whenever a "movement" occurs.

I originally had all the checks and inserts happening in PHP, but I felt that the number of things that need to happen in the database made it more controllable by putting it in a stored procedure and denying INSERT/UPDATE permissions to these tables.

So history aside, I'm curious if there is a way to run a prepared statement for the stored procedure in which I can pass multiple values that will be insert into a detail table.

For example, let's say I want to insert the following data into a parent and children records.

"ACME", "TNT", 100
"ACME", "Anvil", 5

The parent record would contain ACME, and the children records would have the details of TNT, 100 and Anvil, 5.

Is it possible to somehow pass this data into a single stored procedure?

The reason I'm hoping to do this in one pass, is let's say we don't have enough TNT available, I don't want the original parent record to be inserted unless the entire transaction can take place.

why not just begin/commit transaction via php if you already have the code that works? — Bulat, Sep 25 '14 at 00:09
I want to make sure that calls from different applications perform certain checks to maintain data integrity. I can easily throw an error and rollback a transaction if a customer flag/requirement isn't met by the data provided to the stored procedure. Something that would be dependent on whoever implemented it in PHP/Java/etc.. — Kyle Johnson, Sep 25 '14 at 18:31
Well, if you want to build on top of reusable code, I though it is common to use service layer for that rather then building your application inside the database. Then you can pass it in JSON or XML no problems. — Bulat, Sep 25 '14 at 21:09
That is very true. I'd still outsource at least part of it to stored procedures in order to remove the direct access to the important tables, but I could do the checks and errors in the service layer. — Kyle Johnson, Sep 25 '14 at 21:26

score 1 · Accepted Answer · edited May 23 '17 at 10:33

Would concatenating the data sent to the stored procedure work? I don't imagine sending more than 1000 detail lines at a time to the database.

do_movement('ACME','2:TNT,100||Anvil,5'); The 2 in front denotes the number of details to expect, might help with error catching. Just a thought.

This way the code would be responsible for formatting, but I could output SQLEXCEPTIONS and have everything happen in one true transaction.

Drawing heavily from this post: MySQL Split Comma Separated String Into Temp Table

Using the SPLIT_STR function from (also referenced in previous post): http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

And then my functionality to parse N:s1|s2|..|sN and check.

I could easily call SPLIT_STR again if I had something like N:s1,a1|s2,a2|..|sN,aN

BEGIN
    DECLARE defined_quantity INT;
    IF serial_numbers REGEXP '^[^0]{0,}[[:digit:]]+:.*$' = TRUE THEN -- check that we are indeed at least starting properly formatted.
        SET defined_quantity = CONVERT(LEFT(serial_numbers,LOCATE(':',serial_numbers)-1), UNSIGNED);
        IF defined_quantity <= 0 THEN
            SIGNAL SQLSTATE '45006'
                SET MESSAGE_TEXT = 'The quantity defined with the serial number list is <= 0.';
        END IF;
        SET serial_numbers = RIGHT(serial_numbers,LENGTH(serial_numbers) - LOCATE(':',@serial_numbers));
        BEGIN
            DECLARE a INT Default 0 ;
            DECLARE str VARCHAR(255);
            DECLARE q INT;
            simple_loop: LOOP
                SET a=a+1;
                SET str=TRIM(SPLIT_STR(serial_numbers,"|",a));
                IF str='' THEN
                    SET a=a-1; -- we ignore the last increment
                    LEAVE simple_loop;
                END IF;
                #Do Inserts into temp table here with str going into the row
                INSERT INTO transaction_detail (transaction_id, serial_number,created_at,updated_at) VALUES(transaction_id, str,NOW(),NOW());
            END LOOP simple_loop;
            SELECT a, defined_quantity, quantity;
            IF a <> defined_quantity OR a <> quantity OR defined_quantity <> quantity THEN
                SIGNAL SQLSTATE '45007'
                    SET MESSAGE_TEXT = 'The quantities do not match for the serial numbers provided.';
            END IF;
        END;
    ELSE
        SIGNAL SQLSTATE '45005'
            SET MESSAGE_TEXT = 'The serial number formatted list is not properly formatted.  Please provide in "n:s1|s2|s3|...|sn" format.';
    END IF;
END;

MySQL Stored Procedures with detail table and transactions

1 Answers1