overextended/oxmysql

Not possible way for custom transactions

Closed this issue · 2 comments

kratess commented

Issue checklist

  • I am using the latest release
  • I have referenced previously reported issues
  • I have referenced available documentation and cannot resolve the issue
  • I'm certain this is an issue with oxymysql, and not with my resource

Describe the bug
local response = MySQL.query.await([[
START TRANSACTION;
INSERT INTO users (identifier) VALUES ("identifier");
SELECT * FROM users WHERE ID = LAST_INSERT_ID();
COMMIT;
]], {})

Will go into error.

Database details

  • MariaDB
  • You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO users (identifier) VALUES ("identifier");
  • It works with phpmyadmin
thelindat commented

Won't work because multi-statements are disabled to prevent easy SQL injection.

Use a stored procedure if it's that important to perform a single transaction, or just perform a second query using the result of the first.

thelindat commented

With multi-statements enabled:
image

Somewhat annoying results.

Returning a response from MySQL.transaction will just return the resultset for the COMMIT query, so I'd instead have to get each result and return those. Potentially breaking change and doesn't really match expected behaviour.

image

Best solution is a stored procedure (as expected).

MySQL.transaction.await({
    'DROP PROCEDURE IF EXISTS `create_user`',
    [[
    CREATE PROCEDURE `create_user`(in identifier varchar(50))
    BEGIN
        INSERT INTO `users` (`license2`) VALUES (identifier);
        SELECT * FROM `users` WHERE `userid` = LAST_INSERT_ID();
    END
    ]]
})

local response = MySQL.query.await('CALL create_user("identifier")')

print(json.encode(response, {indent=true,sort_keys=true}))

image

With the way results are handled, using MySQL.scalar will actually return the selected row here.

image