Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Tables

Posted on 1998-10-23
4
Medium Priority
?
447 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 600 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

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.

Question has a verified solution.

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

In days of old, returning something by value from a function in C++ was necessarily avoided because it would, invariably, involve one or even two copies of the object being created and potentially costly calls to a copy-constructor and destructor. A…
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. …
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

722 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