Zugg Software :: View topic - Database scripting: #dbdelete and %query() troubles

Apprentice Joined: 13 Feb 2004 Posts: 157 Location: USA

I've built a pretty large crafting system using the database module of zMUD and most of it works pretty well, but I have two problems that I can't make heads or tails of.

1) #dbdelete simply doesn't do anything for me and I have no idea why. I only use it in one place where I save the results of a %query() in a variable, then loop over it in a #forall and do #dbdelete %i. The values of %i are the right values (of the format "123wo"), but the #dbdelete line just doesn't do anything, and I can't see why. I've tried different combinations of #dbreset, #dbfirst, #dbload, and #dboffline before and during, but nothing changes the outcome. Any ideas?

2) I use %query() extensively, including a klugy mess where I build a %query() dynamically, and it works for me (with adequate use of #dbreset and such) in all but one place, where I'm checking an ID number to see if it's in the database. Using the exact same code, or using a simpler form (#if (%query( &ID = %1) = "")), either way I get inconsistent results. %1 definitely holds the right values but the query sometimes returns null even when the ID is in the database. Sometimes, but not always, and completely inconsistently; passing through the same list of IDs twice, I can have it misfire 1/2 the time once, not at all another time, and midway between a third. If I double the query (#if (%query( &ID = %1) = "" and %query( &ID = %1) = "")) it'll misfire less often on average... how can that be? Anyone know of any gotchas I might be getting bit by, or a workaround?

SubAdmin Joined: 18 Nov 2001 Posts: 5182

The #DBDELETE issue is likely a bug. While the command has been around since the introduction of the database module I don't think anyone ever used it.

My best guess on the %query problem is that sometimes the %rec predefined variable has a value, and since ID is a field it occasionally is expanding the &ID portion of the expression based on the value of %rec. I know it is wierd for it to do this part of the time even when doubling up the queries with an AND. I would suggest trying '#IF (%query("&ID=%1")="")'. The reason this should work is that the parameter list for %query from the Function Wizard shows that the first paremeter is an Expression; making it subject to various types of expansion and evaluation before it gets intpretted by the function. By surrounding it in quotes we eliminate that unwanted behavior, and make use of the fact that zMud will substitute the %nn reference even in quotes.

An even more proper syntax would be #IF (%query(%concat("&ID=",%char(34),%1,%char(34)))) which makes the query parameter (&ID="actual %1 value"). In one case we surrounded the entire string with quotes so query did the evaluation, in the other the concat effectively does that part and additional quotes are passed within the query parameter.

Apprentice Joined: 13 Feb 2004 Posts: 157 Location: USA

Is there any other way to delete records under script control?

I'll give the %query() thing a try tomorrow. Thanks for the explanation, it makes sense.

Apprentice Joined: 13 Feb 2004 Posts: 157 Location: USA

The alternate %query() thing isn't helping. I added some debug code and found something odd. Every entry in my database has a unique ID but this query:
%query( %concat( "&ID=", %char( 34), %1, %char( 34)))
either returns nothing or everything, it never returns just the one item it should. But if I just type
#show %query(&ID=4348)
I get the proper result.

SubAdmin Joined: 18 Nov 2001 Posts: 5182

I just checked through one of my old scripts that made some farily heavy database usage and found I had no problems with straight forward forms of query. There seems to be no expansion or evaluation problems with it. This makes my guess above is totally erronius. The only thing that leaves is the value of %1, which you stated with certainty is correct at the query. I can say I am stumped without something more to go on.

Apprentice Joined: 13 Feb 2004 Posts: 157 Location: USA

What seems to be working now is this:

tempvar = %concat( "%query(&ID=", %1, ")")
#if (@tempvar = "") {...}

If I build only the inside of the %query it still evaluates &ID too early. I have to build the whole %query and then when I check the variable it evaluates it anyway.