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.