Register to post in forums, or Log in to your existing account
 

 Related 
Contents
Whats new in v3
  New commands
  New functions
  New Mapper
  Script Wizard
  Pattern Wizard
  Stringlists / Tables
  GMCP Support
  SQL Scripting
Related Links:
  Whats new in v3
SQL Scripting [[sql_scripting]] 
CMUD v3 contains several new commands and functions to enable SQL database scripting:

  • #SQLDB : by itself, this displays a list of currently defined databases and their status
  • #SQLDB filename : opens the "filename" database and gives it a name of "filename" without any file extension. By default, ".db" is added as the file extension, which will open/create SQLite databases. CMUD users can only use SQLite. CMUDPro and TeSSH users can use many other database formats. The database is opened automatically.
  • #SQLDB name database-name protocol server port username password : The full syntax for opening a remote database of any protocol. Again, only CMUDPro and TeSSH users can use remote databases. The database is opened automatically.
  • %sqldb(name) : Returns a COM object reference to the named database object. Methods and properties are documented in the %sqldb topic.
  • %sqldb(name,database,protocol,server,port,username,password) : Same as the #SQLDB full command, but returns the COM Connection object and does NOT automatically connect to the database. Allows you to change connection properties. Use the Open method to open the object when you are ready
  • %sql(name,query) : Performs an SQL query on the named database and returns Query object. Methods and properties are documents in the %sql topic. See below for some examples.
  • #SQLCLOSE name : Closes a named database. Can also be done via the Close method of the SQLDB Connection object.

Examples

Code:
#SQLDB muds.db
row = %sql(muds, "SELECT * FROM mudlist")
#LOOP 100 {
  #SHOW "Title: " @row.Item("Title")
  #CALL @row.Next
}

Loops through the MUD list and displays the title of the first 100 records. To loop through ALL rows of a query, you would use:
Code:
#WHILE (!@row.Eof()) {
  do whatever
  #CALL @row.Next
}

It is very important to remember to use @row.Next to advance to the next record or else you'll get an infinite loop.

The @row.Item("fieldname") syntax can be used to fetch columns of the current query row. To *change* the value of a field, use the syntax:
Code:
row.Item("field") = "New value"

CMUD will automatically put the dataset in Edit mode, change the field, then Post the results. To change multiple fields more efficiently, put the row into Edit mode yourself and then Post it at the end, like this:
Code:
#CALL @row.Edit
row.Item("field1") = "value1"
row.Item("field2") = "value2"
#CALL @row.Post


The "List" property returns the entire row as a database variable "string list". For example: "#SHOW @row.List" might look like:

field1="value1"|field2="value2"|field3="value3"...

You can then manipulate the string list/database variable and then assign it back to the database using:

row.List = @stringlistvar

and CMUD will loop through each Key in the hash table and set the proper field value.

However, try to avoid using a syntax such as:

#SHOW @row.List.Fieldname

to retrieve a column from the database. While this works, it causes CMUD to recreate the entire hash table string result. It is *much* faster to use the

#SHOW @row.Item("Fieldname")

syntax instead.
Viewer Comments [0 - Post your comments]

Jump to:  

© 2009 Zugg Software. Hosted by Wolfpaw.net