Solved

ISAPI Database Access (MS Access) - How do I retreive a list of tables?

Posted on 2001-06-14
17
241 Views
Last Modified: 2010-05-18
Trying to use DAO to access a Microsoft Access97/2000 database from ISAPI.
I need to be able to retreive a list of Tables from the database and this is why I am using DAO. However the problem that I have is that DAO is NOT Thread Safe, and all work in ISAPI must be thread safe.

What I need is either:

1) A way of retreivig a list of table names from the database without using DAO.
2) A way of making DAO run in a thread safe way, so that i can use it from within ISAPI.
3) Any other ideas that I havent thought of. (I know that with Oracle for example u can use "SELECT * FROM User_Tables;" to get a list of tables but that doesnt work in Microsoft Access.

Thanks

Adam Retter
adam@cosmic.org.uk
Network and Systems Administrator
Project COSMIC
0
Comment
Question by:deliriumsky
  • 7
  • 6
  • 2
  • +2
17 Comments
 
LVL 30

Expert Comment

by:Axter
ID: 6191627
You could create a thread safe variable that all your threads can use to determine if the thread can access the database.
0
 

Author Comment

by:deliriumsky
ID: 6191663
how ?
0
 
LVL 30

Expert Comment

by:Axter
ID: 6191674
If you're using MFC, you could use CSingleLock class.
An object of class CSingleLock represents the access-control mechanism used in controlling access to a resource in a multithreaded program.

What type of project do you have, and are you using MFC?
What's your compiler?
0
 
LVL 30

Expert Comment

by:Axter
ID: 6191725
If you have a Win32 Application, you can use CreateMutex and WaitForSingleObject.
0
 
LVL 30

Accepted Solution

by:
Axter earned 75 total points
ID: 6191811
Example:
In your main, WinMain, or CWinApp::InitInstance() function, you can add the following code:

HANDLE hMyGlobalHandleToAccessDatabase;
LPCTSTR szMyMutexName = "ControlAccessToXYZDatabase";

BOOL CMyApp::InitInstance()
{
     //First try opening the handle just incase there's another instance of your app running
     hMyGlobalHandleToAccessDatabase = OpenMutex(SYNCHRONIZE,TRUE,szMyMutexName);
     if (hMyGlobalHandleToAccessDatabase == NULL)
     {//Could not open it, so lets create a new one
          SECURITY_ATTRIBUTES sa;
          memset(&sa,0,sizeof(sa));
          sa.nLength = sizeof(sa);
          hMyGlobalHandleToAccessDatabase = CreateMutex(&sa,TRUE,szMyMutexName);
     }

// the rest of your function code......
}

Now right before your code trys to access the database put the following code in it.

WaitForSingleObject(hMyGlobalHandleToAccessDatabase,INFINITE);
0
 
LVL 30

Expert Comment

by:Axter
ID: 6191816
Let me know if you have any questions.
0
 

Author Comment

by:deliriumsky
ID: 6191850
I am Using MFC and Visual C++ 6
0
 
LVL 30

Expert Comment

by:Axter
ID: 6191872
Then you can either use the CSingleLock class or the CreateMutex function.
The CreateMutex function will work on Win32 Non-MFC and MFC applications.

Do you understand the example I posted?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:deliriumsky
ID: 6191950
I am Using MFC and Visual C++ 6
0
 

Author Comment

by:deliriumsky
ID: 6191959
Yes i understand the example thanks for the help, I am quite keen to try and use CSingleLock() could you provide an example for this?

Will this allow me to use DAO in ISAPI, as at the present you can run the ISAPI Extension once and then it hangs and causes problems with the webserver if you try and run it again, I presume this is because ADO is not thread safe which ISAPI requires.
0
 
LVL 30

Expert Comment

by:Axter
ID: 6192079
I've never used CSingleLock in any of my code, and I would hate to give misleading information.

In looking at it, I would say you would need to use CSingleLock in conjunction with CMutex.
In the CMutex constructor you would use a similar name like in my previous example.

In my opinion, the code required to use CSingleLock and CMutex is more complicated then my previous example.
Of course this is a bios opinion, since I am more familiar with the previous method. :-)
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 6193512
1) A way of retreivig a list of table names from the database without using DAO.

The MSysObjects table contains the list of table names in an Access database.

  SELECT Name FROM MSysObjects WHERE Type=1

You may have to grant Admin read access on MSysObjects.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 6193826
You should also be able to use ADO's OpenSchema function to retrieve the list of tables.

// adoconsole.cpp
//

#include "stdafx.h"
#include <windows.h>
#include <atlbase.h>  // For conversion macro
#include <iostream.h>
#import "msado25.tlb" no_namespace rename("EOF","adoEOF") named_guids

int main(int argc, char* argv[])
{
   USES_CONVERSION;

   CoInitialize(NULL);

   try
   {
      _ConnectionPtr pConn("ADODB.Connection");

      pConn->Open(_bstr_t("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/test.mdb"),_bstr_t("Admin"),_bstr_t(""),0);

      _RecordsetPtr pRS;

      pRS = pConn->OpenSchema(adSchemaTables);

      _variant_t nameField(_bstr_t("TABLE_NAME"));
      _variant_t typeField(_bstr_t("TABLE_TYPE"));
      _variant_t filter(_bstr_t("TABLE"));

      while (!pRS->adoEOF)
      {
         if (pRS->Fields->GetItem(typeField)->GetValue() == filter)
            cout << OLE2A(pRS->Fields->GetItem(nameField)->GetValue().bstrVal) << endl;
         pRS->MoveNext();
      }

      pRS->Close();
      pConn->Close();

   }
   catch  (_com_error& e)
   {
      cout << (char *) e.Description();
   }


   CoUninitialize();

   return 0;
}
0
 

Author Comment

by:deliriumsky
ID: 6194500
Thanks Guys I am going to need a little time to read through all of this and try out some of the stuff you have mentioned.

I will get back to you all soon.

Thanks Adam Retter
Network and Systems Administrator
Project COSMIC
0
 

Author Comment

by:deliriumsky
ID: 6194579
Thanks Guys I am going to need a little time to read through all of this and try out some of the stuff you have mentioned.

I will get back to you all soon.

Thanks Adam Retter
Network and Systems Administrator
Project COSMIC
0
 
LVL 11

Expert Comment

by:griessh
ID: 6819143
I think you forgot this question. I will ask Community Support to close it unless you finalize it within 7 days. Unless there is objection or further activity,  I will suggest to accept "Axter, clockwatcher" comment(s) as an answer.

If you think your question was not answered at all, you can post a request in Community support (please include this link) to refund your points.
The link to the Community Support area is: http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
======
Werner
0
 

Expert Comment

by:ComTech
ID: 6837605
As recommended, this question will be split between two experts:

Axter=75 points
clockwatcher=75 points

Will accept Axter here, and will create a new question for clockwatcher in this Topic Area for the other half of the award.

Best regards,
ComTech
CS Admin @ EE
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Templates For Beginners Or How To Encourage The Compiler To Work For You Introduction This tutorial is targeted at the reader who is, perhaps, familiar with the basics of C++ but would prefer a little slower introduction to the more ad…
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

896 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

15 Experts available now in Live!

Get 1:1 Help Now