Reading data from DB

[Criimson]

Hello everyone

So I am trying to learn how to read data from the DB and use it as a check in the bot ai.

I looked over other areas where data is being read and I thought I had it, but after trying several different ways I have decided to once again ask for some help.

Here is the code:

if(IsEffectInSpell(selectedBotSpell.SpellId, SE_Rune)){
			std::string errorMessage;
			char* Query = 0;
			char TempErrorMessageBuffer[MYSQL_ERRMSG_SIZE];
			MYSQL_RES* DatasetResult;
			MYSQL_ROW value;

			if(!database.RunQuery(Query, MakeAnyLenString(&Query, "SELECT values FROM bot_variables WHERE bot_variable = 'auto_rune';", TempErrorMessageBuffer, &DatasetResult))) {
				errorMessage = std::string(TempErrorMessageBuffer);
			}
						
			value = mysql_fetch_row(DatasetResult);
			int iAR = atoi(value[0]);
			if (iAR == 0){
				continue;
			}
		}

The table is very simple.
TABLE : bot_variables
Column 1: bot_variable
Column 2: values
Column 3: notes

I had very little trouble learning how to write to the DB, but extracting the information is giving me problems. From what I can tell the problem is arising from one of two things.
Option 1: values are stored as a text string and I am trying to use it as an int. However, I also tried running a check based on a string using if (sAR == '0'){ and that also didn't work.

Which leads me to think that
Option 2: I am not correctly grasping how
MYSQL_RES* DatasetResult;
MYSQL_ROW value;
mysql_fetch_row(DatasetResult);
works.

Any help would be appreciated. Good thing about programming is once you learn how something works you can do it as long as you keep practicing.

Criimson

[Congdar]

it's probly your semi-colon in your SELECT string. btw, if this call is made a lot, it will slow down your server. if possible, try to make as few db calls as possible and store information in memory/bot object
EDIT:
the closing right parin for MakeAnyLenString should go after the ending quote.

[Criimson]

Well I tried your suggestions and it is still crashing zone when it is called.

After looking through mysql.h definitions I see:

MYSQL_FIELD *fields;
MYSQL_DATA *data;
MYSQL_ROWS *data_cursor;

MYSQL_ROWS - Is to pull up associated rows

To read a single cell could I use the MYSQL_FIELD or MYSQL_DATA?
Seems there should be a way.

Something like:

uint32 len_query = MakeAnyLenString(&query, "SELECT values FROM bot_variables WHERE bot_variable = 'auto_rune'");
	
	if (database.RunQuery(query, len_query, errbuf, &result)) {
	     safe_delete_array(query);
						
	value = mysql_fetch_field(result);
	if (value == "0"){
		continue;
             }

But alot of this type of coding is a learning experience for me.

Criimson

[Derision]

It would be much easier to do this via the rules system, e.g. go to common/ruletypes.h and goto around line 354 where you will see this:

#ifdef BOTS
RULE_CATEGORY ( Bots )
RULE_REAL ( Bots, BotManaRegen, 2.0 ) // Adjust mana regen for bots, 1 is fast and higher numbers slow it down 3 is about the same as players.
RULE_BOOL ( Bots, BotFinishBuffing, false ) // Allow for buffs to complete even if the bot caster is out of mana.  Only affects buffing out of combat.
RULE_INT ( Bots, CreateBotCount, 150 ) // Number of bots that each account can create
RULE_INT ( Bots, SpawnBotCount, 71 ) // Number of bots a character can have spawned at one time, You + 71 bots is a 12 group raid
RULE_BOOL ( Bots, BotQuest, false ) // Optional quest method to manage bot spawn limits using the quest_globals name bot_spawn_limit, see: /bazaar/Aediles_Thrall.pl
RULE_BOOL ( Bots, BotGroupBuffing, false ) // Bots will cast single target buffs as group buffs, default is false for single. Does not make single target buffs work for MGB.
RULE_BOOL ( Bots, BotSpellQuest, false ) // Anita Thrall's (Anita_Thrall.pl) Bot Spell Scriber quests.
RULE_CATEGORY_END()
#endif

Then add:

RULE_BOOL (Bots, AutoRune, true)

just after RULE_BOOL ( Bots, BotSpellQuest, false )

(this is assuming you want AutoRune to default to true.)

Then at the point in the code were you want to test the value of this rule:

if(RuleB(Bots, AutoRune))
{
      // Do AutoRune stuff
}

The first time you go in game after adding your new rule to the source and recompiling, if you issue the command #rules store, it will write the current values into the database, then if you go into your rule_values table and search for Bots:AutoRune you can set it to false if you decide you want to disable that feature.

[Derision]

Things to be aware of if you do want to read the database directly:

if database.RunQuery returns false, your DatasetResult will be NULL, i.e. invalid, so all you should do is send some debugging info to the zone log,
free the query if you allocated the memory with MakeAnyLenString, and then bail out, e.g.:

printf("Query: %s failed with error %s\n", Query, TempErrorMessageBuffer);
fflush(stdout);
safe_delete_array(Query);
return;

Also, if the query is successful, it can still return zero rows, so you need to call mysql_num_rows(DatasetResult) and check it is >0 before calling mysql_fetch_row(),
and/or check mysql_fetch_row() doesn't return NULL when you call it before attempting to process the returned row.

[Criimson]

Thank you for all of the help. Call me a nerd, but I love learning how to code better. Really should have made it a career.
From my understanding though, aren't the rulesets something that cannot be changed on the fly? Wouldn't any changes require a reboot and be server wide? Now it is true that my primary function is for those that run a solo server. However, I know there are many servers that allow bots and I am trying to impliment a system in which each player can choose how their bots behave.
I chose to learn how to read info using autorune because I began noticing that my enchanter was casting rune on me so much that it was burning through mana at these lower levels. And to be honest, its rune wasn't helping all that much. However, when the bot begins to gear up it would have more mana to spend. I have planned other checks and such, but I would like to be able to create a system that would allow player choice that doesn't require a reboot with each change.

Maybe I am wrong on this though. I have never programmed using client/server code before and am learning by reading the code that is there.

Criimson

[Criimson]

Couldn't I do something like:

ifdef PLAYERBOTS
RULE_CATEGORY ( PlayerBots )
RULE_BOOL ( PlayerBots, AutoRune, false ) // Allows Enchanter to automatically cast Rune line of buffs
RULE_INT ( PlayerBots, HybridHeals, 20 ) // Percentage at which hybrids will begin to cast heals on group
RULE_INT ( PlayerBots, ClericDDs, 75 ) // Percentage at which clerics will stop casting DDs and save mana for heals
RULE_CATEGORY_END()
#endif

And add that to the zone.cpp so the rules were reset when zoning?
Then have the check at cast time...that would allow only a read when zoning and not lag the server very much?

Criimson

[Derision]

If you want something that is bot specific, you would probably be best adding columns to the bots table, e.g. an autorune column, so you could set it on a per bot basis,
via something like #bot autorune <botname> [on|off].

You would then add a member variable, bool AutoRune to the bot class and set this to true/false when each bot is spawned based on what the autorune column for that
bot is set to in the database.

Then in your #bot autorune command you would need to execute an UPDATE bots set autorune = 0/1 where BotID/BotName = whatever, and also set the AutoRune member
appropriately for that particular bot to have it take effect immediately.

[Criimson]

Hmm you did show me a flaw in my table structure. I didnt add a character column.

I was doing something like that with #bot autorune on/off

//Set if chanters automatically cast rune
	if(!strcasecmp(sep->arg[1], "autorune")) {

		std::string errorMessage;
		char* Query = 0;
		char TempErrorMessageBuffer[MYSQL_ERRMSG_SIZE];

		if (!strcasecmp(sep->arg[2], "off")){	
			if(!database.RunQuery(Query, MakeAnyLenString(&Query, "UPDATE `bot_variables` SET `values`= 0 WHERE bot_variable = 'auto_rune';"))){
					errorMessage = std::string(TempErrorMessageBuffer);
			}
		}
		else if (!strcasecmp(sep->arg[2], "on")){	
			if(!database.RunQuery(Query, MakeAnyLenString(&Query, "UPDATE `bot_variables` SET `values`= 1 WHERE bot_variable = 'auto_rune';"))){
					errorMessage = std::string(TempErrorMessageBuffer);
			}
		}
		else {
			c->Message(0,"Correct input is #bot autorune (on/off)");
		}
		return;
	}

This code works perfectly. Then in the botspellsai.cpp under case SpellType_Buff:

I was adding a check: if(IsEffectInSpell(selectedBotSpell.SpellId, SE_Rune))

and this is where it all broke down.

I am going to comment out the code and work on something else in the code for a bit. Restructured the bot_variables table so it can be set on a character by character basis. But my brain is starting to hurt.

Criimson

[Criimson]

Originally Posted by Derision

If you want something that is bot specific, you would probably be best adding columns to the bots table, e.g. an autorune column, so you could set it on a per bot basis,
via something like #bot autorune <botname> [on|off].

I am doing my best to be very low impact on the current DB/emu structure. That is why I was leaning towards a seperate table. I figure a person could add it and then use it or not without affecting a stock build.

Criimson

[lerxst2112]

The problem with striving to be low impact is that the code will move forward and your patches will no longer be relevant and they will be increasingly difficult for people to use or they will be forgotten.

If you want people to be able to use this for more than a short time, your best bet is to do things "the right way" with the goal of getting them accepted into the official codebase. If your intent is to have something set on a bot specific basis then adding it to each bot in the table seems like the right thing to me. Either way you do it, it will require a database update to be applied.

[Criimson]

Originally Posted by lerxst2112

The problem with striving to be low impact is that the code will move forward and your patches will no longer be relevant and they will be increasingly difficult for people to use or they will be forgotten.

If you want people to be able to use this for more than a short time, your best bet is to do things "the right way" with the goal of getting them accepted into the official codebase. If your intent is to have something set on a bot specific basis then adding it to each bot in the table seems like the right thing to me. Either way you do it, it will require a database update to be applied.

I totally agree with this. However, I am not sure that doing it this way is "the wrong way". Right now I am testing reading and writing to the DB and trying to do it in a way that allows for many players to have various settings.

How it is finalized is yet to be determined.

I did figure out how to read. As I was looking at the command to write I was hit with inspiration. I love it when that happens.

std::string errorMessage;
char* Query = 0;
uint32 bot_id = this->GetBotID(); 
int runeCheck = 0;

runeCheck = (database.RunQuery(Query, MakeAnyLenString(&Query, "SELECT `auto_rune` FROM `peq`.`bot_variables` WHERE `botID` = '%i';", bot_id)));
//Say("auto_rune is '%i'", runeCheck);
safe_delete_array(Query);
if (runeCheck == 0){
	continue;
}

This worked well.

Criimson

[lerxst2112]

I'm no expert, but I believe all that is telling you is that the query succeeded, not the actual value in the table. If you were to change the value in the database 0 you would still get a true value from that code.

There are various examples throughout the code on how to read from the database and parse the results. Look at Bot::GetPetSaveId() for a pattern that should work for you.

[Criimson]

Ah thank you
It might have been awhile before I noticed it wasn't retrieving the data. I tested it by it starting at 1 and changing it to 0 using a Say("<>") as a test. It seemed to work, but actually I hadn't placed the clear query in the code and so the 1 becoming 0 was actually a good query followed by a failed query :P

I'll check out that function.

Criimson