[AndreT]

mysql_store_result and mysql_free_result can still be used in R7 if the bool:cache toggle is set as false in the corresponding mysql_function_query call.

As for the code shown, there actually don't need to be very drastic changes for just threading it, however to make it use the cache, yes.

[SWEMike]

Quote:

Originally Posted by AndreT pawn Код: mysql_function_query(dbHandle, "SELECT * FROM players WHERE name = 'Andre' LIMIT 0,1", "true", "OnPlayerDataLoad", "s", "Andre"); forward OnPlayerDataLoad(name[]); public OnPlayerDataLoad(name[]) {     new rows, fields;     cache_get_data(rows, fields);     if(!rows)     {         print(!"Andre is not a registered account on this server!");     }     else     {         printf("Andre is registered (1 row with %d fields)", fields);     } }

Ahem, you put quotation marks around a boolean value... :P

[AndreT]

Thank you for noticing! An interesting mistake!

On another note, I have modified the first post a little, added some comments to code and perhaps I could say, "simplified" it a little bit.

[Splav]

Hello, all
I have special function written to R6 MySQL plugin
How can I rewrite it on R7 version?

Quote:

stock fromUnixTime(time) { new unbantime[25]; format(str, sizeof(str), "SELECT FROM_UNIXTIME( %d )", time); if(mysql_query(str) && mysql_store_result()) { mysql_fetch_row_format(unbantime); } mysql_free_result(); return unbantime; }

Sorry for my English

[Coicatak]

Erm. How to get the return value of a callback?

For example here, my callback "LoadSQLVehicle" return the id of the created vehicle with CreateVehicle() or -1 if there was an error. So, how to I get this returned value?

Код:

mysql_format_ex(str, "SELECT * FROM vehicle WHERE veh_id=%d", id);
mysql_query(str, "LoadSQLVehicle", "ii", i, 0);

[scott1]

I all,

i am triyng to update my gm to the r7,

but i got an probleme,

before i was using R5

i made when playerconnect

Код:

if(IsIpBanned(ip))
{
}

IsIpBanned return 1 if find row with the select, and 0 if not.

But with the R7 on don't know how to do it, because i must call an another callback .

Thank you

Max

[AndreT]

Quote:

Originally Posted by Splav Hello, all I have special function written to R6 MySQL plugin How can I rewrite it on R7 version? Sorry for my English

Quote:

Originally Posted by Coicatak Erm. How to get the return value of a callback? For example here, my callback "LoadSQLVehicle" return the id of the created vehicle with CreateVehicle() or -1 if there was an error. So, how to I get this returned value? Код: mysql_format_ex(str, "SELECT * FROM vehicle WHERE veh_id=%d", id); mysql_query(str, "LoadSQLVehicle", "ii", i, 0);

Quote:

Originally Posted by scott1 I all, i am triyng to update my gm to the r7, but i got an probleme, before i was using R5 i made when playerconnect Код: if(IsIpBanned(ip)) { } IsIpBanned return 1 if find row with the select, and 0 if not. But with the R7 on don't know how to do it, because i must call an another callback . Thank you Max

Functions like that are not exactly "supported" by the new plugin and the threaded approach. Why? Because the query is not executed in the script execution thread and a callback will be fired once the query has finished.

Previously the server worked like this:
1. Do a million of things - synchronize players, run OnPlayerUpdate, other callbacks and code.
2. Query is fired!
3. Server waits until query is finished - does not do anything else meanwhile.
4. Can proceed with the code where it was left off before the query started.

Now it looks like this:
1. Do a million of things...
2. Query is fired!
3. Do a million of things...
4. Do a million of things...
5. Callback is called once query finishes.

So this means that functions like the ones you mentioned have become more difficult to create. It is possible, but I would not suggest it.

To show some code modifications, lets say you have something like the poster above mentioned:

pawn Код:

stock fromUnixTime(time)
{
    new unbantime[25];
    format(str, sizeof(str), "SELECT FROM_UNIXTIME( %d )", time);
    if(mysql_query(str) && mysql_store_result())
    {
        mysql_fetch_row_format(unbantime);
    }
    mysql_free_result();
    return unbantime;
 }

This is probably called like this:

pawn Код:

format(string, sizeof(string), "Hey, the unban time is %s!", fromUnixTime(timeeeeee));

Now what you will have to do is make the query inside your function where format is called. Pass the necessary parameters that you desire to keep to mysql_function_query.

pawn Код:

format(query, sizeof(query), "SELECT FROM_UNIXTME(%d)", timeeeeee);
mysql_function_query(connectionHandle, query, true, "OnUnixTimeSelected", "i", playerid); // Change the "i" and following parameters according to what you need!

// now a new callback, out of the previous scope.
forward OnUnixTimeSelected(...);
public OnUnixTimeSelected(...)
{
    cache_get_data(rows, fields, connectionHandle);
    if(rows)
        cache_get_row(0, 0, time);
    // Continue operating with the time string.
    return 1;
}

Hopefully this helps you a bit!

Quote:

Originally Posted by costel_nistor96 "row" parameter from cache_get_field_content is always 0 ? If not, when is different ?

It depends on which row you're actually parsing. If your query returns 4 rows and you know it does, you can do:

pawn Код:

cache_get_field_content(0, ...);
cache_get_field_content(1, ...);
cache_get_field_content(2, ...);
cache_get_field_content(3, ...);

But mostly, it is used in loops:

pawn Код:

cache_get_data(rows, fields, connectionHandle);
for(new i = 0; i != rows; i++)
{
    cache_get_field_content(i, ...);
}

[scott1]

Thank you for the time that you spend for us,
i got an another question is it possible to not call and callback with the mysql func query?

like this

Код:

new query[768];
    format(query, sizeof(query), "SELECT * FROM `"db_players"` WHERE ID = %d LIMIT 1", id);
 	mysql_function_query(MySQL_Handle, query, false,"","");
	mysql_store_result(MySQL_Handle);
	new resultat = mysql_num_rows(MySQL_Handle);
	mysql_free_result(MySQL_Handle);
	if(resultat >= 1) { return 1; }
	return 0;

Max

[Gumica]

Is this correct usage of new query:

pawn Код:

mysql_function_query(dbHandle,"UPDATE playerdata SET score= %d, money= %d WHERE user=%s",false,"","dds", score, money, pname)

[AndreT]

Quote:

Originally Posted by scott1 Thank you for the time that you spend for us, i got an another question is it possible to not call and callback with the mysql func query? like this Код: new query[768]; format(query, sizeof(query), "SELECT * FROM `"db_players"` WHERE ID = %d LIMIT 1", id); mysql_function_query(MySQL_Handle, query, false,"",""); mysql_store_result(MySQL_Handle); new resultat = mysql_num_rows(MySQL_Handle); mysql_free_result(MySQL_Handle); if(resultat >= 1) { return 1; } return 0; Max

Hey.

This code has to follow the examples I set in the post above yours, as well. There are no exceptions to this.

Quote:

Originally Posted by Gumica Is this correct usage of new query: pawn Код: mysql_function_query(dbHandle,"UPDATE playerdata SET score= %d, money= %d WHERE user=%s",false,"","dds", score, money, pname)

No. mysql_function_query does not behave as format or mysql_format functions. Since I have been experiencing issues with the mysql_format function, I've reverted to using regular format. Like this:

pawn Код:

format(query, sizeof(query), "UPDATE playerdata SET score=%d,money=%d WHERE user='%s'", score, money, pname);
mysql_function_query(dbHandle, query, false, "", "");

Note that usually player names do not need to be escaped since they cannot contain harmful characters at all. However if your server does a SetPlayerName depending on the user input, it would be good if you added extra checks prior to SetPlayerName to ensure that only the characters allowed to connect with are being used.
Additionally, since you're not going to fire a callback once the query finishes, there's no need to pass the score, money and pname parameters to an nonexistent callback.

Quote:

Originally Posted by costel_nistor96 Thanks. (Already repped you when you replied me, but I didn't made a post) Now I have 3 new questions: 1. Cache can be used in every thread, if no, when it's bad to use it ? 2. Which one is faster, sscanf, or cache_get_row ? 3. Why you use "LIMIT" in this: Код: mysql_function_query(dbHandle, "SELECT kills,deaths,level FROM players WHERE name = 'MP2' LIMIT 0,1", true, "OnPlayerDataLoad", "i", playerid); ? What it actually does ? I thought it's good only on ordering ("ORDER BY").

Thank you, and:
1. Cache functions can be used in every thread, yes. I suppose that the benefit from the cache functions is negligible in many cases and I have not ran any speed tests with queries that only receive, lets say, 1-2 fields of data.
2. However I'll combine the answer to the second question with the first one a bit: you need to think through what the plugin does in the first place. When you use cache, it stores the data to a vector as soon as the query is finished. This means that later on, the MySQL result does not have to be accessed, only the vector. About the parsing: you cannot use sscanf with caching, because it does not output a raw string with all fields. I have found sscanf to be quite quite fast in the past though, and I might be wrong, but I think when parsing about 20 fields, the cache_get_row and sscanf parsing were almost the same in speed. Or if slower, then the benefit of not having to use mysql_fetch_row makes up for all the lost microseconds!
3. I am not sure whether that code is from the original post or not, but it would be smart to specify a LIMIT clause when you're expecting that the query could return multiple rows. This will just speed up the behavior a bit since only one row will be returned to the plugin by the MySQL server. However if the name is a PRIMARY KEY, this should not be an issue (that'll allow only one such name to be present). Also I'd suggest everyone to use a system of unique keys for registered player. This will also make using multiple tables a lot easier.

Quote:

Originally Posted by TheArcher Any basic tutorial about how to create a saving system? :/

Sorry, I have been real busy with personal stuff and taking a vacation from my job in the past few weeks. I know that you and few others have requested for a tutorial on a saving/account system. I am going to make one, yes, but it needs some more thinking to be done.

Sorry for the late reply, and thanks for using the tutorial!

[AndreT]

I'm glad you fixed the issue.

What I meant by vulnerable to SQL injections is this textbook example when a player inserts a single quote (') as their password, the query will look like:
SELECT * FROM players WHERE name = 'Andre' and pass = '''
(3 single quotes in the end).
This will just break the query, but think what would happen if the user entered "lol'; DROP TABLE players;" as their password. Poof, whole table with all infos = gone.

Use mysql_real_escape_string to avoid it (or try using mysql_format, see if you can without crashing).

Also, 100 posts.

[Richie©]

Thanks for the good tutorial!
I succeeded on converting my cmds from normal threaded queries to cache, but im stuck on this were server is supposed to load all vehicles. How to make this with cache?
Note: 'mysql_cquery' is a macro, so nvm that, its enabled cache.

pawn Код:

mysql_cquery("SELECT * FROM `vehicles", THREAD_INITIATE_PUBVEHICLES);

while(mysql_fetch_row(Str))
{
    sscanf(Str, "p<|>iiffff", vehicleid, dModel, VX, VY, VZ, VA);
    //rest of code, unrelevant to cache.
}

[ReneG]

Quote:

Originally Posted by Richie© Thanks for the good tutorial! I succeeded on converting my cmds from normal threaded queries to cache, but im stuck on this were server is supposed to load all vehicles. How to make this with cache? Note: 'mysql_cquery' is a macro, so nvm that, its enabled cache. pawn Код: mysql_cquery("SELECT * FROM `vehicles", THREAD_INITIATE_PUBVEHICLES); while(mysql_fetch_row(Str)) {     sscanf(Str, "p<|>iiffff", vehicleid, dModel, VX, VY, VZ, VA);     //rest of code, unrelevant to cache. }

You need to specify a callback. This should work provided your field names match or w/e

pawn Код:

enum vInfo
{
    vid,
    Float:vPos[4],
    vmodel,
    vcolor1,
    vcolor2
}

new gVehicles[MAX_VEHICLES][vInfo];

stock LoadVehicles()
{
    return mysql_function_query(1, "SELECT * FROM `vehicles", true, "OnVehicleLoad", "");
}

forward OnVehicleLoad();
public OnVehicleLoad()
{
    new rows, fields, content[40];
    cache_get_data(rows, fields);

    new i;
    for(i=0; i<rows; i++) {
        cache_get_field_content(i, "vX", content),          gVehicles[i][vPos][0] = floatstr(content),
        cache_get_field_content(i, "vY", content),          gVehicles[i][vPos][1] = floatstr(content),
        cache_get_field_content(i, "vZ", content),          gVehicles[i][vPos][2] = floatstr(content),
        cache_get_field_content(i, "vA", content),          gVehicles[i][vPos][3] = floatstr(content),
        cache_get_field_content(i, "vModel", content),      gVehicles[i][vmodel] = strval(content),
        cache_get_field_content(i, "vcolor1", content),     gVehicles[i][vcolor1] = strval(content),
        cache_get_field_content(i, "vcolor2", content),     gVehicles[i][vcolor2] = strval(content);
        
        gVehicles[i][vid] = CreateVehicle(...); // fill with with variables fetched above
    }
    
    printf("%d vehicles loaded from database.", i);
    
    return 1;
}

[Richie©]

Thanks Vincent!

[ScriptWriter]

I have this query.

Код:

SELECT * FROM `players` WHERE `Name` = '%s' AND `Password` = '%s'

So how load player data from database using cache_get_row?

Sorry for bad English. :/

[AndreT]

Follow the tutorial, as simple as that.

1. Format the query. Keep in mind that any user input should be escaped prior to querying. In your case, escape the password (either use mysql_format with the %e specifier or mysql_real_escape_string prior to format). I suggest adding a LIMIT clause to your query.
2. Execute it using mysql_function_query, specify a callback that will be fired when the query finishes. Also pass on the player ID to the callback.

pawn Код:

mysql_function_query(dbHandle, query, true, "OnUserDataLoad", "i", playerid);

3. Jump out of your current function and create a new callback.

pawn Код:

forward OnUserDataLoad(playerid);
public OnUserDataLoad(playerid)
{
    // This callback is fired once the query is finished!
}

4. See if the query returned any rows.

pawn Код:

// In the new callback
new rows, fields;
cache_get_data(rows, fields);
if(rows)
{
    // The query returned a row!
}
else
{
    // The query returned nothing!
}

5. Grab the data returned by the query. I suggest you read this post.

pawn Код:

if(rows)
{
    new temp[64];
    cache_get_row(0, 0, temp);
    printf("First field data: %s", temp);
    cache_get_row(0, 1, temp);
    printf("Second field data: %s", temp);
    cache_get_row(0, 7, temp);
    printf("Eight field data: %s", temp);
}

Good luck.

[ScriptWriter]

Thanks, AndreT. But when I using "*" must load field with 0 index or I can start load field with 4 index. Maybe you understand me.
Sorry for bad English. :/

[AndreT]

Since you are selecting all fields, which in some cases is not the recommended procedure as the output could probably be reduced and so on, I don't know how many fields your query returns, hence I could not give a valid range of cache_get_row(0, 0-x, temp) lines.

If your query returns at least 5 rows, you can use index 4, sure.

[ScriptWriter]

My query returns about 18 fields, so I can start return field with index sample 4?

[AndreT]

If your query returns 18 fields, your field indexes go from 0 to 17.

If the fields returned are, for example:
name, password, score, autotune_setting, kills, deaths
Then their indexes will respectively be:
0, 1, 2, 3, 4, 5

name	password	score	autotune_setting	kills	deaths
0	1	2	3	4	5