Zugg Software :: View topic

Newbie Joined: 16 Jul 2012 Posts: 3

Is it possible to search with %mapquery for rooms with a given room note, in the way that one can with room name?
If so, what is the required syntax for my sql query?

Wizard Joined: 22 Mar 2007 Posts: 2320

Yes it is possible. Unfortunately, %mapquery sort of relies on knowing the actual name of the columns in the underlying database, which are not necessarily the same as the Cmud functions which retrieve them. These column names are not really documented anywhere. From memory, I believe the column you are looking for is named "Notes" or "Note". So, if you know the exact note, the query would be something like:

GURU Joined: 23 Dec 2000 Posts: 4834 Location: USA

%mapquery() is limited to just the ObjectTbl table, so it's not possible to use this function to search based on notes. You'll need to use a separate sql connection to the mapfile to do this.

Newbie Joined: 16 Jul 2012 Posts: 3

GURU Joined: 23 Dec 2000 Posts: 4834 Location: USA

1)set up the database connection object. Using %sqldb() instead of #SQLDB lets you use SQL commands like UPDATE, INSERT, and DELETE (far as I know, these commands cannot be used with the %sql() function).

//file name is required, and if it's not located in the main working directory you also need to specify the path.
//Connection name (and all other parameters other than file name) is optional, but useful if you have multiple tables in the same database file for unrelated stuff.
$map = %sqldb(file name,connection name)
$map.execute(sql statement)

2)using the query object. It is the result result of the %sql() function, which is how you retrieve information from the database. Only SQL commands that return a result can be used here.

$results = %sql(connection name,sql statement)

Some tricks to remember, in case you start getting errors or unexpected results:

a)The variable you assign an SQL connection object to can be seen throughout all of CMud as per normal scoping rules. This does NOT mean that the SQL connection object is available to the script! Like the mapper object, these are apparently tied to a specific window/package so attempting to use them in other places will result in an AV error.

b)speaking of AV errors, CMud doesn't try to protect us from them with regards to SQL (this is actually the case in general, but SQL happens to be even more exposed). Make sure to double-check your code, including from which window the script happens to be firing from. Tests from the command line might work fine, but tests from another window might not work at all.

c)if at any point you change the DDL (Data Design Langage, which is the part of SQL that forms the structure of tables and stuff), any connection object that references that database table is no longer valid. Trying to use it will result in an AV, so you will need to create a new connection object.