Zugg Software :: View topic - sqlite field not updating using $row.Item("fieldname") = <new value>

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

CMUD 3.34

I have the following code in an event:

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

I just changed

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

Well.. it sort of works. Most of the time it is fine... but from time to time I now get this message in a pop-up "OK" box:

Posted: Tue Apr 26, 2011 4:51 pm

I haven't seen this problem before. Since you are already checking the RecordCount, I'm not sure how this could ever happen. You might need to run the Script Debugger to see exactly which trigger/script is running when you get that message. Maybe you have another script that is also using the database, or maybe your script shown above is getting executed more than once at the same time.

For example, if you have multiple threads then maybe the event above is running more than once?

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

I'll try the script debugger, but it doesn't happen that often. And I'm not accessing the db from outside of CMUD... and am not (intentionally) using threads. Is it possible for things to get "executed more than once at the same time" without intentionally creating separate threads?

I still wonder if it's tied to my original problem. Shouldn't the {$row.Item("lowPrice") = $price} command have taken care of edit() and post()? It just wasn't updating the database. Now I wonder if it *was* but only rarely, and now those rare occasions are what's causing the "2 records" error since I've wrapped the command in calls to edit() and post(). Or maybe that makes no sense. Smile

Thanks again.

GURU Joined: 23 Dec 2000 Posts: 4834 Location: USA

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

Ok, I captured what was going on in the script debugger at the point in time when I got the "2 record(s) updated" error. I'm probably missing something, but it looks right to me. This happens when I check a shop's inventory. I have triggers to check if each item is in my sqlite3 db. For each item that is in the db, I update the item's highPrice or lowPrice fields if the current sale price is higher or lower than the existing values.

Here's the debugger output:

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

The error occurs during the onShopInventory event handler. In the case of the shop inventory I viewed for this particular debug log, mud output stopped right after displaying the line

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

and here's the raw mud output that I'm processing with this script:

Posted: Wed Apr 27, 2011 5:01 pm

It looks good to me too. Try adding some #SHOW commands in your onShopInventory script before and after various lines, like your Edit and Post lines, etc. Also add a #show command near the top to actually display the RecordCount. See if you can determine which line of the script is actually causing the popup error message.

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

All kinds of weirdness going on.

I added echoes in the following places:

state 0 of FINDMISSINGPURCHASELONG -> #ECHO FINDMISSINGPURCHASELONG name = @current_item
state 1 of FINDMISSINGPURCHASELONG -> #ECHO RAISE onShopInventory @current_item %1 (***BEFORE*** I #RAISE onShopInventory)
inside my onShopInventory event -> #ECHO Executed onShopInventory with arguments $name $price

Output from a shop listing is:

Posted: Thu Apr 28, 2011 4:29 pm

I can see this in your original Script Debugger output too:

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

I just did the global search, and onShopInventory was found in only 3 places:

The named event handler.
That handler raised in FINDMISSINGPURCHASESHORT.
That handler raised in state 1 of FINDMISSINGPURCHASELONG (although the global search lists this match with a '2:' in front of it, like it was state 2)

As always seems to be the case.. the answer (or part of it anyway) was staring me in the face. The trigger pattern for FINDMISSINGPURCHASESHORT was matching the second line of 2-line items. I fixed that, and now onShopInventory only gets raised once per item.

I'm still a little confused. Even if I was raising the event twice, why was I getting errors about updating 2 records? The incorrect raising of the event was sending a blank item name, resulting in RecordCount() not being equal to 1. Since all the Edit()/Post() calls are wrapped inside a test to make sure RecordCount() IS 1, I'm not sure how this error occurred. Even if I had wanted to raise the event twice for the same line of output, shouldn't they happen one after the other based on trigger priority?

Anyway, thanks again for the help. It seems to all be working now, and I was even able to remove the calls to edit() and post() and rely on the direct assignments work.

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

Whoops.. spoke too soon.

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

So I decided to try and start with a simple stand-alone alias to see if I can duplicate the problem. However, what should be simple is apparently not so simple for me. Sad

Here's the alias, which goes into an infinite loop. Mad

Posted: Fri Apr 29, 2011 4:24 pm

I'm not near my main computer to test this right now, but needing the Edit/Post was a separate issue from the popup error about the records. My guess with calling your event twice was that it was a threading issue. Raising an event doesn't necessarily block other triggers from running on the same line sometimes. So I think there were rare timing cases where the previous event wasn't finished when another call was raised.

I will test your new infinite loop issue next week, but hopefully somebody else can test it this weekend to see what might be wrong. At first glance your code seems ok to me.

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

Well I think I got my script working properly. Aside from the known stupid things I had wrong, it took two fixes that may or may not represent bugs...

1. I wrapped every occurrence of $row.Item("fieldname") assignments inside of calls to $row.Edit() and $row.Post()
The database just wasn't getting the updated values without this.

2. I replaced my onShopInventory event handler with an alias.
There apparently is some threading issues such that you can't rely on raised events running in the order they were raised and not stepping on each other. Since I've made the switch to an alias, I've had no more issues with the error: 2 record(s) updated. Only one record should have been updated. I did make this fix after the first one, so it may be possible this was related to the first issue.

Thanks again for the help. Smile

Posted: Mon May 02, 2011 4:00 pm

Glad you got it working. I have put the issue with Edit/Post on my bug list. The other issues does make some sense since #events are handled by Windows message events and there is definitely the possibility of getting them running at the same time in different threads. Your idea to use an alias instead is a good one since aliases do not create new threads.

Apprentice Joined: 18 Jan 2001 Posts: 116 Location: USA

Sorry to keep flip-flopping... but I was wrong (again). Changing the event to an alias did not solve the problem after all. Just wanted to let ya know. It's not a huge issue since it doesn't seem to be affecting anything other than I just have to hit <enter> to clear the dialog box when it pops up.

Posted: Tue May 03, 2011 6:02 pm

Hmm, thanks for letting me know. It makes me wonder if the database system itself is somehow delaying some operations or doing something in a multi-threaded way that might be causing this. If this was using ADO/COM then I'd be suspicious, but the SQLite database shouldn't be doing this kind of stuff, so I'm still rather perplexed.