Writing BLOBS to ODBC

Can someone show me how to store BLOBs in an ODBC database, using VC++ & .NET?
The databases I want to connect to are:
   either Access or SqlServer  (do either of these support BLOBs?)


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Both SQL Server and Access can support BLOBs.  How are you trying to access the database - ADO .NET, ADO, OLE DB, ATL database helper classes, other...?  Mainly, are you using managed or unmanaged C++ and what API are you using/want to use for data access?
jpalumbosAuthor Commented:
I'm just using ODBC, with SQL statements to access the database.
I don't know the answers to the other questions you're asking.  (managed c++?  api for data access?)
Since you don't know what managed C++ is, I'll assume you are using standard C++.  In that case, you can use ADO to access the database.  SQL Server, and probably Oracle, have other ways of accessing BLOB data as well, but this will work for Access too.  For it to work, you will need to have:

#include <atldbcli.h>
#include <iostream>

#undef EOF
#import "C:\Winnt\System32\dllcache\msado15.dll"

Also, you will need to initialize COM (like CoInitialize(0);) prior to using ADO calls.  ADO is well documented in MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoapireference.asp

If this is not what you were looking for, let me know.  The code used a database that was set up with an ODBC DSN  called "BLOB Test" and had a table called "tblPics" with an autonumber column and then a "Filename" and "Picture" column.  The "Picture" column was a binary data column that stored the contents of an image file (like a jpg).

     ADODB::_ConnectionPtr     con = NULL;
     ADODB::_RecordsetPtr     rec = NULL;
     HRESULT               hr = S_OK;
     VARIANT               *vRecordsAffected = NULL;

     // Create and open the connection
     hr = con.CreateInstance (__uuidof(ADODB::Connection));
     con->Open ("DSN=BLOB Test;", "", "", 0);

     // Execute a query and move to the first record of the result
     rec = con->Execute("SELECT * FROM tblPics;", vRecordsAffected, 1);

     // Get the picture data (a jpg file)
     _variant_t idx = "Picture";
     ADODB::FieldPtr pf = rec->Fields->Item["Picture"];
     long sz = pf->ActualSize;
     _variant_t val = pf->GetChunk(sz);
     // The result should be a SAFEARRAY of bytes
     if ( val.vt == (VT_UI1 | VT_ARRAY) )
         std::cout << "Got BLOB of size " << val.parray->rgsabound->cElements << std::endl;

     // Close the recordset

     // Create a new recordset on the connection

     // Populate the recordset
     _variant_t cmd = "SELECT * FROM tblPics;";
     _variant_t varcon;
     varcon.pdispVal = con;
     varcon.vt = VT_DISPATCH;
     rec->Open(&cmd, &varcon, ADODB::adOpenDynamic, ADODB::adLockOptimistic, 0);

     // Add a new record and copy the picture we just read into a new record
     rec->Fields->Item["Filename"]->Value = "bogus.jpg";
     rec->Fields->Item["Picture"]->Value = val;

     // Clean up

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual C++.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.