[Tutorial] Using BlueG's MySQL plugin R7 (with cache)
#21

Got it working
Reply
#22

Usefull, thanks
Reply
#23

Thanks
Reply
#24

Now i better understand R7 functions, thanks for your help.
Reply
#25

error 017: undefined symbol "dbHandle"
how to fix it?
Reply
#26

Define it.
pawn Code:
new dbHandle;
Reply
#27

Don't only define it, also create the connection!
pawn Code:
new dbHandle;

// Whereever you connect!
mysql_connect("localhost", "root", "database", "password", 3306);
The port parameter is optional, so in case your MySQL server runs on port 3306, you don't have to specify it:
pawn Code:
mysql_connect("localhost", "root", "database", "password");
I hope the parameter names are self-explanatory. See the plugin's own topic for such documentation. This tutorial actually presumes that the scripter has slight knowledge of how MySQL server works, how the SQL language works and how to implement BlueG's plugin for their server. However if one lacks such knowledge, it is easy to obtain it from one of the several tutorials on this board alone or the official topic once again!

Quote:
Originally Posted by MP2
View Post
Thank you so much for this, I can't begin to explain how grateful I am. Everything seems to be going along nicely now. I haven't really set up the table properly yet as it's early days.

I now understand the purpose of threaded queries, and shouldn't have said the things I said in the mySQL plugin topic!
Thanks, I'm glad you have a hang of it now.
And excuse me for my attitude on a few occasions!

Quote:
Originally Posted by Hiddos
View Post
Not bad I guess, thanks for the heads up. Nice to see it forces me using threaded queries, knowing I had to switch over to them a long time ago.
No problem. I don't know if we're alike, but for at least a year or so, and more specifically after starting to learn programming at a university, improvements to coding practices make me a lot happier. Things such as knowing the advantages of threaded queries or code changes like
pawn Code:
if(IsPlayerInAnyVehicle(playerid))
{
    vID = GetPlayerVehicleID(playerid);
}
// to:
vID = GetPlayerVehicleID(playerid);
if(vID != 0)
{
}
Quote:
Originally Posted by Vukilore
View Post
Usefull, thanks
Quote:
Originally Posted by Sauxe
View Post
Thanks
No problem and good luck scripting!
Reply
#28

Quote:
Originally Posted by ArchBishop
View Post
BlueG: There seem to be server crashes time to time when using %e in mysql_format, the mysql_log.txt prints [15:26:08] CMySQLHandler::EscapeString(SomeGuy); - Escaped 7 characters to SomeGuy. and the callback used with cache=true does not even get triggered.
Hello AndreT, I haven't had any response from BlueG yet, do you perhaps experience crashes when using %e with mysql_format? Especially when using more than 1 time mysql_format quickly behind each other. I have changed all of them back to the normal format and mysql_real_escape_string function and I haven't got ANY crash at all since then.
Reply
#29

The same happened for me. I had to revert back to using mysql_real_escape_string and then formatting using format(). As far as I know mysql_format serves no other purpose than just making scripters' jobs easier using the same escaping function internally. And an implementation using mysql_real_escape_string in PAWN should not be slower than mysql_format. Also, some might even prefer having their own code written using without such "wrapper" functions.

So it should not be much of a problem, I hope!
Reply
#30

I currently have an IRC filterscript that connects to the MySQL DB and a gamemode that does the exact same, would this cause any problems? Or could this be easily optimized to only 1 time usage of mysql_connect. As the mysql_log says, it connects and reconnects a second time if I'm correct. The filterscript is designed this to way to directly communicate with the IRC users that used irc commands that I cannot do with a gamemode (irc fs and gamemode should remain seperated but use same MySQL DB).

I have almost no server crashes since I've reverted the mysql_format but now once a while my server seems to hang/freeze for some reason which I didn't have before and my irc filterscript seems to bug out (doesn't respond on irc commands after a while but still echoes the chat from ingame) since I've updated to R7.
Reply
#31

Quote:
Originally Posted by KyleSmith
Посмотреть сообщение
Hi, I'm trying to make it give me the result / date without the need of calling a function as I have many of these queries and making functions for similar queries would be hassle and lots of them too:
You already created a thread about it and I gave you a reply.
Reply
#32

For starters, you query does not look quite fine. I tried having a look in the documentation, but it still beats me how a query like
SELECT Date < UNIX_TIMESTAMP() FROM bans WHERE IP = '...'
could return anything but true or false.

Also, the callback that is fired after query execution is NoThreadResult with the INVALID_PLAYER_ID passed to it. There are 2 issues to this:
1. Why would you name it NoThreadResult while you clearly need to work with the result and do it INSIDE the callback that is fired, not below the mysql_function_query itself?
2. Why do you pass INVALID_PLAYER_ID to the callback? How will you know the player ID when it appears that there is indeed a ban?

Lets get to fixing this code. What you need to do first is have the code in a custom callback, have a working query and have the right player ID passed to it.
pawn Код:
GetPlayerIp(playerid, plrIP, sizeof(plrIP));
mysql_format(MySQLConnection, MySQL, "SELECT Date FROM bans WHERE IP = '%s' AND Date > UNIX_TIMESTAMP()", plrIP);
mysql_function_query(MySQLConnection, MySQL, true, "OnBanQueryFinish", "i", playerid);
This query will return data only if there's a ban which has not expired yet (I assumed that the field Date stores the unban timestamp).
Now move on to your custom callback.
pawn Код:
forward OnBanQueryFinish(playerid);
public OnBanQueryFinish(playerid)
{
    new rows, fields;
    cache_get_data(rows, fields);
    // You might also want to add a LIMIT clause to your ban since you don't seem to use more than one row here.
    if(rows)
    {
        new data[12];
        cache_get_row(0, 0, data);
        new expire = strval(data);
        printf("Extracted unban time %d from string '%s'", expire, data);
        SendClientMessage(playerid, COLOR, "You banned bro!");
    }
    return 1;
}
Extremo is right and I feel incredibly shit for helping you out since you don't seem to put much effort into this, instead you just go around the forum and spam topics with the same issue. But actually, this seems to be the first time you ever do some code on your own rather than throw a bunch of code together into one .pwn file, so you might as well read this post of mine
Reply
#33

Thanks for giving some advice. I've changed yours a little bit and added a field on. Would this work?

http://pastie.org/private/tzztcvzdyjg27smiyjuvw
Reply
#34

Quote:
Originally Posted by KyleSmith
pawn Код:
public OnPlayerConnect(playerid)
{
  mysql_format(MySQLConnection, MySQL, "SELECT Date FROM bans WHERE IP = '%s' AND Date > UNIX_TIMESTAMP()", plrIP);
  mysql_function_query(MySQLConnection, MySQL, true, "CheckBanned", "is", playerid, plrIP);
  return 1;
}

stock ReturnNull()
{
  return 1;
}

forward OnBanQueryFinish(playerid, ip[]);
public OnBanQueryFinish(playerid, ip[])
{
    new rows, fields;
    cache_get_data(rows, fields);
    if(rows)
    {
        new data[12], data2[1];
        cache_get_row(0, 0, data);
        cache_get_row(1, 0, data2);
        new expire = strval(data);
        new expirer = strval(data2);
        printf("Extracted unban time %d from string '%s'", expire, data);

        if(expirer == 1)
    {
        printf("Player is NOT Banned - Query Complete");
      mysql_format(MySQLConnection, MySQL, "DELETE FROM bans WHERE Name = '%s' LIMIT 1", ip);
      mysql_function_query(MySQLConnection, MySQL, false, "ReturnNull", "i", INVALID_PLAYER_ID);
        }
        else if(expirer == 0)
    {
        printf("Player is Banned - Query Complete");
            SendFormatMessage(playerid, COLOR_DEFAULT, "You are banned from this server untill %s", dater(expirer, 3));
            Kick(playerid);
            DontShow[playerid] = 1;
            return 1;
        }
    }
    return 1;
}
1. Your query returns ONE FIELD (SELECT Date FROM ...), not two (you're trying to get 2 fields) I don't understand why you added an extra field?

2. Your mysql_function_query makes the plugin execute public CheckBanned, not public OnBanQueryFinish when done. Also take into consideration that in OnPlayerConnect, in the code that you posted, plrIP is not defined.

3. What are you trying to do with the expirer variable?

4. ReturnNull is a stock function, not a public callback. Further on, it is USELESS, since you can also do:
pawn Код:
mysql_function_query(MySQLConnection, MySQL, false, "", "");
... to send a simple UPDATE query.

5. The DontShow does not have to be an array. It can be a simple variable due to the fact that two things never happen at once.
pawn Код:
DontShow = true;

public OnPlayerDisconnect(playerid, reason)
{
    if(!DontShow)
    {
        // Send disconnect message
    }
    else DontShow = false;
}
Please, dude, try to learn a bit about how MySQL queries work before making such assumptions.
Reply
#35

AndreT - http://forum.sa-mp.com/showpost.php?...12&postcount=8

You could of saved your breath there hehe. I actually already told him pretty much the same.
Reply
#36

I'm currently using R6 on my server. I need to know if the code will drastically change if I update to R7. For example this is one of my login queries:

pawn Код:
new resultline[64];
        format(querystring,sizeof(querystring), "SELECT Skin, IP, logincam, vcolor, vcolor2, time_h, time_m, weather FROM players WHERE Name = '%s' LIMIT 1", pName[playerid]);
        mysql_query(querystring);
        mysql_store_result();
        mysql_fetch_row_format(resultline);
        mysql_fetch_field_row(querystring, "Skin");
        SetPVarInt(playerid, "Skin", strval(querystring));
        SetPVarInt(playerid, "TempSkin", strval(querystring));
        SetPlayerSkin(playerid, strval(querystring));
        mysql_fetch_field_row(querystring, "vcolor");
        SetPVarInt(playerid, "vcolor", strval(querystring));
        mysql_fetch_field_row(querystring, "vcolor2");
        SetPVarInt(playerid, "vcolor2", strval(querystring));
        mysql_fetch_field_row(querystring, "logincam");
        SetPVarInt(playerid, "logincam", strval(querystring));
        mysql_fetch_field_row(querystring, "IP");
        SetPVarString(playerid, "temp_ip", querystring);

        mysql_fetch_field_row(querystring, "time_h");
        if(strval(querystring) != -1) curPlayerHour[playerid] = strval(querystring);
        else curPlayerHour[playerid] = DEFAULT_HOUR;
        SetPVarInt(playerid, "time_h", curPlayerHour[playerid]);
       
        mysql_fetch_field_row(querystring, "time_m");
        if(strval(querystring) != -1) curPlayerMin[playerid] = strval(querystring);
        else curPlayerMin[playerid] = DEFAULT_MIN;
        SetPVarInt(playerid, "time_m", curPlayerHour[playerid]);
       
        mysql_fetch_field_row(querystring, "weather");
        if(strval(querystring) != -1) curPlayerWeather[playerid] = strval(querystring);
        else curPlayerWeather[playerid] = DEFAULT_WEATHER;
        SetPVarInt(playerid, "save_weather", curPlayerWeather[playerid]);


        mysql_free_result();
Would I just have to move all that in to the callback? Does mysql_fetch_field_row and mysql_store_result work for threaded queries? I assume so?
Reply
#37

Quote:
Originally Posted by MP2
Посмотреть сообщение
I'm currently using R6 on my server. I need to know if the code will drastically change if I update to R7. For example this is one of my login queries:

pawn Код:
new resultline[64];
        format(querystring,sizeof(querystring), "SELECT Skin, IP, logincam, vcolor, vcolor2, time_h, time_m, weather FROM players WHERE Name = '%s' LIMIT 1", pName[playerid]);
        mysql_query(querystring);
        mysql_store_result();
        mysql_fetch_row_format(resultline);
        mysql_fetch_field_row(querystring, "Skin");
        SetPVarInt(playerid, "Skin", strval(querystring));
        SetPVarInt(playerid, "TempSkin", strval(querystring));
        SetPlayerSkin(playerid, strval(querystring));
        mysql_fetch_field_row(querystring, "vcolor");
        SetPVarInt(playerid, "vcolor", strval(querystring));
        mysql_fetch_field_row(querystring, "vcolor2");
        SetPVarInt(playerid, "vcolor2", strval(querystring));
        mysql_fetch_field_row(querystring, "logincam");
        SetPVarInt(playerid, "logincam", strval(querystring));
        mysql_fetch_field_row(querystring, "IP");
        SetPVarString(playerid, "temp_ip", querystring);

        mysql_fetch_field_row(querystring, "time_h");
        if(strval(querystring) != -1) curPlayerHour[playerid] = strval(querystring);
        else curPlayerHour[playerid] = DEFAULT_HOUR;
        SetPVarInt(playerid, "time_h", curPlayerHour[playerid]);
       
        mysql_fetch_field_row(querystring, "time_m");
        if(strval(querystring) != -1) curPlayerMin[playerid] = strval(querystring);
        else curPlayerMin[playerid] = DEFAULT_MIN;
        SetPVarInt(playerid, "time_m", curPlayerHour[playerid]);
       
        mysql_fetch_field_row(querystring, "weather");
        if(strval(querystring) != -1) curPlayerWeather[playerid] = strval(querystring);
        else curPlayerWeather[playerid] = DEFAULT_WEATHER;
        SetPVarInt(playerid, "save_weather", curPlayerWeather[playerid]);


        mysql_free_result();
Would I just have to move all that in to the callback? Does mysql_fetch_field_row and mysql_store_result work for threaded queries? I assume so?
I'm no expert but for a start it would go into a callback, you would need to change your mysql_query to call the function and then remove all of your free_result's and stuff like that.
There is no need to use mysql_fetch_field_row and mysql_store_result any more as caching does that for you.
Reply
#38

There is no cache in R6.
Reply
#39

Quote:
Originally Posted by MP2
Посмотреть сообщение
There is no cache in R6.
You said will it drasticly change if I change to R7.

That's why I said about cache as it's best to use it in R7 for when you switch.
Reply
#40

if mysql_free_result etc. don't work in R7, then why are they still included?
Reply


Forum Jump:


Users browsing this thread: 4 Guest(s)