Click here to Skip to main content
15,039,470 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
C++
CDatabase db;
CRecordset rcd;
...
rcd.Open(CRecordset::forwardOnly, "SELECT * FROM [sheet1$]", CRecordset::readOnly);
...


In this example it's assumed that the table name is "sheet1". But when I don't know the name, how can I get it?
Thanks in advance.
Posted

1 solution

You can get the list of table names from a database using the SQLTables function.

C++
// Example code for a CDatabase derived class to read Excel files
//  using the ODBC driver.
// Fill CStringArray with list of table names.
// System tables are Excel sheets (trailing dollar sign).
// Normal tables are named ranges (no trailing dollar sign). 
bool CMyDatabase::GetSheetNames(CStringArray& arr) const
{
    ASSERT(IsOpen());
    SQLHSTMT hst = NULL;
    SQLRETURN nSqlResult = ::SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &hst);
    if (!SQL_SUCCEEDED(nSqlResult))
        TRACE1("Error %d in SQLAllocHandle()\n", nSqlResult);
    else
    {
        nSqlResult = ::SQLTables(hst, NULL, 0, NULL, 0, NULL, 0, NULL, 0);
        if (!SQL_SUCCEEDED(nSqlResult))
            TRACE1("Error %d in SQLTables()\n", nSqlResult);
        else 
        {
            // Col 1, TABLE_CAT:   File name
            // Col 2, TABLE_SCHEM: Not used with Excel
            // Col 3, TABLE_NAME:  Table name
            // Col 4, TABLE_TYPE:  Table type (e.g. TABLE, SYSTEM TABLE)
            // Col 5, REMARKS:     Not used with Excel
            // Max. sizes may be retrieved using SQLGetInfo().
            // 64 for Excel ODBC driver
            SQLTCHAR szName[64];
            //SQLTCHAR szType[64];
            SQLINTEGER cbName;
            //SQLINTEGER cbType;
            ::SQLBindCol(hst, 3, SQL_C_TCHAR, szName, sizeof(szName), &cbName);
            //::SQLBindCol(hst, 4, SQL_C_TCHAR, szType, sizeof(szType), &cbType);
            while ((nSqlResult = ::SQLFetch(hst)) != SQL_NO_DATA) 
            {
                if (SQL_SUCCEEDED(nSqlResult))
                    arr.Add(szName);
                else // if (SQL_ERROR == nSqlResult)
                {
                    TRACE1("Error %d fetching SQLTables\n", nSqlResult);
                    break;
                }
            }
        }
        ::SQLFreeHandle(SQL_HANDLE_STMT, hst);
    }
    return SQL_NO_DATA == nSqlResult && arr.GetSize();
}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900