Database interaction

Motivation

Have you ever identified an object two times, because you couldn't remember the stats on that specific object? Or do you sometimes wish you had a list of every item you know so that you quickly and easily could find the best possible weapon for your avatar? In situations like this you wish you had saved the information for later retrieval. Databases are really good at indexing large amounts of information and in this tutorial i will show you how to store information grabbed from the mud in a relational database. Through the power of VBScript this becomes an easy task.

Setting up the Database.

In this part assume that you have Microsoft Access installed on your machine. If you don't like Access you can also use almost any other database on the market, providing you have a ODBC or and OLEDB driver for it.

First thing you need to do is to make an Access database containing the table you would like to store your items in. So you open Access and choose to create a new table. You should create the database with the following fields:

CREATE TABLE Items (
    Name TEXT(50) NOT NULL, 
    ItemType TEXT(20) NOT NULL, 
    WearPos TEXT(50) NOT NULL, 
    Level LONG NOT NULL, 
    FullDescription TEXT(250) NOT NULL)

The next thing you need to do is to create a Data Source Name (DSN) associated with the database. This makes it easier to open the database connection in the code. This is done in "Control Panel"->"Data Sources" or "Control Panel"->"Administrative tools"->"Data Sources". You want to create an ordinary USER-DSN. Choose New and Select The Access ODBC driver. Give it the name "MonkeyTut" and select the .mdb file you created in the step above.

This part is more AstroMud specific, but you can make the same trick for most muds. The next thing you want to do is to make a new helper substitute. This substitute will make it easier to determine where the Hp prompt line starts. The idea is to put an invisible otherwise unused character on the same line as the prompt. The idea will become more apparent when we design the action that detects identified items.

As you can see the pattern uses the non regexp standard \a character. This matches ansi codes. This is because AstroMud changes the color between the numbers and the field specifiers. The Command inserts a FE and 08 character on each side of the prompt. The 08 character is a backspace and ensures that the FE char is invisible. We might as well grab the content of the HP line into some variables for later use, so we do.

Next we want to make an action that grabs identified items. So first we have to observe what the mud outputs when we make an identify:

From this we can make an action that grabs the relevant part of the text:

Here i used the a priori information that prompts always start with a FE character. So the pattern reads in natural language: "You feel informed:" must be followed by a newline. Then grab everything from here to the next prompt-line. As you can see from the command field we need a VBScript SUB called LogIDitem. I have chosen to assume you keep your avatars current level in a variable called level. The reason we also send the avatar level is that we can't trust identifies when the level of the item is greater. So we wont log these items. Open a notepad and create a new .mos file you can add to your session. Here's the contents of my Monkey Script file.

set ItemDB=createobject("ADODB.Connection")
ItemDB.Open "DSN=MonkeyTut"
'Create the DB connection and keep it open

The statements that are outside subs and functions will run when the script is loaded. The two above statements create an ADODB Connection object and opens a connection to the database. Through this object we will be able to execute sql statements to the database or retrieve data. A full ADO tutorial is beyond the scope of this tutorial but you might want to check the following site out: www.adodb.com

public sub LogIDItem(sFullDesc,AvatarLevel)

    set oRe = createobject("VBScript.Regexp")

    oRe.IgnoreCase = True
    oRe.MultiLine = True
    oRe.Global = False
    oRe.Pattern="^Object '(.*)', Item type: (\w+)[^\xFE]+" & _
        "Can be worn on:([^\n]*)[^\xFE]+, Level: (\d+)"
   

First thing you will notice is that we make an regular expression object (oRE) and we make a pattern that grabs the fields we want to store in the database. MultiLine is set to true because this allows ^ to match any linestarts instead of just the start of the string. Global is false because we wont expect more than one match.

    
    set oMC = oRe.execute(sFullDesc)
    
    if oMC.Count=0 then exit sub
    
   
    sName = replace(trim(oMC.item(0).submatches(0)),"'","''")
    sType = replace(oMC.item(0).submatches(1),"'","''")
    sWearPos = replace(trim(oMC.item(0).submatches(2)),"'","''")
    lLevel = clng(oMC.item(0).submatches(3))

Then we execute the regular expression and read the grabbed values. oMC is short for match collection. Item number 0 refers to the first match, and since we have global=false we know that there is at the most one match. The submatches refers to the values in ( ). The reason we replace ' with '' is that this is how you escape ' characters in sql syntax.

    'Read the fields and sql-escape ' characters
    if lLevel<=clng(AvatarLevel) then 'otherwise the identify cannot be trusted.
        sSql = "Insert Into Items (Name,ItemType,WearPos,[Level]," & _
            "FullDescription) Values " & _
            "('" & sName & "','" & sType & "','" & sWearPos & "'," & _
            lLevel & ",'" & replace(sFullDesc,"'","''") & "')"
        ItemDB.execute sSql
        session.terminal.printstring vbcrlf & "LOGGED ITEM " & sName & vbcrlf 
    end if
end sub 

Then we compare the level of the identified object with the current level of your character. If the identify can be trusted then we choose to store the identified object in the database. This is done through the sql that is composed in the string sSql. A full explanation of SQL syntax is also beyond the scope of this tutorial but heres how you generally insert data into a database. Here's some SQL tutorials.

INSERT INTO TABLENAME (FIELD1, FIELD2, ....) VALUES (VALUE1, VALUE2, ....)

You'll notice that sSql contains [Level]. This is simply because level is a reserved word in Access and this is the way you escape "object" names in access. The connection is already open so we can simply execute it and this can be done through the ADODB.Connection objects execute method. When the item has been logged we write a small notification to the terminal window.

And that is that. =)

More info

• Whats that? ADODB.Connection
• Whats with that VBScript.RegExp
• What that strange text you put into the variable sSql.
  Its a SQL statement. Its like a standardized way of talking to databases. heres a link.
• Why not just make the regexp complicated in the action instead of in the code.
  I just felt like it.
• Other questions? Just send us an email.

Improvements you might consider to do yourself.

• Make an easy way of retrieving data from the database. Maybe using the browser bar.
• Make sure that items are stored in the database only once.
• Add a timestamp to the database so you can discard really old identifies.
• Split the information up more nicely.