database.cpp

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

}
/*
功能：根据指定的主键更新一条记录
参数：
pBusTableStruct pBusStr－－修改的业务表的结构
char *pRowStr －－要修改的记录的结构，所有的字段均要有
返回：int 
	< 0 －－错误代码
	0 －－修改成功
编写人：吕黄梁
时间：2004-12-23
*/
int CDataBase::Update(pBusTableStruct pBusStr,char *pRowStr)
{
	WCHAR SqlBuf[1000]=L"",*pSql,SqlWhere[100]=L"";
	pSql=SqlBuf;
	WCHAR sTemp[500]=L"",sTemp1[500]=L"";
	swprintf(SqlBuf,L"update %s set ",pBusStr->Name);
	char *p;
	int nVal;
	short shVal;
	pBusFieldStruct pField;
	p=pRowStr;
	BOOL bFirst=TRUE;
	pField=pBusStr->pBusFields;
	for(int i=0;i<pBusStr->Num;i++,p+=pField->Length,pField++)
	{
		if(i == pBusStr->KeyIndex1 )
		{
			ASSERT(pField->Type == DATA_INT);
			memcpy(&nVal,p,sizeof(int));
			swprintf(SqlWhere,L" where %s = %d",pField->NAME,nVal);
		//	p+=(pBusStr->pBusFields +i)->Length;
		//	if(wcscmp(pField->NAME,L"CCID") !=0)//中控默认可以更改主键
				continue;
		}else if( i== pBusStr->KeyIndex2)
		{
			ASSERT(pField->Type == DATA_INT);
			memcpy(&nVal,p,sizeof(int));
			swprintf(sTemp,L" and %s = %d",pField->NAME,nVal);
			wcscat(SqlWhere,sTemp);
		//	p+=(pBusStr->pBusFields +i)->Length;
			continue;
		}

		switch(pField->Type)
		{
		case DATA_INT:
			memcpy(&nVal,p,sizeof(int));
			wcscpy(sTemp,pField->NAME);
			swprintf(sTemp1,L" = %d",nVal);
			wcscat(sTemp,sTemp1);
//			swprintf(sTemp1,L"%s = %d",pField->NAME,nVal);
			break;
		case DATA_SMALLINT:
			memcpy(&shVal,p,sizeof(short));
			swprintf(sTemp,L"%s = %d",pField->NAME,shVal);
			break;
		case DATA_TINYINT:
			swprintf(sTemp,L"%s = %d",pField->NAME,*(BYTE*)p);
			break;
		case DATA_NVARCHAR:
			MultiByteToWideChar(CP_ACP,MB_PRECOMPOSED,p,pField->Length-1,sTemp1,pField->Length);
			ReplaceYinhao(sTemp1);
		//	wcscpy(sTemp,L" = '");
		//	wcscpy(sTemp,pField->NAME);
		//	wcscat(sTemp,L" = '");
		//	wcscat(sTemp,sTemp1);
		//	wcscat(sTemp,L"'");
			swprintf(sTemp,L"%s = '%s'",pField->NAME,sTemp1);
			memset(sTemp1,0,300);
			break;
		default:
			ASSERT(0);
			break;
		}	
		if(!bFirst)
		{
			wcscat(SqlBuf,_T(","));
		}else
			bFirst = FALSE;
		wcscat(SqlBuf,sTemp);
	//	p+=(pBusStr->pBusFields +i)->Length;
	}
		wcscat(SqlBuf,SqlWhere);
		wcscat(SqlBuf,L";");
//		TRACE(L"%s",SqlBuf);
//		writebuginfo(SqlBuf);
		if(ExecuteSql(SqlBuf))
			return ERR_BUS_SUCCESS;
		else
			return ERR_BUS_SQLEXE;
}
/*
功能：根据指定的业务表及查询条件取记录
参数：
pBusTableStruct pBusStr－－查询的业务表的结构
WCHAR *wcWhere －－要查询的条件
RetResultStruct **ppResult－－要返回的查询记录指针的指针
返回：int 
	< 0 －－错误代码
	
	  >=0－－查询结果记录个数
编写人：吕黄梁
时间：2004-12-23
*/
int CDataBase::GetRows(pBusTableStruct pBusStr,WCHAR *wcWhere,RetResultStruct **ppResult)
{
	HRESULT     hr            = NOERROR;  
	DBBINDING			*prgBinding			= NULL;				// Binding used to create accessor
	HROW				rghRows[1];								// Array of row handles obtained from the rowset object
	HROW				*prghRows			= rghRows;			// Row handle(s) pointer
//	DBID				TableID;								// Used to open/create table
//	DBPROPSET   dbpropset[1]; 
//	DBPROP      dbprop[1]; 
	DBPROPSET   rowsetpropset[1]; 
	DBPROP      rowsetprop[2]; 
//	ULONG       cbRecordCount;
	DBCOLUMNINFO		*pDBColumnInfo		= NULL;				// Record column metadata
	BYTE				*pData				= NULL;				// record data
	WCHAR				*pStringsBuffer		= NULL;
	DWORD				dwBindingSize		= 0;
	DWORD				dwIndex				= 0;
	DWORD				dwOffset			= 0;
//	DWORD				dwOrdinal			= 0;
	ULONG				ulNumCols;
	WCHAR				wcTemp[CC_MAX_FIELDS_LEN];
	int nCount=0,i=0,index=0;
	int nSizeStr=0;
	char *pRowStr;
	RetResultStruct *pCurResult,*pNextResult;
	// Provider interfaces
//	IRowsetPosition *    pIRowsetPos       = NULL;
//	ICommandProperties * pICmdProps        = NULL;
	IDBCreateCommand *   pIDBCrtCmd        = NULL;
	ICommandText *       pICmdText         = NULL;
	IRowset *            pIRowset          = NULL;
	IAccessor			*pIAccessor			= NULL;				// Provider Interface Pointer
//	ILockBytes			*pILockBytes		= NULL;				// Provider Interface Pointer
	IColumnsInfo		*pIColumnsInfo		= NULL;				// Provider Interface Pointer
	HACCESSOR			hAccessor			= DB_NULL_HACCESSOR;// Accessor handle

	// Initialize the environment.
	if (NULL == m_pIDBCreateSession)
	{
		ASSERT(0);
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		return ERR_PROG_NOTOPEN;//编程错误
	}
	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;
	}
	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;
	}

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

	rowsetprop[0].dwPropertyID   = DBPROP_CANFETCHBACKWARDS;
	rowsetprop[0].dwOptions      = DBPROPOPTIONS_REQUIRED;
	rowsetprop[0].vValue.vt      = VT_BOOL;
	rowsetprop[0].vValue.boolVal = VARIANT_TRUE;

	// Set the query text for the command.
	WCHAR SqlBuf[1024];
	swprintf(SqlBuf,L"select * from %s %s;",pBusStr->Name,wcWhere);
//	writebuginfo(SqlBuf);
	hr = pICmdText->SetCommandText(DBGUID_SQL, SqlBuf); 
	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->QueryInterface(IID_ICommandProperties, (void**) &pICmdProps);
	if (FAILED(hr))
	{
		//Send an error-specific message and do error handling.
		goto Exit;
	}
	hr = pICmdProps->SetProperties(1, rowsetpropset);
	if (FAILED(hr))
	{
		//Send an error-specific message and do error handling.
		goto Exit;
	}
*/
	// Creates an IRowsetPosition object for the returned rowset.

	hr = pICmdText->Execute(NULL, IID_IRowset, NULL, NULL,
		(IUnknown**)&pIRowset);
	if (FAILED(hr))
	{
		//Send an error-specific message and do error handling.
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	} // Get a count of the number or rows returned in the rowset.

    // 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.
	//
	dwBindingSize = pBusStr->Num;//sizeof(pwszEmployees)/sizeof(pwszEmployees[0]);
	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)
    {
	/*	if (!GetColumnOrdinal(pDBColumnInfo, ulNumCols, pwszEmployees[dwIndex], &dwOrdinal))
		{
			hr = E_FAIL;
			goto Exit;
		}
	*/
		// Prepare structures to create the accessor
		//
		prgBinding[dwIndex].iOrdinal	= pDBColumnInfo[dwIndex].iOrdinal;
		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);
		prgBinding[dwIndex].pTypeInfo	= NULL;
		prgBinding[dwIndex].pBindExt	= NULL;
		prgBinding[dwIndex].dwMemOwner	= DBMEMOWNER_CLIENTOWNED;
		prgBinding[dwIndex].dwFlags		= 0;
		prgBinding[dwIndex].bPrecision	= pDBColumnInfo[dwIndex].bPrecision;
		prgBinding[dwIndex].bScale		= pDBColumnInfo[dwIndex].bScale;

		switch(pDBColumnInfo[dwIndex].wType)
		{
		case DBTYPE_BYTES:		// Column "Photo" binding (BLOB) 
			// Set up the DBOBJECT structure.
			//
			ASSERT(0);
			break;

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

		default:
			prgBinding[dwIndex].pObject		= NULL;
			prgBinding[dwIndex].wType		= pDBColumnInfo[dwIndex].wType;
			prgBinding[dwIndex].cbMaxLen	= pDBColumnInfo[dwIndex].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
	//
	hr = pIRowset->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
	if(FAILED(hr))
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

    // Create accessor.
	//
    hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 
									dwBindingSize, 
									prgBinding,
									0,
									&hAccessor,
									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)
	{
		hr = E_OUTOFMEMORY;
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;
	}

    // Set data buffer to zero
    //
    memset(pData, 0, dwOffset);
	// 取数据
    // Retrieve a row handle for the row resulting from the seek
    //计算结构长度
	pBusFieldStruct pField;
	pField=pBusStr->pBusFields;
	for(i=0;i<pBusStr->Num;i++,pField++)
	{
		nSizeStr+=pField->Length;
	}
	ULONG cRowsObtained;
    hr = pIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &prghRows);
	if(FAILED(hr))
	{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		goto Exit;	
	}
	while(DB_S_ENDOFROWSET != hr)
	{
		hr = pIRowset->GetData(rghRows[0], hAccessor, pData);
		if (FAILED(hr))
		{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
			goto Exit;
		}
		pNextResult =(RetResultStruct*)malloc(sizeof(RetResultStruct));//new RetResultStruct;
		memset(pNextResult,0,sizeof(RetResultStruct));
		pNextResult->nSize=nSizeStr;
		pNextResult->pBuf =(BYTE*)malloc(nSizeStr); //new char(nSizeStr);
		memset(pNextResult->pBuf,0,nSizeStr);
		if(nCount==0)
		{
			*ppResult = pNextResult;
			pCurResult=pNextResult;
		}else
		{
			pCurResult->pNext=pNextResult;
			pCurResult=pNextResult;
		}
		pField=pBusStr->pBusFields;
		pRowStr=(char*)pCurResult->pBuf;
#ifdef DEBUG
//		room_info_struct *pRoom=(room_info_struct*)pCurResult->pBuf;
#endif
		for(index=0;index<pBusStr->Num;index++,pRowStr+=pField->Length,pField++)
		{//取每个字段的值
			switch(pDBColumnInfo[index].wType)
			{
			case DBTYPE_BYTES:		// Column "Photo" binding (BLOB) 
				ASSERT(0);

			case DBTYPE_WSTR:
				memcpy(wcTemp,pData+prgBinding[index].obValue,prgBinding[index].cbMaxLen-2);
				memset(wcTemp+prgBinding[index].cbMaxLen-2,0,2);
				WideCharToMultiByte(CP_ACP,WC_COMPOSITECHECK,wcTemp,-1,pRowStr,pField->Length,NULL,NULL);
				break;
			default:
				memcpy(pRowStr,pData+prgBinding[index].obValue,prgBinding[index].cbMaxLen);
				break;
			}
		}


		nCount ++;
		hr=pIRowset->ReleaseRows(1,prghRows,NULL,NULL,NULL);
		{
	/*		if(hr ==S_OK )
			{
				;
			}else if(hr ==DB_S_ERRORSOCCURRED)
			{
				MessageBox(NULL,L"",L"数据库错误",MB_OK);
			}else if(hr ==E_FAIL)
			{
				MessageBox(NULL,L"",L"数据库错误",MB_OK);
			}else if(hr ==E_INVALIDARG)
			{
				MessageBox(NULL,L"",L"数据库错误",MB_OK);
			}else if(hr ==E_UNEXPECTED)
			{
				MessageBox(NULL,L"",L"数据库错误",MB_OK);
			}else if(hr ==DB_E_ERRORSOCCURRED)
			{
				MessageBox(NULL,L"",L"数据库错误",MB_OK);
			}else
			{
				MessageBox(NULL,L"unknown",L"数据库错误",MB_OK);
			}
		*/
		}
		if (prghRows)
		{
		 //  CoTaskMemFree(prghRows);
		//   prghRows = NULL;
		}		

		hr = pIRowset->GetNextRows(DB_NULL_HCHAPTER, 0/*nCount*/, 1, &cRowsObtained, &prghRows);
		if (FAILED(hr))
		{
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
			goto Exit;
		}

	    memset(pData, 0, dwOffset);

	}
Exit: 

	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;
    }
	
    if (pStringsBuffer)
    {
        CoTaskMemFree(pStringsBuffer);
        pStringsBuffer = NULL;
    }

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

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

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

//	pIRowset->ReleaseRows(1,rghRows,NULL,NULL,NULL);
	if (prghRows)
	{
//	   CoTaskMemFree(prghRows);
//	   prghRows = NULL;
	}		
//	if(pICmdProps)
	{
//		pICmdProps->Release();
	}

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

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


	if(FAILED(hr))
	{
		return ERR_BUS_SQLEXE;
	}else
		return nCount;
}
int CDataBase::CountSql(WCHAR *pSql)
{
	HRESULT     hr            = NOERROR;  
//	DBPROPSET   dbpropset[1]; 
//	DBPROP      dbprop[1]; 
	DBPROPSET   rowsetpropset[1]; 
	DBPROP      rowsetprop[2]; 
	ULONG       cbRecordCount;

	// Provider interfaces
	IRowsetPosition *    pIRowsetPos       = NULL;
	ICommandProperties * pICmdProps        = NULL;
	IDBCreateCommand *   pIDBCrtCmd        = NULL;
	ICommandText *       pICmdText         = NULL;
	IRowset *            pIRowset          = NULL;

	// Initialize the environment.
	if (NULL == m_pIDBCreateSession)
	{
		ASSERT(0);
		writebuginfo("file:%s,line:%d\r\n",__FILE__,__LINE__);
		return ERR_PROG_NOTOPEN;//编程错误
	}
	hr = m_pIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
		(IUnknown**) &pIDBCrtCmd);
	if (FAILED(hr))
	{