[HELP] Fetching info from the DB for use in script

[Auntie Mangos]

Check out my auto-announcer for some database query examples.

http://getmangos.eu/community/viewtopic.php?id=5174

[kingdutka]

Basically, I need to be able to read in values from a given table for use in a custom script, but I have no clue how C++ and MySQL are working together...

For example, how would I make the following work (used in a custom *.cpp):

       std::stringstream ss;
   ss << "You spent " << {SELECT cost FROM telenet WHERE id=1} << " on your teleport."
   _Creature->MonsterWhisper(ss.str().c_str(), player->GetGUID());

I am thinking to make my own custom table added to the SD2 database. 'Telenet' would be my custom table and 'cost' and 'id' would be a couple of the columns.

I have been asking for help on this in both the UDB Forums and the SD2 forums, but not getting anywhere at all on either... I even went so far as to ask on the MySQL forums and still get nothing...

[kingdutka]

Making progress!!

This code compiles but crashes MaNGOS:

QueryResult* pResult = SD2Database.PQuery("SELECT Cost,Name FROM telenet WHERE ID=1");
   Field* pFields = pResult->Fetch();

   std::stringstream ss;
   ss << "You spent " << pFields[0].GetUInt32() << " on your teleport to " << pFields[1].GetString() << std::endl;
   _Creature->MonsterWhisper(ss.str().c_str(), player->GetGUID());

While this code compiles and does not crash but...

QueryResult* pResult = SD2Database.PQuery("SELECT Cost FROM telenet WHERE ID=1");

   std::stringstream ss;
   ss << "You spent " << pResult << " on your teleport." << std::endl;
   _Creature->MonsterWhisper(ss.str().c_str(), player->GetGUID());

It prints this:

"You spent 000000 on your teleport."

[NeWbY]

Do error checking eh?

pFields is a pointer, doing ss << pResult will end up showing the address pFields points to. In this case 0x00000, a null pointer, meaning the query failed.

The first case crashes beause pFields failed and doing pFields[0].<method> refers to invalid memory access.

[DasBlub]

don't post the same question in two forums... http://www.scriptdev2.com/help-fetching-info-t4716.html

[kingdutka]

Do error checking eh?

pFields is a pointer, doing ss << pResult will end up showing the address pFields points to. In this case 0x00000, a null pointer, meaning the query failed.

The first case crashes beause pFields failed and doing pFields[0].<method> refers to invalid memory access.

Ok.... But that doesn't help me fix the problem at all! I am VERY new to this kind of code and am asking for help figuring out what to write...

don't post the same question in two forums... http://www.scriptdev2.com/help-fetching-info-t4716.html

What's wrong with posting on 2 VERY DIFFERENT forums?? That's just ridiculous!

[DasBlub]

Ok.... But that doesn't help me fix the problem at all! I am VERY new to this kind of code and am asking for help figuring out what to write...

it means that your sql query returned no rows, therefore there was no row to fetch -> you get NULL pointer for pFields.

so run once your sql query from hand (on the mysql console) and check if you get a result.

What's wrong with posting on 2 VERY DIFFERENT forums?? That's just ridiculous!

'very different'? you're joking? sd2 is for mangos, most (if not all) from sd2 forum are registered here too. furthermore it's bad because then in one forum they might find a solution and in the other one they're still searching and know nothing about it.

[kingdutka]

Ok, 'VERY' was an overstatement

I don't wanna turn this into a debate about MaNGOS vs. SD2 cuz that would essentially be thread hijacking would it not?

So, I have confirmed the issue is with my query returning nothing, but I don't understand why...

I even went so far as to:

QueryResult* pResult = SD2Database.PQuery("SELECT * FROM script_waypoint");
   if(!pResult){
       ss << "Couldn't access DB!";
       _Creature->MonsterWhisper(ss.str().c_str(), player->GetGUID());
       return;
   }

And that's what my creature is saying... "Couldn't access DB!"

I know my query is correct because I can run it in SQLyog and it is returning the exact field I am looking for...

Oh, and I have this at the top of my script:

#include "Database/DatabaseEnv.h"
extern DatabaseType SD2Database;

[DasBlub]

have once a look at this source: http://scriptdev2.svn.sourceforge.net/viewvc/scriptdev2/system/system.cpp?view=markup

look at SystemMgr::LoadVersion, just try this once (with the exactly same sql statement).

[kingdutka]

have once a look at this source: http://scriptdev2.svn.sourceforge.net/viewvc/scriptdev2/system/system.cpp?view=markup

look at SystemMgr::LoadVersion, just try this once (with the exactly same sql statement).

QueryResult* pResult = SD2Database.PQuery("SELECT version FROM sd2_db_version LIMIT 1");

   if(!pResult){
       ss << "Couldn't access DB!";
       _Creature->MonsterWhisper(ss.str().c_str(), player->GetGUID());
       return;
   }

Monster says: "Couldn't access DB!"

I just don't understand it... I have the include at the top of my script, it's compiling, and I was sure to copy the new compiled files to my server folder...

EDIT:

Does it matter if I have the code embedded in a 'case'?

I stuck all my relevant code into 'pastebin'. This link is good for one month or until the issue has been resolved and I delete the post:

http://pastebin.com/m18d16145

[kingdutka]

The only thing I've been able to come up with so far is another glitch...

With the current code above, ( Returning "Cannot connect to DB!" for my monster whisper), if I close the dialog window and re-open it, then talk to him again, he says:

"Cannot connect to DB!Cannot connect to DB!"

Every time I close the window and try again, another one gets added:

"Cannot connect to DB!Cannot connect to DB!Cannot connect to DB!Cannot connect to DB!"

So although the original problem with not being able to connect to the DB is still the most prevailant, I now need to know how to clear out the memory of that 'ss' variable:

QueryResult* pResult = SD2Database.PQuery("SELECT cost FROM telenet");

   if(!pResult){
       ss << "Cannot access DB!";
       _Creature->MonsterWhisper(ss.str().c_str(), player->GetGUID());
       ss << "";
       return;
   }

This does not work...

As you can see, I am definitely a C++ NOOB, but I am learning a lot here!

Again let me re-iterate, I STILL cannot get "pResult" to return any values which is the single most important thing I need to do here... Once I get that, I think I can figure out the rest and quit buggin everyone

PS: I updated the code at that pastebin link in my last post to include the ENTIRE cpp file. The test code starts at line 1832.

[Lynx3d]

You made "ss" a global variable, no surprise it keeps the garbage from previous calls...very bad idea.

[kingdutka]

You made "ss" a global variable, no surprise it keeps the garbage from previous calls...very bad idea.

I figured that's what it was, but I only did so in attempt to cut down on repeat code... I can fix that no prob.

Still can't figure out why the DB won't give me any results tho... I've looked at every bit of code I could find and mine is pretty much identical..

I even tried copy/pasting code from system.cpp, overwriting my query, and still no go...

[NeWbY]

Look at the code of SD2Database.PQuery and analyse why it could return fail. From there anaylse the functions used in the pQuery function.

Also, it seems to me you don't have much knowledge of C++ yet, so I'd suggest to read some C++ tutorials first.

[kingdutka]

Seriously guys, I have this all down to the very utmost simplest form and it is still returning no results... Check out this code and tell me if you see ANYTHING wrong with it...

http://pastebin.com/m6a686c38

I honestly can't figure out for the life of me why my creature is always whispering "Cannot access DB!"....

[kingdutka]

Seriously guys, I have this all down to the very utmost simplest form and it is still returning no results... Check out this code and tell me if you see ANYTHING wrong with it...

http://pastebin.com/m6a686c38

I honestly can't figure out for the life of me why my creature is always whispering "Cannot access DB!"....

The last 'pastebin' script I posted DOES work!! Had anyone actually taken a few seconds to test it out for me, they could've confirmed it IS in fact working!

So how did I figure this out? Dumb luck! For some reason, my scriptdev2.config file got overwritten with a default one and it wasn't actually accessing the DB!!!

As soon as I changed the config file from:

ScriptDev2DatabaseInfo = "127.0.0.1;3306;mangos;mangos;scriptdev2"

To:

ScriptDev2DatabaseInfo = "127.0.0.1;3306;root;mangos;scriptdev2"

Voila!!

Ok, so now I feel like an idiot for that, but now I can get the rest of my code done and get my new release of the mod out!

[FeRkEl]

so i guess your scriptdev2 database can't be accessed by the mangos -sql user

Lg

[xeross155]

Depends if they're the same username, but it uses a different object for the ScriptDev database.

[NeWbY]

Error checking dude. Learn C++