database.cpp

wince下对sqlserver数据库的操作,增删改操作,单进程单线程

// DataBase.cpp: implementation of the CDataBase class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
//#include "ccdb.h"
#include "BusDef.h"
#include "DataBase.h"

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

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

extern void writebuginfo(const char* lpszFormat, ...);



CDataBase::CDataBase()
{
	m_pIDBCreateSession=NULL;
	m_bOpen=FALSE;

}

CDataBase::~CDataBase()
{
	if(m_bOpen)
		CloseData();

}
/*
功能：打开数据库
参数：无
返回：BOOL 
	TRUE －－成功
	FALSE －－失败
编写人：吕黄梁
时间：2004-12-23
*/
BOOL CDataBase::OpenData(const WCHAR *wpDbfile)
{
	if(m_bOpen)
		return TRUE;
    HRESULT			   	hr				= NOERROR;	// Error code reporting
	DBPROP				dbprop[1];					// property used in property set to initialize provider
	DBPROPSET			dbpropset[1];				// Property Set used to initialize provider

    IDBInitialize       *pIDBInitialize = NULL;		// Provider Interface Pointer
	IDBProperties       *pIDBProperties	= NULL;		// Provider Interface Pointer


	// Initialize environment
	//
/*	hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);

	if(FAILED(hr))
	{ 
		return FALSE;
	}
*/
	VariantInit(&dbprop[0].vValue);		

    // Create an instance of the OLE DB Provider
	//
	hr = CoCreateInstance(	CLSID_SQLSERVERCE_2_0, 
							0, 
							CLSCTX_INPROC_SERVER, 
							IID_IDBInitialize, 
							(void**)&pIDBInitialize);
	if(FAILED(hr))
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

	// Initialize a property with name of database
	//
    dbprop[0].dwPropertyID	= DBPROP_INIT_DATASOURCE;
	dbprop[0].dwOptions		= DBPROPOPTIONS_REQUIRED;
    dbprop[0].vValue.vt		= VT_BSTR;
	if(wpDbfile ==NULL)
    dbprop[0].vValue.bstrVal= SysAllocString(DATABASE_PMDB);
	else
    dbprop[0].vValue.bstrVal= SysAllocString(wpDbfile);
	if(NULL == dbprop[0].vValue.bstrVal)
	{
		hr = E_OUTOFMEMORY;
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

	// Initialize the property set
	//
	dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
	dbpropset[0].rgProperties	 = dbprop;
	dbpropset[0].cProperties	 = sizeof(dbprop)/sizeof(dbprop[0]);

	//Set initialization properties.
	//
	hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
    if(FAILED(hr))
    {
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
    }

	// Sets properties in the Data Source and initialization property groups
	//
    hr = pIDBProperties->SetProperties(1, dbpropset); 
	if(FAILED(hr))
    {
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
    }

	// Initializes a data source object 
	//
	hr = pIDBInitialize->Initialize();
	if(FAILED(hr))
    {
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
    }

    // Get IDBCreateSession interface
    //
  	hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**)&m_pIDBCreateSession);
	if(FAILED(hr))
    {
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
    }
//.........


// Specify the command text using parameter markers in the query syntax.
// We know that this command will return 1 row and one image column (Photo).


Exit:
    // Clear Variant
    //
	VariantClear(&dbprop[0].vValue);
	
	// Release interfaces
	//
	if(pIDBProperties)
	{
		pIDBProperties->Release();
	}

    if (pIDBInitialize)
    {
        pIDBInitialize->Release();
    }
	if(FAILED(hr))
    {
		return FALSE;
    }else
	{
		m_bOpen= TRUE;
		return TRUE;
	}

	/*
	HRESULT hr = NOERROR; 

	// Property used in property set to initialize provider
	DBPROP dbprop[1]; 

	// Property Set used to initialize provider
	DBPROPSET dbpropset[1]; 

	// Provider Interface Pointers
	IDBInitialize *pIDBInitialize = NULL; 
	IDBProperties *pIDBProperties = NULL; 

	VariantInit(&dbprop[0].vValue); 
	// Create an instance of the OLE DB Provider
	hr = CoCreateInstance( CLSID_SQLSERVERCE_2_0, 
	0,
	CLSCTX_INPROC_SERVER, 
	IID_IDBInitialize, 
	(void **) &pIDBInitialize );
	if ( FAILED(hr) )
	{
	MessageBox(NULL,L"CoCreateInstance failed!", L"Casper", MB_OK);
	goto Exit;
	}
	// Initialize a property with name of database
	dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
	dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
	dbprop[0].vValue.vt = VT_BSTR;
	dbprop[0].vValue.bstrVal = SysAllocString(DATABASE_PMDB);

	if(dbprop[0].vValue.bstrVal == NULL)
	{
	hr = E_OUTOFMEMORY;
	goto Exit;
	}

	// Initialize the property set
	dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
	dbpropset[0].rgProperties = dbprop;
	dbpropset[0].cProperties = sizeof(dbprop) / sizeof(dbprop[0]);


	// Set initialization properties.
	hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)
	&pIDBProperties);

	if(FAILED(hr))
	{ 
	MessageBox(NULL,L"pIDBInitialize->QueryInterface failed!",
	L"Casper", MB_OK);
	goto Exit;
	}
	// Sets properties in the Data Source and initialization property	groups
	// new -> 
	hr = pIDBProperties->SetProperties( sizeof(dbprop) /	sizeof(dbprop[0]), dbpropset );
//	hr = pIDBProperties->SetProperties(1, dbpropset); 

	if(FAILED(hr))
	{
	MessageBox(NULL,L"pIDBProperties->SetProperties failed!",
	L"Casper", MB_OK);
	goto Exit;
	}
	// Initializes a data source object 
	hr = pIDBInitialize->Initialize();

	// CRASHS!!!! Error = E_FAIL.

	if(FAILED(hr))
	{
	switch(hr)
	{

	case
	DB_S_ASYNCHRONOUS :
	MessageBox(NULL,L"async", L"Casper", MB_OK);
	break;
	case DB_S_ERRORSOCCURRED :
	MessageBox(NULL,L"error", L"Casper", MB_OK);
	break;
	case E_FAIL :
		PrintErrorInfo (IID_IDBInitialize, pIDBInitialize);
	MessageBox(NULL,L"efail", L"Casper", MB_OK);
	break;
	case E_OUTOFMEMORY :
	MessageBox(NULL,L"out of mem", L"Casper", MB_OK);
	break;
	case E_UNEXPECTED :
	MessageBox(NULL,L"unex", L"Casper", MB_OK);
	break;
	case DB_E_ALREADYINITIALIZED :
	MessageBox(NULL,L"already", L"Casper", MB_OK);
	break;
	case DB_E_CANCELED :
	MessageBox(NULL,L"canc", L"Casper", MB_OK);
	break;
	case DB_E_ERRORSOCCURRED :
	MessageBox(NULL,L"errotrs occ", L"Casper", MB_OK);
	break;
	case DB_SEC_E_AUTH_FAILED :
	MessageBox(NULL,L"auth", L"Casper", MB_OK);
	break;


	}


	MessageBox(NULL,L"pIDBInitialize->Initialize() failed!",
	L"Casper", MB_OK);
	goto Exit;
	}
	// Get IDBCreateSession interface
	hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **)
	&m_pIDBCreateSession);
	if (FAILED(hr))
	MessageBox(NULL,L"pIDBInitialize->QueryInterface!", L"Casper",
	MB_OK);
Exit:
	// Clear Variant
	VariantClear(&dbprop[0].vValue);

	// Release interfaces
	if(pIDBProperties)
	pIDBProperties->Release();

	if(pIDBInitialize)
	pIDBInitialize->Release();
	
	if (FAILED(hr))
	return FALSE;
	else
		return TRUE;

/*    HRESULT			   	hr				= NOERROR;	// Error code reporting
	DBPROP				dbprop[1];					// property used in property set to initialize provider
	DBPROPSET			dbpropset[1];				// Property Set used to initialize provider

    IDBInitialize       *pIDBInitialize = NULL;		// Provider Interface Pointer
	IDBProperties       *pIDBProperties	= NULL;		// Provider Interface Pointer


	// Initialize environment
	//

	VariantInit(&dbprop[0].vValue);		

    // Create an instance of the OLE DB Provider
	//
	hr = CoCreateInstance(	CLSID_SQLSERVERCE_2_0, 
							0, 
							CLSCTX_INPROC_SERVER, 
							IID_IDBInitialize, 
							(void**)&pIDBInitialize);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// Initialize a property with name of database
	//
    dbprop[0].dwPropertyID	= DBPROP_INIT_DATASOURCE;
	dbprop[0].dwOptions		= DBPROPOPTIONS_REQUIRED;
    dbprop[0].vValue.vt		= VT_BSTR;
    dbprop[0].vValue.bstrVal= SysAllocString(DATABASE_PMDB);
	if(NULL == dbprop[0].vValue.bstrVal)
	{
		hr = E_OUTOFMEMORY;
		goto Exit;
	}

	// Initialize the property set
	//
	dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
	dbpropset[0].rgProperties	 = dbprop;
	dbpropset[0].cProperties	 = sizeof(dbprop)/sizeof(dbprop[0]);

	//Set initialization properties.
	//
	hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
    if(FAILED(hr))
    {
		goto Exit;
    }

	// Sets properties in the Data Source and initialization property groups
	//
    hr = pIDBProperties->SetProperties(1, dbpropset); 
	if(FAILED(hr))
    {
		goto Exit;
    }

	// Initializes a data source object 
	//
	hr = pIDBInitialize->Initialize();
	if(FAILED(hr))
    {
		goto Exit;
    }

    // Get IDBCreateSession interface
    //
  	hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**)&m_pIDBCreateSession);
	if(FAILED(hr))
    {
		goto Exit;
    }
//.........


// Specify the command text using parameter markers in the query syntax.
// We know that this command will return 1 row and one image column (Photo).


Exit:
    // Clear Variant
    //
	VariantClear(&dbprop[0].vValue);
	
	// Release interfaces
	//
	if(pIDBProperties)
	{
		pIDBProperties->Release();
	}

    if (pIDBInitialize)
    {
        pIDBInitialize->Release();
    }
	if(FAILED(hr))
    {
		return FALSE;
    }else
		return TRUE;
		*/
}
/*
功能：执行一句SQL语句，用于执行更新数据，删除数据的操作
参数：WCHAR *pSql －－要执行的SQL语句
返回：BOOL 
	TRUE －－成功
	FALSE －－失败
编写人：吕黄梁
时间：2004-12-23
*/
BOOL CDataBase::ExecuteSql(WCHAR *pSql)
{
    HRESULT			   	hr				= NOERROR;	// Error code reporting
	IDBCreateCommand	*pIDBCrtCmd;	// Provider Interface Pointer
	ICommandText		*pICmdText;	// Provider Interface Pointer
//	writebuginfo(pSql);
	if(!m_pIDBCreateSession)
	{
		ASSERT(0);
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		return FALSE;
	}
	hr = m_pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
    (IUnknown**) &pIDBCrtCmd);
	if (FAILED(hr))
	{
		//Send an error-specific message and do error handling.
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}


	// Create the new command that uses parameters.
	hr = pIDBCrtCmd->CreateCommand(NULL, IID_ICommandText, (IUnknown**) &pICmdText);
	if (FAILED(hr))
	{
		//Send an error-specific message and do error handling.
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}	
	
	
	hr = pICmdText->SetCommandText(DBGUID_DBSQL, pSql);
	if (FAILED(hr))
	{
		//Send an error-specific message and do error handling.

		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

	hr = pICmdText->Execute(NULL, IID_NULL, NULL, NULL, NULL);
	if(FAILED(hr))
	{
		//Send an error-specific message and do error handling.
		char tempbuf[2048]="";
		WideCharToMultiByte(CP_ACP,WC_COMPOSITECHECK,pSql,-1,tempbuf,2048,NULL,NULL);
		writebuginfo("sql exe err:%s\n",tempbuf);
		goto Exit;
	}
Exit:

	
	// Release interfaces
	//
	if(pICmdText)
		pICmdText->Release();
	if(pIDBCrtCmd)
		pIDBCrtCmd->Release();

	if(FAILED(hr))
    {
		return FALSE;
    }else
		return TRUE;
}
/*
功能：执行一句SQL语句，用于执行更新数据，删除数据的操作
参数：WCHAR **pSql －－要执行的多个SQL语句的指针
int num
返回：BOOL 
	TRUE －－成功
	FALSE －－失败
编写人：吕黄梁
时间：2004-12-23
*/
BOOL CDataBase::ExecuteSql(NodeSql *pNode)
{
 HRESULT			   	hr				= NOERROR;	// Error code reporting
	IDBCreateCommand	*pIDBCrtCmd;	// Provider Interface Pointer
	ICommandText		*pICmdText;	// Provider Interface Pointer
//	writebuginfo(pSql);
	if(!m_pIDBCreateSession)
	{
		ASSERT(0);
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		return FALSE;
	}
	hr = m_pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
    (IUnknown**) &pIDBCrtCmd);
	if (FAILED(hr))
	{
		//Send an error-specific message and do error handling.
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}


	// Create the new command that uses parameters.
	hr = pIDBCrtCmd->CreateCommand(NULL, IID_ICommandText, (IUnknown**) &pICmdText);
	if (FAILED(hr))
	{
		//Send an error-specific message and do error handling.
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}	
	char tempbuf[300];
	while(pNode != NULL)
	{
		WideCharToMultiByte(CP_ACP,WC_COMPOSITECHECK,pNode->pSql,-1,tempbuf,2048,NULL,NULL);
		writebuginfo(tempbuf);
		hr = pICmdText->SetCommandText(DBGUID_DBSQL, pNode->pSql);
		if (FAILED(hr))
		{
			//Send an error-specific message and do error handling.
	//		writebuginfo(L"SetCommand Text failure!");
	//		writebuginfo(pNode->pSql);
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
			goto Exit;
		}

		hr = pICmdText->Execute(NULL, IID_NULL, NULL, NULL, NULL);
		if(FAILED(hr))