JetCreateTableColumnIndex3 Yields EsentIndexInvalidDefException in VB, But Not C#

Jan 27, 2012 at 10:15 PM
Edited Jan 27, 2012 at 10:26 PM

Hello,

I downloaded the source code, built it, and tested the BasicTest project in C#.  This works great.

I translated the pertinent portions of the code into Visual Basic, yet I get an EsentIndexInvalidDefException when calling the Api.JetCreateTableColumnIndex3 function.  I have had similar VB results with attempts using Api.JetCreateIndex and Api.JetCreateIndex2, as well.

Any insights or advise would be appreciated.

Thank you.

 

 

Code Sample for Reference, VB, translated directly from C# Source Code:

mdlMain.vb... Imports System Imports System.IO Imports Microsoft.Isam.Esent.Interop Imports Microsoft.Isam.Esent.Interop.Windows7 Namespace Esent2Test Module mdlMain Dim Basename As String = "E00" Dim DirectoryPath As String = New String("Basic") Dim Database As String = New String("Basic\Database.edb") Sub Main() Dim Table As String = New String("testtable") Dim startTime As DateTime = DateTime.Now CreateDirectory(DirectoryPath) Dim CacheSizeInBytes As Integer = 32 * 1024 * 124 SystemParameters.DatabasePageSize = 8192 SystemParameters.CacheSizeMin = CacheSizeInBytes / SystemParameters.DatabasePageSize SystemParameters.CacheSizeMax = CacheSizeInBytes / SystemParameters.DatabasePageSize ' Create an instance, session and database Dim instance As JET_INSTANCE = CreateInstance() Dim sesid As JET_SESID = New JET_SESID() Dim dbid As JET_DBID = New JET_DBID() Api.JetBeginSession(instance, sesid, Nothing, Nothing) Api.JetCreateDatabase(sesid, Database, Nothing, dbid, CreateDatabaseGrbit.None) Api.JetCloseDatabase(sesid, dbid, CloseDatabaseGrbit.None) Api.JetDetachDatabase(sesid, Database) Api.JetAttachDatabase(sesid, Database, AttachDatabaseGrbit.None) Api.JetOpenDatabase(sesid, Database, Nothing, dbid, OpenDatabaseGrbit.None) ' DDL creation ' Creates the tables/columns/indexes used by the DDLTest Dim ddltests As DdlTests = New DdlTests(sesid, dbid, Table) ddltests.Create() End Sub Private Sub CreateDirectory(ByVal directory As String) If (System.IO.Directory.Exists(directory)) Then System.IO.Directory.Delete(directory, True) End If System.IO.Directory.CreateDirectory(directory) End Sub Private Function CreateInstance() As JET_INSTANCE Dim instance As JET_INSTANCE = New JET_INSTANCE() Api.JetCreateInstance(instance, "BasicInstance") Api.JetSetSystemParameter(instance, JET_SESID.Nil, JET_param.LogFileSize, 1 * 1024, Nothing) Api.JetSetSystemParameter(instance, JET_SESID.Nil, JET_param.SystemPath, 0, DirectoryPath) Api.JetSetSystemParameter(instance, JET_SESID.Nil, JET_param.LogFilePath, 0, DirectoryPath) Api.JetSetSystemParameter(instance, JET_SESID.Nil, JET_param.TempPath, 0, DirectoryPath & "\") Api.JetSetSystemParameter(instance, JET_SESID.Nil, JET_param.BaseName, 0, Basename) Api.JetSetSystemParameter(instance, JET_SESID.Nil, Windows7Param.WaypointLatency, 1, Nothing) Api.JetInit(instance) Return instance End Function End Module End Namespace
clsDdlTests.vb... Imports Microsoft.VisualBasic Imports System Imports System.Text Imports Microsoft.Isam.Esent.Interop Imports Microsoft.Isam.Esent.Interop.Vista Imports Microsoft.Isam.Esent.Interop.Windows7 Namespace Esent2Test Public Class DdlTests Private dbid As JET_DBID Private sesid As JET_SESID Private table As String Sub New(ByVal sesid As JET_SESID, ByVal dbid As JET_DBID, ByVal table As String) Me.sesid = sesid Me.dbid = dbid Me.table = table End Sub Public Sub Create() Console.WriteLine("DDL creation") JetCreateTableColumnIndex() End Sub Private Sub JetCreateTableColumnIndex() Console.WriteLine("\tJetCreateTableColumnIndex()") Dim columncreates(12) As JET_COLUMNCREATE Dim i As Integer For i = 0 To columncreates.Length - 1 columncreates(i) = New JET_COLUMNCREATE() Next columncreates(0).szColumnName = "recordID" columncreates(0).coltyp = JET_coltyp.Long columncreates(1).szColumnName = "tagged" columncreates(1).coltyp = VistaColtyp.LongLong columncreates(1).grbit = ColumndefGrbit.ColumnTagged columncreates(2).szColumnName = "separated_lv" columncreates(2).coltyp = JET_coltyp.LongBinary columncreates(3).szColumnName = "compressed_unicode" columncreates(3).coltyp = JET_coltyp.LongText columncreates(3).cp = JET_CP.Unicode columncreates(3).grbit = Windows7Grbits.ColumnCompressed columncreates(4).szColumnName = "compressed_ascii" columncreates(4).coltyp = JET_coltyp.LongText columncreates(4).cp = JET_CP.ASCII columncreates(4).grbit = Windows7Grbits.ColumnCompressed columncreates(5).szColumnName = "compressed_binary" columncreates(5).coltyp = JET_coltyp.LongBinary columncreates(5).grbit = Windows7Grbits.ColumnCompressed columncreates(6).szColumnName = "columntodelete" columncreates(6).coltyp = JET_coltyp.Long columncreates(7).szColumnName = "autoinc" columncreates(7).coltyp = JET_coltyp.Long columncreates(7).grbit = ColumndefGrbit.ColumnAutoincrement columncreates(8).szColumnName = "version" columncreates(8).coltyp = JET_coltyp.Long columncreates(8).grbit = ColumndefGrbit.ColumnVersion columncreates(9).szColumnName = "unicode" columncreates(9).coltyp = JET_coltyp.LongText columncreates(9).cp = JET_CP.Unicode columncreates(9).pvDefault = Encoding.Unicode.GetBytes("This is the default value for the unicode column") columncreates(9).cbDefault = columncreates(9).pvDefault.Length columncreates(10).szColumnName = "ascii" columncreates(10).coltyp = JET_coltyp.LongText columncreates(10).cp = JET_CP.ASCII columncreates(10).pvDefault = Encoding.ASCII.GetBytes("This is the default value for the ASCII column") columncreates(10).cbDefault = columncreates(10).pvDefault.Length columncreates(11).szColumnName = "columntodelete2" columncreates(11).coltyp = JET_coltyp.Long columncreates(12).szColumnName = "fixed" columncreates(12).coltyp = VistaColtyp.LongLong columncreates(12).grbit = ColumndefGrbit.ColumnFixed Dim primarySpaceHints As JET_SPACEHINTS = New JET_SPACEHINTS() primarySpaceHints.ulInitialDensity = 100 primarySpaceHints.cbInitial = 512 * 1024 Dim secondarySpaceHints As JET_SPACEHINTS = New JET_SPACEHINTS() secondarySpaceHints.ulInitialDensity = 80 secondarySpaceHints.cbInitial = 96 * 1024 secondarySpaceHints.ulGrowth = 150 secondarySpaceHints.cbMinExtent = 64 * 1024 secondarySpaceHints.cbMaxExtent = 256 * 1024 Dim indexcreates(13) As JET_INDEXCREATE For i = 0 To indexcreates.Length - 1 indexcreates(i) = New JET_INDEXCREATE() Next indexcreates(0).szIndexName = "index_recordID" indexcreates(0).szKey = "+recordID\0\0" indexcreates(0).cbKey = indexcreates(0).szKey.Length indexcreates(0).grbit = CreateIndexGrbit.IndexPrimary indexcreates(0).pSpaceHints = primarySpaceHints indexcreates(1) = MakeIndexcreate("tagged") indexcreates(2) = MakeIndexcreate("separated_lv") indexcreates(3) = MakeIndexcreate("compressed_unicode") indexcreates(4) = MakeIndexcreate("compressed_ascii") indexcreates(5) = MakeIndexcreate("compressed_binary") indexcreates(6) = MakeIndexcreate("autoinc") indexcreates(7) = MakeIndexcreate("version") indexcreates(8) = MakeIndexcreate("unicode") indexcreates(9) = MakeIndexcreate("ascii") indexcreates(10) = MakeIndexcreate("fixed") indexcreates(11).szIndexName = "secondary" indexcreates(11).szKey = "+autoinc\0+compressed_unicode\0+recordID\0\0" indexcreates(11).cbKey = indexcreates(11).szKey.Length indexcreates(11).grbit = CreateIndexGrbit.IndexUnique indexcreates(11).pSpaceHints = secondarySpaceHints indexcreates(12).szIndexName = "indextodelete" indexcreates(12).szKey = "+autoinc\0+recordID\0\0" indexcreates(12).cbKey = indexcreates(12).szKey.Length indexcreates(13) = MakeIndexcreate("columntodelete2") Dim tablecreate As JET_TABLECREATE = New JET_TABLECREATE() tablecreate.szTableName = Me.table tablecreate.ulPages = 1 tablecreate.ulDensity = 100 tablecreate.rgcolumncreate = columncreates tablecreate.cColumns = tablecreate.rgcolumncreate.Length tablecreate.rgindexcreate = indexcreates tablecreate.cIndexes = tablecreate.rgindexcreate.Length Api.JetBeginTransaction(sesid) Api.JetCreateTableColumnIndex3(sesid, dbid, tablecreate) Api.JetCloseTable(sesid, tablecreate.tableid) Api.JetCommitTransaction(sesid, CommitTransactionGrbit.LazyFlush) End Sub Private Function MakeIndexcreate(ByVal column As String) As JET_INDEXCREATE Dim indexcreate As JET_INDEXCREATE = New JET_INDEXCREATE() indexcreate.szIndexName = String.Format("index_{0}", column) indexcreate.szKey = String.Format("-{0}\0\0", column) indexcreate.cbKey = indexcreate.szKey.Length Return indexcreate End Function End Class End Namespace

 

 

Developer
Jan 27, 2012 at 10:22 PM

You may laugh, but I don't know Visual Basic at all. :)

Something that stands out to me are the strings. In C++ and C# the "\0" provide embedded null characters in the string. Because of your code that has 'DirectoryPath & "\")', my guess is that the backslash is not an escape character, and the VB string will actually have a backslash and a zero in it (and not a null). So I'd suggest changing those \0 characters to the appropriate VB syntax.

-martin

Jan 27, 2012 at 11:51 PM

 

Thank you so much for your response.

You put me on the right track.

VB likes it like this, with literal null embedded in the string:

            indexcreates(12).szKey = "+autoinc" & vbNullChar & "+recordID" & vbNullChar & vbNullChar