Databases and Threads

I am uploading data to a Microsoft SQL database, and I decided to use a thread to allow the user to see the progress. All code worked fine without threads, but inside the thread. Below is the code that is causing problems. As I step through the code in the debugger, when I hit the first line of code below, I am sent to the Window, and I am kicked out of the debugger, as if all code has completed executing. Any ideas.

rs = app.dbSQL.SQLSelect(SQL) If app.dbSQL.Error Then //Put messages in an object to be displayed outside of the thread lbClaimsMessagesCol1.Append("Fail - Claim sheet name not found in database") lbClaimsMessagesCol2.Append("0") End If

from : Thread — Xojo documentation

The rs is dimensioned within the method, is the application considered a built-in property?

  1. dont share a db across a thread and another thread or the app etc
  2. and dont share any of its properties between threads and anything else
  3. when you think you need to realize you will have weird hard to find but - just dont
  4. IF the thread alls this method in APP then its “part of the thread” - everything called from a threads run method is
    and in that case I suspect lbClaimsMessagesCol1 is a listbox and that IS UI and you cannot touch UI from a thread

Norman,

lbClaimsMessagesCol1 is a property of the window that I am using to share information in and out of the thread. If this is a no-no,
how do you pass information from a thread?

what IS LB ? a listbox instance ?
if so that UI and that a no no

one way to get stuff into and out of threads is

  1. make a subclass of thread
  2. add a private property to it - mTimer
  3. add a method to your thread subclass - timerHandler( instance as timer )
  4. add an event definition to your thread - UpdateUI ( here you can make it pass whatever params you want )
  5. add “raiseEvent UpdateUI” to the method called TimerHandler
    you will need to make sure you pass the parameters you defined in step 4
  6. add a method to the thread - Run
             mTimer = new Timer
             addhandler mTimer.Action, AddressOf timerHandler
             mTimer.Mode = Timer.ModeMultiple
            mTimer.Period = 10 // or whatever you want
            mTimer.Enabled = true
    
            // and whatever else you want
    
           Super.Run

what this does is

  1. adds a private timer to the thread that will run its action event every X many msec
  2. timers ALWAYS run their action event on the main thread
  3. and since the timer is part of the thread it has access to whatever properties are defined in the thread
  • at least this should be right I wrote most of this just in here without running code as I’m in the middle of something else
1 Like

Thanks Norman

lb is an array property of the window that I was using to store information to be passed to the listbox, providing progress to the user. I will now make a thread subclass with private properties rather than putting the properties in the window.

What still confuses me is that I get an error if I attempt to access a UI, but when I execute the recordset code I am passed back to the window (even though I am stepping through the code one line at a time in debug mode), and there are no error notifications.

if its JUST an array that should be ok from a thread afaict
but its hard to tell for sure from that short snippet what the issue might be
nothing obvious

It doesn’t even make it to the array, when it hits the line below is when I lose the debugger

 rs = app.dbSQL.SQLSelect(SQL)

SQL is a string to execute a stored procedure.

"execute SPclaimTabExcel @Customer = '" + Customer + "'"

Perhaps it database in the app cannot be reached?

Well have a look on examples -> Desktop -> UpdateUIFromThread-> UIThreadingWithTask, is closely to what Norman mentioned. and it works quite well, I start to use it in few places just to avoid the progress rainbow wheel blocking the interface.

Hope it helps.

If I do not use the database in the App, and put the database in the method I am running, then it works fine. I still do not understand why the debugger does not work.

Thanks everyone for all the assistance. I love this forum.

Believe it or not, getting or setting properties of windows is considered accessing the UI, so you’ll need to find a way to work around that…:frowning:

Of course, using a property of a window is NOT considered accessing the UI.

That wasn’t my experience, but then again I’m not a very good programmer. The only way to find out for sure is to try it.

This is correct. I‘m doing this all the time.

It depends on the property. Any property you create on the Window is fine. Not part of the UI. The built-in properties may or may not be UI.

True, but it does tightly couple the thread to the window for no good reason. I prefer to subclass the Thread class and add such properties to it.

Better be safe than sorry :slight_smile:

I count running Threads in each Scope and let Objects not run out of Scope until all Threads in it’s Scope are = Thread.NotRunning.

I was successful at updating the UI when updating a database from all Excel files contained in a directory. Now I need to take data from a listbox and update a database. Since I cannot access any UI from the thread how can this be done. I could write the listbox information to a property in the thread, but that would add a substantail amount of time to the execution. Is there a more efficient method?

You can do

dim s as string = myListBox.Cell(-1,-1)
...
dim rows() as string = split(s, EndOfLine)
for i as integer = 0 to Ubound(rows)
   dim cols() as string = split(rows(i), chr(9))    // tab
   ...
next

That puts the entire contents of the listbox into the string in one go. Hand that to your thread. You basically get a tab-delimited file.