Inflikted's Idiot Guide to SQL Manipulation in Mir3

Inflikted

LOMCN Veteran
Veteran
Aug 4, 2003
256
7
114
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/vbulletin/showthread.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.
Code:
[@main]
#ACT
    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 

has. 
[@nextline()]
#ACT
    Mov    D1      %ARG(2)    ;Retrieves gold amount and stores into D1 variable
    Mov   A3      %ARG(1)     ;Retrieve guild name and stores into A3
goto @nextline2
#IF
   Equal  A3   ""     ;If there is no guild listed under this name it makes an entry below
#ACT
    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

    goto @nextline2

[@nextline2]
whatever....

This is probably the most difficult concept to grasp, pay particular attention to the formatting of the ReadValueSql and

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.

Code:
[@main]
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.

After reviewing this breakdown, hopefully you can look through the full scripts I posted and understand them better. Please

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 :)
 
Last edited:
  • Like
Reactions: zomvra

Sammy

Dedicated Member
Dedicated Member
Sep 9, 2007
66
1
54
Mov D6 5
Mov D7 10
FormatStr "Character_Name='%s'" %USERNAME
Mov A8 %A9
FormatStr "Point1='%s'" %D6
FormatStr "Point2='%s'" %D7
UPDATEVALUESQL "TBL_Align" %A8 %D6 %D7

how i get it so the points build up instead of over write so if u get 2 lots of Point1 u would have 10 and not 5

Thanks
 
Upvote 0

BlackT

Dedicated Member
Dedicated Member
Sep 20, 2006
91
10
54
If columns Point1 has a numerical type, maybe will work with

FormatStr "Point1=Point1 + %s" %D6

"Translating", the query would be: UPDATE TBL_Align SET Point1 = Point1 + [incremental value] WHERE Character_Name = '[char name]'

PS: I haven't tested this!
 
Upvote 0

Zordon

Golden Oldie
Golden Oldie
Apr 26, 2008
1,176
20
145
Manchester, UK
Mov D6 5
Mov D7 10
FormatStr "Character_Name='%s'" %USERNAME
Mov A8 %A9
FormatStr "Point1='%s'" %D6
FormatStr "Point2='%s'" %D7
UPDATEVALUESQL "TBL_Align" %A8 %D6 %D7

how i get it so the points build up instead of over write so if u get 2 lots of Point1 u would have 10 and not 5

Thanks

Late reply I know, but for future reference and for anyone else trying anything like this, you would need to break it down abit like so, and instead of mov use inc like so:

Code:
#ACT
FormatStr "Character_Name='%s'" %USERNAME
INC D6 10
FormatStr "Point1='%s'" %D6
UPDATEVALUESQL "TBL_Align" %A2 %A9
INC D7 10
FormatStr "Point2='%s'" %D7
UPDATEVALUESQL "TBL_Align" %A2 %A9

That will include the amount your adding to the total already stored instead of overwriting it.
 
Upvote 0