Multiple statements in one prepared statement

[notthelewis]

Using a prepared statement, the .execute call, with multiple statements is giving me a syntax error:

async function examplePreparedMultiStmt() {
    let cnx;
    try {
        cnx = await mysql.createConnection({multipleStatements: true, user: '', host: '', port: '', password: '', database: ''});
        let [ res, fields ] = await cnx.execute(`SELECT 1 as ?; SELECT 2 as ?`, ['one', 'two'], (e, res, fields )=> {
            if (e) { throw e; } 
            return [ res, fields ];
        });
    
        return [ res, fields ];
    
    } catch (e) {
        console.log(e);
        throw e;
    }
}

Specifically:

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'SELECT 1 as one; SELECT 2 as two',
  sqlState: '42000',
  sqlMessage: "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 'SELECT 2 as two' at line 1"

But the same code, using the .query call instead of the .execute call, works perfectly fine:

async function examplePreparedMultiQuery() {
    let cnx;
    try {
        cnx = await mysql.createConnection({multipleStatements: true, user: '', host: '', port: '', password: '', database: ''});
        let [ res, fields ] = cnx.query('SELECT 1 AS ?; SELECT 2 AS ?', ['one', 'two'], (e, res, fields)=> {
            if (e) { throw e; } 
            return [ res, fields ];
        });

        return [ res, fields ];
    } catch(e) {
        console.log(e);
        throw e;
    }
}

Please could someone let me know if this is just me being stupid or if this is a genuine issue?

Is there a fundamental difference between the .execute and the .query calls to a connection if the query call can still be 'prepared' by passing in parameters? I was using the .execute as this is the example provided by the docs on both NPM and Github when regarding prepared statements.

[sidorares]

.execute sends full query as PREPARE command first (on the first call), if that fails it fails.
You can test prepare from cli - see docs at https://dev.mysql.com/doc/refman/8.0/en/prepare.html

I never tried multiple statements with prepared statements, not sure if that possible at all. What's the reason you have to use multiple statements? Is it just convenience and and existing being converted to prepared statements or something else?

[notthelewis]

Thanks for getting back to me so quickly.

I was using prepared statements for their security & efficiency benefits, and I am using multiple statements to prevent data race conditions and cut down on DB trips.

The thinking was that combining the two would've brought the benefits from both approaches... But that might just be my inexperience talking. Looks like it might be a better idea to just scrap the execute calls for the cases where I'm going to execute multiple statements and try a different approach, as it sounds like that's not a feasible method.

[sidorares]

you could try packaging related code as a stored procedure. You can then call that stored procedure in a single .execute() query and pass all the input parameters in the single call

[notthelewis]

Stored procedures are something I did consider, but the decoupling of the database and backend code (in this instance) just adds an extra layer of complexity for something relatively trivial. Is it possible (or feasible) to create stored procedures via the node-mysql2 API?

Basically what I was originally doing to solve this part of the problem was a batch operation inside a transaction, so I'd check some conditions in a table and dependent on the whether:

• dateEnd IS NULL
• foreignKey1 = ?
• foreignKey2 != ?

An update then a create, or just a create are performed. Originally I had like 3/4 execute() database calls in a promise chain for something relatively trivial and it raised some concerns surrounding scaling.

To remediate, the plan was to send one query containing all of the operations, so that it's done in 1 trip rather than the original 3/4, and I suppose my lack of understanding as to the fundamental difference between query() and execute() calls was to blame here.

[sidorares]

There is an example of using stored procedures in unit tests, and it looks like I'm using them to solve exactly the issue you are having -

node-mysql2/test/integration/connection/test-binary-multiple-results.js

Line 123 in e76d196

// prepared statements do not support multiple statements itself, we need to wrap quey in a stored procedure

( unit test for multiple result set, which in "binary protocol" only possible as result of stored procedure call )

[notthelewis]

Excellent, I've got this as a bookmark and I will consider this when I come to refactor this code post-deadline.

Thank you so much for your time, patience and quick response.

[lewismoten]

I am the lewis... However, I just wanted to express that I had been fighting this issue as well and traced it to a simple query failing an execute for select 1;select 2 with multipleStatements: true. I tried increasing maxPreparedStatements and queueLimit without any effect. I eventually found this issue. Thank you @sidorares, as your answer immediately fixed my issue. I'm using large batch scripts to update a database, where running 30,000 queries individually was painfully slow. These batch scripts only work with query for me, and fail for execute.