 |
| Dharkael |
Posted: Thu Apr 03, 2008 10:09 am
Python Sqlite COM Component for ZMUD/CMUD |
|
Progonoi
Magician
Joined: 28 Jan 2007
Posts: 430
|
 Posted: Sun Apr 20, 2008 5:52 pm
|
Excellent! Yeah, restarting ZMud fixed it.
Now I have to start thinking about all my stuff in relation to this awesome feature! 
I'm sure I'll going to have more questions and/or problems, though. Usual case with me.
Thanks for good advices.
Prog |
|
_________________
The Proud new owner of CMud.
--------------------------------
Intel Core i5-650 3,2GHz
4 DD3 RAM
GTX 460 768MB
Win 7 Home Premium 64x
-------------------------------- |
 |
|
|
charneus
Wizard
Joined: 19 Jun 2005
Posts: 1876
Location: California
|
Posted: Sun Apr 20, 2008 9:29 pm
|
Ok. Sorry for the delay in my response - Dharkael, I did what you suggested (however, I went ahead and ran it off the demo).
This is what the code looks like:
| Code: |
#var stuff %comcreate("Sqlite.ZMUD.WSC")
#ECHO Is it created? @stuff.AutoCommit
#call @stuff.Open(":memory:")
#ECHO Is database open? @stuff.IsOpen
#call @stuff.Execute("create table herblist (herb,method,effect,cures)")
#echo Error after create table: @stuff.LastError
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Siriena","Eat","Cure for the Black Death. ","Black death")
#echo Error after adding value1: @stuff.LastError
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Aldaka","Apply","Restores sight. ","Blind")
#echo Error after adding value2: @stuff.LastError
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Echianta","Smoke","Heals physical afflictions such as clumsiness.","Clumsiness")
#echo Error after adding value3: @stuff.LastError
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Febfendu","Drink","Restores hearing.","Deaf")
#echo Error after adding value4: @stuff.LastError
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Courgia","Eat","Cure for a few diseases including haemophilia.","Haemophilia")
#echo Error after adding value5: @stuff.LastError
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Madabril","Eat","Often known to cure the plague.","Plague")
#echo Error after adding value6: @stuff.LastError
#call @stuff.Execute("insert into herblist values(?,?,?,?)","Witan","Eat","Relieves stun and holding spells.","Stun")
#echo Error after adding value7: @stuff.LastError
#for @stuff.Execute("SELECT * FROM herblist WHERE method=? AND effect REGEXP ?","Eat","(C|c)ure") {#echo ******;#showdb {%i}} |
This is the result:
| Code: |
Is it created? True
Is database open? True
Error after create table:
Error after adding value1:
Error after adding value2:
Error after adding value3:
Error after adding value4:
Error after adding value5:
Error after adding value6:
Error after adding value7:
******
******
****** |
Shrug. Wish I knew what was happening. :\
Charneus |
|
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Sun Apr 20, 2008 9:53 pm
|
Okay hmm, notice you're getting 3 lines of ****** which means there's three items being returned which is correct.
So either its returning 3 empty items, or for some reason #showdb is having a hard time with the items returned.
I try replacing
| Code: |
| #for @stuff.Execute("SELECT * FROM herblist WHERE method=? AND effect REGEXP ?","Eat","(C|c)ure") {#echo ******;#showdb {%i}} |
With
| Code: |
| #var result {@stuff.Execute("SELECT * FROM herblist WHERE method=? AND effect REGEXP ?","Eat","(C|c)ure")} |
Or maybe
| Code: |
| #show {@stuff.Execute("SELECT * FROM herblist WHERE method=? AND effect REGEXP ?","Eat","(C|c)ure")} |
That way you'll be able to see what's actually being returned. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
charneus
Wizard
Joined: 19 Jun 2005
Posts: 1876
Location: California
|
Posted: Sun Apr 20, 2008 10:17 pm
|
Well, it is an issue with #showdb. Weird. This is what is outputted when I changed it to #show:
| Code: |
******
herb Siriena method Eat effect Cure for the Black Death. cures Black death
******
herb Courgia method Eat effect Cure for a few diseases including haemophilia. cures Haemophilia
******
herb Madabril method Eat effect Often known to cure the plague. cures Plagu
|
Won't display it here, but it separates the herb from Siriena, method from Eat, effect from Cure for the Black Death, and cures from Black Death. Sigh... now I get to find a workaround for that. :\
Charneus |
|
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Sun Apr 20, 2008 10:33 pm
|
Does the EXECUTE() allows the values to be passed in a string list?
I'd Like to do the following:
| Code: |
#CALL @comsql.Execute("SELECT * FROM table_name WHERE field1 = ? AND field2 = ? AND... fieldn =?",@valuelist)
|
The reason for this is that I have an opportunity to create a dynamic SELECT and I don't know beforehand how many values there will be or what they might be.
EDIT: This would be a handy way to create a VIEW on the fly...
EDIT: Never mind. I see I can use an array which makes more sense since you'd have to convert a string list internally, anyway. |
|
_________________
Sic itur ad astra.
Last edited by Anaristos on Mon Apr 21, 2008 12:23 am; edited 1 time in total |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Sun Apr 20, 2008 10:35 pm
|
@Charneus
How about
| Code: |
| #for @stuff.Execute("SELECT * FROM herblist WHERE method=? AND effect REGEXP ?","Eat","(C|c)ure") {#echo ******;#echo %expanddb(%i,%crlf,":")} |
|
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
charneus
Wizard
Joined: 19 Jun 2005
Posts: 1876
Location: California
|
Posted: Sun Apr 20, 2008 10:41 pm
|
Finally, it works. Now to start building up my own databases. :P Thanks, Dharkael.
Charneus |
|
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Mon Apr 21, 2008 12:32 am
|
| Anaristos wrote: |
| Does the EXECUTE() allows the values to be passed in a string list? |
No not in the way that you mean.
There really is no difference between a regular string and a stringlist except. Because of that the component would have no idea if you were passing a single string value which happens to contain a "|" character or a stringlist.
So if you pass in a stringlist of values it is received as just one value.
Now I had previously mean to allow one to pass an Array object and have that interpreted as an array of values by the component.
I JUST edited the component to allow that.
It works like this the first argument to Execute is of course the SQLString before substitution, if the second argument is an array object and there are NO other arguments beyond that.
the second argument is interpreted as an array of arguments
| Code: |
| #call @comsql.Execute("SELECT * FROM sometable WHERE junk=? AND thing=?","Whatever","Something") |
is the same as
| Code: |
| #call @comsql.Execute("SELECT * FROM sometable WHERE junk=? AND thing=?",%array("Whatever","Something")) |
or
| Code: |
#var somearray %array("Whatever","Something")
#call @comsql.Execute("SELECT * FROM sometable WHERE junk=? AND thing=?",@somearray) |
To get this functionality though you will have to update your Sqlite.ZMUD.WSC file with what is just now currently on the first post. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Mon Apr 21, 2008 1:35 am
|
Thanks, Dharkael. I see that you added what I requested on my post yesterday. That's quick service. I did figure out that an array would do just what I wanted after I re-read the documentation so now I will give it a try. My stuff is working quite well and my head if full of ideas, one of which is to create temporary views (with the CMUD database they are permanent but since SQLite only create the views when you access them, I guess there is no point in making them "permanent", though I've done that and it works).
OK, let me re-register my WSC file and give this a try.
EDIT: BTW, I created a COM object last night, logged off and came back this morning. The COM object was still active and the file was still open. I am wondering if Windows re-establishes the COM connection automatically on start-up.
EDIT: About what I put on my EDIT about WINDOWS, that doesn't make sense but I bet what is happening, and this makes sense, is that CMUD reloads the package and re-issues the %comcreate to refresh the variable because I strong-typed it (I selected COM object from the options menu). |
|
_________________
Sic itur ad astra. |
|
|
|
Fang Xianfu
GURU
Joined: 26 Jan 2004
Posts: 5155
Location: United Kingdom
|
Posted: Mon Apr 21, 2008 3:12 am
|
Presumably the file is still open because it was ActivePython that opened it and not z/CMUD. ActivePython was never told to close the connection (or itself) so it never did, it just stayed open in the background waiting for more instructions. I have no idea how you'd go about closing ActivePython itself, but closing the file should be simple enough.
|
|
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Mon Apr 21, 2008 3:26 am
|
But I took Windows down and turned the machine off. When I brought it back up, the file was open. So maybe Python keeps a table of open connections and CMUD must refresh the object on this side. So the end result is that the connection stays "open" even when the machine is turned off.
|
|
_________________
Sic itur ad astra. |
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Mon Apr 21, 2008 3:53 am
|
Yeah that doesn't make sense Anaristos.
You don't even have to shut the comp down just shut CMUD down and reopen it.
Fang's supposition is incorrect WSC COM components behave exactly as other COM components, that is once the reference count reaches zero the object is released from memory.
WSC or not sometimes it seems ZMUD/CMUD takes a while to release the object but there should be no way for it to persist past the closing of CMUD.
You can verify that your database file is being closed by using FileMon
using FileMon you can create a filter to log read/writes/open/close request and if these request suceeds.
You can clearly see that when you close CMUD or reassign your comsql var that the db file is closed.
EDIT:even setting the variable type to COM Object didnt change anything.
What exactly do you mean by connection "stays open" what exactly do you do to arrive at that conclusion? |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Mon Apr 21, 2008 4:50 am
|
This morning, without using %comcrate or @comsql.Open() I proceeded to access tables in my database. That is why I came to the conclusion that CMUD must be refreshing the object just as it does other variables when the main package is loaded. I didn't mention before that I had checked the default box without giving a default value for the COM variable (@comsql). This might have made CMUD re-issue the %comcreate. Just a guess in my part, though. I am about to re-start CMUD. I'll let you know what happens.
EDIT: OK, I tried it. I was dead wrong. I had to re-issue both the %comcreate and the @comsql.Open(). |
|
_________________
Sic itur ad astra. |
|
|
|
xekon
Apprentice
Joined: 11 Oct 2007
Posts: 154
|
Posted: Tue Apr 22, 2008 9:05 pm
|
I ran the demo but with a file path. it worked, i even changed the demo to just do the following and it returned the results from the db3 file.
| Code: |
#var stuff %comcreate("Sqlite.ZMUD.WSC")
#call @stuff.Open("C:\Core.db3")
#for @stuff.Execute("SELECT * FROM herblist WHERE method=? AND effect REGEXP ?","Eat","(C|c)ure") {#echo ******;#showdb {%i}} |
When I try and do the following however I get nothing... no ******, no nothing... so i can only assume that it dont like my db for some reason... I can open the db with some of the sqlite3 db ides though, so im not really sure whats wrong, any pointers? And yes I simplified the query just to be sure that wasnt the problem, it didnt change anything... still nothing happens at all.
| Code: |
#var eqall %comcreate("Sqlite.ZMUD.WSC")
#call @eqall.Open("C:\alleq.db3")
#for @eqall.Execute("SELECT * FROM allequip") {#echo ******;#showdb {%i}} |
|
|
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Tue Apr 22, 2008 9:20 pm
|
LastError is your friend.
#echo the LastError after each call to see if helps you spot the problem. ala:
| Code: |
#var eqall %comcreate("Sqlite.ZMUD.WSC")
#echo @eqall.Open("C:\alleq.db3")
#echo Error fter Open?:@eqall.LastError
#for @eqall.Execute("SELECT * FROM allequip") {#echo ******;#showdb {%i}}
#echo Error after Execute?:@eqall.LastError |
Try that and let me know what comes up. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
xekon
Apprentice
Joined: 11 Oct 2007
Posts: 154
|
Posted: Tue Apr 22, 2008 9:25 pm
|
oooo thank you for that Dharkael
| Code: |
Error after Open?:
Error after Execute?:Could not decode to UTF-8 column 'name' with text ':::Manicúla Sutta:::' |
|
|
|
|
|
xekon
Apprentice
Joined: 11 Oct 2007
Posts: 154
|
Posted: Tue Apr 22, 2008 9:27 pm
|
figured it out, i changed that odd 'character set' u to a regular 'u' and it just happened to be the only item in my db with that problem, after that it ran perfect.
This is sooooooo awesome, thank you very much for posting this to the forums Dharkael, I am really having a blast with it, time for me to get scripting some neato queries. |
|
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Tue Apr 22, 2008 10:45 pm
|
Okay Xekon just updated the component it should no longer have problems with non UTF-8 strings.
I didn't change the interface so it won't be necessary to (un)register the component again.
You may have to restart ZMUD/CMUD to see the effect.
I never even thought to test it with anything but ascii characters. |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Wed Apr 23, 2008 12:18 am
|
OK, Dharkael, I just confirmed that I must create the COM object as follows:
| Code: |
#VAR comsql %comcreate("SQLite.ZMUD.WSC")
|
and not this way:
| Code: |
comsql = %comcreate("SQLite.ZMUD.WSC")
|
Last night, remembering you said you had no problems using the latter format, I changed it just before logging off. Today, forgetting that I had done that, I spent a half hour trying to figure out why the file couldn't be opened. There was no error. Also none of the properties returned any values. Finally, I remembered what I had done and returned to the earlier creation format and everything began to work again. I am using CMUD 2.18 and I believe you are using the BETA version. Perhaps this is a bug that has been fixed in your version.
Something to be noted is that the first format did create the COM object. It was just not operational. |
|
_________________
Sic itur ad astra. |
|
|
|
xekon
Apprentice
Joined: 11 Oct 2007
Posts: 154
|
Posted: Wed Apr 23, 2008 12:41 am
|
Neat so here is what im working with now:
| Code: |
#var eqall %comcreate("Sqlite.ZMUD.WSC")
#call @eqall.Open("C:\alleq.db3")
#for @eqall.Execute("SELECT * FROM allequip WHERE level BETWEEN 140 AND 142") {#echo ******;#showdb {%i}}
#echo "Error after Execute?:"@eqall.LastError |
here is the result:
| Code: |
Error after Open?:
Error after Execute?:cannot concatenate 'str' and 'NoneType' objects |
Now I hate to bother you again, but what does this mean, i noticed 'str' is something used in the com object... does this mean I need to make an adjustment to my database? or does the Com object need another adjustment?
My DB consists of entirely Integers and Varchars. The level field is an integer. |
|
Last edited by xekon on Wed Apr 23, 2008 2:23 am; edited 2 times in total |
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Wed Apr 23, 2008 1:53 am
|
You could explicitly tell SQLite3 what type your columns are:
| Code: |
#CALL @eqall.Execute("CREATE TABLE xxxxx (level Integer, dirt Text)")
|
Though level may be a reserved word and that may be your error. |
|
_________________
Sic itur ad astra. |
|
|
|
xekon
Apprentice
Joined: 11 Oct 2007
Posts: 154
|
Posted: Wed Apr 23, 2008 2:13 am
|
well the table already exists... and has 7,000 items(record) in it.
The data type of the level field is already defined as an integer, im retrieving data not storing it.
I changed the field name from level to ilevel, same problem. |
|
|
|
|
Dharkael
Enchanter
Joined: 05 Mar 2003
Posts: 593
Location: Canada
|
Posted: Wed Apr 23, 2008 2:59 am
|
Okay the problem is this
Some of the results being returned to you have a NULL value which is translated to Python as None.
Since everything is returned to ZMUD/CMUD as a string, problems arose when we tried to concatenate a string with a None/NULL type.
Now NULL is NOT the same as an emptry string, NULL means no data.
How do we represent no data as a string?
Well I've added the ability for the User to decide,
I've added the readable/writable property called NullString when the component sees a NULL value it will use whatever is specified in NullString to represent it.
The default is an empty string but if you allow yourself to believe that NULL is the same as empty string it will catch you offguard at some point.
The database itself does not equate NULL to an empty string
Really the best thing to do, is just not to allow NULL values in your database.
You can specify this when creating the table by ending each column declaration with NOT NULL
If you've already got NULL value you can just write an update statement to update that to some other "default value"
Anyways NullString will at least let you control how you see NULL's it wont control what NULL means to sqlite3.
Because I added a property and therefore changed the interface, it will be necessary to unregister and then register the component.
look at this page for info on how SQLite handles NULL's internally |
|
_________________
-Dharkael-
"No matter how subtle the wizard, a knife between the shoulder blades will seriously cramp his style." |
|
|
|
xekon
Apprentice
Joined: 11 Oct 2007
Posts: 154
|
Posted: Wed Apr 23, 2008 3:24 am
|
WOOOOO! you are the man Dharkael.... can you show me the changes you made to the object... or better yet maybe I should read up on Python, anyways The null error I was getting has affectively been stomped out.
I completely agree with you on the null fields, the problem is that most of the data came from the http://iwuvaard.org/ and there are just a TON of fields that have Null values so this way of handling the null values works much better for my case.
here is my next problem:
| Code: |
#var eqall %comcreate("Sqlite.ZMUD.WSC")
#call @eqall.Open("C:\alleq.db3")
#for @eqall.Execute("SELECT * FROM alleq WHERE level BETWEEN 140 AND 142") {#echo ******}
#echo "Error after execute?:"@eqall.LastError |
returns:
| Code: |
| Error after execute?:cannot concatenate 'str' and 'int' objects |
Now I think I understand why the concatenate is an issue and its because #showdb shows the entire record and I will probably be working with the individual fields... so this may not really be an issue.... unless you can think of an instance in which you might need to.
EDIT: Wait just attempting to show the ****** to indicate that the query worked is throwing the error... so im not really sure what i need to do. |
|
|
|
|
xekon
Apprentice
Joined: 11 Oct 2007
Posts: 154
|
Posted: Wed Apr 23, 2008 3:45 am
|
Also one more question.. when I run a SELECT query how do i access the individual values
like #show @eqall.record.field(value) ....
I figured it out:
| Code: |
| #SHOW %db(%i,"name") |
|
|
Last edited by xekon on Wed Apr 23, 2008 4:26 am; edited 1 time in total |
|
|
|
|
|
|
