How to use JET_prepInsertCopy for single instance storage?

Jul 2, 2010 at 1:42 AM


This is more a general ESE question, but I'm not sure where else to ask (and I am using this library).

I'm looking to utilize the single instance storage feature of ESE when storing long value columns.  The MSDN entry says that in order to be able to use the JET_prepInsertCopy (JET_prep.InsertCopy), the record must already exist.  In my tests, the call will fail as documented when tying to insert new rows using InsertCopy and the data isn't present already.  My question is, whats the best way to utilize the single instance storage feature?  Do I need to do a search for the data I'm about to insert first, then adjust the Update call to use Insert if it's not there, and InsertCopy if it is?


Jul 6, 2010 at 6:32 PM

Most applications use single-instance storage for cases when two rows will share some common data. The second row is created as a copy of the first with (JET_prep.InsertCopy).

As a simple example consider an application that stores e-mail messages. Creating a new message would create a new row (JET_prep.Insert) but copying a message could copy the existing row (JET_prep.InsertCopy). The thing to note here is that the insert/copy semantic is implied by the operation being performed.

The other possibility is that you just want reference counted data. In that case I suggest adding an explicit reference count, instead of creating a new row for each reference.


Jul 6, 2010 at 11:34 PM

Thanks.  My app more resembles your first example than the second one.  Is the single instance per row or per column?  The a page on MSDN makes look like it's per column.


Jul 6, 2010 at 11:39 PM

JET_prep.InsertCopy copies all columns from the source record into a new record. If a column is large (e.g. >1KB) it is stored in a separate long-value table. Single-instancing means that a copy of a record just adds a reference to the long value. This happens for all long-value columns.

Suppose you have a record with 4 columns, an integer, a short string (16 bytes), a long string (4KB) and a long binary column (20MB). Calling JET_prep.InsertCopy will copy the integer and short string into the new record and increment the reference counts on the long columns. If you modify the long column we will make a copy of it and modify the copy.


Jul 6, 2010 at 11:51 PM

Ah ha!  Ok, I think I wasn't quite understanding it correctly before.  Thanks for the explanation.