Tag Archives: procedure
MySQL Stored Procedure for CRUD Dynamic Queries
The new MySQL 5.7 version has some great features. One of them is the ‘JSON data’ type. We can use it for column of table like ‘Virtual Colunm’ with Indexing. But, ‘JSON data’ is also useful for stored procedure. Because we can compact parameters and the procedure could be more flexible.I made the procedures for table insert/update via JSON parameter. When I apply them to my project, data-handling codes in the application decreased dramatically. (These procedures impliment ‘MySQL Procedure Pattern for Nested Transaction‘) The code below is an old version. The ‥‥‥
MySQL Procedure Pattern for Nested Transaction
The MySQL dose not supports ‘Nested Transaction’. Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms. (https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html) But I want to use ‘Stored Procedure’ what work well with a transaction independently and with nested calling. This pattern isn’t correct nested transactions, but enables the use of nested procedures. The basic idea is to use the @@ AUTOCOMMIT environment variable. The procedure will determine whether the transaction is started from the ‥‥‥
