database.cpp

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

		{
			//Send an error-specific message and do error handling.
//			writebuginfo(L"execute Command  failure!");
	//		writebuginfo(pNode->pSql);
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
			goto Exit;
		}
		pNode=pNode->pNext;
	}
Exit:

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

	if(FAILED(hr))
    {
		return FALSE;
    }else
		return TRUE;
}
/*
功能：关闭数据库
参数：无
返回：无 
编写人：吕黄梁
时间：2004-12-23
*/
void CDataBase::CloseData()
{
	if(m_pIDBCreateSession)
	{
        HRESULT        hr = NOERROR;
        IDBInitialize *pIDBInitialize = NULL;

	    hr = m_pIDBCreateSession->QueryInterface(IID_IDBInitialize, (void **) &pIDBInitialize);
	    if(SUCCEEDED(hr))
	    {
            pIDBInitialize->Uninitialize();
            pIDBInitialize->Release();
        }
        
		m_pIDBCreateSession->Release();
		m_pIDBCreateSession=NULL;
	}
	m_bOpen= FALSE;

//	CoUninitialize();

}
/*
功能：插入一条新记录
参数：
pBusTableStruct pBusStr－－修改的业务表的结构
char *pRowStr －－要修改的记录的结构，所有的字段均要有
返回：int 
	< 0 －－错误代码
	> 0－－新记录的主键
编写人：吕黄梁
时间：2004-12-23
*/
int CDataBase::Insert(pBusTableStruct pBusStr,char *pRowStr,BOOL bAutoKey)
{
	HRESULT				hr					= NOERROR;			// Error code reporting
	DBBINDING			*prgBinding			= NULL;				// Binding used to create accessor
    HROW				rghRows[1]          = {DB_NULL_HROW};   // Array of row handles obtained from the rowset object
	HROW				*prghRows			= rghRows;			// Row handle(s) pointer
	DBID				TableID;								// Used to open/create table
	DBID				IndexID;								// Used to create index
	DBPROPSET			rowsetpropset[1];						// Used when opening integrated index
	DBPROP				rowsetprop[1];							// Used when opening integrated index
   	ULONG				cRowsObtained		= 0;				// Number of rows obtained from the rowset object
    DBOBJECT			dbObject;								// DBOBJECT data.
	DBCOLUMNINFO		*pDBColumnInfo		= NULL;				// Record column metadata
	BYTE				*pData				= NULL;				// record data
	WCHAR				*pStringsBuffer		= NULL;
	DWORD				dwBindingSize		= 0;
	DWORD				dwIndex				= 0;
	DWORD				dwRow				= 0;
	DWORD				dwCol				= 0;
	DWORD				dwOffset			= 0;
	ULONG				ulNumCols;

	IOpenRowset			*pIOpenRowset		= NULL;				// Provider Interface Pointer
	IRowset				*pIRowset			= NULL;				// Provider Interface Pointer
	ITransactionLocal	*pITxnLocal			= NULL;				// Provider Interface Pointer
	IRowsetChange		*pIRowsetChange		= NULL;				// Provider Interface Pointer
	IAccessor			*pIAccessor			= NULL;				// Provider Interface Pointer
	ISequentialStream	*pISequentialStream = NULL;				// Provider Interface Pointer
	IColumnsInfo		*pIColumnsInfo		= NULL;				// Provider Interface Pointer
	HACCESSOR			hAccessor			= DB_NULL_HACCESSOR;// Accessor handle，
	HACCESSOR			hAccessor1			= DB_NULL_HACCESSOR;// Accessor handle
	
	int nID=0;

	VariantInit(&rowsetprop[0].vValue);

	// Validate IDBCreateSession interface
	//
	if (NULL == m_pIDBCreateSession)
	{
		ASSERT(0);
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		return -101;//编程错误
	}

    // Create a session object 
    //
    hr = m_pIDBCreateSession->CreateSession(NULL, IID_IOpenRowset, (IUnknown**)&pIOpenRowset);
    if(FAILED(hr))
    {
 		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
       goto Exit;
    }

	hr = pIOpenRowset->QueryInterface(IID_ITransactionLocal, (void**)&pITxnLocal);
	if(FAILED(hr))
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

	// Set up information necessary to open a table 
	// using an index and have the ability to seek.
	//
	TableID.eKind			= DBKIND_NAME;
	TableID.uName.pwszName	= pBusStr->Name;//(WCHAR*)TABLE_EMPLOYEE;

	IndexID.eKind			= DBKIND_NAME;
	IndexID.uName.pwszName	= L"PK_Employees";

	// Request ability to use IRowsetChange interface
	// 
	rowsetpropset[0].cProperties	= 1;
	rowsetpropset[0].guidPropertySet= DBPROPSET_ROWSET;
	rowsetpropset[0].rgProperties	= rowsetprop;

	rowsetprop[0].dwPropertyID		= DBPROP_IRowsetChange;
	rowsetprop[0].dwOptions			= DBPROPOPTIONS_REQUIRED;
	rowsetprop[0].colid				= DB_NULLID;
	rowsetprop[0].vValue.vt			= VT_BOOL;
	rowsetprop[0].vValue.boolVal	= VARIANT_TRUE;

	// Open the table using the index
	//
	hr = pIOpenRowset->OpenRowset(	NULL,
									&TableID,
									NULL,//&IndexID,
									IID_IRowset,
									sizeof(rowsetpropset)/sizeof(rowsetpropset[0]),
									rowsetpropset,
									(IUnknown**) &pIRowset);
	if(FAILED(hr))
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

    // Get IRowsetChange interface
	//
	hr = pIRowset->QueryInterface(IID_IRowsetChange, (void**)&pIRowsetChange);
	if(FAILED(hr))
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

    // Get IColumnsInfo interface
	//
    hr = pIRowset->QueryInterface(IID_IColumnsInfo, (void **)&pIColumnsInfo);
	if(FAILED(hr))
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

	// Get the column metadata 
	//
    hr = pIColumnsInfo->GetColumnInfo(&ulNumCols, &pDBColumnInfo, &pStringsBuffer);
	if(FAILED(hr) || 0 == ulNumCols)
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

    // Create a DBBINDING array.
    // The binding doesn't include the bookmark column (first column).
	//
	dwBindingSize = ulNumCols - 1;
	ASSERT(pBusStr->Num == dwBindingSize);

	prgBinding = (DBBINDING*)CoTaskMemAlloc(sizeof(DBBINDING)*dwBindingSize);
	if (NULL == prgBinding)
	{
		hr = E_OUTOFMEMORY;
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

	// Set initial offset for binding position
	//
	dwOffset = 0;

	// Prepare structures to create the accessor
	//
    for (dwIndex = 0; dwIndex < dwBindingSize; ++dwIndex)
    {
		prgBinding[dwIndex].iOrdinal	= pDBColumnInfo[dwIndex + 1].iOrdinal;
		prgBinding[dwIndex].pTypeInfo	= NULL;
		prgBinding[dwIndex].pBindExt	= NULL;
		prgBinding[dwIndex].dwMemOwner	= DBMEMOWNER_CLIENTOWNED;
		prgBinding[dwIndex].dwFlags		= 0;
		prgBinding[dwIndex].bPrecision	= pDBColumnInfo[dwIndex + 1].bPrecision;
		prgBinding[dwIndex].bScale		= pDBColumnInfo[dwIndex + 1].bScale;
		prgBinding[dwIndex].dwPart		= DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
		prgBinding[dwIndex].obLength	= dwOffset;                                     
		prgBinding[dwIndex].obStatus	= prgBinding[dwIndex].obLength + sizeof(ULONG);  
		prgBinding[dwIndex].obValue		= prgBinding[dwIndex].obStatus + sizeof(DBSTATUS);

		switch(pDBColumnInfo[dwIndex + 1].wType)
		{
		case DBTYPE_BYTES:
			// Set up the DBOBJECT structure.
			//
			dbObject.dwFlags = STGM_WRITE;
			dbObject.iid = IID_ISequentialStream;

			prgBinding[dwIndex].pObject		= &dbObject;
			prgBinding[dwIndex].cbMaxLen	= sizeof(IUnknown*);
			prgBinding[dwIndex].wType		= DBTYPE_IUNKNOWN;
			break;

		case DBTYPE_WSTR:
			prgBinding[dwIndex].pObject		= NULL;
			prgBinding[dwIndex].wType		= pDBColumnInfo[dwIndex + 1].wType;
			prgBinding[dwIndex].cbMaxLen	= sizeof(WCHAR)*(pDBColumnInfo[dwIndex + 1].ulColumnSize + 1);	// Extra buffer for null terminator 
			break;

		default:
			prgBinding[dwIndex].pObject		= NULL;
			prgBinding[dwIndex].wType		= pDBColumnInfo[dwIndex + 1].wType;
			prgBinding[dwIndex].cbMaxLen	= pDBColumnInfo[dwIndex + 1].ulColumnSize; 
			break;
		}

		// Calculate new offset
		// 
		dwOffset = prgBinding[dwIndex].obValue + prgBinding[dwIndex].cbMaxLen;

		// Properly align the offset
		//
		dwOffset = ROUND_UP(dwOffset, COLUMN_ALIGNVAL);
	}

	// Get IAccessor interface for getdata
	//
	hr = pIRowset->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
	if(FAILED(hr))
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

    // Create accessor.for getdata
	//
    hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 
									dwBindingSize, 
									prgBinding,
									0,
									&hAccessor,
									NULL);
    if(FAILED(hr))
    {
 		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
       goto Exit;
    }
    // Create accessor.for autokey insert
	//
    hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 
									dwBindingSize-1, 
									prgBinding+1,
									0,
									&hAccessor1,
									NULL);
    if(FAILED(hr))
    {
 		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
       goto Exit;
    }

	// Allocate data buffer for seek and retrieve operation.
	//
	pData = (BYTE*)CoTaskMemAlloc(dwOffset);
	if (NULL == pData)
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		hr = E_OUTOFMEMORY;
		goto Exit;
	}

	// Begins a new local transaction
	//
	hr = pITxnLocal->StartTransaction(ISOLATIONLEVEL_READCOMMITTED | ISOLATIONLEVEL_CURSORSTABILITY, 0, NULL, NULL);
	if(FAILED(hr))
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

	// Insert sample data
	//
//	for (dwRow = 0; dwRow < sizeof(g_SampleEmployeeData)/sizeof(g_SampleEmployeeData[0]); ++dwRow)
	{

		// Set data buffer to zero
		//
		memset(pData, 0, dwOffset);
		for (dwCol = 0; dwCol < dwBindingSize; ++dwCol)
		{
			// Get column value in string
			//

			switch(prgBinding[dwCol].wType)
			{
				case DBTYPE_WSTR:
					// Copy value to binding buffer, truncate the string if it is too long
					//
					
				//	*(pRowStr+prgBinding[dwCol].cbMaxLen/sizeof(WCHAR) - 1)=0;
				//	wsprintf((WCHAR*)(pData+prgBinding[dwCol].obValue),L"%ls",pVoid);
					MultiByteToWideChar(CP_ACP,MB_PRECOMPOSED,pRowStr,(pBusStr->pBusFields +dwCol)->Length-1,(WCHAR*)(pData+prgBinding[dwCol].obValue),
						prgBinding[dwCol].cbMaxLen/sizeof(WCHAR));
					*(ULONG*)(pData+prgBinding[dwCol].obLength)		= wcslen((WCHAR*)(pData+prgBinding[dwCol].obValue))*sizeof(WCHAR);//prgBinding[dwCol].cbMaxLen;
					*(DBSTATUS*)(pData+prgBinding[dwCol].obStatus)	= DBSTATUS_S_OK;
					break;

				case DBTYPE_IUNKNOWN:
					ASSERT(0);
					break;

				case DBTYPE_I4:
			//		if(dwCol == pBusStr->KeyIndex1)
			//		{
			//			*(int*)(pData+prgBinding[dwCol].obValue)		= -1;//_wtoi(g_SampleEmployeeData[dwRow].wszEmployeeInfo[dwCol]);
			//		}else
			//			*((int*)(pData+prgBinding[dwCol].obValue))		= *((int *)pRowStr);//_wtoi(g_SampleEmployeeData[dwRow].wszEmployeeInfo[dwCol]);
			//		*(ULONG*)(pData+prgBinding[dwCol].obLength)		= 4;
			//		*(DBSTATUS*)(pData+prgBinding[dwCol].obStatus)	= DBSTATUS_S_OK;
			//		break;

				default:
					memcpy(pData+prgBinding[dwCol].obValue,pRowStr,(pBusStr->pBusFields + dwCol)->Length);
				//	*(int*)(pData+prgBinding[dwCol].obValue)		= *((int *)pVoid);//_wtoi(g_SampleEmployeeData[dwRow].wszEmployeeInfo[dwCol]);
					*(ULONG*)(pData+prgBinding[dwCol].obLength)		= prgBinding[dwCol].cbMaxLen;
					*(DBSTATUS*)(pData+prgBinding[dwCol].obStatus)	= DBSTATUS_S_OK;
					
					break;
			}
			pRowStr +=(pBusStr->pBusFields + dwCol)->Length;//prgBinding[dwCol].cbMaxLen;
		}

		// Insert data to database
		//
		if(bAutoKey)
			hr = pIRowsetChange->InsertRow(DB_NULL_HCHAPTER, hAccessor1, pData, prghRows);
		else 
			hr = pIRowsetChange->InsertRow(DB_NULL_HCHAPTER, hAccessor, pData, prghRows);
		if (FAILED(hr))
		{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
			goto Abort;
		}

		// Get the row data
		//
		hr = pIRowset->GetData(rghRows[0], hAccessor, pData);
        if(FAILED(hr))
        {
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
			goto Abort;
        }
		nID=*(int*)(pData+prgBinding[0].obValue);
        // Check the status
        //
/*        if (DBSTATUS_S_OK != *(DBSTATUS*)(pData+prgBinding[dwPhotoCol].obStatus))
        {
            hr = E_FAIL;
			goto Abort;
        }

		// Insert photo into database through ISequentialStream
		//
		pISequentialStream = (*(ISequentialStream**) (pData + prgBinding[dwPhotoCol].obValue));
		if (pISequentialStream)
		{
			// Insert photo
			//
			hr = SaveEmployeePhoto(pISequentialStream, g_SampleEmployeeData[dwRow].dwEmployeePhoto);
			if(FAILED(hr))
			{
				goto Abort;
			}

			// Release ISequentialStream interface
			//
			hr = pISequentialStream->Release();
			if(FAILED(hr))
			{
				pISequentialStream = NULL;
				goto Abort;
			}

			pISequentialStream = NULL;
		}
*/
        // Release the rowset
		//
		hr = pIRowset->ReleaseRows(1, prghRows, NULL, NULL, NULL);
        if(FAILED(hr))
        {
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
			goto Abort;
        }

        prghRows[0] = DB_NULL_HROW;
	}

	// Commit the transaction
	//
	if (pITxnLocal)
	{
		pITxnLocal->Commit(FALSE, XACTTC_SYNC, 0);
	}

	goto Exit;

Abort:
    if (DB_NULL_HROW != prghRows[0])
    {
        pIRowset->ReleaseRows(1, prghRows, NULL, NULL, NULL);
    }

	// Abort the transaction
	//
	if (pITxnLocal)
	{
		pITxnLocal->Abort(NULL, FALSE, FALSE);
	}

Exit:
    // Clear Variants
    //
	VariantClear(&rowsetprop[0].vValue);

    // Free allocated DBBinding memory
    //
    if (prgBinding)
    {
        CoTaskMemFree(prgBinding);
        prgBinding = NULL;
    }

    // Free allocated column info memory
    //
    if (pDBColumnInfo)
    {
        CoTaskMemFree(pDBColumnInfo);
        pDBColumnInfo = NULL;
    }
	
	// Free allocated column string values buffer
    //
    if (pStringsBuffer)
    {
        CoTaskMemFree(pStringsBuffer);
        pStringsBuffer = NULL;
    }

    // Free data record buffer
    //
	if (pData)
	{
        CoTaskMemFree(pData);
		pData = NULL;
	}

	// Release interfaces
	//
    if(pISequentialStream)
    {
		pISequentialStream->Release();
    }

	if(pIAccessor)
	{
		pIAccessor->ReleaseAccessor(hAccessor, NULL); 
		pIAccessor->ReleaseAccessor(hAccessor1, NULL); 
		pIAccessor->Release();
	}

	if (pIColumnsInfo)
	{
		pIColumnsInfo->Release();
	}

	if (pIRowsetChange)
	{
		pIRowsetChange->Release();
	}

	if (pITxnLocal)
	{
		pITxnLocal->Release();
	}

	if(pIRowset)
	{
		pIRowset->Release();
	}

	if(pIOpenRowset)
	{
		pIOpenRowset->Release();
	}
	if(nID >0)
		return nID;
	else
	{
        if(FAILED(hr))
        {
			
			return ERR_BUS_SQLEXE;
        }
		ASSERT(0);
		return ERR_BUS_SQLEXE;
	}