[EnforcerDon]

Error when sending multiple queries

Problem - I am using BlueG's mysql plugin, and I want to send two queries to my sql server, as below.

pawn Код:

// here I am sending two queries to my sql server
// UPDATE playerdata SET Name='this';
// UPDATE vehicledata SET Owner='that';
mysql_query(1, "UPDATE playerdata SET Name='this'; UPDATE vehicledata SET Owner='that'", false);

Sending the above query will give me this error [below] in the mysql log.

Код:

[20:00:14] [ERROR] CMySQLQuery::Execute - (error #1064) 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 'UPDATE vehicledata SET Owner='that';' at line 1

Sending the queries separately [as below] will work no problem.

pawn Код:

mysql_query(1, "UPDATE vehicledata SET Owner='that';", false);
mysql_query(1, "UPDATE playerdata SET Name='this';", false);

Obviously, the second method sends two queries to the server separately, and would thus be slower than sending them both at once, especially if the function were to be called over 2000 times consecutively (and in my case I am going to be doing exactly that).

I also cannot find [in the plugin documentation] that the plugin cannot handle multiple queries. Could someone please tell me if they have the same problem and there is a bug in the plugin, if I am doing something wrong, or if there is a solution to my problem that would speed up my server (like I said, the query has to be made about 2000 times).

Thanks in advance for any help.

Note - My actual server code is much more complicated, I have only shown here the theory of what I am doing for the sake of simplicity.

[Boooth]

In terms of MySQL, I don't believe it is possible to update multiple tables within a single statement unfortunately.

[Vince]

The fact that's even necessary indicates that the database model isn't optimized. From the looks of it, it looks like you're trying to update a player's name in every table it appears in. However, a player's name should appear in the entire database just ONCE. All other features that need the player's name should refer to that name by its uniquely assigned id.

Also if you intend to execute a large sum of queries use transactions.

PHP код:

START TRANSACTION;
-- queries;
COMMIT;