Inflikted's Idiot Guide to SQL Manipulation in Mir3
I will go through a few very basic steps and strings to get you started on your way to SQL scripting in mir3
RECOMMENDATIONS BEFORE TRYING THIS!!!!! --> I highly recommend you look over CheekyVimto's tutorial on
manipulating mir3 variables http://www.xtreme-server.co.uk/vbull...ead.php?t=2990
Or else you might get lost, or you might not understand how to implement these features onto your server. I wont cover this, in my tutorial, read his
\\\\\GET TO KNOW SQL\\\\\
You must familiarize yourself with how to add in new tables and fields in SQL. And you can only add in new tables into the
Game3G database (its hardcoded, you cant edit any other database using this method)
So open up SQL, and expand your Game3G database. Now right click on any of the white area, and click add new table. Now the
wierd thing is it will ask you to configure your fields first, and ask for a table name later. So go head and pick a field name. Lets
called it FLD_GUILD, now another option to the right of it will become available to select the type of field, depending on what
your trying to do, for numbers you usually use tinyint, int, bigint and for fields that have letters/numbers you use varchars. I
usually use varchars for everything, since it allows more information (up to 50 characters). Now lets go ahead and make
another field and call it FLD_GOLD, and set it to varchars aswell. Now exit out of the fieldmaker, it will ask you if you like to
make this into a new table, click yes, and enter in a table name TBL_NOTHING
So you should now have sucessfully added in a new table into your game3g database.
\\ADDING A NEW ENTRY INTO A DATABASE\\
Ok lets start by placing an example in, realize I try to include footnotes after each new command, so you understand what each step does.
This is probably the most difficult concept to grasp, pay particular attention to the formatting of the ReadValueSql and
FormatStr "FLD_GUILD='%s'" %GUILDNAME ;This puts your guildname into this format FLD_NOTHING1='%s'
which will be read by the database
ReadValueSql "TBL_NOTHING" %A9 "FLD_GUILD,FLD_GOLD" [@nextline] ;We use %A9 as this is a temp variable
stored and created by Mir3 from the previous FormatStr, in other words, %A9 = FLD_NOTHING1='%GUILDNAME'
Now this line will try to read your guildname in TBL_NOTHING, and look up the name, and the amount of gold your guild
Mov D1 %ARG(2) ;Retrieves gold amount and stores into D1 variable
Mov A3 %ARG(1) ;Retrieve guild name and stores into A3
Equal A3 "" ;If there is no guild listed under this name it makes an entry below
FormatStr "'%s',%s" %GUILDNAME 0 ;We format your guildname and add in 0 gold to the string
Mov A8 %A9 ;Move the temporarily generated mir3 variable into a permanent one
FormatStr "FLD_GUILD='%s'" %GUILDNAME ;Format your guildname to do a search for the guild in the field
Mov A2 %A9 ;Move the results into a permanent A2 variable
WriteValueSql "TBL_NOTHING" %A2 "FLD_GUILD,FLD_GOLD" %A8 ;So we add in a new entry to the table, that includes your guildname and the amount 0 gold
WriteValueSql, using proper "" and '', as you can see its very easy to make a slight mistake with the comma in the wrong
place. So what your mistakes .
And to check you got this part done all correctly, you can do into your game3g database and see in the new table you created
whether there is a new entry that exists
\\UPDATING AN EXISTING FIELD ENTRY\\
So assuming you got a new entry added, now I will move onto how to update certain fields in the string.
Lets say.. we want to add 100,000 gold to your guild's account. We need to start off with mir3 variables and translate it into a
language that SQL can understand.
After reviewing this breakdown, hopefully you can look through the full scripts I posted and understand them better. Please
Mov D7 100000 ;Moving 100000 into D7 variable to store it
FormatStr "FLD_GUILD='%s'" %GUILDNAME ;As stated above, formats your guildname into a way SQL can read it and
search for it
Mov A8 %A9 ;Move this temp variable into a permanent one.
FormatStr "FLD_GOLD='%s'" %D7 ;Moving D7 which contains 100,000 to FLD_GOLD so it can be read later by SQL
UPDATEVALUESQL "TBL_GUILDBANK" %A8 %A9 ;Put this all together now, A8 contains the guildname, A9 which is a temp variable includes the Gold Amount, we did not need to put it into a permanent variable for this example since it was in
the line just above.
ask questions or request any clarifications if they are needed
It really is all about mastering 3 SQL commands.. ReadValueSQL (Used to full information from the tables), WriteValueSQL
(make a new entryline into a table), and UpdateValueSQL (update existing entries in the table)
Once you master these commands, the potentials are limitless