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

Play RetroMUD
Post new topic  Reply to topic     Home » Forums » CMUD Beta Forum
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23377
Location: Colorado, USA

PostPosted: Sat Oct 24, 2009 12:20 am   

New SQL commands docs
 
I'll add the new SQL commands to the official documentation next week when I get some time. Until then, here are the basics:

#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 any database format available via zApp (see zConnection). 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. You can use most properties and methods as defined in the zApp zConnection documentation.
%sqldb(name,database,protocol,server,port,username,password)
Save 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 the zApp zQuery object. Most properties and methods are supported. See below for examples.
#SQLCLOSE name
Closes a named database. Can also be done via the Close method of a 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


In addition to the features documented in the zQuery zApp docs, an additional property is available in CMUD called "List". 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.

I haven't done much testing with remote databases. Remember to make NEW POSTS for any bugs that you find in this new scripting code. Enjoy!


Last edited by Zugg on Tue Sep 21, 2010 10:04 pm; edited 1 time in total
Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California

PostPosted: Wed Dec 23, 2009 10:14 am   Last record flag is not being set
 
The following code (taken from the published docs):
Code:

#WHILE (!@row.Eof()) {
  do whatever
  #CALL @row.Next
}

does not work. From what I've gathered while conducting different tests, querying the eof state always gets a FALSE returned.
_________________
Sic itur ad astra.
Reply with quote
nexela
Wizard


Joined: 15 Jan 2002
Posts: 1644
Location: USA

PostPosted: Wed Dec 23, 2009 2:14 pm   
 
This is a known bug in 3.12. I am pretty sure it will be fixed when the next version is released.
_________________
Zmud Support Library
Zmud Knowledge Base
Reply with quote
Moo
Apprentice


Joined: 10 Apr 2009
Posts: 145

PostPosted: Sat Jun 12, 2010 10:47 am   
 
Looks like "next week" never happened..
Anyway.. Is there a way to create new records using the query object, or can it only be done with "raw" SQL?
Reply with quote
Ithilion
Wanderer


Joined: 02 Sep 2005
Posts: 85

PostPosted: Sat Jun 12, 2010 4:06 pm   
 
Quote:
I'll add the new SQL commands to the official documentation next week when I get some time. Until then, here are the basics:
is what Moo's referring to, btw.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23377
Location: Colorado, USA

PostPosted: Mon Jun 14, 2010 5:19 pm   
 
To create new records you'll need to use the %sql query function to execute an SQL INSERT command. So yes, it can only be done with "raw sql".

And sorry "next week" never happened. Documentation happens before the Public Release and too many other issues have come up to postpone the public release.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23377
Location: Colorado, USA

PostPosted: Tue Sep 21, 2010 10:03 pm   
 
Topic moved to Documentation
Reply with quote
Display posts from previous:   
Post new topic   Reply to topic     Home » Forums » CMUD Beta Forum All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

© 2009 Zugg Software. Hosted by Wolfpaw.net