 |
orphean
Apprentice
Joined: 21 Oct 2008
Posts: 147
Location: Olympia, WA
|
 Posted: Sat Oct 24, 2009 6:47 am
[3.11 Pro] Remote sql database weirdness.
|
Can anyone out there with Cmud pro try something out for me? There is a player run mysql database for Aardwolf that hosts quite a bit of useful info. I started playing with the new remote sql stuff tonight to see if I can't get some cmud/aard/database integration going.
I am getting really inconsistent results though.
Here are a couple of test aliases I am using:
| Code: |
<?xml version="1.0" encoding="ISO-8859-1" ?>
<cmud>
<alias name="dbconnect">
<value>#sqldb iwuvaard iwuvaard mysql iwuvaard.ipdaily.net 3306 guest iwuvaard</value>
</alias>
</cmud> |
| Code: |
<?xml version="1.0" encoding="ISO-8859-1" ?>
<cmud>
<alias name="dbtest">
<value>row = %sql(iwuvaard, "SELECT * FROM areas")
#loop 100 {
#show "Name: " @row.Item("name")
#call @row.Next
}</value>
</alias>
</cmud> |
The 'dbtest' alias is ripped directly from Zugg's sample code. On a fresh startup of Cmud it will usually run perfectly (though not always) and will continue to run until I modify the alias script. Then no matter what I do all it will return is:
| Code: |
| Name: <COMObject>.Item |
And won't work until I restart cmud, and even then it seems to be a crap shoot on whether or not it will start working again.
Can anyone else gives this a try? Plop those into your Cmud Pro (make sure you have a mysql dll on your system), then 'dbconnect' and 'dbtest'. It should work. Now open the dbtest alias in the editor and change the loop to 10, save and try again, and the problem should manifest.
I've tried opening the database each dbtest and closing it with #sqlclose after the loop also and experienced the same behavior. |
|
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: Sat Oct 24, 2009 3:57 pm
|
I'm not at a computer I can test this on right now, but you might try adding a line like:
row = ""
to clear out the row variable at the end of the loop, or maybe right before the %sql function call to see if that helps with anything. It looks like "row" is getting set to a COM object that doesn't have any Item property somehow.
Or, the @row variable is getting converted to a String value instead of the COM object for the database. You can use the
#VAR row
command to display the variable to see what "type" the variable is set to. Maybe it's getting converted to AutoType somwhere in the editing process? |
|
|
|
|
orphean
Apprentice
Joined: 21 Oct 2008
Posts: 147
Location: Olympia, WA
|
Posted: Sat Oct 24, 2009 5:57 pm
|
Well row was being set as an Autotyped variable, so I explicitly set its type to ComObject hoping that would help:
| Code: |
Name: <COMObject>.Item
Name: <COMObject>.Item
Name: <COMObject>.Item
Name: <COMObject>.Item
Name: <COMObject>.Item
Name: <COMObject>.Item
Name: <COMObject>.Item
Name: <COMObject>.Item
Name: <COMObject>.Item
Name: <COMObject>.Item
Variable: + row (COM) <COMObject> |
So it seems I'm getting a COM object without an Item property. |
|
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: Fri Oct 30, 2009 9:16 pm
|
I was not able to reproduce this. You must have some other script that is modifying the "row" variable, or else you are missing a step that you are not telling me.
I put the libmysql50.dll library into my CMUD directory. Then I created a session and pasted your scripts shown above. I then typed "dbconnect" and it properly connected to the database. Then I did "dbtest" several times and that worked as expected.
Then I clicked the Settings button, clicked on the "dbtest" alias and changed the 100 to 20 and clicked the Save button. Then, back in the main window I typed "dbtest" again several times and it worked. I changed the dbtest alias several more times and never ran into any problems with it.
Try doing this offline if you were connected to the MUD to ensure that some script wasn't running at the same time that might have messed up your procedure. But until I can find a way to reproduce the problem, I have no idea what to fix. |
|
|
|
|
orphean
Apprentice
Joined: 21 Oct 2008
Posts: 147
Location: Olympia, WA
|
Posted: Fri Oct 30, 2009 11:09 pm
|
I'm using a different mysql dll. Hopefully that is the problem. Going to try this with a new mysql dll, in a blank session, disconnected from a mud, and in a fresh installation of 3.12 Beta Pro.
It's frustrating when you can't reproduce an issue! :) |
|
|
|
|
orphean
Apprentice
Joined: 21 Oct 2008
Posts: 147
Location: Olympia, WA
|
Posted: Fri Oct 30, 2009 11:40 pm
|
Seems to work fine in both a blank session and connected to a mud with no scripts. So definitely something on my end. Problem solved ;) Thanks.
|
|
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: Sat Oct 31, 2009 12:05 am
|
Was it changing the DLL or the session scripts that did it? If it was the DLL, did you get it from the zApp Download area on this site, or from somewhere else? I wouldn't think it was the DLL since using the wrong version DLL with the wrong database would just give an error when you tried to connect to the database.
|
|
|
|
|
orphean
Apprentice
Joined: 21 Oct 2008
Posts: 147
Location: Olympia, WA
|
Posted: Sat Oct 31, 2009 2:56 am
|
It wasn't the DLL, it's something in my scripts. I'm guessing something is using 'row' which I haven't had the time to track down yet. It's definitely something on my end script-wise since without scripts and a mud connection it works fine. I'm going to start enabling the scripts one by one until it breaks then try to figure out what's going on from there.
|
|
|
|
|
orphean
Apprentice
Joined: 21 Oct 2008
Posts: 147
Location: Olympia, WA
|
Posted: Wed Nov 04, 2009 6:03 pm
|
Another script was using @row, whoops. :) So now I have things working more or less, which is really cool, and I've contained the database variables into their own class.
I've encountered a strange problem when trying to read all the results: Eof() never returns true. Here is the script:
| Code: |
<func name="iwuv_queryall" id="7">
<value>$ret = ""
$key = 0
#newvar iwuv_row %sql( iwuvaard, $query )
#while ( !@iwuv_row.Eof() ) {
#show "Val: " @iwuv_row.List
$ret = %addkey($ret, "" $key, @iwuv_row.List)
$key = $key + 1
#call @iwuv_row.Next
}
#return $ret</value>
<arglist>$query</arglist>
</func> |
And here is the output I get:
| Code: |
<snipped>
.
.
.
Val: id=357|from_level=155|to_level=195|level_lock=|builder=Dirtworm|name=Black Lagoon|virtual=0
Val: id=358|from_level=200|to_level=201|level_lock=|builder=Tir|name=Ketu Uplands|virtual=0
Val: id=359|from_level=25|to_level=35|level_lock=|builder=Dras|name=Sheila's Cat Sanctuary|virtual=0
Val: id=359|from_level=25|to_level=35|level_lock=|builder=Dras|name=Sheila's Cat Sanctuary|virtual=0
Val: id=359|from_level=25|to_level=35|level_lock=|builder=Dras|name=Sheila's Cat Sanctuary|virtual=0
Val: id=359|from_level=25|to_level=35|level_lock=|builder=Dras|name=Sheila's Cat Sanctuary|virtual=0
Val: id=359|from_level=25|to_level=35|level_lock=|builder=Dras|name=Sheila's Cat Sanctuary|virtual=0 |
And it will happily keep looping and printing out the last row from the results ad infinitum until I #stop the thread I have it running in (it does the same thing without the thread with the added bonus of locking up Cmud ;) yay infinite loops).
As long as I ask for a certain number of results and use a #loop (which a sister function to iwuv_queryall does) it works fine. Any ideas on what's going on here Zugg? I'm happy to try anything if you need more information. I tried using the Data property of the zQuery and it returns fine but it wasn't clear to me how to work with the com variant array that I get from it within Cmud.
And as a small addendum, I really hope you're still planning on ZML Forms for CMud. Having a ui interface to this database from within cmud would be pretty damn cool. |
|
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: Fri Nov 06, 2009 5:26 pm
|
Hmm, the Eof seems to work with SQLite databases. I'll test this with a MySQL database to see if it is something specific to your setup or some kind of MySQL driver issue.
You'll probably see the rewrite to the Database Module for the SQL GUI interface before you see the ZML forms for CMUD. |
|
|
|
|
orphean
Apprentice
Joined: 21 Oct 2008
Posts: 147
Location: Olympia, WA
|
Posted: Fri Nov 06, 2009 7:05 pm
|
| Zugg wrote: |
| Hmm, the Eof seems to work with SQLite databases. I'll test this with a MySQL database to see if it is something specific to your setup or some kind of MySQL driver issue. |
Thanks Zugg. If you wanted to test on a 'live database', #sqldb iwuvaard iwuvaard mysql iwuvaard.ipdaily.net 3306 guest iwuvaard, will connect you to what I'm using. It's readonly, but that shouldn't matter to test this.
| Zugg wrote: |
| You'll probably see the rewrite to the Database Module for the SQL GUI interface before you see the ZML forms for CMUD. |
Sigh, well a man can dream, can't he?  |
|
|
|
|
|
|
|
