Extensible Storage Engine

This project pre-dates the excellent work of the Managed ESENT work at https://managedesent.codeplex.com/

The Extensible Storage Engine (ESE) is a low-level database engine that is used internally by Windows for all sorts of things like Microsoft Exchange message store, Active Directory database, Microsoft Updates, Local Security Policy, etc. It is also called "Jet Blue"... not to be confused with "Jet Red", the engine that's used in Microsoft Access.

The Application Programming Interface (API) for ESE has recently been published, so now ordinary users like us can make use of ESE to poke around the internal Windows databases. However, the API documentation is horrible! It lacks critical information like the basic flow of accomplishing simple tasks. It's also full of omissions and errors.

One of the critical tools for dealing with an ESE database is the command-line tool called ESENTUTL.EXE (which is installed by default in Windows XP in the \Windows\System32 directory). You can use this tool to get the listing of tables in a database. An example command to dump the "meta data" for a database (that includes the table lists) is as follows:

esentutl /mm DataStore.edb

This article will describe the steps to use the low-level ESE APIs to read the contents of a table. The ESE engine itself does not support an SQL language interface. This might be a good time to look at a companion article In-memory SQL Engine.

API Declarations

Let's start with the API "boiler plate" stuff. This section is rather large, but that's OK considering that's what this article is all about... using the ESE API. I've also annotated where the API documentation is wrong or misleading.

'JET_ERR JET_API JetCreateInstance(
'  JET_INSTANCE* pinstance,
'  const tchar* szInstanceName
');
Declare Ansi Function JetCreateInstance Lib "esent.dll" ( _
    ByRef instance As IntPtr, _
    ByVal szInstanceName As String _
) As Integer

'JET_ERR JET_API JetInit(
'  JET_INSTANCE* pinstance
');
Declare Function JetInit Lib "esent.dll" ( _
    ByRef instance As IntPtr _
) As Integer

'JET_ERR JET_API JetBeginSession(
'  JET_INSTANCE instance,
'  JET_SESID* psesid,
'  JET_PCSTR szUserName,    ' Not used
'  JET_PCSTR szPassword     ' Not used
');
Declare Ansi Function JetBeginSession Lib "esent.dll" ( _
    ByVal instance As IntPtr, _
    ByRef psesid As IntPtr, _
    ByVal szUserName As String, _
    ByVal szPassword As String _
) As Integer

'JET_ERR JET_API JetAttachDatabase(
'  JET_SESID sesid,
'  const tchar* szFilename, ' ANSI only
'  JET_GRBIT grbit
');
Declare Ansi Function JetAttachDatabase Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal szFilename As String, _
    ByVal grpit As Integer _
) As Integer

'JET_ERR JET_API JetOpenDatabase(
'  JET_SESID sesid,
'  const tchar* szFilename, ' ANSI Only
'  const tchar* szConnect,  ' Not Used
'  JET_DBID* pdbid,
'  JET_GRBIT grbit
');
Declare Ansi Function JetOpenDatabase Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal szFilename As String, _
    ByVal szConnect As String, _
    ByRef pdbid As Integer, _
    ByVal grpit As Integer _
) As Integer

'JET_ERR JET_API JetOpenTable(
'  JET_SESID sesid,
'  JET_DBID dbid,
'  const tchar* szTableName,    ' ANSI Only
'  const void* pvParameters,
'  unsigned long cbParameters,
'  JET_GRBIT grbit,
'  JET_TABLEID* ptableid
');
Declare Ansi Function JetOpenTable Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal dbid As Integer, _
    ByVal szTableName As String, _
    ByVal pvParameters As Integer, _
    ByVal cbParameters As Integer, _
    ByVal grbit As Integer, _
    ByRef ptableid As IntPtr _
) As Integer

'JET_ERR JET_API JetGetTableColumnInfo(
'  JET_SESID sesid,
'  JET_TABLEID tableid,
'  const tchar* szColumnName,   ' ANSI Only
'  void* pvResult,
'  unsigned long cbMax,
'  unsigned long InfoLevel
');
Declare Ansi Function JetGetTableColumnInfo Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal tableid As IntPtr, _
    ByVal szColumnName As String, _
    ByVal pvResult As IntPtr, _
    ByVal cbMax As Integer, _
    ByVal InfoLevel As Integer _
) As Integer

'JET_ERR JET_API JetRetrieveColumn(
'  JET_SESID sesid,
'  JET_TABLEID tableid,
'  JET_COLUMNID columnid,
'  void* pvData,
'  unsigned long cbData,
'  unsigned long* pcbActual,
'  JET_GRBIT grbit,
'  JET_RETINFO* pretinfo
');
Declare Function JetRetrieveColumn Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal tableid As IntPtr, _
    ByVal columnid As Integer, _
    ByVal pvData As IntPtr, _
    ByVal cbData As Integer, _
    ByRef pcbActual As Integer, _
    ByVal grbit As Integer, _
    ByRef pretinfo As JET_RETINFO _
) As Integer

'JET_ERR JET_API JetMove(
'  JET_SESID sesid,
'  JET_TABLEID tableid,
'  long cRow,
'  JET_GRBIT grbit
');
Declare Function JetMove Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal tableid As IntPtr, _
    ByVal cRow As Integer, _
    ByVal grbit As Integer _
) As Integer

'JET_ERR JET_API JetCloseTable(
'  JET_SESID sesid,
'  JET_TABLEID tableid
');
Declare Function JetCloseTable Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal tableid As IntPtr _
) As Integer

'JET_ERR JET_API JetCloseDatabase(
'  JET_SESID sesid,
'  JET_DBID dbid,
'  JET_GRBIT grbit
');
Declare Function JetCloseDatabase Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal dbid As Integer, _
    ByVal grpit As Integer _
) As Integer

'JET_ERR JET_API JetDetachDatabase(
'  JET_SESID sesid,
'  const tchar* szFilename
');
Declare Ansi Function JetDetachDatabase Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal szFilename As String _
) As Integer

'JET_ERR JET_API JetEndSession(
'  JET_SESID sesid,
'  JET_GRBIT grbit
');
Declare Function JetEndSession Lib "esent.dll" ( _
    ByVal sesid As IntPtr, _
    ByVal grbit As Integer _
) As Integer

'JET_ERR JET_API JetTerm(
'  JET_INSTANCE instance
');
Declare Function JetTerm Lib "esent.dll" ( _
    ByVal instance As IntPtr _
) As Integer

'JET_ERR JET_API JetGetSystemParameter(
'  JET_INSTANCE instance,
'  JET_SESID sesid,
'  unsigned long paramid,
'  JET_API_PTR* plParam,    ' really is Int32
'  JET_PSTR szParam,        ' ANSI Only
'  unsigned long cbMax
');
Declare Ansi Function JetGetSystemParameter Lib "esent.dll" ( _
    ByVal instance As IntPtr, _
    ByVal sesid As IntPtr, _
    ByVal paramid As Integer, _
    ByRef plParam As Integer, _
    ByVal szParam As String, _
    ByVal cbMax As Integer _
) As Integer

'JET_ERR JET_API JetSetSystemParameter(
'  JET_INSTANCE* pinstance,
'  JET_SESID sesid,
'  unsigned long paramid,
'  JET_API_PTR lParam,      ' really is Int32
'  JET_PCSTR szParam
');
Declare Ansi Function JetSetSystemParameter Lib "esent.dll" ( _
    ByRef pinstance As IntPtr, _
    ByVal sesid As IntPtr, _
    ByVal paramid As Integer, _
    ByVal lParam As Integer, _
    ByVal szParam As String _
) As Integer

'typedef struct {
'  unsigned long cbStruct;
'  JET_TABLEID tableid;
'  unsigned long cRecord;
'  JET_COLUMNID columnidPresentationOrder;
'  JET_COLUMNID columnidcolumnname;
'  JET_COLUMNID columnidcolumnid;
'  JET_COLUMNID columnidcoltyp;
'  JET_COLUMNID columnidCountry;
'  JET_COLUMNID columnidLangid;
'  JET_COLUMNID columnidCp;
'  JET_COLUMNID columnidCollate;
'  JET_COLUMNID columnidcbMax;
'  JET_COLUMNID columnidgrbit;
'  JET_COLUMNID columnidDefault;
'  JET_COLUMNID columnidBaseTableName;
'  JET_COLUMNID columnidBaseColumnName;
'  JET_COLUMNID columnidDefinitionName;
'} JET_COLUMNLIST;
' Note: is not packed (ie Pack:=4)
<StructLayout(LayoutKind.Sequential)> _
Structure JET_COLUMNLIST
    Dim cbStruct As Integer
    Dim tableid As IntPtr
    Dim cRecord As Integer
    Dim columnidPresentationOrder As Integer
    Dim columnidcolumnname As Integer
    Dim columnidcolumnid As Integer
    Dim columnidcoltyp As Integer
    Dim columnidCountry As Integer
    Dim columnidLangid As Integer
    Dim columnidCp As Integer
    Dim columnidCollate As Integer
    Dim columnidcbMax As Integer
    Dim columnidgrbit As Integer
    Dim columnidDefault As Integer
    Dim columnidBaseTableName As Integer
    Dim columnidBaseColumnName As Integer
    Dim columnidDefinitionName As Integer
End Structure

'typedef struct {
'  unsigned long cbStruct;
'  unsigned long ibLongValue;
'  unsigned long itagSequence;
'  JET_COLUMNID columnidNextTagged;
'} JET_RETINFO;
<StructLayout(LayoutKind.Sequential)> _
Structure JET_RETINFO
    Dim cbStruct As Integer
    Dim ibLongValue As Integer
    Dim itagSequence As Integer
    Dim columnidNextTagged As Integer
End Structure

' bunch of constants from essent.h
Const JET_bitDbReadOnly As Integer = 1
Const JET_bitTableReadOnly As Integer = 4
Const JET_ColInfoListSortColumnid As Integer = 7

Const JET_paramErrorToString As Integer = 70
Const JET_paramTempPath As Integer = 1
Const JET_paramLogFilePath As Integer = 2
Const JET_paramSystemPath As Integer = 0
Const JET_paramAccessDeniedRetryPeriod As Integer = 53

Const JET_cbNameMost As Integer = 64
Const JET_wrnColumnNull As Integer = 1004
Const JET_wrnBufferTruncated As Integer = 1006

Const JET_MoveFirst As Integer = &H80000000
Const JET_MoveNext As Integer = 1

Const JET_coltypNil As Integer = 0
Const JET_coltypBit As Integer = 1
Const JET_coltypUnsignedByte As Integer = 2
Const JET_coltypShort As Integer = 3
Const JET_coltypLong As Integer = 4
Const JET_coltypCurrency As Integer = 5
Const JET_coltypIEEESingle As Integer = 6
Const JET_coltypIEEEDouble As Integer = 7
Const JET_coltypDateTime As Integer = 8
Const JET_coltypBinary As Integer = 9
Const JET_coltypText As Integer = 10
Const JET_coltypLongBinary As Integer = 11
Const JET_coltypLongText As Integer = 12
Const JET_coltypSLV As Integer = 13
Const JET_coltypUnsignedLong As Integer = 14 ' New for Vista/2008
Const JET_coltypLongLong As Integer = 15
Const JET_coltypGUID As Integer = 16
Const JET_coltypUnsignedShort As Integer = 17
Const JET_coltypMax As Integer = 18

API Documentation Links

Extensible Storage Engine

The Example Code

The typical "flow" of opening an ESE table is the followng:

JetCreateInstance()
JetInit()
JetBeginSession()
JetAttachDatabase()
JetOpenDatabase()
JetOpenTable()
JetCloseTable()
JetCloseDatabase()
JetDetachDatabase()
JetEndSession()
JetTerm() ' also destroys the Instance

This section shows in detail the flow required to open an ESE table.

Dim inst_id, sess_id, table_id As IntPtr
Dim ret, db_id As Integer

 ' create an instance
ret = JetCreateInstance(inst_id, "MyDemo")
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' initialize the instance
ret = JetInit(inst_id)
If ret <>0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' start the session
ret = JetBeginSession(inst_id, sess_id, Nothing, Nothing)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' attach the database file
ret = JetAttachDatabase(sess_id, DatabaseFile, JET_bitDbReadOnly)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' open the database
ret = JetOpenDatabase(sess_id, DatabaseFile, Nothing, db_id, JET_bitDbReadOnly)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' open the table
ret = JetOpenTable(sess_id, db_id, TableName, 0, 0, JET_bitTableReadOnly, table_id)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' do something here!

 ' close the table
ret = JetCloseTable(sess_id, table_id)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' close the database
ret = JetCloseDatabase(sess_id, db_id, JET_bitDbReadOnly)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' detach the database file
ret = JetDetachDatabase(sess_id, DatabaseFile)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' end the session
ret = JetEndSession(sess_id, 0)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' terminate the instance
ret = JetTerm(inst_id)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

The JetErrorMessage is a "home made" function that translate the ESE error code into a text message:

' convert a JET_ERR code into a text message
Private Function JetErrorMessage(ByVal error_code As Integer) As String
    Dim msg As String
    Dim ret As Integer

    msg = Space(256)
    ret = JetGetSystemParameter(IntPtr.Zero, IntPtr.Zero, JET_paramErrorToString, _
     error_code, msg, 256)
    If ret <> 0 Then
        Throw New ApplicationException("Can't get error message for code " & error_code)
    End If
    Return msg
End Function

You read the contents of a Table in a manner similar to using the DataReader function in ADO.Net. However, that means you'll need to know the schema of the table. The technique to get the column names/data types from a table is kinda complicated. You use the JetGetTableColumnInfo function which returns a listing of column IDs that map to known parts of the schema. The actual schema itself is returned in a temporary table. So, now that you know the column IDs to the temp table, you can read the records of the temp table to get the schema of the real table. Whew!

Dim iPtr As IntPtr
Dim ret, req_size As Integer
Dim col_list As JET_COLUMNLIST
Dim ret_info As JET_RETINFO

col_list = Nothing
iPtr = Marshal.AllocHGlobal(Marshal.SizeOf(col_list))
ret = JetGetTableColumnInfo(sess_id, table_id, Nothing, iPtr, Marshal.SizeOf(col_list), _
 JET_ColInfoListSortColumnid)
If ret <> 0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' do some pointer magic to populate the structure
col_list = CType(Marshal.PtrToStructure(iPtr, GetType(JET_COLUMNLIST)), _
 JET_COLUMNLIST)

' clean up
Marshal.FreeHGlobal(iPtr)

' Set the cursor at the beginning of the temp table. Probably not necessary,
' but what the heck...
ret = JetMove(sess_id, col_list.tableid, JET_MoveFirst, 0)
If ret <>0 Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

' allocate a buffer for the column names, et al
iPtr = Marshal.AllocHGlobal(JET_cbNameMost)
ret_info.cbStruct = Marshal.SizeOf(ret_info)

' If there are multiple values, we get only the first one
ret_info.itagSequence = 1

'
' Loop thru each record in the temp table get the column name, column ID, and data type
'
Do While ret = 0
    Dim col_name As String
    Dim col_id, col_type As Integer

    ' column name
    ret = JetRetrieveColumn(sess_id, col_list.tableid, col_list.columnidcolumnname, iPtr, _
     JET_cbNameMost, req_size, 0, ret_info)
    If ret <> 0 Then
        Throw New ApplicationException(JetErrorMessage(ret))
    End If
    col_name = Marshal.PtrToStringAnsi(iPtr, req_size)

    ' column data type
    ret = JetRetrieveColumn(sess_id, col_list.tableid, col_list.columnidcoltyp, iPtr, 4, req_size, _
     0, ret_info)
    If ret <> 0 Then
        Throw New ApplicationException(JetErrorMessage(ret))
    End If
    col_type = Marshal.ReadInt32(iPtr)

    ' column ID
    ret = JetRetrieveColumn(sess_id, col_list.tableid, col_list.columnidcolumnid, iPtr, 4, _
     req_size, 0, ret_info)
    If ret <> 0 Then
        Throw New ApplicationException(JetErrorMessage(ret))
    End If
    col_id = Marshal.ReadInt32(iPtr)

    ' do something here!

    ret = JetMove(sess_id, col_list.tableid, JET_MoveNext, 0)
Loop

' Free the memory we allocated and destroy the temporary table that got
' automatically created by the JetGetTableColumnInfo command.
Marshal.FreeHGlobal(iPtr)
JetCloseTable(sess_id, col_list.tableid

Now that you have the column ID and data types of the ESE table, you can use the JetRetrieveColumn function to get the data. For example to read an Integer from a known column ID, you'd use the following:

Dim ret, col_id, req_size, IntValue as Integer

ret = JetRetrieveColumn(sess_id, table_id, col_id, iPtr, 4, req_size, 0, ret_info)
If ret <> 0 And ret <> JET_wrnColumnNull Then
    Throw New ApplicationException(JetErrorMessage(ret))
End If

If ret <>JET_wrnColumnNull Then
    IntValue = Marshal.ReadInt32(iPtr)
End If

End Notes

This example doesn't go into too much detail and doesn't do anything useful. However, the sample source code below is for a full-blown application that will read the contents of the ESE database used by the Microsoft Update service and return the rows in a DataGridView as an ADO.Net DataTable.

Most folks will probably get as much or more out of poking around a working example than reading a bunch of articles. As far as I know, this will be the first source code example of using the ESE database APIs ever published on the net.

Download/Links

Download the complete VB.Net Source code example used in this article: ESE_Demo.zip