Silencer <3 Cock
Likes Likes:  0
Results 1 to 7 of 7
  1. #1
    LOMCN Senior Member Inflikted's Avatar
    Join Date
    Aug 2003
    Posts
    256

    Inflikted's Idiot Guide to SQL Manipulation in Mir3

    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.
    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 by Inflikted; 10-02-2008 at 06:31 PM.

  2. #2
    Golden Oldie mirlin6's Avatar
    Join Date
    Aug 2004
    Location
    United Kingdom
    Posts
    532

    Re: Inflikted's Idiot Guide to SQL Manipulation in Mir3

    thanks very much inflicted its alot clearer to me now, i can see where i was going wrong


    Jabba - Level 23 Taoist - Ace Mir 2

  3. #3
    Mad Dog Geo Geordiehc's Avatar
    Join Date
    Jul 2007
    Location
    Redditch, UK
    Posts
    2,824

    Re: Inflikted's Idiot Guide to SQL Manipulation in Mir3

    Brilliant m8, glad to see people in this community who are willing to share
    If this Post has been helpful then please click the +rep symbol on the bottom left to show your appreciation!

    LOM 3 Community On ********!

    http://www.*************/groups/113381352098871/
    Stay In Touch With Friends Whatever Server You Play

    I do not answer server help questions via PM. PM'ing me will not get you a quicker answer than posting.

  4. #4
    LOMCN Rookie Sammy's Avatar
    Join Date
    Sep 2007
    Posts
    66

    Re: Inflikted's Idiot Guide to SQL Manipulation in Mir3

    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

  5. #5
    LOMCN Rookie BlackT's Avatar
    Join Date
    Sep 2006
    Posts
    78

    Re: Inflikted's Idiot Guide to SQL Manipulation in Mir3

    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!

  6. #6
    mapadale
    Guest mapadale's Avatar

    Re: Inflikted's Idiot Guide to SQL Manipulation in Mir3

    Stickied for future reference.

  7. #7
    LOMCN Veteran Zordon's Avatar
    Join Date
    Apr 2008
    Location
    Berks, UK
    Posts
    1,164

    Re: Inflikted's Idiot Guide to SQL Manipulation in Mir3

    Quote Originally Posted by Sammy View Post
    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.
    Loong: DragonBlood.
    Level 100/Ascended Polearm/Sabre (RETIRED)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •