[Guide] Accessing DB through your favorite desktop database program

[Gnarlz]

Greetings and welcome to my first (of hopefully many) guides.

First things first, I am a PC. The information I'll provide is relevant for MAC's and Linux, but the method to do so will differ.

Secondly, I loathe SQL. This brings me to the point of this guide:

Using your desktop database program to access your MySQL databases

This guide will show you how to combine the power of MySQL with the ease of use of such programs as Microsoft Access or OpenOffice's Base graphical front- ends. This guide is not intended on completely replacing the functionality of MySQL browsers such as Navicat, only the day- to- day searching, editing, and maintenance that you may do on existing records.

Although I am an Access person, I will write this guide for OpenOffice Base, as it is an open source program freely available to anyone under the GNU.

What you will need:

1. A working instance of a server, including functional MySQL database (found through this fine website)
2. MySQL ODBC connector (MySQL :: MySQL Connector/ODBC 5.1 Downloads)
3. A desktop database program (www: OpenOffice.org - The Free and Open Productivity Suite)

Step 1: Setup your server
To setup your server, follow any one of the guides on this forum. This will get you running, accessing your database through one of many MySQL browsers such as Navicat.

Step 2: Setup the MySQL ODBC connector
After downloading and installing the ODBC connector from (MySQL :: MySQL Connector/ODBC 5.1 Downloads), get to the configuration of the connector by following these steps:

1. Open up Control Panel
2. Go to Administrative Tools (Windows XP+) or access the ODBC icon (Windows 2000- and skip to step 4)
3. Select Data sources (ODBC)
4. Click on System DSN tab (for a single user machine) or User DSN (on a multiple user machine, where the others users will not access your DB)
5. Click Add
6. Scroll down and select MySql ODBC [version] Driver

At this point, you will be ready to configure your ODBC connection. You will need to repeat these steps for each database structure you have, generally Ascent_World and Logon. I will detail the steps for Ascent_World.

Configure the connector as per the below:

1. Data Source Name: Ascent_World
2. Description: Ascent_World's database connection
3. Server: localhost (IP address if remote)
4. User: root (or user name if different)
5. Password: (Enter the password you setup for MySQL)
6. Database: ascent_world
7. Click OK

You should now have a fully configured connection. Again, repeat for as many databases as you have on your machine.


Step 3: Connecting via your database software
After downloading/ installing OpenOffice (used for this example):

1. Open up OpenOffice Base
2. In the wizard, select Connect to an existing database option
3. In the "Connect" dropdown, select ODBC as the method of connection
4. Click Next
5. At the next screen, click browse to choose your source
6. Select the source you would like to connect to. In our example above, this would be Ascent_World
7. Click Finish

The system will prompt you for a location to save the database. Repeat as required for your logon database, or you can even bring the logon tables into this database.

Congratulations, you now have a user- friendly front- end to your databases.

Conclusion
You can now perform very fast edits to records, search for things on key fields, instead of the entire table, create new items, link creature_names to creature_proto and find mobs without waypoints. The "stuff" you can do with a great front end that is easy to use is nearly limitless. I have even create a user form to mimic a dollman of the characters in my tables. This allows me to edit the items they have equipped or are carrying with ease, without the strain of figuring out which slot is which.

Good luck, I hope this helps in developing your private server.

[Gnarlz]

I'm amazed nobody has found this useful, so I will post a follow up in this post to showcase some of the uses.

I wanted to find particular gameobjects in my database by name, to help build my custom starting area. I found it tedious to open the game object tables and search one- by- one for a brazier I liked, repeat the process to find a table I liked, and again for a chair, yet again for a tent, etc. So, using the OpenBase database configured per the above post, I built a simple query.

As an aside, I could have used NaviCat's query functions, but I do not like writing in the SQL language.

Let's begin:

1. Open up the database containing your World linked tables (I named mine Ascent_World, as shown in the previous post)
2. Click on "Queries" in the toolbar on the left
3. Click "Create Query in Design View" in the task bar, to the top
4. Select the table you want to use (gameobject_names for this example) and click add

You now have the graphical query builder in from of you. To the top is the gameoject_names table with all of its fields, to the bottom is a spreadsheet looking form. The bottom is where we will do most of the work here, after selecting the fields we want to display.

Double click (or drag and drop into the bottom form) the below fields:

• entry
• Type
• DisplayID
• Name

Rearrange their positions if you'd like, I am OK with the above order.

If you'd like to see a sample of the results, click the Run Query icon. I do this to confirm what fields I will be filtering on, and in some cases will filter directly on the results, but lets continue to make this query a little more permanent.

If you recall, I was interested in a table, chair, brazier and tent. I set up the below criteria in the Name column:

• Criterion: LIKE '*table*'
• Or: LIKE '*chair*'
• Or: LIKE '*brazier*'
• Or: LIKE '*tent*'

I used the * wildcard to give me an item containing the word. Click run to see the results...

3115 results (DB release dependent)! OK, this is far too many, and it is not sorted in a way that will let me navigate it with ease.

Some of the duplications are in objects with the same Type, DisplayID, and name. But, because they are spawned across the WOW world, they have unique entry numbers. Here's how we will reduce the number of results:

1. In the entry column, set it's function to Maximum. This will give us the last spawn ID in the database.
2. For Type, DisplayID, and Name, change their Function to "Group". This will combine any records with the same data.

Finally, set the sort orders of the Type, DisplayID, and Name column to Ascending. This will display the list in a way will can scroll down easily.

Go ahead, give your query a run.

277 (DB release dependent)! Much better. We can refine our results further by setting the criterion of the Type to be types 3, 5, 7 and 8, too. This will linit the query to non- interactive objects.

Click save, save your query as qryGameObjectBrowser, and you can begin building your custom area by .go spawn'ing the entry from the query. Add as many additional criterion as you wish to get a full list of objects all at once. LIKE '*bed*', LIKE '*portal*', etc.

I used the same principles to browse my items table.

Please provide some feedback. Anything will do, I just need to know where I am going right or wrong in this guide. Next guide (if any) will show you how to search for the items sold by vendors, with the vendors name and price in the results.

The SQL code to accomplish the same as the above simple query, by the way, is:

SELECT MAX( `entry` ), `Type`, `DisplayID`, `Name` FROM `gameobject_names` GROUP BY `Type`, `DisplayID`, `Name` HAVING ( ( `Name` LIKE '%table%' ) OR ( `Name` LIKE '%chair%' ) OR ( `Name` LIKE '%brazier%' ) OR ( `Name` LIKE '%tent%' ) ) ORDER BY `Type` ASC, `DisplayID` ASC, `Name` ASC

See why I hate SQL?

[Swenky]

Good job i gave u some rep.

[Sounddead]

From what I understood of that guide(Kinda skimmed through it.. Everything is grey, its a bit hard to read) why can't you just use Navicat?

Seems like the same thing

[Tornad0z]

i agree with Sounddead.

[Gnarlz]

The sequal language is not a particularly easy one to learn. Can you use Navicat? Absolutely, and I said so in my opening post. In fact, I gave the sequal equivalent code in my second post.

However, using a GUI to write structured querying language if far easier than learning a whole new programming language. Products like Microsoft Access (my preferred), OpenBase, etc, make writing query's as easy as writing a simple algebra equation. Were many of the people on these boards to use a database front- end program, I'm sure they find many more things to actually *do* with their database., and make their server even more enjoyable to play on.

For instance, using a left- join query ("all of [this] table, and only records from [that] table that match"), you can find all of the bugged quests on your server. Identify missing questgivers, missing gameobjects, missing items for these quests, Spawns without waypoints, all sorts of things.

To build a query in Navicat, you have nothing but the SQL language or a bunch of alt- tabbing to different tables to figure out what each ID corresponds to in the other tables. A GUI front- end allows you to select the tables you want to work with, draw your links between them, then drag and drop the field names you want to see in your result set.

TL;DR version: You can use Navicat, but using Access to browse and manipulate your database is much, much easier. Users who do so will find themselves correcting more errors and doing more "stuff" in their database than they ever have before.

BTW, thanks for the feedback, I changed to white.

[jmcolvin]

nice guide, + rep if i could

[m3k0]

Nice guide w00t +rep

[[IceCreaM]]

Nice guide