olehandler.cpp

从vc连接到oracle数据库。包括连接

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

  File Overview

  This file implements the DBHandler class and its functions
  which is used by the application to communicate to the database.
'*****************************************************************/


#include "StdAfx.h"
#include <atldbcli.h>
#include "ConnectionParams.h"

#define UNICODE
#define DBINITCONSTANTS
#define INITGUID

#include "OraOLEDB.h"
#include "OLEHandler.h"
    
    
    //Variable declaration

    const ULONG         nInitProps=4;
    const ULONG         nPropSet=3;
	const ULONG         nParams=5; //Number of parameters in the command

	DBPROP              InitProperties[nInitProps];
	DBPROPSET           rgInitPropSet[nPropSet];
    DBPARAMBINDINFO     ParamBindInfo[nParams];
    ULONG               ParamOrdinals[nParams];
   	HRESULT             hr;
   


	//Constructor definition
	DBHandler::DBHandler()
	{
		cbColOffset = 0;
		pRows = &hRows[0];
		cNumRows = 0;
	}

	
   /* Declare an array of DBBINDING structures, one for each parameter
    in the command.
    */
    DBBINDING           acDBBinding[nParams];
    DBBINDSTATUS        acDBBindStatus[nParams];

    
   
  //The following buffer is used to store parameter values.    
  typedef struct tagSPROCPARAMS
  {  
	 BSTR languageid;
	 int  productid;
	 BSTR  tname;
	 BSTR tdescription;
	 int check;
	  
  }SPROCPARAMS;


    /**********************************************************************
	This function is used to intialize the database properties and get a 
	connection to it in following steps:
	1. Call CoCreateInstance specifying the provider to obtain access to it.
	2. Set the properties and values of database in DBPROP struct 
	3  Create a array of properties using DBPROPSET struct
	4. Pass this array to initialize the database connection
	***********************************************************************/
	void DBHandler:: InitializeAndEstablishConnection()
    {    
        //Initialize the COM library.
        CoInitialize(NULL);

        //Obtain access to the SQLOLEDB provider.    
        hr = CoCreateInstance(
                        CLSID_OraOLEDB, 
                        NULL, 
                        CLSCTX_INPROC_SERVER,
                        IID_IDBInitialize, 
                        (void **) &pIDBInitialize);
        if (FAILED(hr))
        {
            AfxMessageBox("Failed in CoCreateInstance()");
			DispErrorInfo(pIDBInitialize, IID_IDBInitialize);
        }

        /*
        Initialize the property values needed
        to establish the connection.
        */
        for(i = 0; i < nInitProps; i++)
            VariantInit(&InitProperties[i].vValue);
        //Specify server name.
        InitProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
        InitProperties[0].vValue.vt = VT_BSTR;
        InitProperties[0].vValue.bstrVal = Datasource.AllocSysString();
        InitProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
        InitProperties[0].colid = DB_NULLID;

        //Specify level of prompting to be done
        InitProperties[1].dwPropertyID = DBPROP_INIT_PROMPT;
        InitProperties[1].vValue.vt = VT_I2;
        InitProperties[1].vValue.iVal = DBPROMPT_NOPROMPT;  
        InitProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;
        InitProperties[1].colid = DB_NULLID;

        //Specify username (login).
        InitProperties[2].dwPropertyID = DBPROP_AUTH_USERID; 
        InitProperties[2].vValue.vt = VT_BSTR;
        InitProperties[2].vValue.bstrVal=Username.AllocSysString();
        InitProperties[2].dwOptions = DBPROPOPTIONS_REQUIRED;
        InitProperties[2].colid = DB_NULLID;

        //Specify password.
        InitProperties[3].dwPropertyID = DBPROP_AUTH_PASSWORD;
        InitProperties[3].vValue.vt = VT_BSTR;
        InitProperties[3].vValue.bstrVal = Password.AllocSysString();
        InitProperties[3].dwOptions = DBPROPOPTIONS_REQUIRED;
        InitProperties[3].colid = DB_NULLID;

        /*
        Now that properties are set, construct the DBPROPSET structure
        (rgInitPropSet).  The DBPROPSET structure is used to pass an array
        of DBPROP structures (InitProperties) to the SetProperties method.
        */
        rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
        rgInitPropSet[0].cProperties = 4;
        rgInitPropSet[0].rgProperties = InitProperties;

        //Set initialization properties.
        hr = pIDBInitialize->QueryInterface(
                                    IID_IDBProperties, 
                                    (void **)&pIDBProperties);
        if (FAILED(hr))
        {
            AfxMessageBox("Failed to obtain IDBProperties interface");
			DispErrorInfo(pIDBInitialize, IID_IDBInitialize);
        }
	

        hr = pIDBProperties->SetProperties(nPropSet,rgInitPropSet);

        if(FAILED(hr))
        {
            AfxMessageBox("Failed to set initialization properties.");
			DispErrorInfo(pIDBProperties, IID_IDBProperties);
        }
        pIDBProperties->Release();

        //Now establish a connection to the data source.
        if(FAILED(pIDBInitialize->Initialize()))
        {
			AfxMessageBox("Problem in initializing.");
			DispErrorInfo(pIDBInitialize, IID_IDBInitialize);
        }
    } //end of InitializeAndEstablishConnection.



    
	/**********************************************************************************
	This function gets the product names and languages from database. 
	Steps for retrieving data are as follows -
	1.Specify the SQL select statement (passed as parameter to function)
	2.Establish a connection to database and create a session
	3.Create a command ,set command text and execute it. Get results in rowset pointer.
	4.Determine the data (columns contained in rowset pointer)
	5.Bind the data in each column to a consumer buffer specifying attributes of each
	  column(like type,size,value etc)
    6.Create an accessor using the column bindings in the consumer buffer
	7.Return the rowset pointer containing data to calling function and get the 
	  actual data there
	************************************************************************************/
    IRowset* DBHandler::ExecuteQuery(CString p_sqlString)
    {    
      
         cbColOffset=0;
         /*****************************************
		 Make Connection and create session
		 ******************************************/

	    //Establish connection to database
        InitializeAndEstablishConnection();
     
	     //Obtain a pointer of IDBCreateSession interface
	     if(FAILED(pIDBInitialize->QueryInterface(
                                        IID_IDBCreateSession,
                                        (void**) &pIDBCreateSession)))
		 {
           AfxMessageBox("Failed to get IDBCreateSession interface in ExecuteQuery()");
           DispErrorInfo(pIDBInitialize, IID_IDBInitialize);
		 }

         //Create a session with database
        if(FAILED(pIDBCreateSession->CreateSession(
                                         NULL, 
                                         IID_IDBCreateCommand, 
                                         (IUnknown**) &pIDBCreateCommand)))
		{
          AfxMessageBox("CreateSession failed in ExecuteQuery()");
	      DispErrorInfo(pIDBCreateSession, IID_IDBCreateSession);
           
		}
		//Release the pointer
        pIDBCreateSession->Release();
        

		/*******************************************************
		 Create and execute the command text
		 *******************************************************/
		 //Create a command to execute SQL statement
		if(FAILED(pIDBCreateCommand->CreateCommand(NULL, 
                                         IID_ICommandText, 
                                         (IUnknown **)&pICommandText))) 

		{
			AfxMessageBox("CreateSession failed in ExecuteQuery()");
            
            DispErrorInfo(pIDBCreateCommand, IID_IDBCreateCommand);
		}
		 
		//Release the pointer
		pIDBCreateCommand->Release();
         
		 unsigned short* cmdText=p_sqlString.AllocSysString(); 

		//Set the command text to SQL statment
        if(FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, cmdText)))
        {
            AfxMessageBox("Failed to set command text in ExecuteQuery()");
             DispErrorInfo(pICommandText, IID_ICommandText);

         }
		
        //Execute the command
		if(FAILED(hr = pICommandText->Execute(
                                        NULL, 
                                        IID_IRowset, 
                                        NULL, 
                                        &cNumRows,                //No of rows affected
                                        (IUnknown **) &pRowset))) //Resultset pointer
        {
           AfxMessageBox("Failed to execute command in ExecuteQuery()");
            DispErrorInfo(pICommandText, IID_ICommandText);
        }
        
        //Release the pointer 
        pICommandText->Release();  


         /*******************************************************************
          Determine the data contained in the row set, using the
                 GetColumnInfo method.
         *******************************************************************/
      
       // Obtain access to the IColumnsInfo interface, from the Rowset 
       // object
        if(FAILED(hr =pRowset->QueryInterface(IID_IColumnsInfo, (void **) &pColumnsInfo)))
        {
             AfxMessageBox("Cannot get IColumnInfo interface in ExecuteQuery()");
             DispErrorInfo(pRowset,IID_IRowset);
        }


        // Retrieve the Column information
        if(FAILED(hr =pColumnsInfo->GetColumnInfo(&lNumCols, &pDBColumnInfo,&pStringsBuffer)))
        {
			AfxMessageBox("Cannot get column information in ExecuteQuery()");
           DispErrorInfo(pColumnsInfo, IID_IColumnsInfo);
		}

        // Free the column Information interface
        pColumnsInfo->Release();
     
        // *******************************************************************