1. SA-MP Forums Archive
  2. SA-MP Scripting and Plugins
  3. Scripting Help
  4. Help Archive

Threaded Mode
MySQL - Gives all no value back, but database is filled
Jochemd
High-roller
*****
Posts: 2,220
Threads: 154
Joined: Jul 2009
Reputation: 0
#1

18.08.2011, 10:22
Hello,

Check the MySQL Log:

Код:
[18:38:33]  
[18:38:33] ---------------------------
[18:38:33] MySQL Debugging activated (08/17/11)
[18:38:33] ---------------------------
[18:38:33]  
[18:38:33] >> mysql_connect( )
[18:38:33] CMySQLHandler::CMySQLHandler() - constructor called.
[18:38:33] CMySQLHandler::CMySQLHandler() - Connecting to "184.154.33.53" | DB: "jc" | Username: "jc" ...
[18:38:33] CMySQLHandler::Connect() - Connection was successful.
[18:38:33] CMySQLHandler::Connect() - Auto-Reconnect has been enabled.
[18:38:33] >> mysql_ping( Connection handle: 1 )
[18:38:33] CMySQLHandler::Ping() - Connection is still alive.
[18:38:33] >> mysql_query( Connection handle: 1 )
[18:38:33] CMySQLHandler::Query(SELECT * FROM `HouseInfo` WHERE `ID` = '0') - Successfully executed.
[18:38:33] >> mysql_store_result( Connection handle: 1 )
[18:38:33] CMySQLHandler::StoreResult() - Result was stored.
[18:38:33] >> mysql_num_rows( Connection handle: 1 )
[18:38:33] CMySQLHandler::NumRows() - Returned 1 row(s)
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideAngle") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideAngle") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("InteriorID") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Locked") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Price") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Owner") - 
[18:38:33] >> mysql_query( Connection handle: 1 )
[18:38:33] CMySQLHandler::Query(SELECT * FROM `HouseInfo` WHERE `ID` = '1') - Successfully executed.
[18:38:33] >> mysql_store_result( Connection handle: 1 )
[18:38:33] CMySQLHandler::StoreResult() - Result was stored.
[18:38:33] >> mysql_num_rows( Connection handle: 1 )
[18:38:33] CMySQLHandler::NumRows() - Returned 1 row(s)
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupOutsidePosZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("PickupInsidePosZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnOutsideAngle") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideX") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideY") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideZ") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("SpawnInsideAngle") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("InteriorID") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Locked") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Price") - 
[18:38:33] >> mysql_fetch_field_row( Connection handle: 1 )
[18:38:33] CMySQLHandler::FetchField("Owner") - 
[18:38:33] >> mysql_query( Connection handle: 1 )
As you see it all returns no value. This is from my LoadHouses stock. I show it:

pawn Код:
stock LoadHouses()
{
    new Store[30];
    for(new h = 0; h < MAX_HOUSES; h ++)
    {
        format(QueryString,sizeof(QueryString),"SELECT * FROM `HouseInfo` WHERE `ID` = '%d'",h);
        mysql_query(QueryString);
        mysql_store_result();
        if(mysql_num_rows() != 0)
        {
            mysql_get_field("PickupOutsidePosX",Store); HouseInfo[h][PickupOutsidePosX] = floatstr(Store);
            mysql_get_field("PickupOutsidePosY",Store); HouseInfo[h][PickupOutsidePosY] = floatstr(Store);
            mysql_get_field("PickupOutsidePosZ",Store); HouseInfo[h][PickupOutsidePosZ] = floatstr(Store);
            mysql_get_field("PickupInsidePosX",Store); HouseInfo[h][PickupInsidePosX] = floatstr(Store);
            mysql_get_field("PickupInsidePosY",Store); HouseInfo[h][PickupInsidePosY] = floatstr(Store);
            mysql_get_field("PickupInsidePosZ",Store); HouseInfo[h][PickupInsidePosZ] = floatstr(Store);
            mysql_get_field("SpawnOutsideX",Store); HouseInfo[h][SpawnOutsideX] = floatstr(Store);
            mysql_get_field("SpawnOutsideY",Store); HouseInfo[h][SpawnOutsideY] = floatstr(Store);
            mysql_get_field("SpawnOutsideZ",Store); HouseInfo[h][SpawnOutsideZ] = floatstr(Store);
            mysql_get_field("SpawnOutsideAngle",Store); HouseInfo[h][SpawnOutsideAngle] = floatstr(Store);
            mysql_get_field("SpawnInsideX",Store); HouseInfo[h][SpawnInsideX] = floatstr(Store);
            mysql_get_field("SpawnInsideY",Store); HouseInfo[h][SpawnInsideY] = floatstr(Store);
            mysql_get_field("SpawnInsideZ",Store); HouseInfo[h][SpawnInsideZ] = floatstr(Store);
            mysql_get_field("SpawnInsideAngle",Store); HouseInfo[h][SpawnInsideAngle] = floatstr(Store);
            mysql_get_field("InteriorID",Store); HouseInfo[h][InteriorID] = strval(Store);
            mysql_get_field("Locked",Store); HouseInfo[h][Locked] = strval(Store);
            mysql_get_field("Price",Store); HouseInfo[h][Price] = strval(Store);
            mysql_get_field("Owner",HouseInfo[h][Owner]);
            HouseInfo[h][VirtualWorld] = h;
            HouseInfo[h][Owned] = 1;
            if(!strlen(HouseInfo[h][Owner])) HouseInfo[h][PickupID] = CreateDynamicPickup(1273,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
            else HouseInfo[h][PickupID] = CreateDynamicPickup(1272,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
            printf("House File ID %d has been created",h);
        }
    }
}
I don't know why it all returns no value - it worked before.

Now my LoadHouses() is commented in OnGameModeInit. When I load my houses, the server crashes when someone tries to login when mysql_fetch_field_row is called.

Anyone experienced this problem?

Jochem
Find
Reply
JaTochNietDan
High-roller
*****
Posts: 2,502
Threads: 26
Joined: Apr 2007
Reputation: 0
#2

18.08.2011, 10:26
That code is just rediculous, I mean how many SQL queries are you executing? Hundreds? Thousnands!?

Why don't you just use one query to gather all of the information you need, here is a quick example:

pawn Код:
mysql_query("SELECT * FROM `HouseInfo`");
mysql_store_result();

while(mysql_fetch_row(result))
{
    // Now this loop will run once for each row, and you can do what you want
}

mysql_free_result();
Doesn't that make more sense? Instead of running hundreds or maybe even thousands of queries, you can run just one query and gather all of the data from that.
Find
Reply
Jochemd
High-roller
*****
Posts: 2,220
Threads: 154
Joined: Jul 2009
Reputation: 0
#3

18.08.2011, 10:35
The reason I do this, is because I don't want to fetch the line and split it. I make pretty much changes in my design.
Find
Reply
JaTochNietDan
High-roller
*****
Posts: 2,502
Threads: 26
Joined: Apr 2007
Reputation: 0
#4

18.08.2011, 10:39
Quote:
Originally Posted by Jochemd
Посмотреть сообщение
The reason I do this, is because I don't want to fetch the line and split it. I make pretty much changes in my design.
What you're doing is insanity, it is not how SQL databases are intended to be queried at all!
Find
Reply
Jochemd
High-roller
*****
Posts: 2,220
Threads: 154
Joined: Jul 2009
Reputation: 0
#5

18.08.2011, 10:41
Quote:
Originally Posted by JaTochNietDan
Посмотреть сообщение
What you're doing is insanity, it is not how SQL databases are intended to be queried at all!
I can believe that, TBH I only load 30 houses. This has worked before on a database in GTA Multi, and now on this database it doesn't. Could it be this database is weaker?

Edit: 'result' would put out the whole row, right? Then I got to split it, which I am trying to evade.
Find
Reply
[HiC]TheKiller
High-roller
*****
Posts: 2,629
Threads: 40
Joined: Mar 2008
Reputation: 0
#6

18.08.2011, 10:58
Quote:
Originally Posted by Jochemd
Посмотреть сообщение
I can believe that, TBH I only load 30 houses. This has worked before on a database in GTA Multi, and now on this database it doesn't. Could it be this database is weaker?

Edit: 'result' would put out the whole row, right? Then I got to split it, which I am trying to evade.
You can use the mysql_get_field function inside the while statement so that you don't have to split the line.

pawn Код:
mysql_query("SELECT * FROM `HouseInfo`");
mysql_store_result();
new h;
while(mysql_fetch_row(result))
{
    mysql_get_field("PickupOutsidePosX",Store); HouseInfo[h][PickupOutsidePosX] = floatstr(Store);
    mysql_get_field("PickupOutsidePosY",Store); HouseInfo[h][PickupOutsidePosY] = floatstr(Store);
    mysql_get_field("PickupOutsidePosZ",Store); HouseInfo[h][PickupOutsidePosZ] = floatstr(Store);
    mysql_get_field("PickupInsidePosX",Store); HouseInfo[h][PickupInsidePosX] = floatstr(Store);
    mysql_get_field("PickupInsidePosY",Store); HouseInfo[h][PickupInsidePosY] = floatstr(Store);
    mysql_get_field("PickupInsidePosZ",Store); HouseInfo[h][PickupInsidePosZ] = floatstr(Store);
    mysql_get_field("SpawnOutsideX",Store); HouseInfo[h][SpawnOutsideX] = floatstr(Store);
    mysql_get_field("SpawnOutsideY",Store); HouseInfo[h][SpawnOutsideY] = floatstr(Store);
    mysql_get_field("SpawnOutsideZ",Store); HouseInfo[h][SpawnOutsideZ] = floatstr(Store);
    mysql_get_field("SpawnOutsideAngle",Store); HouseInfo[h][SpawnOutsideAngle] = floatstr(Store);
    mysql_get_field("SpawnInsideX",Store); HouseInfo[h][SpawnInsideX] = floatstr(Store);
    mysql_get_field("SpawnInsideY",Store); HouseInfo[h][SpawnInsideY] = floatstr(Store);
    mysql_get_field("SpawnInsideZ",Store); HouseInfo[h][SpawnInsideZ] = floatstr(Store);
    mysql_get_field("SpawnInsideAngle",Store); HouseInfo[h][SpawnInsideAngle] = floatstr(Store);
    mysql_get_field("InteriorID",Store); HouseInfo[h][InteriorID] = strval(Store);
    mysql_get_field("Locked",Store); HouseInfo[h][Locked] = strval(Store);
    mysql_get_field("Price",Store); HouseInfo[h][Price] = strval(Store);
    mysql_get_field("Owner",HouseInfo[h][Owner]);
    HouseInfo[h][VirtualWorld] = h;
    HouseInfo[h][Owned] = 1;
    if(!strlen(HouseInfo[h][Owner])) HouseInfo[h][PickupID] = CreateDynamicPickup(1273,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
    else HouseInfo[h][PickupID] = CreateDynamicPickup(1272,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
    printf("House File ID %d has been created",h);
    h++;
}
mysql_free_result();
Find
Reply
Jochemd
High-roller
*****
Posts: 2,220
Threads: 154
Joined: Jul 2009
Reputation: 0
#7

18.08.2011, 10:59
That is what I am doing now.
Find
Reply
[HiC]TheKiller
High-roller
*****
Posts: 2,629
Threads: 40
Joined: Mar 2008
Reputation: 0
#8

18.08.2011, 11:07
Quote:
Originally Posted by Jochemd
Посмотреть сообщение
That is what I am doing now.
You are sending multiple queries in a loop rather than one single query. Also, are you freeing all of your results? In your first code, you didn't even free the result.
Find
Reply
Jochemd
High-roller
*****
Posts: 2,220
Threads: 154
Joined: Jul 2009
Reputation: 0
#9

18.08.2011, 11:15
Probably not, I'm not long in MySQL yet.

pawn Код:
mysql_query("SELECT * FROM `HouseInfo`");
mysql_store_result();
new h;
while(mysql_fetch_row(result))
{
    mysql_get_field("PickupOutsidePosX",Store); HouseInfo[h][PickupOutsidePosX] = floatstr(Store);
    mysql_get_field("PickupOutsidePosY",Store); HouseInfo[h][PickupOutsidePosY] = floatstr(Store);
    mysql_get_field("PickupOutsidePosZ",Store); HouseInfo[h][PickupOutsidePosZ] = floatstr(Store);
    mysql_get_field("PickupInsidePosX",Store); HouseInfo[h][PickupInsidePosX] = floatstr(Store);
    mysql_get_field("PickupInsidePosY",Store); HouseInfo[h][PickupInsidePosY] = floatstr(Store);
    mysql_get_field("PickupInsidePosZ",Store); HouseInfo[h][PickupInsidePosZ] = floatstr(Store);
    mysql_get_field("SpawnOutsideX",Store); HouseInfo[h][SpawnOutsideX] = floatstr(Store);
    mysql_get_field("SpawnOutsideY",Store); HouseInfo[h][SpawnOutsideY] = floatstr(Store);
    mysql_get_field("SpawnOutsideZ",Store); HouseInfo[h][SpawnOutsideZ] = floatstr(Store);
    mysql_get_field("SpawnOutsideAngle",Store); HouseInfo[h][SpawnOutsideAngle] = floatstr(Store);
    mysql_get_field("SpawnInsideX",Store); HouseInfo[h][SpawnInsideX] = floatstr(Store);
    mysql_get_field("SpawnInsideY",Store); HouseInfo[h][SpawnInsideY] = floatstr(Store);
    mysql_get_field("SpawnInsideZ",Store); HouseInfo[h][SpawnInsideZ] = floatstr(Store);
    mysql_get_field("SpawnInsideAngle",Store); HouseInfo[h][SpawnInsideAngle] = floatstr(Store);
    mysql_get_field("InteriorID",Store); HouseInfo[h][InteriorID] = strval(Store);
    mysql_get_field("Locked",Store); HouseInfo[h][Locked] = strval(Store);
    mysql_get_field("Price",Store); HouseInfo[h][Price] = strval(Store);
    mysql_get_field("Owner",HouseInfo[h][Owner]);
    HouseInfo[h][VirtualWorld] = h;
    HouseInfo[h][Owned] = 1;
    if(!strlen(HouseInfo[h][Owner])) HouseInfo[h][PickupID] = CreateDynamicPickup(1273,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
    else HouseInfo[h][PickupID] = CreateDynamicPickup(1272,23,HouseInfo[h][PickupOutsidePosX],HouseInfo[h][PickupOutsidePosY],HouseInfo[h][PickupOutsidePosZ],-1,-1,-1,200.0);
    printf("House File ID %d has been created",h);
    h++;
}
mysql_free_result();
Wouldn't that do the same...
Find
Reply
« Next Oldest | Next Newest »


Forum Jump:


Users browsing this thread: 1 Guest(s)
  1. SA-MP Forums Archive
  2. SA-MP Scripting and Plugins
  3. Scripting Help
  4. Help Archive