Solved

SQL Tables

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
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 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.

615 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