How to replace values in multi-valued ESE column?

Question

I have a multi-valued short ASCII text column in one of the tables in my ESE database, that holds the person's phone numbers.

I have the new set of values, and I'd like to wipe the old values completely, and only use the new values.

The JET_bitSetRevertToDefaultValue bit doesn't seem to work. While the MSDN documentation says "It causes the column to return the default column value on subsequent retrieve column operations. All existing column values are removed.", I found that it does nothing (no return value is returned).

Or, is there an easy way to find out how many values does the column contain (this could be zero, e.g. when I'm doing an insertion, not update)? If it was, I could just run a loop from 'nValues' to 1, erasing the value by setting it to the null while providing the itagSequence value, to achieve what I want.

I'm programming C#, and using the latest version of ManagedEsent library.

Thanks in advance!

Answer 1

You can get the multivalue instance count from ESENT using JetRetrieveColumns. The description of this feature is buried in the Remarks section of the MSDN documentation.

What you should do is use JetRetrieveColumns to retrieve the column with the itagSequence in the JET_RETRIEVECOLUMN to 0 (leave the pvData and cbData members as null/0). After the call the itagSequence member will contain the number of instances of the multivalue column.

JET_RETRIEVECOLUMN retrievecolumn = new JET_RETRIEVECOLUMN();
retrievecolumn.columnid = multivalueColumn;
retrievecolumn.itagSequence = 0;
Api.JetRetrieveColumns(sesid, tableid, new[] { retrievecolumn }, 1); 
Console.WriteLine("{0}", retrievecolumn.itagSequence);

You will need the latest ManagedEsent source code for this to work! When I wrote a test for this I found a bug in the ManagedEsent code -- the itagSequence wasn't being set after the call. I have just checked in a fix.