[Plugin] [REL] MySQL Plugin (Now on github!)

[Plugin] [REL] MySQL Plugin (Now on github!) - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Plugin Development (https://sampforum.blast.hk/forumdisplay.php?fid=18)
+--- Thread: [Plugin] [REL] MySQL Plugin (Now on github!) (/showthread.php?tid=56564)

Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128

Re: [REL] MySQL Plugin (****** Project) - maddinat0r - 11.09.2013

Quote:

Originally Posted by FanHamMer

Where I can get libmysqlclient.so.18?

It is included in the mysql-client-5.5 package. If you get an error like "libmysqlclient.so.18 not found" use the 5.1-version, available in downloads.

Quote:

Originally Posted by DarkSlyder

Why does 'mysql_connect' always returns a positive handle with a failed connection? Even 'mysql_errno' doesn't seem to be working.

pawn Код:

//Testing code
printf("%d", mysql_connect(SQL_HOST, SQL_USER, "db1", SQL_PWD)); //Correct db
printf("%d", mysql_errno());
printf("%d", mysql_connect(SQL_HOST, SQL_USER, "123456", SQL_PWD)); //Invalid db
printf("%d", mysql_errno());
printf("%d", mysql_connect(SQL_HOST, SQL_USER, "db2", SQL_PWD)); //Correct db
printf("%d", mysql_errno());

Код:

//Output
1
0
2
0
3
0

1. the connection is always created. If it's actually connected to the database, that's another question.
2. your code is wrong. You are calling mysql_errno always with the same (default) handle.

Re: [REL] MySQL Plugin (****** Project) - Aliassassin123456 - 11.09.2013

Hi Again,
I've got new error with mysql

Server Log:

Код:

[15:35:18] Loading plugin: mysql.so
[15:35:18]   Failed (libmysqlclient_r.so.16: cannot open shared object file: No such file or directory)

I'm running CentOS 6 64bit HELLLLLLLLLLLLP

Re: [REL] MySQL Plugin (****** Project) - dusk - 11.09.2013

Quote:

Originally Posted by Aliassassin123456

Hi Again,
I've got new error with mysql

Server Log:

Код:

[15:35:18] Loading plugin: mysql.so
[15:35:18]   Failed (libmysqlclient_r.so.16: cannot open shared object file: No such file or directory)

I'm running CentOS 6 64bit HELLLLLLLLLLLLP

This should help http://forum.sa-mp.com/showpost.php?...&postcount=553

Re: [REL] MySQL Plugin (****** Project) - maddinat0r - 16.09.2013

Quote:

Originally Posted by zPain

Every time I use it, cache_get_field_content returns NULL. Could it be a bug on r33?

Enable the default logging ("mysql_log();"), run your script again and check the mysql_log.txt for any errors.

Re: [REL] MySQL Plugin (****** Project) - zPain - 16.09.2013

I've deleted mysql_log.txt and restarted my samp-server, but the file wasn't created. I suppose there aren't errors.

Sorry for my english (if that's wrong)

Re: [REL] MySQL Plugin (****** Project) - maddinat0r - 16.09.2013

Quote:

Originally Posted by zPain

I've deleted mysql_log.txt and restarted my samp-server, but the file wasn't created. I suppose there aren't errors.

Sorry for my english (if that's wrong)

Maybe there really aren't any values in the table? Look, if there would be a problem with cache_get_field_content always returning NULL, this topic would be flooded with such problems.
You can try cache_get_row, if this also returns NULL, there is probably a problem with the query you send or the table really hasn't any values.

Re: [REL] MySQL Plugin (****** Project) - Kyra - 16.09.2013

Hi, thanks for your plugin but i have a question

I used R5 version now i want to upgrade my GM on R33 on non threaded query cached, but your plugin is faster than R5/R6?

I try to check if is faster i see is same or other check i see R6 is faster, i'm probably wrong.

Example i try this query x 5 k on loop

PHP код:



GetIpCountry_mysql(IpInfo[])

{

    new query[256];

    if(!strcmp("127.0.0.1", IpInfo, true))

    {

        query = "Localhost";

    }

    else

    {

        new DataSetting[4][10];

        if(sscanf(IpInfo, "p<.>s[10]s[10]s[10]s[10]", DataSetting[0], DataSetting[1], DataSetting[2], DataSetting[3])) query = "Invalid IP";

        else

        {

            format(query, sizeof query, "\

            SELECT `Country`\

            FROM `countrydetected`\

            WHERE `Ip_From` <= ((16777216*%d) + (65536*%d) + (256*%d) + %d)\

            AND `Ip_to` >= ((16777216*%d) + (65536*%d) + (256*%d) + %d) LIMIT 1",

            strval(DataSetting[0]), strval(DataSetting[1]), strval(DataSetting[2]), strval(DataSetting[3]),

            strval(DataSetting[0]), strval(DataSetting[1]), strval(DataSetting[2]), strval(DataSetting[3]));

            new Cache:vquery = mysql_query(ConnexionBDD,query);

            if(cache_get_row_count())

            {

                mysql_fetch_row(query);

            }

            else query = "Unknown";

            cache_delete(vquery);

        }

    }

    return query;

}

But i have a problem with the fetch i don't know if is good.

Re: [REL] MySQL Plugin (****** Project) - maddinat0r - 16.09.2013

Quote:

Originally Posted by Kyra

Hi, thanks for your plugin but i have a question

I used R5 version now i want to upgrade my GM on R33 on non threaded query cached, but your plugin is faster than R5/R6?

I try to check if is faster i see is same or other check i see R6 is faster, i'm probably wrong.

Example i try this query x 5 k on loop

PHP код:



[...]

But i have a problem with the fetch i don't know if is good.

Your speed results are probably correct, the unthreaded queries in R33 can be slower than those in R6. Why? Because the R33-queries are still cached (they take the whole result from the MySQL server), but the R6-queries are not (you decide what data you need).

Your code looks generally not very good. Why do you store the sub-numbers of the IP at first in strings and then convert them back into numbers? Why don't you just use "p<.>dddd"?

Re: [REL] MySQL Plugin (****** Project) - Kyra - 16.09.2013

It's not my function ^^ i just test with this https://sampforum.blast.hk/showthread.php?tid=131554

But thanks for your response ! And just for know, why my store result works 1 time and other time returning "NULL" mysql_fetch_row(query); is bad with cache query? What is the similar function with cache function?

Re: [REL] MySQL Plugin (****** Project) - zPain - 16.09.2013

I've tested my script with r20 and got the same problem. I believe that's my mistake...

EDIT: Fixed

I've changed:

pawn Код:

cache_get_field_content(index, "zone_name", zoneArray[id][zone_name], mysql_database);

for:

pawn Код:

cache_get_field_content(index, "zone_name", zoneArray[id][zone_name], mysql_database, 32);

But I thought the length parameter was not necessary.

Re: [REL] MySQL Plugin (****** Project) - Ghazi123 - 16.09.2013

Good job

AW: Re: [REL] MySQL Plugin (****** Project) - maddinat0r - 19.09.2013

Quote:

Originally Posted by Kyra

[...]
But thanks for your response ! And just for know, why my store result works 1 time and other time returning "NULL" mysql_fetch_row(query); is bad with cache query? What is the similar function with cache function?

mysql_fetch_row is in fact just a normal PAWN function which can be found in the MySQL plugin include. It just gets the data in a loop and concatenates it as a string. You really shouldn't use this function, fetch the data with the standard cache_* natives like cache_get_row or cache_get_field_content.

Quote:

Originally Posted by zPain

[...]
But I thought the length parameter was not necessary.

That's the well-known problem with enum-arrays. Take a look at this post, it explains the problem in detail.

Re: [REL] MySQL Plugin (****** Project) - maxisaibot - 22.09.2013

When i trying to get mask name if players name longer than 16, name is not showing. And stplayername not working. Why? What is the best way of get string?

new temp[MAX_PLAYER_NAME];
cache_get_field_content(0, "maskname", temp);

strmid(Player[playerid][pMask], temp, 0, strlen(temp), 255);

or

memcpy(Player[playerid][pMask], temp, strlen(Player[playerid][pMask]) * 4, temp * 4, sizeof Player[playerid][pMask]);

or

memcpy(Player[playerid][pMask], temp, 0, 64+8, 64+8 ); //worked but i guess its not good

or

strmid(Player[playerid][pMask], temp, MAX_PLAYER_NAME*4, MAX_PLAYER_NAME*4);

Re: AW: Re: [REL] MySQL Plugin (****** Project) - Den_Bellik - 26.09.2013

Quote:

Originally Posted by maddinat0r

Okay, again, very easy: Don't use unthreaded queries if you can use threaded ones instead. Your problem is happening because you are using threaded and unthreaded queries wrong.

This is a bug..

http://i.imgur.com/a0tVQ3Y.png

Sorry my English

Re: AW: Re: [REL] MySQL Plugin (****** Project) - maddinat0r - 26.09.2013

Quote:

Originally Posted by Den_Bellik

This is a bug..

http://i.imgur.com/a0tVQ3Y.png

Sorry my English

Okay, I know now where the mistake is, but still, use threaded queries.
The problem is that R33 uses now 2 connections and you only set the character set for one connection. Another solution would be to set the character set once and for ever directly in the table (ALTER TABLE ... SET CHARSET ...).

Re: [REL] MySQL Plugin (****** Project) - saamp - 26.09.2013

maddinat0r, I need an answer ; I want to create a Top Players with some fields ( level, money, crimes, etc. ). The database is actually big enough ( + 500.000 accounts ). Do I have to make 3 threaded queries for all of this?

For example:

pawn Код:

// CMD:top(playerid, params)
mysql_tquery(SQL, "SELECT level FROM accounts ORDER BY level DESC LIMIT 5", "TopLevel", "d", playerid);
mysql_tquery(SQL, "SELECT money FROM accounts ORDER BY money DESC LIMIT 5", "TopMoney", "d", playerid);
mysql_tquery(SQL, "SELECT crime FROM accounts ORDER BY crime DESC LIMIT 5", "TopCrime", "d", playerid);

pawn Код:

forward TopLevel(const playerid);
public TopLevel(const playerid)
{
    if(!IsPlayerConnected(playerid))
        return 1;


    // My Top Level Code .............
    strcat(largeString, editedString);
    // ......................................
    return 1;
}

forward TopMoney(const playerid);
public TopMoney(const playerid)
{
    if(!IsPlayerConnected(playerid))
        return 1;


    // My Top Money Code ........................
    strcat(largeString, editedString);
    // ......................................

    return 1;
}

forward TopCrime(const playerid);
public TopCrime(const playerid)
{
    if(!IsPlayerConnected(playerid))
        return 1;


    // My Top Crime Code .......................
    strcat(largeString, editedString);
    // ......................................

    ShowPlayerDialog(playerid, 0, DIALOG_STYLE_MSGBOX, "Top Players", largeString, "OK", "");
    return 1;
}

AW: Re: [REL] MySQL Plugin (****** Project) - Mellnik - 26.09.2013

Quote:

Originally Posted by saamp

maddinat0r, I need an answer ; I want to create a Top Players with some fields ( level, money, crimes, etc. ). The database is actually big enough ( + 500.000 accounts ). Do I have to make 3 threaded queries for all of this?

For example:

pawn Код:

// CMD:top(playerid, params)
mysql_tquery(SQL, "SELECT level FROM accounts ORDER BY level DESC LIMIT 5", "TopLevel", "d", playerid);
mysql_tquery(SQL, "SELECT money FROM accounts ORDER BY money DESC LIMIT 5", "TopMoney", "d", playerid);
mysql_tquery(SQL, "SELECT crime FROM accounts ORDER BY crime DESC LIMIT 5", "TopCrime", "d", playerid);

pawn Код:

forward TopLevel(const playerid);
public TopLevel(const playerid)
{
    if(!IsPlayerConnected(playerid))
        return 1;


    // My Top Level Code .............
    strcat(largeString, editedString);
    // ......................................
    return 1;
}

forward TopMoney(const playerid);
public TopMoney(const playerid)
{
    if(!IsPlayerConnected(playerid))
        return 1;


    // My Top Money Code ........................
    strcat(largeString, editedString);
    // ......................................

    return 1;
}

forward TopCrime(const playerid);
public TopCrime(const playerid)
{
    if(!IsPlayerConnected(playerid))
        return 1;


    // My Top Crime Code .......................
    strcat(largeString, editedString);
    // ......................................

    ShowPlayerDialog(playerid, 0, DIALOG_STYLE_MSGBOX, "Top Players", largeString, "OK", "");
    return 1;
}

Yes, but you should use LIMIT. For example if you want top 30 put "LIMIT 30" at the end of the query.

Re: [REL] MySQL Plugin (****** Project) - magnusburton - 27.09.2013

Quote:

Originally Posted by saamp

pawn Код:

// CMD:top(playerid, params)
mysql_tquery(SQL, "SELECT level FROM accounts ORDER BY level DESC LIMIT 5", "TopLevel", "d", playerid);
mysql_tquery(SQL, "SELECT money FROM accounts ORDER BY money DESC LIMIT 5", "TopMoney", "d", playerid);
mysql_tquery(SQL, "SELECT crime FROM accounts ORDER BY crime DESC LIMIT 5", "TopCrime", "d", playerid);

pawn Код:

forward TopLevel(const playerid);
public TopLevel(const playerid)
{
    if(!IsPlayerConnected(playerid))
        return 1;


    // My Top Level Code .............
    strcat(largeString, editedString);
    // ......................................
    return 1;
}

forward TopMoney(const playerid);
public TopMoney(const playerid)
{
    if(!IsPlayerConnected(playerid))
        return 1;


    // My Top Money Code ........................
    strcat(largeString, editedString);
    // ......................................

    return 1;
}

forward TopCrime(const playerid);
public TopCrime(const playerid)
{
    if(!IsPlayerConnected(playerid))
        return 1;


    // My Top Crime Code .......................
    strcat(largeString, editedString);
    // ......................................

    ShowPlayerDialog(playerid, 0, DIALOG_STYLE_MSGBOX, "Top Players", largeString, "OK", "");
    return 1;
}

What exactly do you wish to show? If you want to show their name together with their (for example, level) then you must select the name in the SELECT part and order them as (for example, level) as you've done correctly. LIMIT will help you select only the top X (5 in this case) players so that's correct too. And yes, you'd have to make 3 different queries to do this.

Re: [REL] MySQL Plugin (****** Project) - saamp - 27.09.2013

Thanks guys.

Why cache_get_row_count() returns 1 here, when in my bans table I don't have any record?

pawn Код:

mysql_query(databaseConnection, "SELECT COUNT(*) FROM bans WHERE BanIP = '127.0.0.1'");
    printf("Count: %d | Int: %d", cache_get_row_count(), cache_get_row_int(0, 0));

Count: 1 | Int: 0

AW: Re: [REL] MySQL Plugin (****** Project) - maddinat0r - 27.09.2013

Quote:

Originally Posted by saamp

Thanks guys.

Why cache_get_row_count() returns 1 here, when in my bans table I don't have any record?

pawn Код:

mysql_query(databaseConnection, "SELECT COUNT(*) FROM bans WHERE BanIP = '127.0.0.1'");
    printf("Count: %d | Int: %d", cache_get_row_count(), cache_get_row_int(0, 0));

Count: 1 | Int: 0

You are retrieving the amount of rows per query, so your query returns a value: the number of rows. Because the number itself is in a row, cache_get_row_count returns 1.