VB 6 ExecuteSPNewThread

Topics: Developer Forum, User Forum
Jul 12, 2008 at 8:06 PM

Greetings Fellow Developers,

     I know this is a .NET site but I came across a new MAS 500 DataManager method I wasn’t aware of before but now that I know of it, it will become a common use item for me in the future.  That method is ExecuteSPNewThread.  Unlike the method ExecuteSP, this method executes a SQL stored procedure in a separate thread other than the form.   I happened to come across this method while looking up the proper syntax for ExecuteSP in the SDK On Line Reference Guide.    

    As many of you know, one of the most annoying aspects of MAS 500 is the “Not Responding” message you get when trying to interact with a MAS 500 form that is executing a long running SQL process such as posting.   Customers I’ve worked with have voiced frustration at this short coming of MAS 500.   However, with ExecuteSPNewThread, you can now build responsive screens that can still be interacted with while your long running processes are executing!

   Now there is a drawback to using this method.   You cannot run any other database operation while your long running process is executing on its own thread.  So depending on your needs and the purpose of your task this could be an issue.  It is a decision you’ll need to make during the design phase of your project.   However, in most cases I don’t believe this drawback will be an issue.

To see this capability in action simply create a stored procedure that mimics a long running process.  Use SQL’s WAITFOR method to simulate this.

      Example:  WAITFOR DELAY ’00:00:30’  --Delay of 30 seconds before proceeding

Next use the MAS 500 SDK VB 6 Wizard to create a Blank Project

NOTE: You’ll need to add code to the general section of the VB form to correct the compile error for Customizer or remove all references to Customizer in order to compile your project.  This is a known bug with this wizard first reported in 7.05 that has yet to be corrected.

   Now add a button to the form.  Within the click event of the button add the following code:

With moClass.moAppDB
                .SetInParam  “00:00:30”    --I added a parameter to pass in my delay value
              .ExecuteSPNewThread (“MyLongRunningProc”)
                Do While .IsExecuting
End With

   Compile your project and add it to the Business Desktop.  Start the new task and click the button.   While the stored procedure is running, you can move your screen around, minimize it, and maximize it.  This is something you could not do before when simply using ExecuteSP on a long running stored procedure.  

    This is very simple example of the ExecuteSPNewThread.  You are still responsible for handling screen events that the user might do while waiting such as hitting the button again, or some other button or menu item on the form.    There is also a cancel feature associated with this method as well.  However, once the stored procedure is executed there is no way to cancel it and roll back any changes it might be doing.

     One possible way to have a cancel feature is to create a table with a cancel flag in it.  Add code to your stored procedure to initialize this flag to false and add code to check the flag before committing any data changes.   On the VB 6 side of the equation, add a quick SQL update statement to update the flag to true after cancelling ExecuteSPNewThread since we can’t interact with the database while the SP thread is running.

     With ExecuteSPNewThread it is now possible for MAS 500 developers to create responsive forms for long running processes that mimic other Windows application on the market today.  I hope you find that this method will be a valuable asset in your projects.