NextJS Mysql INSERT works with commands through phpmyadmin but not with query from api [duplicate]

Question

Like the title says, my sql code works in phpmyadmin but not with requiest from api. My api uses mysql package:

npm i mysql

SQL Code:

BEGIN;
INSERT INTO Addresses (address, postal_code, city, state, country) 
    VALUES('Something', 69420, 'Something', 'Something', 'Something');
SELECT LAST_INSERT_ID() INTO @mysql_address_id;
INSERT INTO Companies (owner, org_nr, name)
    VALUES('someid', 133769420, 'Something');
SELECT LAST_INSERT_ID() INTO @mysql_company_id;
INSERT INTO Users (company, member, administrator)
    VALUES(@mysql_company_id, 'someid', 1);
INSERT INTO CompanyDetails (shippingAddress, company, shortName, ourReference)
    VALUES(@mysql_address_id, @mysql_company_id, 'Something', 'Something');
COMMIT;

And this works totaly fine in phpmyadmin.

I call the function using:

db.query(
          `BEGIN; INSERT INTO Addresses (address, postal_code, city, state, country) VALUES('Something', 69420, 'Something', 'Something', 'Something'); SELECT LAST_INSERT_ID() INTO @mysql_address_id; INSERT INTO Companies (owner, org_nr, name) VALUES('Something', 133769420, 'Something'); SELECT LAST_INSERT_ID() INTO @mysql_company_id; INSERT INTO Users (company, member, administrator) VALUES(@mysql_company_id, 'someid', 1); INSERT INTO CompanyDetails (shippingAddress, company, shortName, ourReference) VALUES(@mysql_address_id, @mysql_company_id, 'Something', 'Something'); COMMIT;`,
          (err, result) => {
            if (err) return res.status(422).json(err);
            return res.status(200).json(result);
          }
        );

The error that is shown is:

{"code":"ER_PARSE_ERROR","errno":1064,"sqlMessage":"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 Addresses (address, postal_code, city, state, country) VALUES('Ve...' at line 1","sqlState":"42000","index":0,"sql":"BEGIN; INSERT INTO Addresses (address, postal_code, city, state, country) VALUES('Something', 69420, 'Something', 'Something', 'Something'); SELECT LAST_INSERT_ID() INTO @mysql_address_id; INSERT INTO Companies (owner, org_nr, name) VALUES('someid', 133769420, 'Something'); SELECT LAST_INSERT_ID() INTO @mysql_company_id; INSERT INTO Users (company, member, administrator) VALUES(@mysql_company_id, 'someid', 1); INSERT INTO CompanyDetails (shippingAddress, company, shortName, ourReference) VALUES(@mysql_address_id, @mysql_company_id, 'Something', 'Something'); COMMIT;"}

Do anyone know what this might be caused by? Any help is appriciated :)

Victor Svensson · Accepted Answer · 2022-04-01 10:35:25Z

1

I found the solution here: https://stackoverflow.com/a/23267627/13214923 I turns out that node-mysql don't support multiple statements by default.

by adding

{multipleStatements: true,}

To my pool config it worked like a charm :)

answered Apr 1, 2022 at 10:35

Victor Svensson

1066 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

NextJS Mysql INSERT works with commands through phpmyadmin but not with query from api [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related