|
|
楼主 |
发表于 2011-8-22 12:44:27
|
显示全部楼层
ADO Programming in zMUD
Updated: by Mike Potter (Zugg)
What is ADO?
NOTE: This document is written for zMUD v6.26 and later.
ADO stands for ActiveX Data Objects. It is a collection of routines used to access databases independent of the actual database format.
ADO was added to the Windows operating system in Windows 98. It comes with Windows 98, ME, 2000, and XP. It is also added to your system if you install Internet Explorer 5.0 (or later), or Microsoft Office.
Windows 95 and Windows NT systems that do not have ADO can easily download and install it for free. Go to the zMUD Database page for details on doing this.
Starting with version 6.26, zMUD uses ADO for all of it's database chores. Since zMUD databases use ADO, these database can be accessed using any language that can use ADO, which includes most every language available, including Visual Basic, and even ASP web pages! By using the ADO database engine already built into Windows, zMUD no longer has to add it's own proprietary database engine, which reduces the size of zMUD (although starting zMUD is slightly slower since it has to load ADO from Windows).
NOTE: zMUD doesn't yet use ADO for it's Database Module. Only the MUD List and Character List are in ADO format. Future versions of zMUD will include ADO format for the Mapper and the Database modules.
Since you can access ADO databases from any programming language that supports ADO, and ADO is simply a COM interface, that means you can access ADO from within zMUD scripts. For more details on calling COM objects in zMUD, see the COM Programming in zMUD article. This article will show some examples of using ADO within zMUD.
Connecting to a database
Before you can read data from a database, you must connect to it. First, you create the ADO Connection COM object:
#VAR Conn %comcreate( "ADODB.Connection")
Next, you must open a specific database. This is the most complex part of ADO. In order to connect to a database, you must specify the database driver used to access the data. For example, if the database in an SQL Server database, you specify SQL Server. If it's a Oracle database, you specify Oracle. If the database is already defined as a system-wide DSN on your computer, you can open it with just the DSN name:
#CALL @Conn.Open( "DSNName")
To create a DSN, go to the ODBC Control Panel and use it to add a new DSN to your system. The details of this are beyond the scope of this document.
For zMUD databases, it's a bit easier. All zMUD Databases are in Microsoft Access 2000 format. The database driver for Access 2000 is Microsoft.Jet.OLEDB.4.0. So, to open a specific Microsoft Access 2000 database, you use the following command:
#CALL @Conn.Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=filename.mdb")
where "filename.mdb" is the name of the Microsoft Access 2000 database file. As an example, let's open the MUD list database used by zMUD, stored in the file mudlist.mdb:
#CALL @Conn.Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mudlist.mdb")
To verify that a database is open, you can display the full ConnectionString property of the database:
#ECHO @Conn.ConnectionString
If this property has a long value, then the database is open and ready to go.
Querying the database
To search the database for the records that you are interested in, you must query the database using SQL. SQL is a language designed for database queries. These days, most every database supports SQL. So, get yourself a good book on SQL since it is way beyond the scope of this article.
One tricky part about SQL is that some details can be specific to the exact type of database you are working with. Some SQL commands that work with SQL Server or Oracle, will not work with a Microsoft Access 2000 database. But for typical applications you don't have to worry about this.
The most common way to query a database is using the SQL SELECT statement. It has the syntax:
SELECT Fields FROM TableName WHERE Field = 'Value'
To retrieve all of the Fields, use * in place of Fields. For the MUD List, there is only one table in the database, and it is called MUDLIST. One of the fields in the table is called "Title" which is the name of the MUD. So, to query the database for the MUD record for "Realms of Despair", the SQL command would be:
SELECT * FROM Mudlist WHERE Title = 'Realms of Despair'
Now that we have the SQL statement, we need to execute this on our open connection. The result of the execution is a object called a RecordSet which is a set of all records in the database that match our query. So, the zMUD command for this would be:
#VAR rs @Conn.Execute( "SELECT * FROM Mudlist WHERE Title = 'Realms of Despair'")
Be sure and get the quotes correct. SQL wants a single quote around the field value, and zMUD wants double quotes around the entire SQL statement.
To display the entire contents of this record set as a string, use the GetString property:
#ECHO @rs.GetString
There's the data!
Using RecordSets instead of Connection objects
The method described above works well, but is limited in several ways. The most important limitation is that the record set returned by the @Conn.Execute method is a read-only record set. To open a recordset that you can modify, you must work with recordset objects directly.
Let's open the same database using only a recordset:
#VAR rs %comcreate( "ADODB.Recordset")
#VAR ConnStr "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mudlist.mdb"
#VAR Sql "SELECT * FROM Mudlist WHERE Title = 'Realms of Despair'"
#CALL @rs.Open( @Sql, @ConnStr)
Notice that you still need the same Connection String to specify the location of the database, and the same SQL statement to perform the query. The Open method of the recordset opens the connection and executes the SQL statement.
The above example still produces a read-only recordset. To change this, we add a couple of more optional parameters to the Open method.
But first, before we can open a new dataset, we must close the existing one:
#CALL @rs.Close
Now we will open a new recordset in read/write mode:
#CALL @rs.Open( @Sql, @ConnStr, 2, 2)
The two additional arguments are for the Cursortype and the Locktype. A value of 2 for the CursorType gives us a dynamic cursor that can move both fowards and backwards, useful for navigating the dataset. The LockType of 2 indicates optimistic record locking for a read/write recordset. For more information on these parameters, consult an ADO reference. You should be careful with these parameters if you are accessing a remote database. Some CursorType and LockType values might cause it to read the entire database across your network connection.
Accessing the data in a record
Once you have a recordset using either of the above methods, you can now start looking at the actual data.
If you don't know the names of the fields in the database, you can find out. The record set has a property called Fields that contains both the data in the record, and the information about the fields.
#VAR Fields @rs.Fields
Let's loop through this object and display all of the field names:
#VAR Count @Fields.Count
#LOOP 0,@Count-1 {#ECHO @Fields(%i).Name}
even better, let's display both the field name and the value:
#LOOP 0,@Count-1 {#ECHO @Fields(%i).Name=@Fields(%i).Value}
To retrieve a specific field, once you know the name, you can simple do this:
#ECHO @Fields("Host").Value
for example, to display the host name for the Realms of Despair MUD.
Default Properties
Some COM objects have a default property. For example, the default property of the recordset is the Fields property. And the default value of the Fields collection is the Value property. So, to display the value of the "Host" field, you can also use the syntax:
#ECHO @rs("Host")
this is the same as
#ECHO @rs.Fields("Host").Value
Note that you can only use default properties when reading a value. To set a value, you must explicitly list the final property. So, to change the value of this field, you would do:
#VAR rs("Host").Value "New Name"
Notice that the "Fields" default can be omitted, but the "Value" default is still required. Also, keep in mind that this just changed the value of the Host field in memory and didn't effect the actual database. To change the actual database contents you need to use the @rs.Update method as shown in the next section.
Changing database values
Once you have a field or recordset variable, it is easy to modify the value of a field. For example, to change the Comments field for the Realms of Despair entry, you could do:
#VAR rs("Comments").Value "Test comment"
#CALL @rs.Update
Note that you must have a read/write recordset for this to work. The call to the Update method writes any changes to the current record back to the database.
Looping through a recordset
Let's open a new recordset that has the entire Character database. This database contains the login information for each of your MUD characters as displayed in the initial Character Selection window.
First, we get a ReadOnly recordset with a Dynamic cursor that points to the character database:
#VAR CharRS %comcreate( "ADODB.Recordset")
#VAR ConnStr "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=chardb.mdb"
#CALL @CharRS.Open( "chardb", @ConnStr, 2)
Notice that instead of an SQL query statement, we just used the name of the entire table in the database called "chardb". Now, let's loop through each record in this record set and display the title of the character to the screen:
#CALL @CharRS.MoveFirst
#WHILE (not @CharRS.eof) {
#ECHO @CharRS("IconCaption")
#CALL @CharRS.MoveNext
}
The MoveFirst method sets the recordset to the first record, and the MoveNext method sets the recordset to the next record.
Closing the database
When you are done with a database, be sure to close your recordset and release the COM objects by assigning blank values to them:
#CALL @rs.Close
#VAR Fields ""
#VAR rs ""
#VAR Conn ""
Setting the Connection variable to null will close the connection to the database. When closing a recordset, you will get an error message if there are pending changes, in which case you need to call the Update method or the Cancel method to save or throw away the changes.
Summary
This article has shown some simple examples of using ADO from within zMUD. This is a complex subject since it requires knowledge of COM Programming, ADO, and the actual database that you want to query. But once you get the hang of it, you'll have some incredible power available for your scripts. |
|
狗仔卡
发表于 2011-8-21 22:59:03
显身卡