Solved

Accessing database from multiple threads.

Posted on 2002-05-20
25
513 Views
Last Modified: 2013-12-14
Basically what I have is a multi-threading issue here.  I have created a CDatabase class to wrap CDAODatabase.  All it does it make access to the fields in the recordsets and some general database stuff easier.

In this program I have a base class called CSystem.  It makes a CDatabase object (MyDatabase) and does some database stuff when it initalizes.  I also have a class called CSubSystem that inherets CSystem.  It also uses this same object (MyDatabase) for it's database work.  Part of it's work is procssing a queue on a regular interval.  To do this, I use AfxBeginThread to start a new thread which uses SetWaitableTimer to make a timer.  The timer uses a static callback function in the same class which calls a non-static memeber function using a static_cast.  This is where my problem arrises.  The function called from this callback uses MyDatabase and always fails.  It works perfectly when called from the main program but will always fail when called from this thread.  I have tried using critical sections but they don't seem to work.  I'm not sure if I was using them right or not.  I'll post some of my code in a minute.  Please help.  Thanks.
0
Comment
Question by:Ra
  • 8
  • 7
  • 6
  • +1
25 Comments
 
LVL 2

Expert Comment

by:mirtol
ID: 7022066
You say it 'always' fails, is the object often in use by the maint thread then?

What is the error?

Does the thread at the calling time have permission to use the MyDatabase object? (ie the memory associated with it)

0
 

Author Comment

by:Ra
ID: 7022072
Here is the part of my code that deals with the threads and the database

// part of the header for CSystem
class CSystem
{
public:
     CSystem();
     virtual ~CSystem();

protected:
     CDatabase MyDatabase;    

};

// part of the header for CSubSystem
class CSubSystem : public CSystem
{
public:
     CSubSystem();
     virtual ~CSubSystem();    
protected:
     int m_StartTime;
     int m_Period;
     HANDLE m_TimerHandle;
     void OnTimer();
     static void CALLBACK TimerCallback(LPVOID pThis, DWORD low, DWORD high);
     static UINT ThreadProc( LPVOID pParam );
};

// part of the code for the functions in CSubSystem
CSubSystem::CSubSystem()
{
     m_StartTime = 30;
     m_Period = 30;
     AfxBeginThread( ThreadProc, (LPVOID)this );
}

UINT CSubSystem::ThreadProc( LPVOID pParam )
{
     // get the data for the timer
     int Start = static_cast<CSubSystem*>(pParam)->m_StartTime;
     int Period = static_cast<CSubSystem*>(pParam)->m_Period;
     //set the timer to keep the queue in check
     __int64         qwDueTime;
     LARGE_INTEGER   liDueTime;

     // Create a negative 64-bit integer that will be used to
     // signal the timer X seconds from now.
     qwDueTime = -1 * Start * _SECOND;

     // Copy the relative time into a LARGE_INTEGER.
     liDueTime.LowPart  = (DWORD) ( qwDueTime & 0xFFFFFFFF );
     liDueTime.HighPart = (LONG)  ( qwDueTime >> 32 );

     static_cast<CSubSystem*>(pParam)->m_TimerHandle = NULL;
     static_cast<CSubSystem*>(pParam)->m_TimerHandle = CreateWaitableTimer( NULL, FALSE, NULL );
     if ( static_cast<CSubSystem*>(pParam)->m_TimerHandle != NULL )
     {
          SetWaitableTimer(static_cast<CSubSystem*>(pParam)->m_TimerHandle, &liDueTime, Period * 1000, TimerCallback, static_cast<CSubSystem*>(pParam), FALSE);

          // put the thread into an alertable mode
          SleepEx( INFINITE, TRUE );
     }

     return 0;
}

void CALLBACK CSubSystem::TimerCallback(LPVOID pThis, DWORD low, DWORD high)
{
     OutputDebugString( "Timer callback" );
     static_cast<CSubSystem*>(pThis)->OnTimer();

     // put the thread into an alertable mode
     SleepEx( INFINITE, TRUE );
}

void CSubSystem::OnTimer()
{
     // all database calls fail when this is called from the TimerCallback function
     CString SQL;
     SQL.Format ("UPDATE Table SET Field1 = %d", 1 );
     MyDatabase.Command( SQL );
}
0
 

Author Comment

by:Ra
ID: 7022088
mirtol
> You say it 'always' fails, is the object often in use by
> the main thread then?

Yes, but usually not at the same time, but it is possible.

> What is the error?

No idea.  GetLastError return 0.  I can't let it display message boxes because this is in a DLL that is loaded from a non-interactive service.  I have a try/catch block which catches CDaoException and CMemoryException.  Neither gets caught.

> Does the thread at the calling time have permission to
> use the MyDatabase object? (ie the memory associated
> with it)

No idea as to how to do that... that's one reason why I posted this question.
0
 

Author Comment

by:Ra
ID: 7022104
btw, this is in MS Visual C++ 6.0 on Windows 2000.
0
 
LVL 2

Expert Comment

by:mirtol
ID: 7022105
If it weren't accessible you should get a memory exception.

Some thoughts:

Is the MyDatabase object initialized? (check by pausing program on the line and inspecting it - this will also tell you if you can access the object or not)

MyDatabase.Command ??? Is this even a method of the CDatabase class? (Which makes me wander how it even compiles...?) Surely you want MyDatabase.ExecuteSQL

How do you know the function fails?

I'd be tempted to try and capture CException in general just to check.
0
 

Author Comment

by:Ra
ID: 7022149
Sorry for the confusion. CDatabase is a class I created.  Command is a member function that passes the SQL statement to the Execute function of the CDAODatabase object within my CDatabase class.  I guess I should have been a bit more creative in my class naming.  I didn't realize at the time that there is a CDatabase class alredy available.  Could this be causing some issues???

All calls to the MyDatabase object (Execute and query) work find from the main thread.  When I try to call the command or query functions from the thread they always fall to the catch ( ... ) routine.  I'll add CException to the list and see what that gets me.

Here is the part of my CDatabase class, just so you see what I'm talking about.

// CDatabase header
#define GeneralDAOError   1001
#define MemoryExecption   9999

#define FIRST   1
#define     NEXT     2
#define PREVIOUS 3
#define LAST     4

#include <afxdao.h>

class CDatabase
{
public:
     CDatabase();
     ~CDatabase();

     int Query( LPCTSTR SQL );
     int Command( LPCTSTR SQL );

     LPCTSTR GetStringValue( LPCTSTR FieldName );
     int GetIntegerValue( LPCTSTR FieldName );
     long GetLongValue( LPCTSTR FieldName );
     BOOL GetBooleanValue( LPCTSTR FieldName );

     int DatabaseOpen( LPCTSTR Path, LPCTSTR Database, LPCTSTR Password = "" );
     void DatabaseClose();
     void RecordsetClose();
     int GetRecordCount();

     BOOL AtEOF();
     void Move( int Direction = NEXT );
     
     LPCTSTR GetError();
private:
     CDaoDatabase *m_DB;
     CDaoRecordset *m_RST;

     int RecordCount;
     char ErrorMessage[255];
};


// the command function looks like this
int CDatabase::Command( LPCTSTR SQL )
{
     int ReturnValue = 0;
     
     // this is simple... just execute the query and retrun any errors
     try{
          m_DB->Execute ( SQL, dbFailOnError );
     }catch( CDaoException DAOError ){
          // DAO error
          ReturnValue = DAOError.m_pErrorInfo->m_lErrorCode;
          sprintf( ErrorMessage, "CDaoException: %s    Source: %s", DAOError.m_pErrorInfo->m_strDescription, DAOError.m_pErrorInfo->m_strSource);
          DAOError.Delete();
     }catch( CMemoryException MemoryError ){
          // Memory error
          // ok, not much we can do here...
          // close what we can, and return an error
          DatabaseClose();
          MemoryError.ReportError();
          ReturnValue = MemoryExecption;
          MemoryError.Delete();
     }catch( ... ){
          // Unknow error
          ReturnValue = GeneralDAOError;
     }

     return ReturnValue;
}


The class is nowhere near perfect, just something I'm working on to make database access easier.  This class also handles the CDAORecordset object so it can do queries.
0
 
LVL 2

Expert Comment

by:mirtol
ID: 7022166
Ah, I see. The catch(...) routine will be the same as catch(CException someexception), execpt that you can reference the exception and then you can use someexception.GetErrorMessage to find out why it's failing.
0
 
LVL 2

Expert Comment

by:mirtol
ID: 7022171
sorry... and...

Calling your class CDatabase may cause errors (if it's not on your system then the compiler is fine with it and it won't be the source of any problems here) and if you keep it within your app you'll be fine (unless you make it windows, which I assume you won't ;)

But it is generally best and easier to use a different name, maybe CQryDatabase or CQryDb...
0
 
LVL 1

Expert Comment

by:jdrescher
ID: 7022218
Without reading all the discussion above..

I think your problem is that DAO is not thread safe and you should not use a DAO object in any thread that did not create the CDaoDatabase. As a result of these problems and others I moved to ADO. ADO also has restrictions with threads. You may not use the same connection object in more than one thread at a time. However this restriction, can be easily worked around by creating a new connection object for each thred that uses the db or using a connection pool.

John
0
 

Author Comment

by:Ra
ID: 7022222
I added the CException and made it return 1000, but it still falls to the '...' and returns the GeneralDAOError ( 1001 ).  I'm gonna rename the class to something else and see what that does.  I'm not including the afxdb.h file, which is the header for the real CDatabase class, so I wouldn't think there would be a problem, but some of the other afx stuff might be including it.  That would definately explain why I wouldn't be getting the drop down menu when I press a . after typing MyDatabase.
0
 
LVL 2

Expert Comment

by:mirtol
ID: 7022223
John:
This is true, but if a critical section is used (as has been tried) it doesn't matter how unthread happy the functions are...
0
 
LVL 2

Expert Comment

by:mirtol
ID: 7022236
No, you;d get a compile error if the CDatabase class was declared differently elsewhere.

The fact that CException is not caught suggests an access problem. Why can you not set a breakpoint in the function and inspect the MyDatabase object?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Ra
ID: 7022378
mirtol, I can't set a breakpoint because this is a DLL file.  When I try to run it, it complains about not having an exe.  If there is a way to debug a dll file, I don't know of it.  I've been using OutputDebugString and DebugView (http://www.sysinternals.com/ntw2k/freeware/debugview.shtml) to capture the output.  This is how I can tell what error it's getting.  It's not a very efficent way of debuging, but so far it's worked for me.  If you know how I can do real debuging on this, please tell me.

I'm gonna try this critical section stuff again.  I'm pretty sure I was just using it wrong.
0
 
LVL 2

Expert Comment

by:mirtol
ID: 7022393
Yep, you can debug DLLs by specifying a host application.

Go to project settings, debug and set the application to your host app. VC debugger will then run this app and when it calls the DLL you can use the debug facilities...
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7022551
As mentioned above, naming your class CDatabase is certain to cause you endless confusion.  Name it CDb or CMyDatabase or somthing.

I have encountered problems like you describe.  The trick is to open the database (and recordset if used) from within the thread code:

UINT CSubSystem::ThreadProc( LPVOID pParam )
{
    CSubSystem* pc= (CSubSystem*)pParam;
    pc->MyDatabase.OpenEx(....);
...
    pc->MyDatabase.Close();
}

-- Dan
0
 
LVL 1

Expert Comment

by:jdrescher
ID: 7023248
> I can't set a breakpoint because this is a DLL file. When I try to run it, it complains about
not having an exe.  If there is a way to debug a dll file, I don't know of it.

A simple way to debug a dll is to debug the application that calls the dll then open the source files (in the same workspace) from the dll and set the breakpoints.

John
0
 
LVL 1

Accepted Solution

by:
jdrescher earned 200 total points
ID: 7023275
The following articles describe problems with multithreading in MFC DAO:

DAO: Using DAO in DLLs
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore98/HTML/_core_dao.3a_.using_dao_in_dlls.asp


PRB: Thread Safety for DAO/Jet (Q169395)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q169395

MFC DAO, while modified to use Jet 3.5 if present on your machine, has not been modified to be thread-safe. It has a number of internal "state" variables that are not synchronized. Further, calls to critical operations such as database open and close have not been wrapped in any kind of critical section or lock to prevent re-entrance from other threads before completed.


DAO and MFC
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore98/HTML/_core_dao_and_mfc.asp

Caution   DAO is not supported on Win32s. Further, DAO 3.x is not thread-safe. You should use DAO only in the primary thread of an application.


John
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7023323
It looks like all you are doing is periodically hitting the database to do this:

  UPDATE Table SET Field1 = 1

(or perhaps some other periodic polling).  If that is the case, then a Window timer -- running on the U/I thread would be a better, more-easily implemented safer solution for you.

Alternatively, you could switch to the ODBC-oriented CRecordset (rather than CDaoRecordset) since it seems that that would be thread safe.  CRecordset has all of the same capabilites of CDoaRecordset.

-- Dan
0
 

Author Comment

by:Ra
ID: 7024174
Well, based on those three articles I tend to agree with John and have decieded to re-write my database class to use ADO rather then DAO.  Thanks for all the help guys.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7025478
I recommned ODBC (CRecordset) rather than ADO.  The ClassWizard is no help with ADO and you will spend all of your time figuring out how to do the simplest operations because of the BSTR datatypes, etc.

-- Dan
0
 
LVL 1

Expert Comment

by:jdrescher
ID: 7025538
If you are going to use ADO here there is a class library at the following link. I have never (I wrote my code before this was released) used it but it looks fairly complete.
http://www.codeproject.com/database/caaadoclass1.asp

Here is a link that has examples to do some of the more basic things with ado.

http://www.mindcracker.com/mindcracker/c_cafe/ado.asp

If you need any more help. Feel free to leave comments.
0
 

Author Comment

by:Ra
ID: 7025611
Dan, yeah.  I know what you mean.  I've done ADO in VB before but never in C++.  I'm having a bit of difficulty using it.  Is CDatabase and CRecordset thread-safe and how fast is it compaired to ADO?

John, I'll check out that code to see if I can't get this ADO stuff I got right now to work.

Also, this project might be moving from an Access database to SQL Server in the future.  Which is better to use for that, ADO or ODBC?

Thanks again.
0
 
LVL 1

Expert Comment

by:jdrescher
ID: 7025653
I don't know about ODBC but with ADO it was easy to move from Access to SQL Server. I only had to change a few SQL statements and the connection string.

John
0
 
LVL 1

Expert Comment

by:jdrescher
ID: 7025656
> I've done ADO in VB before but never in C++.  I'm having a bit of difficulty using it.

It was a real pain for me at first, because all the documentation was for VB and it was hard to translate some of the COM stuff to VC++.

John
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7025728
Access vs Ms Sql Server

Using ODBC via MFC CRecordset, the transition is completely transparent.  Just connect to a different datasource (DSN).

-- Dan
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

What is C++ STL?: STL stands for Standard Template Library and is a part of standard C++ libraries. It contains many useful data structures (containers) and algorithms, which can spare you a lot of the time. Today we will look at the STL Vector. …
IntroductionThis article is the second in a three part article series on the Visual Studio 2008 Debugger.  It provides tips in setting and using breakpoints. If not familiar with this debugger, you can find a basic introduction in the EE article loc…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now