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

Play RetroMUD
Post new topic  Reply to topic     Home » Forums » Finished MUD Scripts Goto page Previous  1, 2, 3, 4, 5, 6, 7, 8  Next
Dharkael Posted: Thu Apr 03, 2008 10:09 am
Python Sqlite COM Component for ZMUD/CMUD
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Wed Apr 23, 2008 3:46 am   
 
Okay Xekon just updated the component again.
When I removed the call to str() I should have replaced it with unicode() to cast the values to string objects, I didn't but it's done now.

As for All the NULL data you have from that database you can fix it in your current db with some update statements.

for example if a field is supposed to contain Text data and you want to replace the NULLS with an empty string or any other string the format would follow this pattern.
UPDATE allequip SET name = '' WHERE name = NULL
This would change any NULL's in the name column to an empty string.
You could do this for each column changing NULL's to whatever is appropiate.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Wed Apr 23, 2008 4:24 am   
 
OOOOO! this is neato! Ok So im getting down into some of the things I was trying to accomplish... but now I have a weird one.

Code:
#var eqall %comcreate("Sqlite.ZMUD.WSC")
#call  @eqall.Open("C:\alleq.db3")
#for  @eqall.Execute("SELECT * FROM alleq WHERE level BETWEEN 73 AND 73") {
  #echo ******
  #SHOW %db(%i,"name")
}
#echo "Error after execute?:"@eqall.LastError


returned"

Code:
******
Blood Ring Helmet
******
obsidian Hand
******
Dark Hood
******
Suit of Jet Black Platemail
******
Sigul of the Yellow Knight
******
[
******

******

******
crystalline wand
Error after execute?:


Here is what the actual names are for the level 73 items... as you can see once it got to the item with the | it treated it as a pipe it seems and split that one into three parts.... only showed the first part though.... is this a zmud issue or a com object issue. also how would I go about addressing it. Thank you so much :)


Blood Ring Helmet
obsidian Hand
Dark Hood
Suit of Jet Black Platemail
Sigul of the Yellow Knight
[|Pencil|>
crystalline wand
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Wed Apr 23, 2008 4:33 am   
 
xekon wrote:
Also one more question.. when I run a SELECT query how do i access the individual values


If the boolean value of the UseMap property evaluates to True then results are returned as a string that zmud/cmud can interpret as List of DB Variables.
In the initial Example I gave the SELECT query returns a list of 3 items.
The first item which #showdb displayed as
herb: Siriena
method: Eat
effect: Cure for the Black Death.
cures: Black death

You can see that the key/value pairs represents the key as the column-name and the value as the well as the value :p

Now if UseMap is false the string returned can be interpreted by cmud/zmud as a list.
now if each result had 4 columns then the first 4 items in the list would correspond to the first result the next for to the next result etc.
the columns in each result would be ordered according to how you requested it. In the case where you request * columns
the columns would be in the same order that it was declared during the tables creation.
Now I'm far from happy with that implementation it kind of makes it hard to use.
In my original implementation each result was wrapped in () so that ZMUD/CMUD could see it as a list comprised of lists.
I'm not sure why I changed it, but I could and now that I think about I JUST changed it back.
Now each List is wrapped in () so %numitems and %item will behave as if the list contains items equal to the number of results.
%item(@result,1) would return a list where each item represents the data for one field ordered according to to the SELECT statement or in cases of * by order of creation.

If I've managed to confuse you :p then just experiment a little and you'll soon see AND if you're still confused I can write a demonstration with explanation at a later time.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Wed Apr 23, 2008 4:52 am   
 
That fixed it! here are the new results:
Code:

******
Blood Ring Helmet
******
obsidian Hand
******
Dark Hood
******
Suit of Jet Black Platemail
******
Sigul of the Yellow Knight
******
[|Pencil|>
******
crystalline wand
Error after execute?:
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Wed Apr 23, 2008 4:54 am   
 
Okay Xekon keepin me on my toes Wink .
The very LATEST update will Rid you of that problem.
It's basically just an extension of the fix I made for the previous post.
The List returned has each dbvar enclosed in () thus within those brackets the | character does not split the string into another item.

I ran this test
Code:
#var comsql %comcreate("Sqlite.ZMUD.WSC")
#echo @comsql.Open(":memory:")
comsql.UseMap=1
#echo @comsql.Execute("CREATE TABLE test(name TEXT NOT NULL)");
#echo @comsql.Execute("INSERT INTO test VALUES(?)","Blood Ring Helmet")
#echo @comsql.Execute("INSERT INTO test VALUES(?)","obsidian Hand")
#echo @comsql.Execute("INSERT INTO test VALUES(?)","Dark Hood")
#echo @comsql.Execute("INSERT INTO test VALUES(?)","Suit of Jet Black Platemail")
#echo @comsql.Execute("INSERT INTO test VALUES(?)","Sigul of the Yellow Knight")
#echo @comsql.Execute("INSERT INTO test VALUES(?)","[|Pencil|>")
#echo @comsql.Execute("INSERT INTO test VALUES(?)","crystalline wand")
#for @comsql.Execute("SELECT * FROM test") {#echo *******;#SHOW %db(%i,"name")}
#echo Error After SELECT?:@comsql.LastError


It gave this result:
Quote:

*******
Blood Ring Helmet
*******
obsidian Hand
*******
Dark Hood
*******
Suit of Jet Black Platemail
*******
Sigul of the Yellow Knight
*******
[|Pencil|>
*******
crystalline wand
Error After SELECT?:



EDIT: Ninja'd on my own Update
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Thu Apr 24, 2008 12:32 am   
 
here is my command:
eqfind all clan 19 60 int>1

when the last part is manually embeded into the sql statement it works like so:
Code:
#IF (%1=="all") {
  #IF (%2=="clan") {
      #for  @eqall.Execute("SELECT * FROM alleq WHERE clan>0 AND level BETWEEN ? AND ? AND int>5",%3,%4) {
        #show %ansi(white)%format( "&-30s", %left(%db(%i,"name"),30))
      }
  }
}


however when i try and make it so that the end of the sql statment is inserted it does not work, here is what i tried:
Code:
qextra=""
qextra=%-5

#IF (%1=="all") {
  #IF (%2=="clan") {
      #for  @eqall.Execute("SELECT * FROM alleq WHERE clan>0 AND level BETWEEN ? AND ? AND ?",%3,%4,@qextra) {
        #show %ansi(white)%format( "&-30s", %left(%db(%i,"name"),30))
      }
  }
}
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Thu Apr 24, 2008 1:40 am   
 
Substitution is used to replace values and not other parts of the statement, in fact it's specifically designed NOT to do that and it is a safety feature.
It seems to me in your second code example rather than input a value your trying to input part of a statement.
In the first example the statement ends with BETWEEN ? AND ? AND int>5
In the second example it seems you're trying to substitue in something like INT > 5 which wont work.
The statement that SQLite would see is SELECT * FROM alleq WHERE clan>0 AND level BETWEEN 0 AND 20 AND 'INT >5'
Notice the single quotes this is not what was intended.
IF you want to dynamically change a part of the statement that is not a value you could use %concat or %format to do the job, a-la:
Code:
#echo @eqall.Execute(%format("SELECT * FROM alleq WHERE clan>0 AND level BETWEEN ? AND ? AND &s",@qextra),%3,%4)
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Thu Apr 24, 2008 3:30 am   
 
Perfect this is exactly what I was looking for, working like a champ.
Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Thu Apr 24, 2008 5:12 am   
 
Dharkael, you didn't by any chance chnage the API to return values from SELECT in the form of "("+ data +")"? Because that is what I am getting and that is not handled zscript as either a string list or a database record. The parentheses have to go.
_________________
Sic itur ad astra.		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Thu Apr 24, 2008 5:31 am   
 
I did and if you read above you can see why.
Can you demonstrate to me when it does not handle the parentheses?
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Thu Apr 24, 2008 5:47 am   
 
It does handle the parentheses, but my script was expecting something not encased in parentheses, it was expecting a string list of database records, it was a surprise to me when everything stopped working. So now I've gone to all my scripts that do SELECTs and added
Code:

$rec = %leftback(right($rec,1),1)

This returns the return value from SQLite back to its former structure.
Of course, this could be an internal feature, freeing me from the worry that things might change again and the patch will fail.

EDIT: I see why you did it. But I already had figured out that I couldn't do things like:
Code:

$rec = %sort(@comsql.Execute("SELECT * FROM table"))


The above code would drive Phython nuts.
_________________
Sic itur ad astra.		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Thu Apr 24, 2008 6:13 am   
 
I really don't see why you have to manually strip the parens, the parens are around each item in a stringlist, which ZMUD also does when an item in a list contains a |
If you access the items in the list in the normal ways ie with %item or #forall the parens are automatically stripped.
I'm just curious why it would interfere ?
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Thu Apr 24, 2008 6:22 am   
 
Because when called, this began to fail me:
Code:

#RETURN @comsql.Execute("SELECT Portals.EntryPoint, Portals.KeyWord FROM Areas, Portals WHERE Areas.ZoneID = Portals.ZoneID AND Areas.Area LIKE ? AND Portals.Enabled = 1", %concat( "%", %trim( %-1), "%"))


The caller no longer understood what it was getting and when I looked, it had the parentheses around the returned value, something it didn't have before. Before it just looked like an ordinary string list of db records.

EDIT: When I dump my string lists they are not encased in parentheses. They look like item1|item2|...|itemn. So in that way the values from a SELECT defer from string lists.
_________________
Sic itur ad astra.		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Thu Apr 24, 2008 7:28 am   
 
Im not sure why his is failing exactely but, EVERYTHING is working great for me now :)
Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Thu Apr 24, 2008 8:13 am   
 
The changes he made were to make your stuff work, but my stuff was working before then.
_________________
Sic itur ad astra.		 Reply with quote
Anaristos
Sorcerer


Joined: 17 Jul 2007
Posts: 821
Location: California
PostPosted: Thu Apr 24, 2008 9:53 am   
 
Here is another problem that may be related. The following does not work:
Code:

$list = @comsql.Execute("SELECT * FROM [Ready Portals]")

where Ready Portals is a view. There are no errors, the list returned is null.
_________________
Sic itur ad astra.		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Thu Apr 24, 2008 12:01 pm   
 
Have you tried running that exact query from whatever IDE you use for Sqlite?
I have no idea what the SELECT statement that is the that defines the VIEW and no idea what data is backing that.
Try running the exact query that defines the view to make sure you have data that matches the view criteria.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Thu Apr 24, 2008 4:09 pm   
 
Anaristos wrote:
The changes he made were to make your stuff work, but my stuff was working before then.

The changes I made were to fix a problem, not just to fix his problem.
One which would affect anyone that happened to have a |(vertical bar) in their data.
It's unfortunate that it can make it somewhat more difficult to use in some situations in CMUD.
CMUD it seems uses quotes instead of brackets to handle this situation.
CMUD can be made to understand this ZMud syntax but the reverse is not true.
If you're iterating through the data using #forall then it makes no differences the %i that is returned does not contain the enclosing ()
Otherwise if you force the variable containing the data to a stringlist type %vartype(result,4) it will also take care of the enclosing ()s
Unfortunately there's currently no way to use vartype on temporary variables.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Thu Apr 24, 2008 4:14 pm   
 
If $list is really a list would you not do this, instead of the way you have it?

Code:
$list = %additem(@comsql.Execute("SELECT * FROM [Ready Portals]"))


unless of coarse your just taking in the entire result set... but your data would have to be compatible with a list var for sure.
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Thu Apr 24, 2008 4:28 pm   
 
Xekon.
It really doesn't sound to me like whether the data is in list format or not is the problem in this case.
the query should still return something, he says it returns an empty list, which to me points to a possible problem with the VIEW's query.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."

Last edited by Dharkael on Thu Apr 24, 2008 4:29 pm; edited 1 time in total		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Thu Apr 24, 2008 4:29 pm   
 
I changed my regular u back to the goofy u to test:

Code:
Error After SELECT?:Could not decode to UTF-8 column 'name' with text ':::Manicúla Sutta:::'
Reply with quote
Dharkael
Enchanter


Joined: 05 Mar 2003
Posts: 593
Location: Canada
PostPosted: Thu Apr 24, 2008 4:54 pm   
 
Meh are you sure you're using a version since I posted the fix for it. If you haven't restarted the client it might still be using the old one.
Here's a test I tried using CMUD 2.18
Code:
#var comsql %comcreate("Sqlite.ZMUD.WSC")
#call  @comsql.Open(":memory:")
#echo IsOpen?:@comsql.IsOpen()
#call @comsql.Execute("CREATE TABLE sometable ( name  text NOT NULL,  rank  integer);")
#echo Error After Create?:@comsql.LastError
#call @comsql.Execute("INSERT INTO sometable (name, rank) VALUES ('Steven Carpenter', 45);")
#echo Error 1:@comsql.LastError
#call @comsql.Execute("INSERT INTO sometable (name, rank) VALUES ('Rick James', 12);")
#echo Error 2:@comsql.LastError
#call @comsql.Execute("INSERT INTO sometable (name, rank) VALUES ('Manicúla Sutta', 120);")
#echo Error 3:@comsql.LastError
#for @comsql.Execute("SELECT * FROM sometable") {#show ******;#showdb {%i}}
#echo Error SELECT?:@comsql.LastError


Quote:
IsOpen?:-1
Error After Create?:
Error 1:
Error 2:
Error 3:
******
name: Steven Carpenter
rank: 45

******
name: Rick James
rank: 12

******
name: Manicúla Sutta
rank: 120

Error SELECT?:


So for me now at least it's handling non utf-8 characters, but before I made this fix I was getting the same error message as you.
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style."		 Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Thu Apr 24, 2008 4:57 pm   Found another anomaly.
 
Found another anomaly.

Code:
#for  @eqall.Execute("SELECT * FROM alleq WHERE type=? AND level BETWEEN ? AND ?",%1,%2,%3) {
   #show %format( "&-30s", %db(%i,"name"))
}


here is what was returned

Code:
blank chaos portal           
White page                   
garbage can                   
amulet of the planes         
two-way mirror               
train ticket                 
golem's ear                   
-*                           
                             
Trip To The Killing Fields   
Enchanted Dreams             
pendant in the likeness of Kali
dark crystal                 
a share ownership of Hotel Royale
Bloody Griffin Wing           
ring of pale mushrooms       
holy well                     
Gate Rune                     
Broken Curse                 
horn of VALERE               
starburst                     
Tiger of Kai                 
MYST Linking Book 


here is what should have been returned:

Code:
blank chaos portal
White page
garbage can
amulet of the planes
two-way mirror
train ticket
golem's ear
-*)Fu-leng's Legacy(*-
Trip To The Killing Fields
Enchanted Dreams
pendant in the likeness of Kali
dark crystal
a share ownership of Hotel Royale
Bloody Griffin Wing
ring of pale mushrooms
holy well
Gate Rune
Broken Curse
horn of VALERE
starburst
Tiger of Kai
MYST Linking Book
Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Thu Apr 24, 2008 4:58 pm   
 
OK let me make sure i have latest and retest.
Reply with quote
xekon
Apprentice


Joined: 11 Oct 2007
Posts: 154
PostPosted: Thu Apr 24, 2008 5:08 pm   
 
OK well I unregistered the file, copy and pasted in the file from page 1, reregistered the file, rebooted my computer. Im still getting:

Code:
Error SELECT?:Could not decode to UTF-8 column 'name' with text ':::Manicúla Sutta:::'


Going to try your example just like you did and see if it does the same for me.

INTERESTING! running the test code you provided I DO NOT, get the error, wonder why i get the error when the data comes from my db...

The name field of my db is type: varchar

not sure if that makes any difference....
Reply with quote
Display posts from previous:   
Post new topic   Reply to topic     Home » Forums » Finished MUD Scripts All times are GMT
Goto page Previous  1, 2, 3, 4, 5, 6, 7, 8  Next
Page 4 of 8

 
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