ADO, VC++2k5, SQL-2k5, store pdf file into Db

[Jimmie C.]

(cross-posted to microsoft.public.sqlserver.clients, microsoft.public.data.ado)

I need to store and retrieve PDF files from SQL.

I am able to store and retrieve data from the Db already. I am not using
stored procedures yet. I had already got it working this way from web
examples when I learned that stored procedures and T-SQL should be the way to
put data in.

Anyway, this is a single user database for a piece of test equipment and it
is not time critical either. So, actually, it has been a good test bed for
learning SQL.

Below is an example of my function to write to the database for typical data
types (int, char, etc.). I have searched and searched. I found a lot of
examples for VB & ASP but NOT C++ (I'm using MFC). I tried to massage a
couple of the VB examples but it didn't work. There is not much documentation
or support out there for C++ & ADO beyond basic examples. That is how I got
this far. I have 4 ADO books but they are all VB, ASP, (&some C#) etc.
centric.

I am new to Db so I really need someone to give the "kiddie steps" to make
this work.

Thanks,
Jim
--------------------------------------------------------------
Here is my example for my basic data writes:
---------------------
try
{
hr = pRecordSet.CreateInstance(_uuidof(Recordset));

if(SUCCEEDED(hr))
{
pRecordSet->PutRefActiveConnection(m_pConnection);
hr = pRecordSet->Open(_variant_t(bstrQuery), vNull, adOpenDynamic,
adLockOptimistic, adCmdText);
if(SUCCEEDED(hr))
{
COleSafeArray vaFieldlist;
vaFieldlist.CreateOneDim(VT_VARIANT, 2);

COleSafeArray vaValuelist;
vaValuelist.CreateOneDim(VT_VARIANT, 2);

long lArrayIndex[1] = {0};

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("test_profile_id")));
vaValuelist.PutElement(lArrayIndex, &(TestProfile_id));
lArrayIndex[0]++;

vaFieldlist.PutElement(lArrayIndex,
&(_variant_t("controller_id")));
vaValuelist.PutElement(lArrayIndex, &(_variant_t(argpCtlr->ID)));
lArrayIndex[0]++;

pRecordSet->AddNew(vaFieldlist, vaValuelist);
pRecordSet->Close();
}
}
}
catch( _com_error &e )
{
::CatchError(e);
}

[Jimmie C.]

By the way, just to give more of an idea where I am at, below is one of my
functions to read from the Db. Like I mentioned in my earlier post, I can
read and write to the Db for typical data types.

However, I need to read and write PDF files. I have set up a column for
varbinary(max) but have not been successful in getting anything into it.

Thanks
Jim

note:
I am not sure if it is customary in these newsgroups to edit the previous
post in the reply. I notice that a lot of posters just leave the whole thing
and let it grow. I am sure this is useful so you can just look at the most
recent post and get the whole picture. Anyway, as I was posting another
function, I deleted my previous post from the reply just to make it cleaner.

--------------------------------------------------------------
Here is my example for my basic data reads:
---------------------
_RecordsetPtr pRecordSet;
PDEPT pData;

_bstr_t bstrQuery("SELECT * FROM COOK.STATIC_DEPT");
_variant_t vRecsAffected(0L);
try
{
pRecordSet = m_pConnection->Execute(bstrQuery, &vRecsAffected,
adOptionUnspecified);

if(!pRecordSet->GetadoEOF())
{
_variant_t vFirstName;
_variant_t vLastName;
_variant_t vDeptNum;
_variant_t vID;
while(!pRecordSet->GetadoEOF())
{
vFirstName = pRecordSet->GetCollect(L"dept_mgr_firstname");
vLastName = pRecordSet->GetCollect(L"dept_mgr_lastname");
vID = pRecordSet->GetCollect(L"dept_id");
vDeptNum = pRecordSet->GetCollect(L"dept_num");
pData = new DEPT;
pData->NameF = vFirstName;
pData->NameL = vLastName;
pData->ID = vID;
pData->DeptNum = vDeptNum;
m_pPSheetRobot->m_PPage1->UpdateCtlDept(pData);
pRecordSet->MoveNext();

[Erland Sommarskog]

Jimmie C. (Jim...@discussions.microsoft.com) writes:
> By the way, just to give more of an idea where I am at, below is one of my
> functions to read from the Db. Like I mentioned in my earlier post, I can
> read and write to the Db for typical data types.
>
> However, I need to read and write PDF files. I have set up a column for
> varbinary(max) but have not been successful in getting anything into it.

Chalk one up more for using ODBC. For ODBC (and OLE DB) there are samples
of this kind: http://www.codeplex.com/MSFTDPProdSamples/

A couple of years back, I started a venture where I needed to
access SQL Server from C++. Somewhat mistakenly, I went for OLE DB,
but I found these samples invaluable. If the samples for ODBC are
equally good, you should get a head start.

> I am not sure if it is customary in these newsgroups to edit the
> previous post in the reply. I notice that a lot of posters just leave
> the whole thing and let it grow.

Which is a bl**dy nusiance.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[chaosu]

You can use adodb.stream to save and retrieve binary file(jpg ,pdf and so on)
http://support.microsoft.com/kb/276488
I list a VBS sample code to save JPG to DB, you also can write it with VC++

set conn=CreateObject("adodb.connection")
connectme="pooling=true;Provider=sqloledb;Data Source=xxx;Initial
Catalog=ADO;Integrated Security=SSPI"
conn.open connectme

set rs = createobject("adodb.recordset")
rs.open "select * from table_1", conn,3,3
rs.addnew
set stream = createobject("adodb.stream") <<< by stream we can convert to
binary file to variant

stream.open
stream.Type = 1
stream.loadfromfile "d:\test\vbs\image3.jpg"
rs(0)= stream.read << adodb::stream will convert image3.jpg to variant and
save to column
rs.update
rs.close