This project has moved and is read-only. For the latest updates, please go here.

Simple Primary Key Index

Feb 2, 2012 at 6:35 PM

Can't figure out what I'm doing wrong, but I create a table, create an index to be the primary key, populate the table, but when I dump out the data, it's not in order

Here's the code, boiled down to its simplest form.  Figure I'm doing something dumb, but I just can't see it.

        public static void Run()
        {
            Instance instance;
            Session session;
            JET_DBID dbid;
            JET_TABLEID tableid;
            JET_COLUMNDEF colDefText;
            JET_COLUMNID colIdCol1;
            JET_COLUMNID colIdCol2;

            // Create the Instance & Session
            instance = new Instance("instance");
            instance.Parameters.CircularLog = true;
            instance.Init();
            session = new Session(instance);

            // Create the database.
            Api.JetCreateDatabase(session, "simple.db", null, out dbid, CreateDatabaseGrbit.OverwriteExisting);

            // Create table & columns
            using (var transaction = new Transaction(session))
            {
                Api.JetCreateTable(session, dbid, "simpletable", 0, 100, out tableid);
                colDefText = new JET_COLUMNDEF { coltyp = JET_coltyp.LongText, cp = JET_CP.Unicode };
                Api.JetAddColumn(session, tableid, "column1", colDefText, null, 0, out colIdCol1);
                Api.JetAddColumn(session, tableid, "column2", colDefText, null, 0, out colIdCol2);

                string indexDef = "+column1\0\0";
                Api.JetCreateIndex(session, tableid, "index1", CreateIndexGrbit.IndexPrimary, indexDef, indexDef.Length, 100);
                transaction.Commit(CommitTransactionGrbit.LazyFlush);
            }

            // Add the data
            using (var transaction = new Transaction(session))
            {
                AddRow(session, tableid, colIdCol1, colIdCol2, "row1", "col2_row1");
                AddRow(session, tableid, colIdCol1, colIdCol2, "row4", "col2_row4");
                AddRow(session, tableid, colIdCol1, colIdCol2, "row3", "col2_row3");
                AddRow(session, tableid, colIdCol1, colIdCol2, "row5", "col2_row5");
                AddRow(session, tableid, colIdCol1, colIdCol2, "row2", "col2_row2");
                transaction.Commit(CommitTransactionGrbit.None);
            }

            // Dump out the data, in theory this ought to be in order "row1" -> "row5"
            Api.JetSetCurrentIndex(session, tableid, "index1");
            if (Api.TryMoveFirst(session, tableid))
            {
                do
                {
                    string col1Val = Api.RetrieveColumnAsString(session, tableid, colIdCol1, Encoding.ASCII);
                    string col2Val = Api.RetrieveColumnAsString(session, tableid, colIdCol2, Encoding.ASCII);
                    Console.Write("\t{0}\t{1}", col1Val, col2Val);
                    Console.WriteLine();
                }
                while (Api.TryMoveNext(session, tableid));
            }
            Console.WriteLine();

            // Terminate ESENT. This performs a clean shutdown.
            Api.JetCloseTable(session, tableid);
            Api.JetEndSession(session, EndSessionGrbit.None);
            Api.JetTerm(instance);
        }

        private static void AddRow(Session session, JET_TABLEID tableid, JET_COLUMNID colIdCol1, JET_COLUMNID colIdCol2, string col1Val, string col2Val)
        {
            using (var update = new Update(session, tableid, JET_prep.Insert))
            {
                Api.SetColumn(session, tableid, colIdCol1, col1Val, Encoding.ASCII);
                Api.SetColumn(session, tableid, colIdCol2, col2Val, Encoding.ASCII);
                update.Save();
            }
        }

Feb 2, 2012 at 8:29 PM

You are adding the column as unicode (JET_CP.Unicode), but setting the data as ASCII (Encoding.ASCII). Changing everything to Unicode or ASCII should solve your problem.

 

Feb 2, 2012 at 8:38 PM

Doh!  Thanks, I figured it was something obvious.