max length sql statement

Question

i have a node js app where i connect to mysql database via express.

Now i am trying to lock a table, calling a function and unlock these tables.

when i print the string everything works fine but when i call it in connection.query(sqlStatement) the string somehow gets sliced?

here is the node js function:

exports.participateTournament = function(req, res){
    const {pid, tid} = req.body;
    let sqlStatement = `lock Tables Tournament_Participant WRITE, Tournament_Approved READ; 
                        select participate_Tournament('${pid}', '${tid}') as 'result'; 
                        unlock tables;`;

    console.log(sqlStatement);

    connection.query(sqlStatement, function(error, rows){
        if(error){
            console.log(error.message);
            return res.status(400).send();
        } else {
            return res.status(200).send(rows[0].result);
        }
    })
};

when i print out the string this is the output

lock Tables Tournament_Participant WRITE, Tournament_Approved READ; 

select participate_Tournament('0780b926a41bd17877894771841e6179', 'a9f0e61a137d86aa9db53465e0801612') as 'result'; 

unlock tables;

but i get this error message from mysql:

ER_PARSE_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 participate_Tournament('0780
b926a41bd17877894771841e6179', 'a9f0e61a137d8' at line 2

Here is the participate_Tournament Function: I need to check if the max limit of participants of a tournament is reached

create function participate_Tournament(
    pidP varchar(64),
    tidP varchar(64)
)
returns smallint
BEGIN
    declare par_amount int;
    declare max_amount int;

    select count(TID) into par_amount from Tournament_Participant where TID = tidP;
    select max_participants into max_amount from Tournament_Approved where TID = tidP;

    if(par_amount < max_amount) then
        insert into Tournament_Participant(TID,PID) values(tidP, pidP);
        return 1; #true
    end if;

    return 0;
end;
```

Thanks in Advance.

why are you locking the table? Its normally either not needed, you aren't using transactions, or something in the function participate_Tournament isn't right. The error is because you can't do multiple statements the same SQL string. — danblack, Feb 27 '20 at 21:50
because more than one person can call this request at once and i have to check if the max limit is reached. What would you consider to do? — A. Crispino, Feb 27 '20 at 21:57
SQL is pretty good at ensuring that separate requests don't interfere with each other. Can you include details of `participate_Tournament` by editing your question? What happens when this request happens a second time? What do you want to happen? I don't know what max limit your are referring to. Can you explain that further. — danblack, Feb 27 '20 at 22:12
That's good. Hopeing someone has seen pattern before otherwise I'll attempt this later. — danblack, Feb 27 '20 at 23:03
You can't execute multiple SQL statements in one call by default. See https://stackoverflow.com/questions/23266854/node-mysql-multiple-statements-in-one-query But there's no reason to execute multiple SQL statements in one call! Just execute them one at a time. — Bill Karwin, Feb 28 '20 at 00:29

score 1 · Answer 1 · answered Feb 28 '20 at 01:03

I have solved it like this now, does work for me

    const {pid, tid} = req.body;
    let tmpResult;

    connection.beginTransaction(function(err){
        if(err){
            res.status(400).send();
            return;
        }
        sqlStatement = `lock Tables Tournament_Participant WRITE, Tournament_Approved READ;`;
        connection.query(sqlStatement, function(err, rows){
            if(err){
                console.log(err);
                res.status(400).send();
            }else{
                sqlStatement = `select participate_Tournament('${pid}', '${tid}') as 'result';`;

                connection.query(sqlStatement, function(err, rows){
                    if(err){
                        console.log(err);
                        res.status(400).send();
                    }else{
                        tmpResult = rows[0].result;

                        sqlStatement = `unlock tables;`;
                        connection.query(sqlStatement, function(err, rows){
                            if(err){
                                console.log(err);
                                res.status(400).send();
                            }else{
                                connection.commit(function(err){
                                    if(err){
                                        connection.rollback(function(){
                                            res.status(400).send();
                                        });
                                    }else{
                                        res.status(200).send(tmpResult.toString());
                                    }
                                })
                            }
                        })
                    }
                })
            }
        })
    })
};

max length sql statement

1 Answers1