Storing Large Binary Data in a SQLSERVER Database

Introduction

I had to store rows of several fairly large multidimensional numerical arrays in a SQLSERVER database. These data must be exportable from there to a mySQL database as well. Since it is absolutely impossible (and fortunately not necessary) to have a separate column for each value, I decided to store them as a blob.

Background

Although this is not a big problem using ODBC, I did not find explicit examples of how to do this using C++ and ADO anywhere (perhaps my fault...).

Using the Code

All arrays/variables in question are serialized into a single unsigned char array using memcpy which is copied into a SAFEARRAY afterwards.

The SAFEARRAY is attached to a _variant_t variable of type VT_ARRAY | VT_UI1.

This _variant_t variable can now be stored in a varbinary(MAX) column in a SQLSERVER database using the ADO PutValue method:

// let no_of_Bytes be the size of the array in bytes
// let myArray be the name of the array
// let outrecordset be an open ADO recordset with col_name being the name of the respective column

unsigned char *U=new unsigned char[no_of_Bytes]; // perhaps larger, see below
memcpy(&U[0],myArray,no_of_Bytes); // more arrays/variables may follow

SAFEARRAY *SA=SafeArrayCreateVector(VT_UI1,0,no_of_Bytes);
long ix[1]; // array of indexes
for (int i=0;i<no_of_Bytes;i++)
{
    ix[0]=i; // first and only index
    SafeArrayPutElement(SA,ix,&U[i]);
}

_variant_t *V=new _variant_t();
V->vt=VT_ARRAY|VT_UI1; // Type of variable
V->parray=SA;  // attach SAFEARRAY

outrecordset->AddNew(); // new record
outrecordset->Fields->GetItem("col_name")->PutValue(V); // store
outrecordset->UpdateBatch(adAffectAll); // and update

delete [] U;  // clean up
SafeArrayDelete(SA);
V->parray=NULL; // deleting V may throw an exception otherwise
delete V;

Of course, instead of arrays, any binary data that fits into the varbinary(MAX) size limit can be handled like this. Reading the data from the database into a SAFEARRAY is done using GetValue().