[Puppetmaster]

What are the best methods of making queries? In the past I have tried separating queries with the ; character and that seems to work fine until the queries become too long. Is there an upper limit on query size?

I have recently begun using threaded queries and they are nice and do not pause the main thread which allows me to do things in the background. The main issue that I have had is that the execution time for them is extremely inconsistent, some take a few milliseconds while other times the same query will take many times longer. Is it better to open multiple threaded connection handles to the same database and load balance them when I need a large number of threaded queries at once?

[RedSword]

You do not have a size limit (AFAIK), but if your DBMS is MySQL, Sourcemod uses a certain MySQL client flag that prevent doing multiple queries for security (I guess you're using SQLite because you don't seem to have that problem ?). To run multiple query at once you cannot create a transaction as you would normally do (by this I mean via SQL w/ "START TRANSACTION;" and "COMMIT;"). You should instead use SQL_CreateTransaction w/ AddQuery & SQL_ExecuteTransaction to use transactions.

I'd say to use one big query preferably, as the overall overhead should be lighter than making many new query and sending them manually. It however depends of many things. Is your database on the same computer ? How important is it to have a query be executed ? Do they all need to be executed together ?

Red

[Potato Uno]

Can't you deadlock the server if you do this? (Or maybe I'm thinking of something else.)

https://wiki.alliedmods.net/SQL_(SourceMod_Scripting)

[Miu]

i dont think separate queries like that are properly supported, just use transactions

[RedSword]

Originally Posted by Potato Uno Can't you deadlock the server if you do this? (Or maybe I'm thinking of something else.) https://wiki.alliedmods.net/SQL_(SourceMod_Scripting)

Why would you deadlock a server by sending a transaction ? /confused

[Powerlord]

If you fail to properly lock/unlock tables / columns, it's possible to deadlock anything in SQL.

[Puppetmaster]

Originally Posted by RedSword You do not have a size limit (AFAIK), but if your DBMS is MySQL, Sourcemod uses a certain MySQL client flag that prevent doing multiple queries for security (I guess you're using SQLite because you don't seem to have that problem ?). To run multiple query at once you cannot create a transaction as you would normally do (by this I mean via SQL w/ "START TRANSACTION;" and "COMMIT;"). You should instead use SQL_CreateTransaction w/ AddQuery & SQL_ExecuteTransaction to use transactions. I'd say to use one big query preferably, as the overall overhead should be lighter than making many new query and sending them manually. It however depends of many things. Is your database on the same computer ? How important is it to have a query be executed ? Do they all need to be executed together ? Red

Would this be the correct way of doing it?

Handle dbc;
Transaction trans;
char errorc[255];
dbc = SQL_Connect("default", false, errorc, sizeof(errorc));
if(dbc != INVALID_HANDLE)
{
    trans = SQL_CreateTransaction();
    SQL_AddQuery(trans, "UPDATE webData SET amazingVariable = 5 WHERE id = 6;", 0);
    SQL_AddQuery(trans, "UPDATE webData SET amazingVariable = 15 WHERE id = 7;", 0);
    SQL_ExecuteTransaction(dbc, trans);
    CloseHandle(dbc)
}

[RedSword]

Well, I believe you always need a ";" with non-select queries.

Other than that the transaction part looks ok; though I've never used the "Transaction" "type". I used a handle. There should be no differences though (beside type safety in your). Also I'd do "Transaction trans;" instead of "new Transaction:trans;" (I believe it should work) ; you're currently mixing 1.7 syntax with non-1.7 syntax I think (the Transaction type is a 1.7 feature if I'm correct).

Red

[SM9]

Make sure you always use threaded queries to avoid Deadlocks, also using none threaded queries is likely to cause lag spikes on your server.

[Potato Uno]

Is there a reason why non-threaded queries exist then? Everyone says not to use them like as if it's AIDS (for good reason too). Can't we make the API use threaded by default, and only use non-threaded as a fallback? (Rather than allowing people to voluntarily use them?)