 |
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
 Posted: 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 |
 |
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: 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. |
|
|
|
nexela
Wizard
Joined: 15 Jan 2002
Posts: 1644
Location: USA
|
Posted: 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.
|
|
|
|
|
Moo
Apprentice
Joined: 10 Apr 2009
Posts: 145
|
Posted: 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? |
|
|
|
|
Ithilion
Wanderer
Joined: 02 Sep 2005
Posts: 85
|
Posted: 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.
|
|
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: 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. |
|
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
|
|
|
|
|
|
|
