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)

Roma · Accepted Answer · 2019-07-15T22:11:03.117

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:)

Mark Out West · Answer 2 · 2020-10-13T17:36:11.783

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!

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

2 Answers2