• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

Accessing database from multiple threads.

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
Ra
Asked:
Ra
  • 8
  • 7
  • 6
  • +1
1 Solution
 
mirtolCommented:
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
 
RaAuthor Commented:
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
 
RaAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
RaAuthor Commented:
btw, this is in MS Visual C++ 6.0 on Windows 2000.
0
 
mirtolCommented:
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
 
RaAuthor Commented:
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
 
mirtolCommented:
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
 
mirtolCommented:
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
 
jdrescherCommented:
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
 
RaAuthor Commented:
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
 
mirtolCommented:
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
 
mirtolCommented:
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
 
RaAuthor Commented:
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
 
mirtolCommented:
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
 
DanRollinsCommented:
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
 
jdrescherCommented:
> 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
 
jdrescherCommented:
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
 
DanRollinsCommented:
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
 
RaAuthor Commented:
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
 
DanRollinsCommented:
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
 
jdrescherCommented:
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
 
RaAuthor Commented:
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
 
jdrescherCommented:
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
 
jdrescherCommented:
> 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
 
DanRollinsCommented:
Access vs Ms Sql Server

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

-- Dan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now