[HOW] how work Fetch()?

Bobskyflyer · September 6, 2013, 10:03pm

Hello!

I’m trying to make a custom command and I have to do a sql request (the speed of a sql request doesn’t matter , it will not be a command used often )

The request (the number of creature of an certain Id) is sent but I can’t fetch the result : i don’t know how to use Fetch() ><

Please someone can tell me how it work? i serached in the docs but i found nothing !

The code:

int Id = (int)args;
QueryResult result = WorldDatabase.PQuery("SELECT COUNT(guid) FROM creature WHERE id = %u; ", Id);

	if(!result)
		handler->SendGlobalGMSysMessage("no result");
		return 0; 

	int count = result->Fetch()[0].GetUInt32(); 

	if(count = 0 || !count)
		handler->SendGlobalGMSysMessage("no result but request sent");

	char* result_count = (char*)count;
	handler->SendGlobalGMSysMessage(result_count);

PS: sorry for my awful english …

Rochet2 · September 6, 2013, 10:59pm

Notice that

if(count = 0 || !count)
You should use count == 0

Also !count is the same as count == 0

I think the fetch part is right, but other parts of the code have some wrong things.

I did not test this code, but I think it should work:

static bool myCommand(ChatHandler* handler, const char* args)
{
    if (!*args)                             // check that we have arguments
        return false;
    uint32 id = uint32(atoll((char*)args)); // convert arguments to long long int and from there to uint32
    if (!id)                                // check that id is not 0 (was a number and not 0)
        return false;

    QueryResult result = WorldDatabase.PQuery("SELECT COUNT(guid) FROM creature WHERE id = %u;", id);

    if(!result)                             // check that we have a result
    {
        handler->SendGlobalGMSysMessage("no result");
        return true;
    }

    uint32 count = result->Fetch()[0].GetUInt32();

    if(!count)                              // check that count is not 0
        handler->SendGlobalGMSysMessage("no result but request sent");

    std::ostringstream oss;
    oss << "Result: " << count;             // insert count (number) to a string IE: Result: 23454
    handler->SendGlobalGMSysMessage(oss.str().c_str()); // convert the stringstream to a string and a const char* that fits the argument needed
    return true;                            // notice that return false gives error while return true shows no msg to player (success)
}

Aokromes · September 6, 2013, 11:00pm

— Canned message start —

This thread is not related to the official Trinity codebase and was moved to the Custom Code section.

Please read the stickies next time.

— Canned message end —

Bobskyflyer · September 7, 2013, 10:49am

Thx for your answer nwo the command give a result , but the fetch don’t work , i did the SQL request with SQLyog , it return 51 , and with the command in game , it said “no result but request sent Result:0”

The problem donn’t come of my request , i will try to change the row of fetch or do a sql request without variable (maybe the args is mofified during the conversion?)

Rochet2 · September 7, 2013, 11:08am

Not exactly sure how it works with Count.

You could try using a normal SQL to get the rows and then a C++ method to get the row count, not SQL.

uint32 count = result->GetRowCount();

Ascathor · September 7, 2013, 12:54pm

if (!result) // check that we have a result
{
    handler->SendGlobalGMSysMessage("no result");
    return true;
}

uint32 count = (*result)[0].GetUInt32();

Bobskyflyer · September 7, 2013, 2:07pm

Thx now the counter work well , but i still have problems with fetch()

Code:

It return the correct number of guid but not the guid ^^’

QueryResult result = WorldDatabase.PQuery("SELECT guid FROM creature WHERE id = %u;", id);

    if(!result)                             // check that we have a result
    {
        handler->SendGlobalGMSysMessage("no result");
        return true;
    }
    uint32 count = result->GetRowCount();

    if(!count)                              // check that count is not 0
         handler->SendGlobalGMSysMessage("no result but request sent");


     for(uint32 i=0;i<=count;i++)
     {
         handler->SendGlobalGMSysMessage("Entering in the for");
	 uint32 guid = result->Fetch()[(int)i].GetUInt32();
	 std::ostringstream msg_guid;
	 msg_guid << "GUID n°: " << guid;
	 handler->SendGlobalGMSysMessage(msg_guid.str().c_str());
     }

    std::ostringstream oss;
    oss << "Result: " << count;
    handler->SendGlobalGMSysMessage(oss.str().c_str());
    return true;

Ascathor · September 7, 2013, 2:44pm

Alright, I see what you try to do. I’ll do such a command and PR it. Expect it by evening.

Rochet2 · September 7, 2013, 2:49pm

Btw. I think SendGlobalGMSysMessage shows to all GMs ingame. Use PSendSysMessage if this isn’t intended.

Your code doesnt work cause the number after fetch is the column ID. It starts from 0. Example:

// 0 1 2 3 4 SELECT entry, name, subname, damage, armor FROM creature_template WHERE entry = 123;

QueryResult result = WorldDatabase.PQuery("SELECT guid FROM creature WHERE id = %u;", id);

if(!result)                             // check that we have a result
{
    handler->SendGlobalGMSysMessage("no result");
    return true;
}
uint32 count = result->GetRowCount();   // get row count

if(!count)                              // check that count is not 0
    handler->SendGlobalGMSysMessage("no result but request sent");
    
handler->SendGlobalGMSysMessage("Entering in the do loop");
do                                      // do-while loop
{
    uint32 guid = result->Fetch()[0].GetUInt32();   // 0 since guid is the first column in the returned value list in the SQL
    std::ostringstream msg_guid;
    msg_guid << "GUID n°: " << guid;
    handler->SendGlobalGMSysMessage(msg_guid.str().c_str());
    // NextRow is called in the end to move onto the next SQL row!
} while (result->NextRow());            // stops when there is no next row. (NextRow() returns false)
    
std::ostringstream oss;
oss << "Result: " << count;
handler->SendGlobalGMSysMessage(oss.str().c_str());
return true;

Machiavelli · September 7, 2013, 2:54pm

uint32 guid = result->Fetch()[(int)i].GetUInt32();

There’s no need to do (int)i since i is uint32, and the array index only supports unsigned numbers.
is not a row indicator, yet it indicates the column of your resultset. with your query you will only have 1 column, so any value of i over 0 is invalid.

So you would want to do something like:

do

{

Fields* fields = result->Fetch();

[SIZE=14px] uint32 guid = fields[0].GetUInt32();[/SIZE]

} while (result->NextRow())

or alternatively:

do

{

uint32 guid = (*result)[0].GetUInt32();

} while (result->NextRow())

I might have the syntax wrong for the second example, but I’m too lazy to look in the source atm.

In doubt, consult src/server/shared/Database/QueryResult.[h/cpp]

Ascathor · September 7, 2013, 3:01pm

It’s correct.

Bobskyflyer · September 7, 2013, 3:48pm

Ok! i didn’t know about NextRow() , i tried de code of rochet2 and machiavelli and both doesn’t work :-/ , the command do the right loop (confirmed with a message at each loop) but it don’t show any guid .

Thx again for all ^^

Ascathor · September 7, 2013, 4:50pm

Alright, as the command did not pass the “It may be pulled” because of it not being useful enough (using a SQL client may be more useful),

here is the diff:

http://pastebin.com/C0gr8M1E

Works for rev. e8675dd4ed

Bobskyflyer · September 7, 2013, 6:37pm

Yay! thx a lot , your script work perfectly , it seems that the system of statements work better than an normal request /emoticons/default_biggrin.png

Thanks a lot for all your advice the post is resolved /emoticons/default_smile.png

Machiavelli · September 8, 2013, 12:37am

Yeah, lol, because it’s not written to output anything. It was an example on how to use Fetch() so you could put it in your script. I´m not going to rewrite your entire script for you.

Anyway glad it´s resolved.

Bobskyflyer · September 8, 2013, 12:46pm

I wrote an output for your code , it doesn’t work anyway , same for rochet , that’s strange , i’m still searching why ^^