Open fagirton opened 1 month ago
Figured out the method with two queries doesn't works because of queries going asynchronously where SELECT @rep;
gets done before CALL calcUserRep(1, @rep);
But it still doesn't explain why it doesn't accept both of them in one query string
Can you create minimal reproduction in a cloneable git repository?
Just made one here https://github.com/fagirton/nest-mysql-procedures-multiline-bug
The problem still happens there. I ran CALL getString('test', @test); SELECT @test;
and it returned a 500 with an error inside nest: ERROR [ExceptionsHandler] 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 @test' at line 1
I checked the repository, you have to add the SQL otherwise it gives error, in the this point here: https://github.com/fagirton/nest-mysql-procedures-multiline-bug/blob/main/src/app.service.ts#L11
for example:
async runQuery(dto: dtoQuery) {
const data = await this.connection.query('SELECT * FROM table_name', [dto.query]);
return JSON.stringify(Object.assign([{}], data));
}
Here you can find an example tutorial if you find it useful: https://dev.to/tony133/mysql-module-for-nestjs-8x-framework-5gh9.
My package is a wrapper of the mysql2
package, I created it to understand how dynamic modules work with Nest.
You can also just use the mysql2 package if it's easier for you 🙂
I used a simple calcUserRep procedure with user_id In parameter and reputation Out parameter.
The code in service.ts is also very straightforward, it takes string from dto and runs it as query:
However, a single query with CALL and SELECT operators throws error in MySQL: The query:
CALL calcUserRep(1, @rep); SELECT @rep;
The error:Error: 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 @rep' at line 1
Inside MySQL Workbench under the same user the query works perfectly.I also tried running two separate
this.connection.query()
methods, but seems like MySQL doesn't remember variables between the two.Is this made by design or this is a bug? And how I can workaround this?
My software: nestjs-mysql 0.0.14, nest 10.3.8