Solved

SQL Tables

Posted on 1998-10-23
4
424 Views
Last Modified: 2008-02-20
I have been struggling to find a way to query any type of table (Access, Oracle, DBase, etc.) to find what tables it contains.  I know that Access has a nice command CDaoTables, but this works only for Access dbs.  There is suppose to be a command SQLTables, but I can't get it to work correctly.  There is also suppose to be an sp_tables, but that is only for MS SQL Server and the situations where my program is suppose to work do not guarantee any individual server or database.

     THUS, is there a standard way of getting the table information during run-time for a database that will work for all database situations?  I would prefer if I can send it using a normal SQL statement.  Also if possible please show a sample of the code.

Thanks.
0
Comment
Question by:ChefInnocent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 86

Accepted Solution

by:
jkr earned 200 total points
ID: 1175900
The following code does what you want. It is from the MFC sample 'Catalog' (located on the VC5 cdrom in '\DEVSTUDIO\VC\SAMPLES\MFC\DATABASE\CATALOG':
// tableset.h : interface of the CTables class
//
// This is a part of the Microsoft Foundation Classes C++ library.
// Copyright (C) 1992-1997 Microsoft Corporation
// All rights reserved.
//
// This source code is only intended as a supplement to the
// Microsoft Foundation Classes Reference and related
// electronic documentation provided with the library.
// See these sources for detailed information regarding the
// Microsoft Foundation Classes product.

/////////////////////////////////////////////////////////////////////////////

#ifndef __AFXWIN_H__
      #error include 'stdafx.h' before including this file for PCH
#endif

class CTables : public CRecordset
{
DECLARE_DYNAMIC(CTables)

public:
      CTables(CDatabase* pDatabase = NULL);
      BOOL Open(UINT nOpenType = forwardOnly, LPCSTR lpszSQL = NULL,
            DWORD dwOptions = readOnly);

// Field/Param Data
      //{{AFX_FIELD(CTables, CRecordset)
      CString m_strQualifier;
      CString m_strOwner;
      CString m_strName;
      CString m_strType;
      CString m_strRemarks;
      //}}AFX_FIELD

      CString m_strQualifierParam;
      CString m_strOwnerParam;
      CString m_strNameParam;
      CString m_strTypeParam;

// Implementation
protected:
      virtual CString GetDefaultConnect();    // default connection string
      virtual CString GetDefaultSQL();    // default SQL for Recordset
      virtual void DoFieldExchange(CFieldExchange* pFX);  // RFX support
};

// sqltable.cpp : implementation of the CTables class
//
// This is a part of the Microsoft Foundation Classes C++ library.
// Copyright (C) 1992-1997 Microsoft Corporation
// All rights reserved.
//
// This source code is only intended as a supplement to the
// Microsoft Foundation Classes Reference and related
// electronic documentation provided with the library.
// See these sources for detailed information regarding the
// Microsoft Foundation Classes product.


#include "stdafx.h"
#include "tableset.h"

/////////////////////////////////////////////////////////////////////////////
// CTables implementation

IMPLEMENT_DYNAMIC(CTables, CRecordset)

CTables::CTables(CDatabase* pDatabase)
      : CRecordset(pDatabase)
{
      //{{AFX_FIELD_INIT(CTables)
      m_strQualifier = "";
      m_strOwner = "";
      m_strName = "";
      m_strType = "";
      m_strRemarks = "";
      m_nFields = 5;
      //}}AFX_FIELD_INIT
      m_strQualifierParam = "";
      m_strOwnerParam = "";
      m_strNameParam = "";
      m_strTypeParam = "";
}

BOOL CTables::Open(UINT nOpenType, LPCSTR lpszSQL,
      DWORD dwOptions)
{
      ASSERT(lpszSQL == NULL);

      RETCODE      nRetCode;

      // Cache state info and allocate hstmt
      SetState(nOpenType,NULL,noDirtyFieldCheck);
      if (!AllocHstmt())
            return FALSE;

      TRY
      {
            OnSetOptions(m_hstmt);
            AllocStatusArrays();

            // call the ODBC catalog function with data member params
            AFX_SQL_ASYNC(this, (::SQLTables)(m_hstmt,
                  (m_strQualifierParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strQualifierParam), SQL_NTS,
                  (m_strOwnerParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strOwnerParam), SQL_NTS,
                  (m_strNameParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strNameParam), SQL_NTS,
                  (m_strTypeParam.IsEmpty()? (UCHAR FAR *)NULL: (UCHAR FAR *)(const char*)m_strTypeParam), SQL_NTS));
            if (!Check(nRetCode))
                  ThrowDBException(nRetCode, m_hstmt);

            // Allocate memory and cache info
            AllocAndCacheFieldInfo();
            AllocRowset();

            // Fetch the first row of data
            MoveNext();

            // If EOF, result set is empty, set BOF as well
            m_bBOF = m_bEOF;
      }

      CATCH_ALL(e)
      {
            Close();
            THROW_LAST();
      }
      END_CATCH_ALL

      return TRUE;
}

CString CTables::GetDefaultConnect()
{
      return "ODBC;";
}

CString CTables::GetDefaultSQL()
{
      // should SQLTables directly, so GetSQL should never be called
      ASSERT(FALSE);
      return "!";
}

void CTables::DoFieldExchange(CFieldExchange* pFX)
{
      //{{AFX_FIELD_MAP(CTables)
      pFX->SetFieldType(CFieldExchange::outputColumn);
      RFX_Text(pFX, "table_qualifier", m_strQualifier);
      RFX_Text(pFX, "table_owner", m_strOwner);
      RFX_Text(pFX, "table_name", m_strName);
      RFX_Text(pFX, "table_type", m_strType);
      RFX_Text(pFX, "remarks", m_strRemarks);
      //}}AFX_FIELD_MAP
}


0
 

Expert Comment

by:banan
ID: 1175901
jkr's tip looks FINE.
0
 

Author Comment

by:ChefInnocent
ID: 1175902
I had seen this code before, but I was trying to not have to create a class to do this.  However, since I am at ropes end so to speak, I used their class as is, and it works fine.

Thank you for suggesting (implicitely) that I look at this code again.
0
 
LVL 86

Expert Comment

by:jkr
ID: 1175903
No problem - to be honest, i don't like MFC recordsets either... but sometimes they make life easier ;-)
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Grammars for C C++ and java 1 143
How to copy an image file into clipboard C/C++? 1 246
C++ error cannot convert from std::string to const char * 6 360
Finding Divisors 5 57
Errors will happen. It is a fact of life for the programmer. How and when errors are detected have a great impact on quality and cost of a product. It is better to detect errors at compile time, when possible and practical. Errors that make their wa…
C++ Properties One feature missing from standard C++ that you will find in many other Object Oriented Programming languages is something called a Property (http://www.experts-exchange.com/Programming/Languages/CPP/A_3912-Object-Properties-in-C.ht…
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.

738 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