adodbhandler.cpp

vc在线考试系统源码

/************************************************************
@author  Chandar
@version 1.0
Development Environment        :  Visual C++ 6.0
Name of the File               :  ADODBHandler.cpp
Creation/Modification History  :
                                  01-Aug-2001     Created

File Overview

This file implements the ADODBHandler class and its functions
which are used for communicating to the database in this 
application

***************************************************************/



#include "stdafx.h"

#include "ADODBHandler.h"

#include "ConnectionParams.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif


//Initialize static variable
BOOL ADODBHandler::m_bDatabaseOpen = FALSE;


/**********************************************
 Constructor definition for ADODBHandler class
***********************************************/
ADODBHandler::ADODBHandler()
{
    // initialize OLE
    ::CoInitialize(NULL);

	//Get the application path
	strCurAppPath = (AfxGetApp()->m_pszHelpFilePath);
	
	strCurAppPath=strCurAppPath.Left(strCurAppPath.Find("Debug"));

}

/**********************************************
 Destructor definition for ADODBHandler class
***********************************************/
ADODBHandler::~ADODBHandler()
{
    // unload OLE
    ::CoUninitialize();

	
}

/*******************************************************
This function is used for opening connection to database
********************************************************/
BOOL ADODBHandler ::OpenDatabase()
{
	_bstr_t strMissing(L"");     //Variable for missing function parameters
    _bstr_t strConnectionString;

	//Check if connection is already open
	if (!m_bDatabaseOpen)
    {
       //Initialise the connection string for database 
       strConnectionString = "Provider=OraOLEDB.Oracle;Data Source="+ Datasource +
                              "; User Id=" + Username +
							  ";Password="+ Password ;
       try
	   {
		   //Create instance of ADODB connection object
	      m_cpConnection.CreateInstance("ADODB.Connection");
	 
          //Alternate way of creating instance
	      //m_cpConnection.CreateInstance(__uuidof(Connection));
         
		  //Set the connection string for ADODB connection object
		  m_cpConnection->ConnectionString = strConnectionString;
	  
		  //Open Connection to database. Use strMissing for parameters that are not required
		  //to be passed
	      m_cpConnection->Open(strMissing,strMissing,strMissing,-1);
	   }
	   //Catch COM errors
	   catch( _com_error &e)
	   {
          // get info from _com_error
          
		  AfxMessageBox("OpenDatabase Function :"+e.Description());
          
	      return FALSE;
	   }
	   //Catch Unhandled Exceptions
       catch(...)
	   {
		  AfxMessageBox("Unhandled Exception in OpenDatabase Function");
          
		  return FALSE;
	   }

	}

	//Connection to database is open
    m_bDatabaseOpen = TRUE; 
	
	return  TRUE;     //successfully connected

}
   


/*****************************************************************************
This function gets the product names and id's from the database for displaying
the existing products in listbox on  dialog box
*****************************************************************************/ 
_RecordsetPtr ADODBHandler::GetProductNames()
{     
    //Create instance of command object
	_CommandPtr objCmd;
	_RecordsetPtr m_cpRecordset;
	 

	_bstr_t strMissing(L"");      
    
		 	 
     try
	 {
        //Create instance of command object
        objCmd.CreateInstance("ADODB.Command");  

		//Set the active connection property of command object to open connection
        objCmd->ActiveConnection=m_cpConnection;

		//Set command object's command type to text
        objCmd->CommandType = adCmdText;

		//Set the SQL select statement as command object's text
        objCmd->CommandText=  "select product_id,product_name from product_information";
	    
		//Execute command and get results in Recordset object
		m_cpRecordset= objCmd->Execute(NULL,NULL,adCmdText);
     }
	 //Catch COM errors
     catch( _com_error &e)
     {
        // get info from _com_error
       
		 AfxMessageBox("GetProductNames Function :"+e.Description());
         
     }
	 //Catch Unhandled execptions
     catch(...)
     {
		 AfxMessageBox("Unhandled Exception");
        
	 }
      
    //return recordset object on successful execution
	return m_cpRecordset;
}


/***********************************************************************************
This function gets image for the selected product from the database for displaying
in the existing image frame  on  dialog box
***********************************************************************************/ 
BOOL ADODBHandler::GetImage(int p_productid)
{
	//ADODB smart pointer declaration
	_CommandPtr objCmd;
	_ParameterPtr paramProduct;       //Parameter for product id
    _ParameterPtr paramImage;         //Parmeter for product image
	_RecordsetPtr rst;
    

  	try
	{
		//Get the length of image being retrieved using select statement

        //Create instance of recordset object 
		rst.CreateInstance("ADODB.Recordset");

		//Form a SQL string using input parameter to get the size of image for selected product
		CString temp;
        temp.Format("select dbms_lob.getlength(product_image) from product_information where product_id=%d",p_productid);
       
	 
		_bstr_t sql=temp;
    
		//Open the recordset to execute the above SQL statement 
		rst->Open(sql,(_variant_t((IDispatch *)m_cpConnection,true)),adOpenStatic,adLockOptimistic,adCmdText);
		
		//Get the size of image
		int retsize=rst->Fields->GetItem("dbms_lob.getlength(product_image)")->GetValue().intVal;
	
        //Close recordset object
		rst->Close();
	

		// Code to get the image from database

		 //Create instance of command object  
		objCmd.CreateInstance("ADODB.Command");

		//if image exists
		if(retsize!=0)
		{

	      _variant_t id;           //variable for storing value of product id parameter
	      id.vt=VT_I2;             //Set the type of id as integer
	      id.intVal=p_productid;   //set id value to parameter passed to procedure

		  //Create parameter for product id using command object
    	  paramProduct= objCmd->CreateParameter("product",adInteger,adParamInput,100,id);
	    
		  //Append the parameter to command object
		  objCmd->Parameters->Append(paramProduct);
     
          _ParameterPtr paramImage;  
		  paramImage= objCmd->CreateParameter("image",adLongVarBinary,adParamOutput,retsize+5);
	  
	      //Append the image parameter
	      objCmd->Parameters->Append(paramImage);

	      //Set the command object's active connection to open connection object   	
	      objCmd->ActiveConnection=m_cpConnection;

          //Set the 'SPPrmsLOB' property to true to indicate the Provider that a LOB parameter
          //is being passed
		  objCmd->Properties->GetItem("SPPrmsLOB")->Value=true;
          
		  
		  //Set command object's command type to text
          objCmd->CommandType = adCmdText;
   	 
	      //Set the command text for calling the stored procedure using 
	      //ODBC escape sequence . ? is used for parameter that are passsed dynamically
	    
		  objCmd->CommandText="{ Call getproductimage(?,?) }";
	    
		  //Execute the command object to get the image from  database as out parameter
	      objCmd->Execute(NULL,NULL,adCmdText);

		  //Set the 'SPPrmsLOB' property to false
          objCmd->Properties->GetItem("SPPrmsLOB")->Value=false;
	
		  //Get the byte sequence of image from parameter object
		  _variant_t val;
		  val=objCmd->Parameters->GetItem("image")->GetValue();
	     
		  
		  byte data;   //variable for writing data to file
	      CFile file;  //variable to work with files
	 
		  //Open or create a new file in write mode
	      file.Open(strCurAppPath +"image.bmp",CFile::modeCreate|CFile::modeWrite);
        
          //loop to write each byte retrieved from database to created file
	      for (long index=0;index<=retsize;index++)
		  {
             //retrieve the byte at index element in val into data variable
             HRESULT hr=SafeArrayGetElement(val.parray,&index,&data);
         
		     //Write the data variable to file
		     file.Write(&data,sizeof(data));
		  }
	
		  //Close file 
	      file.Close();
			
	   }
	   else 
		   return FALSE;  //return false if image does not exist

	}
	 //Catch COM errors
     catch( _com_error &e)
     {
        // get info from _com_error
		  AfxMessageBox("GetImage Function :"+e.Source() +e.Description());
		  return FALSE;
     }
	 //Catch exception occured during file operations
	 catch(CFileException &e)
     {
        // get info for File Exception
           AfxMessageBox(e.m_cause);
		   return FALSE;
       
	 }
     //catch unhandled exceptions
     catch(...)
     {
		   AfxMessageBox("Unhandled Exception in GetImage Function");
           return FALSE;
	 }
	   return TRUE;    //return true on successful completion of procedure
     
     
}


/********************************************************************************
This function updates the selected image for the selected product in the database
using stored procedure which takes the image as BLOB parameter 
The property 'SPPrmsLOB' is to be set to true to pass LOB parameter
The size of image file can be greater than 32K, the limitation of PLSQL procedure
parameter size
*********************************************************************************/ 
BOOL ADODBHandler::SetImage(int p_productid,_bstr_t p_image)
{
	//Declare ADO smart pointers
	_CommandPtr objCmd;
	_ParameterPtr paramProduct;        //Parameter for product id
    _ParameterPtr paramImage;          //Parameter for product image
    
    try
	{
	  CFile file;       

	  //Open the file to read in binary mode
      file.Open(p_image,CFile::modeRead|CFile::typeBinary);
   
      //Create instance of command object	
	  objCmd.CreateInstance("ADODB.Command");
	
      
	  _variant_t productid;         //variable for storing value of product id parameter
	  productid.vt=VT_I2;           //Set the type of id as integer 
	  productid.intVal=p_productid; //set id value to parameter passed to procedure
	  
	  //Create parameter for product id using command object
	  paramProduct= objCmd->CreateParameter("productid",adNumeric,adParamInput,100,productid);
	  
	  //Append the parameter to command object
	  objCmd->Parameters->Append(paramProduct);
    
	  //Create parameter for the product image.The EnumDataType is adLongVarBinary because
	  //we are passing image data as bytes to store in database. The size parameter takes 
	  //the size of image file plus one.
	  //The value of this parameter will be set later before execution of command
	  paramImage= objCmd->CreateParameter("image",adLongVarBinary,adParamInput,file.GetLength()+5);
	  
	  //Append the image parameter
	  objCmd->Parameters->Append(paramImage);

      //Create a SAFEARRAY pointer to store the data from image file as byte array	 
	  SAFEARRAY FAR *psa;

	  //Define the structure for SAFEARRAY. The dimension of array is 1.
	  SAFEARRAYBOUND rgbound[1];

	  //Set the lower bound of array
	  rgbound[0].lLbound=0;

	  //Set the total no of elements in array to total no of bytes in image file
      rgbound[0].cElements= file.GetLength(); 
	 

      char arr;           //variable to read data from file
      
      unsigned long i=0;
      long index=0;
     
	  //Create SAFEARRAY defined above with datatype as byte
	  psa=SafeArrayCreate(VT_UI1,1,rgbound);  
 
	  //Loop from start to end of file
      while (i<file.GetLength())
	  {
        //Read one byte from file each time
        file.Read(&arr,sizeof(arr));
          
		//Put the read byte into SAFEARRAY at index element
         SafeArrayPutElement(psa,&index,&arr);
         
		 //increment SAFEARRAY element count
		 index=index+1;

		 //increment loop count
         i=i+1;
 	  }
      
	  //declare variant variable to set the value of image parameter
	  _variant_t data;
     
	  //Declare the datatype of variant variable
	  data.vt=VT_ARRAY|VT_UI1;

	  //Set the data in SAFEARRAY into variant variable
      data.parray=psa;

	  //Append the data to image parameter
      paramImage->AppendChunk(data);
     
      //Set command object's active connection property to open connection 
	  objCmd->ActiveConnection=m_cpConnection;
     
      //Set command object's command type to text
	  objCmd->CommandType = adCmdText;
   	  
	  //Set the 'SPPrmsLOB' property to true because a LOB parameter is being 
	  //passed
	  objCmd->Properties->GetItem("SPPrmsLOB")->Value=true;
	 
	  //set the command text to call stored procedure to update the image
	  //using ODBC escape sequence
	  objCmd->CommandText=  "{ Call setproductimage(?,?) }";
	 
	  //Execute the command
	  _variant_t lrecs;
	  objCmd->Execute(&lrecs,NULL,adCmdText);
      
	  if(lrecs.intVal==1)
		  AfxMessageBox("Image Updated successfully");
	  else
          AfxMessageBox("Image cannot be Updated");

	  //Set the value of 'SPPrmsLOB' to false after execution
	  objCmd->Properties->GetItem("SPPrmsLOB")->Value=false;

	  //Close the image file
	  file.Close();  
	   
	}
	//Catch COM errors
    catch( _com_error &e)
    {
        // get info from _com_error
        AfxMessageBox("GetImage Function :"+e.Description());
        return FALSE;
     }
	//Catch File exceptions
    catch(CFileException &e)
    {
        // get info for File Exception
        AfxMessageBox(e.m_cause);
       
	}
	//Catch unhandled exceptions
    catch(...)
    {
		AfxMessageBox("Unhandled Exception in SetImage Function");
       	return FALSE;
	}
	   return TRUE;   //return true on successful completion of procedure
     
     
}


/***********************************************************
This function is called to close the connection to database
***********************************************************/
void ADODBHandler:: CloseDatabase()
{  	
	//Release the connection pointer
	m_cpConnection.Release();

	//Indicate that connection is closed
	m_bDatabaseOpen=FALSE;
}