Silviu-Marius Ardelean's blog

a software engineer's web log

Experiences with Adobe Acrobat/Reader Plug-ins »« HTML files generation using XML and XSLT with Microsoft XML DOM API

Getting Table’s indexes experiences – workaround

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.

Silviu Ardelean

Software Engineer

More Posts - Website

Follow Me:
TwitterFacebookPinterest

, , , , , ,
07/04/2014 at 8:52 AM
2 comments »
  • 07/05/2014 at 11:10 AMDan

    Good fix, but just a comment: isn’t it improper usage of TCHAR into the initial code?

    • 07/05/2014 at 3:14 PMSilviu Ardelean

      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.

Leave a Reply or trackback

*