Elrena

There are many situations where creating SQL queries for DBCs is to die for, so I finally decided to make it happen. SQL queries for DBCs is very useful for mass operations or combining DBC data. It's also useful for version updates. In this tutorial I will teach you how to convert your DBCs to SQL files, which can then be stored on MySQL servers. I will also show you how to export your DBC tables back to the CSV format so it can then be converted back to DBC. Things You Will Need:DBC Util HeidiSQL Server Step 1: Converting DBC to CSVFirst we must convert our desired DBC file to CSV, as there is no other way to reliably import it to MySQL. Simply drag and drop your DBC file onto the DBC Util tool and it will automatically convert to CSV. Step 2: Setting Up Your DBC Table Before we are able to import a CSV file into MySQL, we must first create a table with proper columns and datatypes. There are two methods to choose from here. We can either enter everything manually, or create an SQL file instead. :-:-:-:-: Method A - Manual Creation:Using HeidiSQL, right click your server at the top left and select Create New -> Database. I named mine "dbc". After that, right click your new database and select Create New -> Table. For example, I created "lightintband". Select your new table and navigate to the "Table: lightintband" tab. This is where we will create our columns. Depending on the DBC you're importing, you must make the correct amount of columns. This must be exactly the same. Then you can start adding columns with the green button. Name them whatever you want, but make sure the datatype is always set to VARCHAR due to the DBC files utilizing lots of hexadecimal values. Do this or it won't work. Make sure that the settings Unsigned, Allow NULL, and Zerofill are all unchecked. Also keep the Defaults at 0. :-:-:-:-: Method B - SQL File Creation:Since above I mentioned our Database name is "dbc" and our example table is called "lightintband" we'll run this: CREATE DATABASE IF NOT EXISTS `dbc` USE `dbc`; CREATE TABLE IF NOT EXISTS `lightintband` ( `Field01` varchar(50) NOT NULL DEFAULT '0', `Field02` varchar(50) NOT NULL DEFAULT '0', `Field03` varchar(50) NOT NULL DEFAULT '0', `Field04` varchar(50) NOT NULL DEFAULT '0', `Field05` varchar(50) NOT NULL DEFAULT '0', `Field06` varchar(50) NOT NULL DEFAULT '0', `Field07` varchar(50) NOT NULL DEFAULT '0', `Field08` varchar(50) NOT NULL DEFAULT '0', `Field09` varchar(50) NOT NULL DEFAULT '0', `Field10` varchar(50) NOT NULL DEFAULT '0', `Field11` varchar(50) NOT NULL DEFAULT '0', `Field12` varchar(50) NOT NULL DEFAULT '0', `Field13` varchar(50) NOT NULL DEFAULT '0', `Field14` varchar(50) NOT NULL DEFAULT '0', `Field15` varchar(50) NOT NULL DEFAULT '0', `Field16` varchar(50) NOT NULL DEFAULT '0', `Field17` varchar(50) NOT NULL DEFAULT '0', `Field18` varchar(50) NOT NULL DEFAULT '0', `Field19` varchar(50) NOT NULL DEFAULT '0', `Field20` varchar(50) NOT NULL DEFAULT '0', `Field21` varchar(50) NOT NULL DEFAULT '0', `Field22` varchar(50) NOT NULL DEFAULT '0', `Field23` varchar(50) NOT NULL DEFAULT '0', `Field24` varchar(50) NOT NULL DEFAULT '0', `Field25` varchar(50) NOT NULL DEFAULT '0', `Field26` varchar(50) NOT NULL DEFAULT '0', `Field27` varchar(50) NOT NULL DEFAULT '0', `Field28` varchar(50) NOT NULL DEFAULT '0', `Field29` varchar(50) NOT NULL DEFAULT '0', `Field30` varchar(50) NOT NULL DEFAULT '0', `Field31` varchar(50) NOT NULL DEFAULT '0', `Field32` varchar(50) NOT NULL DEFAULT '0', `Field33` varchar(50) NOT NULL DEFAULT '0', `Field34` varchar(50) NOT NULL DEFAULT '0', PRIMARY KEY (`Field01`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8Please note that I have 34 columns entered. That's because LightIntBand.dbc has 34 columns, at least on Cataclysm. Your situation may vary depending on which version you're on and which DBC files you're using. Adjust accordingly. Step 3: Importing CSV into MySQL:Select your database table which should now be populated with columns, and select Tools -> Import CSV file... Browse to the CSV file that you converted with DBC Util earlier. Select INSERT under "Handling of duplicate rows". Press "Import!" You should now have a complete table for your DBC without error. Create queries and edit this however you see fit. Step 4: Exporting Back to CSV Format:With MySQL all you have to do is create a new query to export your database to CSV. This is what I prefer using: SELECT * FROM lightintband INTO OUTFILE 'C:\\LightIntBand.dbc.csv' FIELDS TERMINATED BY ',' Step 5: Converting Back to DBC Format:Now you should be ready to convert CSV back to DBC, but there's one more thing you must do. I'm not sure what to call it, but there is important information at the top of the original CSV file that is lost when added to MySQL. Before you overwrite the original CSV file, open it up in a text editor and copy the first line. In my case it's: long,long,flags,long,long,long,flags,long,long,flags,flags,long,long,flags,flags, flags,long,long,long,long,long,long,long,long,long,long,long,long,long,long,flags, long,flags,flags, Make sure you copy this from the original CSV file and paste it into the new one exported from your MySQL. Then you should finally be ready to switch back to DBC. Just drag and drop the new CSV file onto DBC Util Note About Converting DB2 to SQL for Cata and Up:If you're looking to do these same methods for DB2 editing, you'll have to use two special programs for conversion. The DB2 to SQL was a little outdated (item-sparse.db2 conversion didn't work) so I made an adjustment and attached the updated version to this thread. For SQL to DB2, I've uploaded another program. Just remember that you can only edit Item.db2 and Item-sparse.db2. Others are not currently supported. DBC2ConverSQL.zip SQLtoDB2.zip

Speaking of which, that action camera in Legion makes me feel sick. Works great in other games though, just not in WoW imo. Really highly doubt you will ever get that working in 3.3.5a

Welcome, Yasuno. Thanks for the help.

This is my new jam fam

If you're interested in learning how to make your own darker nights patch, you need a program called Lightmapper which will show you all the light sources in Azeroth. Each node that you see on Lightmapper refers to an entry in the Light.dbc. Use the WoW Dev Wiki to understand the structure of that dbc file, and you shall see that it refers to LightIntBand.dbc in two columns. One contains information for clear weather lighting and the other for rainy/snowy lighting. There are also entries for underwater lighting, use that if you wish. With the numbers you find in the Light.dbc columns you have to multiply them by 18 and subtract 17. That will be your starting row in LightIntBand.dbc. That row and the next 17 rows in LightIntBand.dbc all contain information regarding that specific locations light settings (the one you found on Lightmapper). You'll need to specify the time of day intervals as well as the color of the lighting using hex color codes. Black lights would make it extremely dark while brighter colors will make it brighter. It's a lot of copy and paste work. The WoW Dev Wiki should help you. There's another way of modifying the lights a little bit faster using Sharp WoW if I remember correctly. I've never done it myself though.

Not sure about the Orgrimmar thing. That might be one of the lights that doesn't appear on the Light Mapper. Maybe with enough fiddling you might be able to figure out which ID its using if you decide to make your own edits. However it does have its own lighting in some areas such as The Drag, which would not work well without knowledge of WMO editing. The weather problem is something I overlooked when I created Darker Nights. I'm not sure who else is making these patches now, as I've never made one for vanilla, but the reason it becomes brighter when it rains is because weather conditions have their very own Light.dbc entry. Clear weather and rainy weather use entirely different settings, but both can be changed if you have the time. With that being said, a patch like this easily takes a solid 2 weeks of hard work. So don't expect anyone to do it for free.

Knowing that someone from our community died with his mods being the last signs of his life... It creates a kind of feeling that I can't describe. I remember seeing Zim4ik's name around Modcraft all the time years ago, and now regret that I never got to know him. Thank you for sharing his work, I'm sure his friends here will really appreciate that. Now, word spreads fast so I hope everyone who wanted to contact his brother has already done so. I'm going to remove his email address from these posts to protect him from spam bots. If you still want to know it, contact Met@ and I'm sure he'll help you. Rest in peace Zim4ik...

Topic is in Random and is also non-abusive, so it stays.

The flaming situation has been handled. Please continue with posts relevant to the topic.

Kaev told Noggit to Noggit off. It Nogged it off. Nice work!

For future TrinityCore related issues, it might be best to post over there instead