[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Writing BLOBS to ODBC

Posted on 2004-11-15
3
Medium Priority
?
715 Views
Last Modified: 2012-05-05
Can someone show me how to store BLOBs in an ODBC database, using VC++ & .NET?
The databases I want to connect to are:
   Oracle
   either Access or SqlServer  (do either of these support BLOBs?)

Thanks.

0
Comment
Question by:jpalumbos
  • 2
3 Comments
 
LVL 19

Expert Comment

by:drichards
ID: 12609192
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?
0
 

Author Comment

by:jpalumbos
ID: 12615308
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?)
0
 
LVL 19

Accepted Solution

by:
drichards earned 900 total points
ID: 12661972
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);
     rec->MoveFirst();

     // 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
     rec->Close();

     // Create a new recordset on the connection
     rec.CreateInstance(__uuidof(ADODB::Recordset));

     // 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->AddNew();
     rec->Fields->Item["Filename"]->Value = "bogus.jpg";
     rec->Fields->Item["Picture"]->Value = val;
     rec->Update();

     // Clean up
     rec->Close();
     con->Close();
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The following diagram presents a diamond class hierarchy: As depicted, diamond inheritance denotes when two classes (e.g., CDerived1 and CDerived2), separately extending a common base class (e.g., CBase), are sub classed simultaneously by a fourt…
In Easy String Encryption Using CryptoAPI in C++ (http://www.experts-exchange.com/viewArticle.jsp?aid=1193) I described how to encrypt text and recommended that the encrypted text be stored as a series of hexadecimal digits -- because cyphertext may…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question