Oh, the power of ODBC!!!

[hogger]

Wow. That's about all I can say. Wow.

Some time in the past few days I decided that I'd prefer some more random spawns than was in Drawde's work (and excellent work it is). So, I dropped the spawn tables all together and went about making spawn groups by race and level. Quite a task, really. I got two of them done in an hour and a half last night. Orcs levels 1-3 and levels 4-5. Ug. That's when it hit me: I need to query this stuff.

Long story short, here's what worked best for me. I went to mysql.com and downloaded myodbc. I established table links with Access 97 (all I had here at home) and started playing around. As of right now I am running queries by race and level, using them to develop reports, veiwing those reports in Word, and copy-pasting my way to fuller spawn tables. In three hours today I did all the orcs, the goblins twice (I realized too late that Kunark goblins are not in the Misty Thicket). Plus I gave them all faction entries in about two minutes.

Pretty darn cool if I don't say so myself. If anyone out there would like to know any details, I'd be happy to provide them, but I need a break. Later I'm going on to Gnolls, Froglocks, etc.

[Gino]

Sounds very interresting.

Would like to know more about it.

Regards

Gino

[TheClaus]

I would like to see a sample of your work. Sounds very cool

[theCoder]

It's great that your doing database work, but mysql does let you manipulate to the data directly:

[scott@len ~/dev/EQEmu-0.2.0/bin]$ mysql -ueqe -p eq020
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 205 to server version: 3.23.41

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+-------------------+
| Tables_in_eq020   |
+-------------------+
| account           |
| authentication    |
| character_        |
| faction_list      |
| faction_values    |
| guilds            |
| items             |
| lootdrop          |
| lootdrop_entries  |
| loottable         |
| loottable_entries |
| name_filter       |
| npc_faction       |
| npc_types         |
| quest             |
| spawn2            |
| spawnbak          |
| spawnentry        |
| spawngroup        |
| variables         |
| zone              |
| zone_points       |
| zone_server       |
| zone_state_dump   |
| zoneserver_auth   |
+-------------------+
25 rows in set (0.01 sec)

mysql> select * from account where name='eqemu';
+----+-------+----------+--------+--------------+
| id | name  | password | status | lsaccount_id |
+----+-------+----------+--------+--------------+
|  1 | eqemu | eqemu    |      0 |         NULL |
+----+-------+----------+--------+--------------+
1 row in set (0.00 sec)

And so on. ODBC is just another way to get at the data, and (as you mentioned) lets some other products get to it. If you really want to get into it, you might want to learn some SQL (it's not that hard -- it's mostly English like sentences). http://www.w3schools.com/sql/ is a good place to start and gives you a quick basic understanding of SQL.

Of course, you should use whatever's easiest for you :)

[Artadius]

I kinda thought this might be possible. I also downloaded the myodbc drivers.

After seeing your post, I loaded up Access XP and connected to the ODBC pathway..and lo and behold...there's my emu database. It looks alot like how Windcatcher has his setup..though the EQAdmin tool has no import ability which could prove VERY useful.

I'd like more information on the steps you took to import all that data into your database.

[hogger]

Well, I don't doubt that I could learn to do this using sql commands, but that's not exactly my style. Let me give you all a look at what I'm doing and you'll see why I'm so 'stoked' about it.

BTW this does nothing to touch the EQEmu Admin utilities by Windcatcher. I still use it for most of what I do.

Step 1. Set it all up. Myodbc, table links, etc. This isn't really my work so I'm not the most qualified to offer help on this part, but if you can't figure it out or something I could put together a step-by-step or something.

Step 2. Run a query. This is 50% of why Access has been such a big help to me. I have only one query that I'm currently using. It's on the table 'npc_types'. I'm displaying the fields Id, name, level, and race. I begin by diong a quick search for,say (let's just do this now), 'gnoll'. So I set the Criteria for name to *gnoll*. As is I get 58 different gnolls in the db. I can see that they're all race 39, so I remove the *gnoll* and set race's Criteria to 39. Now that returns 89 of them - something to work with.

Step 3. Apply this query to the database. I now go to the 'faction' report that I have setup to run on this query. It, very simply, inserts the id's into an sql import string. I have a text box that reads 'INSERT INTO npc_faction VALUES (' then the id from the query and then another text box ' ,4,-3,1);' Gnolls will be my fourth faction entry. I now slip into EqEmu Admin and insert 'the gnoll packs' as id 4 at -700. Now I can run my report. It looks fine, so I click the 'Word' button at the top of the report. This puts the thing into MS Word. It's ugly and has a lot of spaces, but appearantly mysql doesn't care. Now I run mysql.exe and input 'use eq'. I simply copy from the first INSERT to the last ; and paste it into mysql.

The end result is that everything with race = 39 now belongs to the faction 'the gnoll packs'.

The same process is applied to spawn groups except I also sort those by level using the 'Between x and y' criteria.

[hogger]

Proof positive: From my last post until now I've completed those gnolls I was talking about. Here's how the spawn groups now break down:

22 Gnolls 1
23 Gnolls 3
24 Gnolls 4
25 Gnolls 5-7
26 Gnolls 8-10
27 Gnolls 11-15
28 Gnolls 16-20
29 Gnolls 21-24
30 Gnolls 27-30
31 Gnolls 31-35
32 Gnolls 39-40

Not bad for 18 minutes, eh?