Saving Data & Transfering to a SQL DB?

[inkognito]

Is it possible in some way to store the data collected from your char, like name, race, level, stats, resistances etc to another datafile, than the standard file used when Using ResgisterForSave(); Function?
And, are there any functions that you can use to transfer those data to a SQL database, either through web access or some ODBC connection?

[Gello]

SavedVariables is it, mostly. You can write stuff to the chat logs, that will be saved when you exit also. But it will be monumentally easier to parse the SavedVariables files themselves.

Make a simple parser to convert the text to a comma-delimeted format and you should have no problem getting it into a SQL database.

By design the UI attempts to stop all interaction with outside programs.

[farang]

as a little help, of how to convert such savedvariable.lua easy:

Get the Lua standalone exe.

Create a Lua script ,that uses "print()" to output the resulting data (that script won't work inside wow, just with the lua standalone).

Now you can just capture the output of it, to get for example CSV Format or XML Format out of the original Lua Table data.

of course you can also write code in any programming language that does the converstation.

[Gello]

yeah grabbing a lua exe is a great route. You can get them at the lua wiki: http://lua-users.org/wiki/LuaBinaries

Best part of using the lua compiler is you can then directly use the tables you make in game (as long as they're saved to SavedVariables)

For instance if you tag a table named GuildRoster to save in SavedVaribles, your offline script can look like this:

dofile("c:\\Program Files\\World of Warcraft\\WTF\\Account\\YourAccountName\\SavedVariables.lua")
f = io.open("guild.out","w")
for i in GuildRoster do
f:write(GuildRoster[i].Name..","..GuildRoster[i].Class..","..GuildRoster[i].Level.."\n")
end
f:close()

And it will create a file called guild.out with a listing of every guild member in GuildRoster (which is a table your mod would create).

[Devla]

Hmm

Yea I've been working on exporting a 'member list' for my guildmaster. Since our DKP system is a bit different than normal, the GM takes note of everyone online after every boss kill. Pen & paper works ok, and may be the only way...since a forced reloadui would be needed to parse any of the data into an external text file.

The setup I have right now is a custom version of the Guild Roster mod that I hacked up. Then, use the java applet at http://rezzed.com/ to parse the data and export it to an html friendly file.

Now, I've been playing with a script to pull this data myself locally using the lua compiler and a batch file. However I can't seem to pull the proper data. Below is the bit from the mod that pulls the data and saves it into SV:

	for i=1, numGuildMembers do
		name, rank, rankIndex, level, class, zone, group, note, officernote, online = GetGuildRosterInfo(i);
		year, month, day, hour = GetGuildRosterLastOnline(i); 
		
		GuildInfo_GuildRoster[RealmName]["Guild"]["Members"][name] = {};
		GuildInfo_GuildRoster[RealmName]["Guild"]["Members"][name]["Name"] = name; 
		GuildInfo_GuildRoster[RealmName]["Guild"]["Members"][name]["Level"] = level; 
		GuildInfo_GuildRoster[RealmName]["Guild"]["Members"][name]["Class"] = class;
		GuildInfo_GuildRoster[RealmName]["Guild"]["Members"][name]["Note"] = note;
		GuildInfo_GuildRoster[RealmName]["Guild"]["Members"][name]["Online"] = online;
			
		num=i;
	end

And below is a sample of the SV data:

GuildInfo_GuildRoster = {
["Servername"] = {
		["Guild"] = {
			["NumMembers"] = 12,
			["Name"] = "Blahguild",
			["Members"] = {
				["String"] = {
					["Note"] = "",
					["Name"] = "Blahblah",
					["Class"] = "Warlock",
					["Online"] = 1,
					["Level"] = 29,
				},}

If anyone could help me with the actual code for a compiler script to pull this data, it would be greatly appreciated.

Thanks

[Gello]

Really this is the entire script you need and the only part to change is the fields in italics and of course the string.format and which bits of data you want:

dofile("c:\\Program Files\\World of Warcraft\\WTF\\Account\\YourAccountName\\SavedVariables.lua")
-- pretend VARIABLES_LOADED just happen. All your Gatherer, LootLink, every mod's tables are now loaded because the dofile actually ran SavedVariables.
f = io.open("guild.out","w") -- open "guild.out" for write access

for i in GuildInfo_GuildRoster["RealmName"]["Guild"]["Members"] do

info = GuildRoster["RealmName"]["Guild"]["Members"]
text = string.format("Name: %s, Class: %s, Level: %d\n",i,info.Class or "",info.Level or 0)
-- pretend f:write is DEFAULT_CHAT_FRAME:AddMessage
f:write(text)

end

f:close() -- close "guild.out"
--

The script runs SavedVariables.lua, so all tables that the mod uses are available to the script. It's just a matter of writing out the information. The above will create a file called guild.out that contains:

Name: Soandso, Class: Priest, Level: 60
Name: Otherperson, Class: Mage, Level: 35
Name: Thatguy, Class: Druid, Level: 58

[Devla]

Thanks Gello

However I'm getting an error:

lua50: GRoster.lua:5: attempt to index field 'RealmName' (a nil value)
stack traceback:
GRoster.lua:5: in main chunk
[c]: ?

[Gello]

You need to use your RealmName, GuildName, etc. It's the indexes to the table.

Maybe I should rephrase: Forget you're making a file for a moment. Write or edit a mod that will send the information to the screen with DEFAULT_CHAT_FRAME:AddMessage("text") Once you have it, take the exact code that displays this information, character for character, and copy and paste it within this:

dofile("c:\\Program Files\\World of Warcraft\\WTF\\Account\\YOURACCOUNTNAME\\SavedVariables.lua")
f = io.open("guild.out","w")

Copy-Paste *EXACT* code required to display roster information to screen in game here.
Then substitute every DEFAULT_CHAT_FRAME:AddMessage() with f:write()

f:close()
--

Then replace every DEFAULT_CHAT_FRAME:AddMessage with f:write. (If I knew how to make member functions you wouldn't even need to replace those. Maybe someone knows how.) That's all there is except you want to end every line with a "\n" when writing to a file.

[Devla]

Duh I need to learn how to read, Gello you're the bestest

Ok I got it working:

for i in GuildInfo_GuildRoster["Frostmane"]["Guild"]["Members"] do
    info = GuildInfo_GuildRoster["Frostmane"]["Guild"]["Members"]
    text = string.format("Name: %s, Class: %s, Level: %d\n",i,info.Class or "",info.Level or 0)

Now...Its spitting out the name fine, but the level and class are blank and zero

Name: Blahname1, Class: , Level: 0 Name: Blahname2, Class: , Level: 0 Name: Blahname3, Class: , Level: 0

[Gello]

oops forgot the i sorry:

text = string.format("Name: %s, Class: %s, Level: %d\n",i,info[i].Class or "",info[i].Level or 0)

[Devla]

Thanks again for all your help Gello

[Devla]

Now this would be perfect if I could:

Remove labels (Name: Level: Class
Sort the output (i replaced spaces+commas with tabs and use a spreadsheet to get this effect)
Create a function ingame to trigger the parse after doing a ReloadUI

All of which I don't think is possible, at least with my knowledge of LUA.

Hehe...thanks again Gello, my coding is atrocious.

[Gello]

To sort you would need to make a new table that's indexed by numbers. So I think a spreadsheet is a good solution for now. To format is pretty painless:

string.format( pattern, var1, var2, ..etc.. , var99, varetc )

This drops var1-varx into the pattern string as replacements for escape sequences. For simple stuff all you need is %s:
string.format("This %s is %s a %s test", 1, "abc", 3) = "This 1 is abc a 3 test"

So if you wanted just table entries separated by commas:
string.format("%s,%s,%s,%s,%s\n", var1, var2, var3, var4, var5 )

If var1 is "i" in the above example, and var2 is info[i].Class, and var3 is info[i].Level, var4 is info[i].Race, var5 is info[i].Profession:
string.format("%s,%s,%s,%s,%s\n",i,info[i].Class,info[i].Level,info[i].Race,info[i].Profession)
will produce:

Someone,Priest,60,Human,Engineering
Another,Mage,35,Gnome,Alchemy
Yetanother,Druid,58,Night Elf,Leatherworking
etc

Sorting is a bit trickier because that table is not indexed by number so you'd want to make another table to copy the information. I'd probably stick with the spreadsheet since it's just seconds to convert.

edit: You won't be able to write this file or alert the script to know the data has changed from in-game, without some third-party program constantly monitoring SavedVariables.lua. It's something you'll have to do manually. Or by making a batch file to run WoW and then run the script when you exit completely.

[Devla]

Ok, I think I'm done with it.

Thanks again for all your help.

[Conradin]

It looks like you're using the GuildInfo or Guild Roster addon to get the printout. We use that addon too, and I just wrote this php code to parse out the file . As the code is here, it will only print output to the screen, but I have it commented where you should put any calls to a database to INSERT or UPDATE your tables. I should point out that I wrote this code specifically to update a mysql database, so the webpage that displays the guild roster exists elsewhere in the site--this code prepares the info for insertion into the DB.
Best of all, it's a direct step: Copy the GuildRoster.lua file to a location accessible to the code. You don't have to get a lua engine involved


$filepath = ""); //put the relative path to your file inside the quotes, like "../files/uploads/GuildRoster.lua
$fp= fopen($filepath ,'r');
$guildname="AoE,";//put your guild name here **followed by a comma**
if(!$fp)
{
echo "<p> <strong> No lua file available."
."Please try again later.</strong></p></body></html>";
exit;
}

while(!feof($fp))
{
$order=fgets($fp, 100);
$char_array= explode("=",$order);
$element=trim(str_replace('"','',$char_array[0])); //gets rid of the spaces & quotes around data that will compose $element
$attribute =trim(str_replace('"','',$char_array[1])); //gets rid of the spaces & quotes around data that will compose $attribute
$attribute = str_replace(',',"",$attribute);//gets rid of trailing comma after attribute data
if($element=='[Name]' && $attribute != $guildname)
{$currentchar = $attribute;}
if($element=='[Note]' )
{$currentnote = $attribute;}
if($element=='[Zone]' )
{$currentzone = $attribute;}
if($element=='[Class]' )
{$currentclass = $attribute;}
if($element=='[Online]' )
{$currentonline = $attribute;}
if($element=='[RankIndex]' )
{$currentrank = $attribute;}
if($element=='[year]' )
{$currentyear = $attribute;}
if($element=='[month]' )
{$currentmonth = $attribute;}
if($element=='[hour]' )
{$currenthour = $attribute;}
if($element=='[day]' )
{$currentday = $attribute;}
if($element=='[Level]' )
{$currentlevel = $attribute;}
if ($currentonline == 1 ) {$lastonline = "Online during roster snapshot";}
else{
$lastonline = "";
if ($currentyear == 1){$lastonline .="1 year, ";}
if ($currentyear > 1) {$lastonline .=$currentyear." years, "; }
if ($currentmonth == 1){$lastonline .="1 month, ";}
if ($currentmonth > 1) {$lastonline .=$currentmonth." months, ";}
if ($currentday == 1){$lastonline .="1 day, ";}
if ($currentday > 1) {$lastonline .=$currentday." days, ";}
if ($currenthour == 1){$lastonline .="1 hour ";}
if ($currenthour > 1) {$lastonline .=$currenthour." hours ";}
}
if ($element == "[Rank]"){
echo "<BR><BR>Character: $currentchar--Lvl $currentlevel $currentclass.";
echo "Guild Rank=$currentrank. <BR>Last online: $lastonline. Notes: $currentnote";
//if you want to include any interactions with a mysql db regarding this info, run it here, BEFORE the closing bracket of the if statement

}//end of if ($element)
}//closeof while(!feof($fp));
fclose($fp);