insert or update blob of SQLServer via ADO Comman d

//pass blob to stored procedure

//build by masterz 20050301 with VC2003, Windows 2003, SQLServer 2000.

#include "stdafx.h"

#import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" rename( "EOF",

"adoEOF" )

struct InitOle

{

       InitOle()  { ::CoInitialize(NULL); }

       ~InitOle() { ::CoUninitialize();  }

} _init_InitOle_;

void PrintProviderError(ADODB::_ConnectionPtr pConnection);



void print_properties(LPCTSTR name, ADODB::PropertiesPtr Properties)

{

       long prop_count = Properties->GetCount();

       printf("%s property count = %d\n",name,prop_count);

       for(long i=0;i<prop_count;i++)

       {

               printf("%s property

[%d]:%s\n",name,i,(LPCSTR)Properties->GetItem(i)->Name);

       }

}

int main(int argc, char* argv[])

{

       ADODB::_ConnectionPtr  Conn1;

       ADODB::_CommandPtr    Cmd1;

       ADODB::_ParameterPtr  oldParam= NULL;

       ADODB::_ParameterPtr inParam=NULL;

       ADODB::_ParameterPtr blobParam=NULL;

       _variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);

       _variant_t  vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR);

       //_bstr_t    bstrConnect="Provider=OraOLEDB.Oracle;Data

Source=orcl;User Id=system;Password=oracle;";

        _bstr_t    bstrConnect="Driver={SQL

Server};Server=localhost;Database=zxg;Uid=sa;Pwd=sa;" ;

        //create procedure dbo.insert_update_blob(@fn

varchar(9),@filecontent image) as

               // if exists (select * from table1 where filename=@fn )

               //       begin

               //       update table1 set content=@filecontent where filename=@fn

               //       end

               // else

               // begin

               // insert table1 (filename,content) values(@fn,@filecontent)

               // end

_bstr_t    bstrSP("{CALL insert_update_blob(?,?)}" );

       try

       {

               _variant_t varOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);

               ADODB::_StreamPtr adostream;

               adostream.CreateInstance(_T("ADODB.Stream"));

               adostream->Type = ADODB::adTypeBinary;

               adostream->Open(varOptional,ADODB::adModeUnknown,

ADODB::adOpenStreamUnspecified, _T(""), _T(""));

               adostream->LoadFromFile("f:\\masterz.zip");

               _variant_t vReadTo = adostream->Read(ADODB::adReadAll);

               long blob_size = adostream->GetSize();

               adostream->Close();



               _bstr_t bstrEmpty;

               Conn1.CreateInstance( __uuidof( ADODB::Connection ) );

               Cmd1.CreateInstance( __uuidof( ADODB::Command ) );

               Conn1->ConnectionString = bstrConnect;

               Conn1->Open( bstrConnect, bstrEmpty, bstrEmpty, -1 );

               Cmd1->ActiveConnection = Conn1;

               Cmd1->CommandText      = bstrSP;

               Cmd1->CommandType      = ADODB::adCmdText;

               Conn1->Properties->Refresh();

               inParam =

Cmd1->CreateParameter(_bstr_t("@fn"),ADODB::adChar,ADODB::adParamInput

,2,_variant_t( "a" ));

               Cmd1->Parameters->Append(inParam);

               blobParam =

Cmd1->CreateParameter(_bstr_t("@filecontent"),ADODB::adLongVarBinary,A

DODB::adParamInput,blob_size,vReadTo);

               Cmd1->Parameters->Append(blobParam);

               Cmd1->Properties->Refresh();

               print_properties("Cmd1",Cmd1->Properties);

               Cmd1->Execute(NULL,NULL,ADODB::adExecuteNoRecords);

               Conn1->Close();

               //select filename,datalength(content) from table1 where

datalength(content)>0

       }

       catch(_com_error &e)

       {

               _bstr_t bstrSource(e.Source());

               _bstr_t bstrDescription(e.Description());

               printf("\nCOM error occurred, Source : %s \n Description : %s

\n",(LPCSTR)bstrSource,(LPCSTR)bstrDescription);

               PrintProviderError(Conn1);

       }

       printf("\nprogram end\n");

       return 0;

}

VOID PrintProviderError(ADODB::_ConnectionPtr pConnection)

{

       ADODB::ErrorPtr  pErr = NULL;

       long      nCount = 0;

       long      i = 0;

       if( (pConnection->Errors->Count) > 0)

       {

               nCount = pConnection->Errors->Count;

               for(i = 0; i < nCount; i++)

               {

                       pErr = pConnection->Errors->GetItem(i);

                       printf("\n\t Error number: %x\t%s", pErr->Number,

(LPCSTR)pErr->Description);

               }

       }

}