Trying to get table indexes information in SQL Server 2012 I identified a strange situation within a specific method that I was using so long but it was not acting as expected in one situation.
The way of getting indexes information using the ODBC C API into that old and inherited method looks like:
nRetCode = ::SQLStatistics(hstmtAux, NULL, 0, NULL, 0, (TCHAR*)(LPCTSTR)strTempTable, SQL_NTS, SQL_INDEX_ALL, SQL_ENSURE); if (nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO) { nRetCode = ::SQLBindCol(hstmtAux, 4, SQL_C_SHORT, &swNonUnique, sizeof(SWORD), &cbNonUnique); nRetCode = ::SQLBindCol(hstmtAux, 5, SQL_CHAR, szIdxQualif, sizeof(CHAR) * 130, &cbIdxQualif); nRetCode = ::SQLBindCol(hstmtAux, 6, SQL_C_CHAR, szIdxName, sizeof(CHAR) * 130, &cbIdxName); nRetCode = ::SQLBindCol(hstmtAux, 7, SQL_C_SHORT, &swType, sizeof(SWORD), &cbType); nRetCode = ::SQLBindCol(hstmtAux, 8, SQL_C_SHORT, &swSeqInIdx, sizeof(SWORD), &cbSeqInIdx); nRetCode = ::SQLBindCol(hstmtAux, 9, SQL_C_CHAR, szIdxColName, sizeof(CHAR) * 130, &cbIdxColName); while (bNoFetch || nRetCode == SQL_SUCCESS_WITH_INFO || (nRetCode = ::SQLExtendedFetch(hstmtAux, SQL_FETCH_NEXT, 1, &crow, &rgfRowStatus)) == SQL_SUCCESS) { if (cbIdxName != SQL_NULL_DATA && _tcslen((TCHAR)szIdxName) < 0) { // rest of the code } } // rest of the code }
Usually, I got the right information about indexes but in one situation I encounter a strange behavior. It’s about having a clustered index into a scenario. I have a table that contains two indexes referenced to some fields: IndexField_1 and IndexField_3 mapped over int, NULL fields. When IndexField_1 is Non-Unique, Non-Clustered and IndexField_3 is Clustered index I get the right information.
But if the index IndexField_1 is Clustered and the IndexField_3 is Non-Unique, Non-Clustered I get no information about IndexField_1 index (eg. szIdxName and szIdxColName are “” and their length is -1 that means SQL_NULL_DATA). Within while loop, with the next iteration, I get correct information about the second index IndexField_3.
Because SQLExtendedFetch() is deprecated I tried using SQLFetchScroll() but the behavior is the same from my interest point of view.
I was not sure whether the problem is with SQLStatistics, the bindings or SQLFetchScroll (they all always return SQL_SUCCESS). It looks such a problem with the driver when the first index is clustered.
According to SQLStatistics documentation if my swType parameter is SQL_TABLE_STAT I have no information for index or field. But for this scenario, I had no indexes of combined fields.
For the good scenario I observed that my while loop had 3 iterations including one of having swType = SQL_TABLE_STAT without information in szIdxName. But for the bad scenario, the loop had only 2 iterations. So it looks like SQLExtendedFetch() is not getting the last one index.
After some googling and research without very significant solutions, I decided to apply a workaround by avoiding the old API and I rewrite my method.
So, in order to get table indexes information, I have chosen a direct SQL query into SYS tables: sys.tables, sys.indexes, sys.schema.
SELECT DISTINCT I.[name] AS IndexName, I.is_unique AS IsUnique, I.is_primary_key AS IsPrimaryKey, I.type AS IndexType, I.type_desc AS IndexDesc, T.[object_id] AS ObjectID FROM sys.tables AS T INNER JOIN sys.indexes AS I ON T.[object_id] = I.[object_id]and T.name = 'myTABLE' and I.type_desc<> 'HEAP' INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id and s.name = 'myTABLE'
Because I preferred getting also information about the index’s composed fields, I applied a second additional SQL query:
SELECT AC.Name as ColumnName FROM sys.tables as T INNER JOIN sys.indexes as I on T.[object_id] = I.[object_id] INNER JOIN sys.index_columns as IC on IC.[object_id] = I.[object_id] and IC.[index_id] = I.[index_id] INNER JOIN sys.all_columns as AC on IC.[object_id] = AC.[object_id] and IC.[column_id] = AC.[column_id] WHERE I.object_id = 'NumericObjectID' and T.name = 'TableName' and I.name = 'IndexName' order by T.name, I.name
and I have collected data into a container of defined structure according to my SQL Indexes interest information:
struct SQL_INDEX { CString index_name; bool is_unique; long object_id; bool is_primary; short index_type; CString index_desc; std::vector vectColumns; };
The last member vectColumns stores information about the columns that are used for a specific index.
Finally, the new method that collects table indexes information looks like:
// collect table indexes information void CFoo::GetIndexesInformation(const CString& strSchema, const CString& strTable, std::vector& vectKeys, CDatabase* pDBdata) { HSTMT hstmt = SQL_NULL_HSTMT; SQLRETURN nRetCode = 0; CString sqlQuery; sqlQuery.Format( _T("SELECT DISTINCT I.[name] as IndexName, I.is_unique as IsUnique, I.is_primary_key as IsPrimaryKey, I.type as IndexType, I.type_desc as IndexDesc, T.[object_id] as ObjectID \ FROM sys.tables as T \ INNER JOIN sys.indexes as I on T.[object_id] = I.[object_id] and T.name = '%s' and I.type_desc <> 'HEAP' \ INNER JOIN sys.schemas as S on T.schema_id = S.schema_id and s.name = '%s'"), strTable, strSchema); if ((nRetCode = ::SQLAllocStmt(pDBdata->m_hdbc, &hstmt)) == SQL_SUCCESS) { if (SQL_NULL_HSTMT != hstmt) { pDBdata->OnSetOptions(hstmt); nRetCode = ::SQLExecDirect(hstmt, (CHAR*)(LPCTSTR)sqlQuery, sqlQuery.GetLength()); if (SQL_SUCCESS == nRetCode) { CHAR buffer[128] = {0}; SQLLEN iLen = 0; short int val = 0; long obj_id = 0; while (::SQLFetch(hstmt) == SQL_SUCCESS) { SQL_INDEX ob; if (::SQLGetData(hstmt, 1, SQL_C_CHAR, buffer, 128, &iLen) == SQL_SUCCESS) ob.index_name = buffer; if (::SQLGetData(hstmt, 2, SQL_C_SHORT, &val, sizeof(short int), &iLen) == SQL_SUCCESS) ob.is_unique = (1 == val); if (::SQLGetData(hstmt, 3, SQL_C_SHORT, &val, sizeof(short int), &iLen) == SQL_SUCCESS) ob.is_primary = (1 == val); if (::SQLGetData(hstmt, 4, SQL_C_SHORT, &val, sizeof(short int), &iLen) == SQL_SUCCESS) ob.index_type = val; if (::SQLGetData(hstmt, 5, SQL_C_CHAR, buffer, 128, &iLen) == SQL_SUCCESS) ob.index_desc = buffer; if (::SQLGetData(hstmt, 6, SQL_C_LONG, &obj_id, sizeof(long), &iLen) == SQL_SUCCESS) ob.object_id = obj_id; vectKeys.push_back(ob); } } } } // collect index’s columns/fields information for (auto it = vectKeys.begin(); it != vectKeys.end(); ++it) { HSTMT hstmt2 = SQL_NULL_HSTMT; if ((nRetCode = ::SQLAllocStmt(pDBdata->m_hdbc, &hstmt2)) == SQL_SUCCESS) { if (hstmt2 != SQL_NULL_HSTMT) { pDBdata->OnSetOptions(hstmt2); CString sSQL; SQLLEN iLen = 0; sSQL.Format(_T("SELECT AC.Name as ColumnName \ FROM sys.tables as T inner join sys.indexes as I on T.[object_id] = I.[object_id] \ INNER JOIN sys.index_columns as IC on IC.[object_id] = I.[object_id] and IC.[index_id] = I.[index_id] \ INNER JOIN sys.all_columns as AC on IC.[object_id] = AC.[object_id] and IC.[column_id] = AC.[column_id] \ WHERE I.object_id = %ld and T.name = '%s' and I.name = '%s' order by T.name, I.name"), it->object_id, strTable, it->index_name); nRetCode = ::SQLExecDirect(hstmt2, (UCHAR*)(LPCTSTR)sSQL, sSQL.GetLength()); if (SQL_SUCCESS == nRetCode) { CHAR buffer[128] = {0}; while (::SQLFetch(hstmt2) == SQL_SUCCESS) { if (::SQLGetData(hstmt2, 1, SQL_C_CHAR, buffer, 128, &iLen) == SQL_SUCCESS) { it->vectColumns.push_back(buffer); } } } } } } }
In this way I have complete information about the indexes of my tables.
std::vector vectIndexesSQL; pFoo->GetIndexesInformation(strSchema, pDBTable->strTblName, vectIndexesSQL, pDataDB);
Conclusion: When the C/C++ API doesn’t give you any hopes don’t forget that SQL saves you.
Good fix, but just a comment: isn’t it improper usage of TCHAR into the initial code?
You’re right. I inherited that old code from ex-colleagues that I never met, but even correcting that detail it does not solve the main issue. Having a project base on multi-byte character set, the _tcslen() became strlen() in background (strlen.asm).
Also, because strTempTable is a CString a cast to (SQLCHAR*)(LPCTSTR) does not change something in the idea of getting all the table’s indexes for my bad scenario.