Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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

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
deliriumsky
Asked:
deliriumsky
  • 7
  • 6
  • 2
  • +2
1 Solution
 
AxterCommented:
You could create a thread safe variable that all your threads can use to determine if the thread can access the database.
0
 
deliriumskyAuthor Commented:
how ?
0
 
AxterCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AxterCommented:
If you have a Win32 Application, you can use CreateMutex and WaitForSingleObject.
0
 
AxterCommented:
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
 
AxterCommented:
Let me know if you have any questions.
0
 
deliriumskyAuthor Commented:
I am Using MFC and Visual C++ 6
0
 
AxterCommented:
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
 
deliriumskyAuthor Commented:
I am Using MFC and Visual C++ 6
0
 
deliriumskyAuthor Commented:
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
 
AxterCommented:
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
 
clockwatcherCommented:
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
 
clockwatcherCommented:
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
 
deliriumskyAuthor Commented:
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
 
deliriumskyAuthor Commented:
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
 
griesshCommented:
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
 
ComTechCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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