PreparedQueryResult behaviour

Hello everyone,

I am trying to understand why I get garbage result from the following code:

PrepareStatement(CHAR_SEL_BY_PERSONAL_RATING,
"SELECT art.arenaTeamId, c.name, am.personalRating "
"FROM arena_team art "
"INNER JOIN arena_team_member am ON art.arenateamid = am.arenateamid "
"INNER JOIN characters c ON am.guid = c.guid "
"WHERE art.type = ? "
"AND am.personalRating > 0 "
"ORDER BY am.personalRating DESC "
“LIMIT ?”, CONNECTION_SYNCH);

So this query will return top players by personal rating in 2v2, 3v3 or 5v5 arena, and the code that calls it:

CharacterDatabasePreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_SEL_BY_PERSONAL_RATING);
stmt->setUInt8(0, arenaTeamType);
stmt->setUInt16(1, TOP_PLAYER_LIMIT);

    PersonalRatingInfoContainer results;

    if (PreparedQueryResult result = CharacterDatabase.Query(stmt))
        {
            do
            {
                Field* fields = result->Fetch();
                uint32 arenaTeamId = fields[0].GetUInt32();
                std::string charName = fields[1].GetString();
                uint32 personalRating = fields[2].GetUInt32(); // RETURNS GARBAGE?

            results.push_back(PersonalRatingInfo(arenaTeamId, charName, personalRating));
            } while (result->NextRow());
        }

    return results;

I don’t understand why I get garbage result for “personalRating”, for arenaTeamId and charName it seems fine, also running the query directly in DB will return the expected results.

Thank you!

You need to be very careful of the data types you use in the code. They need to match the data base structure.

The personalrating field in database is smallint unsigned. That is equal to uint16, not uint32. I would suspect that the wrong data type you use there is the reason.

Make sure all other data types also match properly. I am unsure, but I think switching on WITH_COREDEBUG in cmake would add logging to the core so that the core would print when you try to use wrong data types.

Still doesn’t work, I really don’t understand. The only way I could get over this was to switch to QueryResult and CharacterDatabase.PQuery( … ). The exact same query works fine with these 2.

PS: I also have another table (not trinity created) where I have a smallint unsigned column, and getUint32() works fine on this one.

It works with PQuery because when you do that, mysql server gives you all results as text (for example “16000” - 5 characters, 5 bytes) so using GetUInt32() just works but when you use prepared statements, it sends data in binary format that matches the column type (16000, 2 bytes, size of a uint16), so when you attempt to use GetUInt32() on that, you are reading 4 bytes, which means 2 bytes of your actual result and 2 bytes of random garbage.

The CMake option is called WITH_STRICT_DATABASE_TYPE_CHECKS (you will get runtime errors in logs when you attempt to use wrong Get function)