ODBC 枚举数据表名

 

#include <afxdb.h>

class CDatabaseEx : public CDatabase
{
  DECLARE_DYNAMIC(CDatabaseEx)
public:
  CDatabaseEx(){};

public:
  int EnumTables(CStringArray &sTabs);

};

IMPLEMENT_DYNAMIC(CDatabaseEx, CDatabase)
int CDatabaseEx::EnumTables(CStringArray &sTabs)
{
  sTabs.RemoveAll();

  RETCODE nRetCode;
  HSTMT hstmt = NULL;
  ENSURE_VALID(this);

  AFX_SQL_SYNC(::SQLAllocStmt(m_hdbc, &hstmt));
  if (!CheckHstmt(nRetCode, hstmt))
    AfxThrowDBException(nRetCode, this, hstmt);
  AFX_SQL_SYNC(::SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0));
  if (!CheckHstmt(nRetCode, hstmt))
    AfxThrowDBException(nRetCode, this, hstmt);
  SDWORD cb = 0;
  char szTableCat[256];    szTableCat[0]=0;
  char szTableSchem[256];  szTableSchem[0]=0;
  char szTableName[256];   szTableName[0]=0;
  char szTableType[256];   szTableType[0]=0;
  char szTableRemark[256]; szTableRemark[0]=0;
  SQLBindCol( hstmt, 1, SQL_C_CHAR, szTableCat, 255, &cb );
  SQLBindCol( hstmt, 2, SQL_C_CHAR, szTableSchem, 255, &cb );
  SQLBindCol( hstmt, 3, SQL_C_CHAR, szTableName, 255, &cb );
  SQLBindCol( hstmt, 4, SQL_C_CHAR, szTableType, 255, &cb );
  SQLBindCol( hstmt, 5, SQL_C_CHAR, szTableRemark, 255, &cb );
  TRACE( _T("TableName [TableType]\n") );
  while( SQL_SUCCESS == SQLFetch(hstmt) )
  {
    sTabs.Add(CString(szTableName));    
  }

  /*
  AFX_SQL_SYNC(::SQLTablePrivileges(hstmt, NULL, 0, NULL, 0, NULL, 0));
  if (!CheckHstmt(nRetCode, hstmt))
    AfxThrowDBException(nRetCode, this, hstmt);
  SDWORD cb = 0;
  char szTableCat[256];       szTableCat[0]=0;
  char szTableSchem[256];     szTableSchem[0]=0;
  char szTableName[256];      szTableName[0]=0;
  char szTableGrantor[256];   szTableGrantor[0]=0;
  char szTableGrantee[256];   szTableGrantee[0]=0;
  char szTablePrivilege[256]; szTablePrivilege[0]=0;
  SQLBindCol( hstmt, 1, SQL_C_CHAR, szTableCat, 255, &cb );
  SQLBindCol( hstmt, 2, SQL_C_CHAR, szTableSchem, 255, &cb );
  SQLBindCol( hstmt, 3, SQL_C_CHAR, szTableName, 255, &cb );
  SQLBindCol( hstmt, 4, SQL_C_CHAR, szTableGrantor, 255, &cb );
  SQLBindCol( hstmt, 5, SQL_C_CHAR, szTableGrantee, 255, &cb );
  SQLBindCol( hstmt, 6, SQL_C_CHAR, szTablePrivilege, 255, &cb );
  while( SQL_SUCCESS == SQLFetch(hstmt) )
  {
    sTabs.Add(CString(szTableName));    
  }
  */

  AFX_SQL_SYNC(::SQLFreeStmt(hstmt, SQL_DROP));

  return (INT)sTabs.GetSize();
}

//调用示例
void Test()
{
  CDatabaseEx m_db;
  try
  {
    if(m_db.OpenEx(NULL, CDatabase::openReadOnly|CDatabase::forceOdbcDialog))
    {
      //获取所有数据表名
      CStringArray szTabsArray;
      m_db.EnumTables(szTabsArray);
     
      //枚举数据
      for(INT_PTR iTab=0; iTab<szTabsArray.GetSize(); iTab++)
      {
        CString szCmd;
        szCmd.Format(_T("SELECT * FROM %s"), (LPCTSTR)szTabsArray[iTab]);

        CRecordset rs(&m_db);
        try
        {
          if(rs.Open(rs.forwardOnly, szCmd, rs.executeDirect))
          {
            short nMaxCol = rs.GetODBCFieldCount();
            //字段名
            {
              for(short iCol=0; iCol<nMaxCol; iCol++)
              {
                CODBCFieldInfo fieldInfo;
                rs.GetODBCFieldInfo(iCol, fieldInfo);
                TRACE(_T("%s,"), fieldInfo.m_strName);
              }
              TRACE(_T("\n"));
            }

            //数据
            while(! rs.IsEOF())
            {
              for(short iCol=0; iCol<nMaxCol; iCol++)
              {
                CString var;
                rs.GetFieldValue(iCol, var);
                TRACE(_T("%s,"), (LPCTSTR)var);
              }
              TRACE(_T("\n"));
              rs.MoveNext();
            }
          }
        }
        catch(CDBException *e)
        {
          e->ReportError();
          e->Delete();
        }

        rs.Close();
      }

    }
  }
  catch(CDBException *e)
  {
    e->ReportError();
    e->Delete();
  }

  //关闭数据库
  m_db.Close();

}