How to read columns names in table in ESE using C language and esent.lib?

Question

I need a code example. I'd like to see how we can enumerate columns names in table. (It's essential for me to use esent.dll/esent.lib and C language)

I tried to use attached code (found a guide but it doesn't work as I expect).

    JET_COLUMNLIST column_info;

    JET_RETRIEVECOLUMN j_rc[4];
    err = JetGetTableColumnInfo(sessionID, curr_table.tableID, NULL, &column_info, sizeof(JET_COLUMNLIST), JET_ColInfoList);

    j_rc[0].columnid = column_info.columnidcolumnname;
    j_rc[0].cbData = sizeof(char)*JET_cbNameMost;
    j_rc[0].itagSequence = 1;
    j_rc[0].grbit = 0;
    char buf[JET_cbNameMost] = { 0 };
    j_rc[0].pvData = buf;


    printf("\nRetrieving columns information:\n");
    printf("Row\tId\tType\tName:\n");

    unsigned long columns_qnt = 0;
    for (err = JetMove(sessionID, curr_table.tableID, JET_MoveFirst, 0);
        JET_errSuccess == err;
        err = JetMove(sessionID, curr_table.tableID, JET_MoveNext, 0))
    {
        err = JetRetrieveColumns(sessionID, curr_table.tableID, j_rc, 4);

        columns_qnt++;

        printf("%u\t%s\n", columns_qnt, buf);

        memset(buf, 0, JET_cbNameMost);
    }

Please show an example. If you know good guides for ESE C programming or just some resources with describing of how it works, please share it with me. (Despite I googled a lot, don't be shy to share obvious for you resourses)

Answer 1

Inside table "MSysObjects" (which exists in every ESE database as service table) are 2 interisting for us columns: "Type" and "Name".

    JetOpenTable(sessionID, dbid, "MSysObjects", NULL, NULL, JET_bitTableSequential, &tableID); 

    JET_COLUMNBASE j_cb_name, j_cb_type, j_cb_coltype;

    JetGetColumnInfo(sessionID, dbid, "MSysObjects", "Name", &j_cb_name, sizeof(JET_COLUMNBASE), JET_ColInfoBase);

    JetGetColumnInfo(sessionID, dbid, "MSysObjects", "Type", &j_cb_type, sizeof(JET_COLUMNBASE), JET_ColInfoBase);

    JET_RETRIEVECOLUMN j_rc[2];

Here we fill structure JET_RETRIEVECOLUMN to get this 2 columns by JetRetrieveColumns

    j_rc[0].columnid = j_cb_name.columnid;
    j_rc[0].cbData = 1024;
    j_rc[0].itagSequence = 1;
    j_rc[0].grbit = NULL;
    char buf[1024] = { 0 };
    j_rc[0].pvData = buf;

    j_rc[1].columnid = j_cb_type.columnid;
    j_rc[1].cbData = sizeof(unsigned short);
    j_rc[1].itagSequence = 1;
    j_rc[1].grbit = NULL;
    unsigned short type;
    j_rc[1].pvData = &type;

    for (err = JetMove(sessionID, root_tableID, JET_MoveFirst, 0);
        JET_errSuccess == err;
        err = JetMove(sessionID, root_tableID, JET_MoveNext, 0))
    {
        JetRetrieveColumns(sessionID, root_tableID, j_rc, 2);

We got them here. If type == 1 it means, that record we got is describing a table and if type == 2, then it's describing a column . (There are also other types) There is strict order, first you will get record with type 1 (table) then you will get records with type 2 that describes columns of that table (in that moment buf keeps column name), then you can get records with other types (except type == 1) that refers to that table. And finally you will get record with type 1, that means that next information we get is about another table.

    }

Feel free to say that my english is awful and I wrote some junk, I'll try to explain in other way then:)

Answer 2

If you just want a list of column names for a particular table without using MSysObjects, here's my approach. The temporary table created by "JetGetTableColumnInfo" contains only the column ID and column Name, so it's pretty fast:

JET_ERR GetEseTableColumnNames(JET_SESID hEseSession, JET_TABLEID hEseTable)
{  JET_ERR        rc;
   JET_COLUMNLIST cl { };

   /* Sort order for the temporary table is column name order */
   rc = ::JetGetTableColumnInfo(hEseSession, hEseTable, nullptr, &cl, sizeof(cl), JET_ColInfoList | JET_ColInfoGrbitMinimalInfo);

   /* Temporary table ("cl.tableid") is opened and positioned on first record */
   if (rc == JET_errSuccess && cl.cRecord > 0)
   {  wchar_t       wszColumnName[MAX_ESE_OBJECT_NAME + 1]; // ESE doesn't play well with std::strings
      unsigned long cbActual;

      for (uint32_t i = 0; i < cl.cRecord; ++i)
      {
         rc = ::JetRetrieveColumn(hEseSession, cl.tableid, cl.columnidcolumnname, wszColumnName, sizeof(wszColumnName), &cbActual, 0, nullptr);
         if (rc == JET_errSuccess)
         {
            /* ESE does not null terminate strings */
            wszColumnName[cbActual / sizeof(wchar_t)] = L'\0';

            //********
            // Okay, so do something with the column name here
            //********

            /* Next record in temporary table */
            if (i < cl.cRecord - 1)
               ::JetMove(hEseSession, cl.tableid, JET_MoveNext, 0);
         }
         else
            break;
      }
   }

   /* Close the temporary table */
   ::JetCloseTable(hEseSession, cl.tableid);

   return rc;
}

I know other folks use MSysObjects to short-cut the process, but this works fine for me. And yes, my code looks old fashioned - I'm stuck in Hungarian!