I have a general question about threads prompted by something strange I'm seeing in an application I developed involving ODBC access to Microsoft Access and SQL Server databases. Let me present the context of the problem first. I have been developing some tutorials on ODBC database access in two different languages, e.g., C++ and PowerBASIC. The programs are GUI apps in pure Sdk style. The way I created the demonstration programs is I created a main window with four buttons on it. When the 1st button is pressed the program scours the registry for SQL Driver information and opens up a scrollable output screen where all the registered ODBC drivers plus their attributes are listed.
Pressing the second button dumps a small sample Microsoft Excel worksheet to another output screen using ODBC.
Pressing the 3rd button creates a Microsoft Access database, creates a table within the database, inserts a few sample records in the database, then creates the output screen and displays diagnostic info and the records added.
The fourth button does the exact same thing as the third except with either Sql Server Express or MSDE.
The program is designed so that each button press creates a new output window, and if you repeatedly click a button, instead of creating a new database, records are simply added to the one created on the first button press. Each output window takes care of its own memory and cleanup.
In the various button press procedures I first turn the mouse cursor into an hourglass, and then turn it back into an arrow when the ODBC functionality is complete and the output screen displays its info. I noted a problem with this with the Sql Server button in that when the button was clicked I would very, very, briefly see the hourglass cursor - and sometimes not at all, but the output window might not become visible for a variable number of seconds - sometimes five or six, while the heavy duty Sql Server database engine created the database, inserted records, and extracted records. This is naturally disconcerting to the user when something like this happens as you wonder if the program locked up or whatever. We've all experienced this with hourglass cursors and progress bars.
It disconcerted me enough that I put a lot of effort into moving the code around in various places to see if I could get the hourglass cursor to remain until the output window showed itself, but all my modifications failed.
My only explanation for this is that asynchronous processing is occurring within the various ODBC and Sql Server layers involving multiple threads of execution such that my program's thread is returning before other threads terminate; hence no or little hourglass cursor in my program.
So - not wanting to give up and determined to 'beat it', I decided to fight fire with fire! I used my own threads! Now I'm not a threading expert by any means, although I have used them.
What I did was spawn a new thread in the button click procedures for the Access & Sql Server functionality I described above. I also did a WaitForSingleObject() on the thread handles in the button click procedures so that I would turn the cursor back into an arrow only when all the database machinations in the various procedures were completed. And it worked! It worked wonderfully well. It completely solved the problem with the code in both languages, and I have yet to see it fail once! In the case of Sql Server, if its the first time I've run the program with a computer startup, it oftentimes takes several seconds for the output window to appear, and the hourglass remains the whole time.
So what is my problem? Well, the problem is this - for me to get the program to work in C++ I had to enclose some of the ODBC code in CRITICAL_SECTIONs. If I didn't, very often something would fail somewhere - either in data insertions or dumps (SELECT statements). I could not get the program to work satisfactorily without doing this. This might not seem strange to you except that these programs have no global variobles at all! None. Zilch. In the case of data insertions all the data was stored in local variables, i.e., stack variables within each function. Same with everything else.
My understanding of the need for critical sections is that global data needs protected from context switches that might leave static data in an unstable state. However, while Sql Server itself is 'static' or 'global', so to speak, to my application, my data itself isn't. My understanding is that if there was a thread context switch (and there surely were) at any point during the execution of my ODBC code, my local variables would have been saved to some sort of thread local storage or stack storage and restored upon resumption of my database thread. Not so? I'm asking here. I just don't know. Could you give me any explanation of what is going on here? I can assure you there are no errors in the ODBC code. Without critical sections or even threads the code runs fine. Its just that hourglass thing. However, if I use threads, the same code won't work unless I enclose some of the insertion/retrieval code in critical sections.
Here is another strange wrinkle. Today I just finished the exact translation of the C++ program to PowerBASIC. And that one doesn't seem to require the critical sections. I did the thread thing and the WaitForSingleObject() on the thread handle, and so far it seems to work perfectly without the need to enclose any of the thread code in critical sections.
Can someone please give me some feedback on this. It is a rather interesting problem! Also, if anyone is interested enough, I'd be happy to forward the code for either or both programs. The C++ program I've tested with VC6, VC9, Dev-Cpp, and CodeBlocks. It behaves the same in all of them.