Click here to Skip to main content
15,393,377 members
Articles / Database Development / SQL Server / SQL Server 2008R2
Tip/Trick
Posted 5 Oct 2014

Stats

10.9K views
2 bookmarked

Storing Large Binary Data in a SQLSERVER Database

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
5 Oct 2014CPOL
How to write data to SQLSERVER varbinary(MAX) columns in C++ using ADO

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:

C++
// 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().

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Schrödter
United States United States
No Biography provided

Comments and Discussions

 
AnswerNeleks suggestions did work very well Pin
Schrödter6-Oct-14 1:53
MemberSchrödter6-Oct-14 1:53 
QuestionAnswer to your private email about code format Pin
Nelek5-Oct-14 23:16
protectorNelek5-Oct-14 23:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.