Solved

SQL Tables

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
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 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.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now