|
Eowyn Apprentice
Joined: 19 Jan 2001 Posts: 117 Location: Norway
|
Posted: Mon Apr 21, 2003 12:06 am
Extracting roomnumber from db query. |
Separating this question from previous post:
Is there any way to extract a roomnumber (vNum) from the map-database?
Let's say I have an exact and unique roomname in @variable1.
I'd like to use something like the
#MAPQUERY {[Name] LIKE '@variable1'}
to collect the roomnumber / vNum of the room to @variable2.
Can queries like that be done to the database? Preferably without popping up the room-search window? |
|
|
|
Eowyn Apprentice
Joined: 19 Jan 2001 Posts: 117 Location: Norway
|
Posted: Mon Apr 21, 2003 12:36 am |
Looked a bit at the ADO-page on database queries in Zmud. That's probably closer to what I want, but the following don't work - why?
#alias {connectdb} {#VAR Conn %comcreate( "ADODB.Connection");#CALL @Conn.Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Zmud/mymud/mymud.mdb");#ECHO @Conn.ConnectionString}
#VAR myvar @Conn.Execute( "SELECT vNum FROM mymud WHERE Name = 'Road to Somewhere Else'")
The alias works fine and connects.
The question is what's wrong with my select-phrase? Is the name of the .mdb file indeed the "table" I want to refer to? Or is it called something else?
I know earlier mapversions couldn't be adressed this way, but now that it's a database this should be possible - no? |
|
|
|
Charbal GURU
Joined: 15 Jun 2001 Posts: 654 Location: USA
|
Posted: Mon Apr 21, 2003 2:11 am |
You could try this little script snippet... it uses the existing connection to the database and puts the appropriate filter on it to extract the single room (untested):
#VAR OldFilter %mapfilter()
#NOOP %mapfilter(%concat("[Name] LIKE '", @variable1, "'"))
#IF (%numrooms() > 0) {#VAR variable2 %mapvnum(1)} {#SAY No rooms found!}
#NOOP %mapfilter(@OldFilter)
There are two reasons your query doesn't work as you want it to. You've hit the first on the head; there is no mymud table in the database. While you can specify the filename of the database, the internal structure of the database uses predefined names which are common to all mapper databases. The table you want to use in this instance is ObjectTbl. Second, there is no field called vNum in this table. This is actually stored in a field called RefNum.
So the query would be:
SELECT RefNum FROM ObjectTbl WHERE Name = 'Road to Somewhere Else'
I hope that helps.
- Charbal |
|
|
|
Eowyn Apprentice
Joined: 19 Jan 2001 Posts: 117 Location: Norway
|
Posted: Mon Apr 21, 2003 12:04 pm |
Thx Charbal - that almost works just like it should. A few problems though.
( "[Name] LIKE '", @enemyloc, "'"))
There are a lot of rooms whith ' in in my mud - Pirate's bar, Someone's kitchen etc. This of course makes the query above search for only half the room (i.e. Pirate or Someone - which returns vNum = 0). I tried replacing the ' with ` or ´ - but that don't seem to do the trick.
I thought perhaps putting a ~ in front of the 's in my variable1 would help - but
Pirate~'s Bar
doesn't work either. Any ideas?
About the
#VAR myvar @Conn.Execute( "SELECT RefNum FROM ObjectTbl WHERE Name = 'Road to Somewhere Else'")
The result from this query is of an odd format:
#echo @myvar gives
%tmpcom504146254
If I ask it to #echo @myvar again (same room - same vNum) I get
%tmpcom504299063
And it keeps counting upwards if I keep echo'ing it. Changing the @variable1 doesn't change the output of @myvar so I'm not quite sure what I'm getting here..
Not important really since the first solution is so close to working :)
-E |
|
|
|
Charbal GURU
Joined: 15 Jun 2001 Posts: 654 Location: USA
|
Posted: Mon Apr 21, 2003 3:59 pm |
In those rooms with apostrophes in the names, you must double them up inside of the string... this is an SQL convention. So Pirate's bar would become Pirate''s bar and so on.
So, in the script above, replace @variable1 with %replace(@variable1,"'","''")
As for the ADO method, the object that is saved in the variable is not the vNum itself, but a COM object representing all matches in the database.
The code you need to use to access the data would be akin to that which is used in the ADO Programming Tutorial in the Support Library.
(with boundary checks):
#IF (not @myvar.BOF or not @myvar.EOF) {#NOOP @myvar.MoveFirst;#VAR variable2 @myvar("RefNum")}
#VAR myvar %null
- Charbal |
|
|
|
|
|