Use database queries in source code?

[Furniture]

Hi, I need to know how to run database queries in my source code. I am trying to remove trade between characters who have different "expansion" values in my character_vars table as I made per here: http://www.eqemulator.net/wiki/wikka.php?wakka=CustomCharacterData

Here is my code:

void Client::Handle_OP_TradeRequest(const EQApplicationPacket *app)
{
if (app->size != sizeof(TradeRequest_Struct)) {
LogFile->write(EQEMuLog::Error, "Wrong size: OP_TradeRequest, size=%i, expected %i", app->size, sizeof(TradeRequest_Struct));
return;
}


// Client requesting a trade session from an npc/client
// Trade session not started until OP_TradeRequestAck is sent

BreakInvis();

// Pass trade request on to recipient
TradeRequest_Struct* msg = (TradeRequest_Struct*) app->pBuffer;
Mob* tradee = entity_list.GetMob(msg->to_mob_id);



if (tradee && tradee->IsClient()) { {
char *query = 0;
MYSQL_RES *result;
MYSQL_ROW row;
char errbuf[MYSQL_ERRMSG_SIZE];
int myExpansion = -1;
int tradeeExpansion = -2;

MakeAnyLenString(&query, "SELECT %s FROM character_vars WHERE character_id = %i", expansion, AccountID());
if (database.RunQuery(query, strlen(query), errbuf, &result))
{
safe_delete_array(query);
row = mysql_fetch_row(result);
mysql_free_result(result);
myExpansion = (atoi(row[0]));
}

MakeAnyLenString(&query, "SELECT %s FROM character_vars WHERE character_id = %i", expansion, tradee->CastToClient()->AccountID());
if (database.RunQuery(query, strlen(query), errbuf, &result))
{
safe_delete_array(query);
row = mysql_fetch_row(result);
mysql_free_result(result);
tradeeExpansion = (atoi(row[0]));
}

if (myExpansion != tradeeExpansion)
{
return;
}










tradee->CastToClient()->QueuePacket(app);
}

This compiles fine but crashes the zone when a trade initiates. Can anybody help me fix this or even know if there is an easier way I could do this?

[demonstar55]

Run this so someone can help further. (since it is a non default table)
DESCRIBE `character_vars`;

EDIT:

Quick assumption

MakeAnyLenString(&query, "SELECT expansion FROM character_vars WHERE character_id = %i", AccountID());

[Furniture]

Field Type Null Key Default Extra
character_id int(11) NO PRI \N
buff_balance int(11) YES 0
port_balance int(11) YES 0
expansion int(11) YES 0

[Furniture]

I will try that and see how it goes, thanks

[Furniture]

Still no luck, zone crashes on character to character trade once you click an item or plat on somebody else. but compiled fine

[Furniture]

To make it easier here is the code that I added from the above: Mob* tradee = entity_list.GetMob(msg->to_mob_id);



if (tradee && tradee->IsClient()) {
char *query = 0;
MYSQL_RES *result;
MYSQL_ROW row;
char errbuf[MYSQL_ERRMSG_SIZE];
int myExpansion = -1;
int tradeeExpansion = -2;

MakeAnyLenString(&query, "SELECT expansion FROM character_vars WHERE character_id = %i", CharacterID());
if (database.RunQuery(query, strlen(query), errbuf, &result))
{
safe_delete_array(query);
row = mysql_fetch_row(result);
mysql_free_result(result);
myExpansion = (atoi(row[0]));
}

MakeAnyLenString(&query, "SELECT expansion FROM character_vars WHERE character_id = %i", CharacterID());
if (database.RunQuery(query, strlen(query), errbuf, &result))
{
safe_delete_array(query);
row = mysql_fetch_row(result);
mysql_free_result(result);
tradeeExpansion = (atoi(row[0]));
}

if (myExpansion != tradeeExpansion)
{
return;
}

[Furniture]

here is my newest code: doesnt crash zone, but trade wont initiate between players with same expansion number:

Mob* tradee = entity_list.GetMob(msg->to_mob_id);



if (tradee && tradee->IsClient()) {
char *query = 0;
MYSQL_RES *result;
MYSQL_ROW row;
char errbuf[MYSQL_ERRMSG_SIZE];
int myExpansion = -1;
int tradeeExpansion = -2;
int myID = CharacterID();
int tID = tradee->CastToClient()->CharacterID();

if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT expansion FROM character_vars WHERE character_vars.character_id=myID"), errbuf, &result)) {
safe_delete_array(query);
row = mysql_fetch_row(result);
mysql_free_result(result);

myExpansion = (atoi(row[0]));

}

if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT expansion FROM character_vars WHERE character_vars.character_id=tID"), errbuf, &result)) {

safe_delete_array(query);
row = mysql_fetch_row(result);
mysql_free_result(result);
tradeeExpansion = (atoi(row[0]));
}

if (myExpansion != tradeeExpansion)
{
return;
}










tradee->CastToClient()->QueuePacket(app);
}

[rencro]

Are the proper user ID's being grabbed?

could try:


if (myExpansion != tradeeExpansion)
{
Message(13, "The trade is not good.");
Message(13, "The value of myID is %i", myID);
Message(13, "The value of tID is %i", tID);
return;
}

[Furniture]

I will try that out and report my findings, cant do any more testing til tonight, thank you i appreciate the help

[Uleat]

Why don't you just use 'ClientVersion' as a comparison?

you shouldn't need to access the db for this info since it a Client class property already.


if (tradee && tradee->IsClient() && (this.ClientVersion == tradee->CastToClient()->GetClientVersion()))
{ ... }


..or something like that...

[Furniture]

This is going to be used on a progression server where there is no trade between characters in different expansions. I'm using the custom expansion variable so I can change the value per character in perl quests and get the value from the source to restrict trade(the part which i'm stuck on)

[Uleat]

Change myID and tID at the end of the queries to %i, then add them post-quote.


EDIT: You are probably translating the id's to zero in those...

[rencro]

I already have the custom char code and table from drajor, and I compiled your code into mine and it all works..

When trade is denied, the proper IDS are echoed.. I tried first with different expansion settings for both chars, and it gave me the Denied message as expected, then i made both chars expansion values equal in the db and trade went through, no crash.. Nice work on this...


if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT expansion FROM character_vars WHERE character_vars.character_id=%i", myID), errbuf, &result)) {

AND

if (database.RunQuery(query, MakeAnyLenString(&query, "SELECT expansion FROM character_vars WHERE character_vars.character_id=%i", tID), errbuf, &result)) {

AND for giggles

if (myExpansion != tradeeExpansion)
{
Message(13, "Trade denied.");
Message(13, "Value of myID is %i", myID);
Message(13, "Value of tID is %i", tID);
return;
}
Message(13, "Yes!! And it counts.");
tradee->CastToClient()->QueuePacket(app);
}

[Uleat]

This would be a little more involved, but you could add the expansion variable to the Client class, along with supporting code, and then access
the memory reference as needed instead of doing a db call each time a trade is initiated.

The world->Client already has access to expansions, so you would need to write code to modify it in zone->Client..that way the only db call is
when the value actually changes.

[Furniture]

I tested this out again and its still not working. Did you use Uleats changes?

[Furniture]

When I trade with someone with the same expansion variable, I get the messages fromMessage(13, "Trade denied.");
Message(13, "Value of myID is %i", myID);
Message(13, "Value of tID is %i", tID);
return;

So the ID's are getting retrieved right, but for some reason it looks like its not updating the variables properly from the db

[Furniture]

ahh nm, just saw you did use uleats changes, gonna try now