[SOLVED] TrinityCore LoginDatabase.PQuery to another Host

[Visa]

Hi

Suppose I wanted to connect to a database like so:

 LoginDatabase.PQuery("SELECT `vp`, `dp` FROM `cms`.`account_data` where `id`=%u", player->GetSession()->GetAccountId());

Which will connect to the database "cms" regardless of the fact that it's a Login query (normally auth)

Suppose I wanted to use this on a completely different hosted database that isn't defined in the worldserver.conf, is such a thing even possible?
Where would I define the Hostname, user, pass, etc?

 

[Lightning Blade]

Actually if it's on another host I'm not sure // edited

 

[Jameyboor]

Yeah but it would require quite some modifications.
There are 3 default DatabaseWorkerPools that you can use within TC : LoginDatabase, CharacterDatabase and WorldDatabase.
If you want to be able to perform queries on another DB or another connection you will need to add new DatabaseWorkerPools for this connection.

 

[Tommy]

It is best to have that particular table in your account database let your CMS connect to it instead the other way around. I don't recommend allowing the server to connect to your web database, considering your CMS should already connect to the account and character DB.

 

[Visa]

I'm using 2 different server hosts / 2 different databases.

Server #1 -> Auth, Characters, World (VPS)
Server #2 -> Forums, Website (Shared Webhost)

Database #1 -> Auth, Characters, World, Website
Database #2 -> Forums

Server #1 = Database #1 = Same Machine
Server #2 = Database #2 = Same Machine

Server #1 (connects to) -> Database #1 (Successful)
Server #2 (connects to) -> Database #1 (Unsuccessful)

Reason I have Website database inside the same database as the VPS is because "account_data" is the output table for Fusion CMS for votes/donate points, as it does not go into account table. So I can't have it on another server DB else I can't connect to it and fetch data from it in-game.

 

[Tommy]

I'm using 2 different server hosts / 2 different databases.

Server #1 -> Auth, Characters, World (VPS)
Server #2 -> Forums, Website (Shared Webhost)

Database #1 -> Auth, Characters, World, Website
Database #2 -> Forums

Server #1 = Database #1 = Same Machine
Server #2 = Database #2 = Same Machine

Server #1 (connects to) -> Database #1 (Successful)
Server #2 (connects to) -> Database #1 (Unsuccessful)

Reason I have Website database inside the same database as the VPS is because "account_data" is the output table for Fusion CMS for votes/donate points, as it does not go into account table. So I can't have it on another server DB else I can't connect to it and fetch data from it in-game.

Such tables that inputs values for both in-game and web usage should be inside of the auth or characters database (which ever you choose) since the CMS already connects to those tables. I don't get the implication of "So I can't have it on another server DB else I can't connect to it and fetch data from it in-game". Regardless of two different servers or connections, whatever the case, it is the logical thing to do, rather than wasting time and performance making a new DB connection that doesn't make much sense to do when it can be done a more efficient way, and a way that makes more sense.

All you have to do is create multiple variables (vote points and donation points) via account source, load it through the vote table (that should be in your auth db) when everything is loading, assign the data, and finished. Update the variables with new values when needed to keep the data updated.

 

[Visa]

Maybe I'm just really confusing myself but when you Vote all votes go into Database 'cms', Table 'account_data'. Are you saying maybe theres a way to duplicate the method so it outputs variables to BOTH account_data and an extra table in auth? like 'vp' or 'dp'

---

Currently there is no vote table in auth. All votes by default go into the CMS database table named 'account_data'

 

[Tommy]

Maybe I'm just really confusing myself but when you Vote all votes go into Database 'cms', Table 'account_data'. Are you saying maybe theres a way to duplicate the method so it outputs variables to BOTH account_data and an extra table in auth? like 'vp' or 'dp'

---

Currently there is no vote table in auth. All votes by default go into the CMS database table named 'account_data'

I'm saying the method should be changed to put the points data in your auth database via a new table. The cms is all in your "Website" database, correct? It doesn't put the vote and donation points in your auth database, right? If your CMS is on an entirely different database (including the vote and donation points table) just make a new table in your auth database (since account_data already exists server side), name it whatever and add two columns: votepoints, donationpoints. After that, require the CMS to insert the vote points or donation points in your auth database under that table instead of its own table via Website database (if that's where that goes). And no, there's not going to be an extra table, just one table for your CMS to insert/pull data to/from and for your server to update/pull data from.

 

[Visa]

I might have to end up getting a new site my php skills aren't up to par to find where to add this variable to insert it into auth.account rather than cms.account_data

Thanks for all the help, as always it's majorly appreciated!