[shavit]

Here's my code:

char[] sQuery = new char[1024];
FormatEx(sQuery, 1024,
	"DELIMITER ;; " ...
	"DROP PROCEDURE IF EXISTS UpdateAllPoints;; " ...
	"CREATE PROCEDURE UpdateAllPoints() " ...
	"BEGIN " ...
		"DECLARE authid CHAR(32); " ...
		"DECLARE done INT DEFAULT 0; " ...
		"DECLARE cur CURSOR FOR (SELECT auth FROM %splayertimes WHERE points > 0.0 GROUP BY auth); " ...
		"DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; " ...
		"OPEN cur; " ...
		"ranks: LOOP " ...
			"FETCH cur INTO authid; " ...
			"IF done THEN " ...
				"LEAVE ranks; " ...
			"END IF; " ...
			"UPDATE %susers SET points = (SELECT SUM((points * (@f := 0.975 * @f) / 0.975)) FROM %splayertimes " ...
				"JOIN (SELECT @f := 1.0) params WHERE points > 0.0 AND auth = authid ORDER BY points DESC) " ...
				"WHERE auth = authid; " ...
		"END LOOP; " ...
		"CLOSE cur; " ...
	"END;;" ...
	"\nDELIMITER ;", gS_MySQLPrefix, gS_MySQLPrefix, gS_MySQLPrefix);

#if defined DEBUG
LogError("%s", sQuery);
#endif

if(!SQL_FastQuery(gH_SQL, sQuery))
{
	char[] sError = new char[255];
	SQL_GetError(gH_SQL, sError, 255);
	LogError("Timer (rankings, create procedure) error! Reason: %s", sError);
}

This procedure on its own works fine when executed via any MySQL client (mysql CLI or even a GUI like HeidiSQL or phpMyAdmin) but it doesn't seem to execute through a SourceMod plugin.

This is the error log - the first "error" is the query itself, so we can see it's valid, and the error right after it is the actual MySQL error:

L 10/29/2017 - 18:52:55: [shavit-rankings.smx] DELIMITER ;; DROP PROCEDURE IF EXISTS UpdateAllPoints;; CREATE PROCEDURE UpdateAllPoints() BEGIN DECLARE authid CHAR(32); DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR (SELECT auth FROM playertimes WHERE points > 0.0 GROUP BY auth); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; ranks: LOOP FETCH cur INTO authid; IF done THEN LEAVE ranks; END IF; UPDATE users SET points = (SELECT SUM((points * (@f := 0.975 * @f) / 0.975)) FROM playertimes JOIN (SELECT @f := 1.0) params WHERE points > 0.0 AND auth = authid ORDER BY points DESC) WHERE auth = authid; END LOOP; CLOSE cur; END;; 
DELIMITER ;
L 10/29/2017 - 18:52:55: [shavit-rankings.smx] Timer (rankings, create procedure) error! Reason: 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 'DELIMITER ;; DROP PROCEDURE IF EXISTS UpdateAllPoints;; CREATE PROCEDURE UpdateA' at line 1

So after some debugging, it seems like there's a limitation and you can't execute more than one query in the same string.
Is there any workaround against this; or will I have to use multiple queries?

Thanks.

[8guawong]

use transaction

https://sm.alliedmods.net/new-api/dbi/Transaction

[shavit]

Originally Posted by 8guawong use transaction https://sm.alliedmods.net/new-api/dbi/Transaction

Transactions are threaded, I'm unable to use threaded queries for my use case.

[Neuro Toxin]

Generally you need to set a procedure owner when you declare. Otherwise users are unable to drop it.

[shavit]

Originally Posted by Neuro Toxin Generally you need to set a procedure owner when you declare. Otherwise users are unable to drop it.

Are you sure? My plugin seems to drop them just fine and the user it's executed by isn't root; it just has all privileges to the database.



Upon testing, it worked on both MySQL and MariaDB.

[ESK0]

Originally Posted by shavit Are you sure? My plugin seems to drop them just fine and the user it's executed by isn't root; it just has all privileges to the database. Upon testing, it worked on both MySQL and MariaDB.

Maybe increase your array size?

[shavit]

Originally Posted by ESK0 Maybe increase your array size?

That's not the issue, I get the same issue even with a 64 characters long query if it runs multiple queries at once.

[Miu]

You can't execute multiple statements in a single query. Transactions are explicitly the solution to this. You should either create your stored procedures by a different process or use transactions.

Also, I don't think you're unable to use threaded queries. If the main thread must truly not proceed before the stored procedures have been created, you could block it until the transactions complete. However, it's more likely that you just haven't designed the rest of the code to account for threaded queries in this area -- e.g., you have code that assumes the stored procedures already exist.

[asherkin]

SourceMod intentionally disables driver support for delimited queries.

Originally Posted by shavit Transactions are threaded, I'm unable to use threaded queries for my use case.

I suggest working out how to, non-threaded SQL is on the (long-tem) deprecation path.

[Neuro Toxin]

You can chain statements one after the other.

1. Use datapack, dynamic, stringmap to store all data required for all statements in the chain
2. Build and execute next statement with unique callback
3. In the callback goto step 2

Each step in the chain will need a seperate callback and you can parse your data through each callback.