Solved

SQL Tables

Posted on 1998-10-23
4
425 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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

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. …
Many modern programming languages support the concept of a property -- a class member that combines characteristics of both a data member and a method.  These are sometimes called "smart fields" because you can add logic that is applied automaticall…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

734 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