lobdlg.cpp

C++OCI,针对8.0.4以上的数据库

	BYTE btBuffArr[LOB_MAXBUFFLEN];
	memset(btBuffArr, 0, LOB_MAXBUFFLEN);
	if(1 != fread(btBuffArr, nByteCount, 1, pfLob))
	{
		sprintf(szInfoArr, "无法取出文件：%s中的数据！", pszFile);
		pctrlButt->SetWindowText((LPCTSTR)szInfoArr);
		return 0;
	}

	ub4 nRemainder = (ub4)nFileLen; //剩余量
	nRemainder -= nByteCount;
	
	int rc = 0; //返回码
	ub4 nAmtp = nFileLen;
	ub4 nOffset = 1; //便移量
	int nErrCode = 0;
	char szInfoStrArr[512];
	if(0 == nRemainder)  //表示文件内容的长度小于等于缓冲区的长度
	{
		rc = OCILobWrite(hOCI.phService,
			             hOCI.phErr,
						 pLob,
						 &nAmtp,
						 nOffset,
						 (dvoid *)btBuffArr,
						 nByteCount,
						 OCI_ONE_PIECE,
						 (dvoid *)0,
						 (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
						 (ub2) 0, 
						 (ub1) SQLCS_IMPLICIT);
		if(OCI_SUCCESS != rc)
		{
			m_oOCIBase.GetOCIErrInfo(hOCI.phErr, nErrCode, szInfoStrArr, 256);
			sprintf(szInfoArr, "无法把数据写到LOB中！");
			pctrlButt->SetWindowText((LPCTSTR)szInfoArr);
			fclose(pfLob);
			return 0;
		}

	}
	else //表示文件内容的长度大于缓冲区的长度
	{
		//首先把第一块写入数据库中
		rc = OCILobWrite(hOCI.phService,
			             hOCI.phErr,
						 pLob,
						 &nAmtp,
						 nOffset,
						 (dvoid *)btBuffArr,
						 LOB_MAXBUFFLEN,
						 (ub4)OCI_FIRST_PIECE,
						 (dvoid *)0,
						 (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
						 (ub2) 0, 
						 (ub1) SQLCS_IMPLICIT);
		if(OCI_NEED_DATA != rc)
		{
			m_oOCIBase.GetOCIErrInfo(hOCI.phErr, nErrCode, szInfoStrArr, 256);
			sprintf(szInfoArr, "无法把数据写到LOB中！");
			pctrlButt->SetWindowText((LPCTSTR)szInfoArr);
			fclose(pfLob);
			return 0;
		}

		ub4 nPiece = OCI_NEXT_PIECE;

		//写后续的数据
		do
		{
			nOffset += nByteCount;
			if(nRemainder > LOB_MAXBUFFLEN)
			{
				nByteCount = LOB_MAXBUFFLEN;
			}
			else
			{
				nByteCount = nRemainder;
				nPiece = OCI_LAST_PIECE;
			}

			memset(btBuffArr, 0, LOB_MAXBUFFLEN);
			if(1 != fread(btBuffArr, nByteCount, 1, pfLob))
			{
				nPiece = OCI_LAST_PIECE;
			}

			rc = OCILobWrite(hOCI.phService,
			                 hOCI.phErr,
						     pLob,
						     &nAmtp,
						     nOffset,
						     (dvoid *)btBuffArr,
						     nByteCount,
						     nPiece,
						     (dvoid *)0,
						     (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
						     (ub2) 0, 
						     (ub1) SQLCS_IMPLICIT);
			if(OCI_SUCCESS != rc && OCI_NEED_DATA != rc && OCI_SUCCESS_WITH_INFO != rc)
			{
				m_oOCIBase.GetOCIErrInfo(hOCI.phErr, nErrCode, szInfoStrArr, 256);
				sprintf(szInfoArr, "无法把数据写到LOB中！");
				pctrlButt->SetWindowText((LPCTSTR)szInfoArr);
				fclose(pfLob);
				return 0;
			}

			nRemainder -= nByteCount;
		}while((OCI_NEED_DATA == rc) && !feof(pfLob));
	}

	if(OCI_SUCCESS != rc)
	{
		sprintf(szInfoArr, "无法把数据写到LOB中！");
		pctrlButt->SetWindowText((LPCTSTR)szInfoArr);
		fclose(pfLob);
		return 0;
	}

	//关闭
	fclose(pfLob);
	return 1;
}

//把Lob数据写到指定的文件中
//成功，返回1，否则返回0
int CLOBDlg::ReadLob(char *pszFile, OCIHANDLE hOCI, OCILobLocator *pLob)
{
	CButton *pctrlButt = (CButton *)GetDlgItem(IDC_INFO);
	char szInfoArr[512];

	//打开文件
	FILE *pfLob = fopen(pszFile, "wb");
	if(NULL == pfLob)
	{
		sprintf(szInfoArr, "无法打开文件：%s", pszFile);
		pctrlButt->SetWindowText((LPCTSTR)szInfoArr);
		return 0;
	}
	
	ub4   nOffset = 1; //偏移量
	ub4   nLobLen = 0; //LOB的长度
	ub1   btBuffArr[LOB_MAXBUFFLEN]; //数据缓冲区
	ub4   nAmtp = 0;    //读出数据的长度
	int   rc = 0;
	//ub4   nRemainder = 0;  //剩余的数量
	int nErrCode = 0;
	char szInfoStrArr[512];

	//获得LOB数据的长度
	rc = OCILobGetLength(hOCI.phService, hOCI.phErr, pLob, &nLobLen); 
	nAmtp = nLobLen;
	//ub4 nByteCount = 0;
	//if(nLobLen > LOB_MAXBUFFLEN)
	//	nByteCount = LOB_MAXBUFFLEN;
	//else
	//	nByteCount = nLobLen;

	do
	{
		rc = OCILobRead(hOCI.phService,
			            hOCI.phErr,
						pLob,
                        &nAmtp,
						nOffset,
						btBuffArr,
						LOB_MAXBUFFLEN,
						(dvoid *) 0,
                        (sb4 (*) (dvoid *, CONST dvoid *, ub4, ub1)) 0, 
						(ub2) 0, 
						(ub1) SQLCS_IMPLICIT);
		if ((rc == OCI_SUCCESS) || (rc == OCI_NEED_DATA))
		{
			fwrite(btBuffArr, nAmtp, 1, pfLob);
			nOffset += nAmtp;
		}
		else
		{
			m_oOCIBase.GetOCIErrInfo(hOCI.phErr, nErrCode, szInfoStrArr, 256);
			sprintf(szInfoArr, "Ora-%d, %s", nErrCode, szInfoStrArr);
			pctrlButt->SetWindowText((LPCTSTR)szInfoArr);
		}
	}while((OCI_NEED_DATA == rc) && !feof(pfLob));

	//关闭文件
	fclose(pfLob);
	return 1;
}

void CLOBDlg::OnTest() 
{
	UpdateData(TRUE);

	//struct MyThreadData sThreadData;
	sThreadData.nRecordStart = 0;
	sThreadData.nRecordEnd = 1000000;  //百万
	sThreadData.nTranDot = 20000;      //20000一提交事务 
	sThreadData.pOra = &m_oOCIBase;

    sThreadData.hOCI.bEnvInit = m_oOCIBase.m_hOCI.bEnvInit;
	sThreadData.hOCI.bErrInit = m_oOCIBase.m_hOCI.bErrInit;
	sThreadData.hOCI.bLdaDefInit = m_oOCIBase.m_hOCI.bLdaDefInit;
	sThreadData.hOCI.bSessionInit = m_oOCIBase.m_hOCI.bSessionInit;
	sThreadData.hOCI.bSrvInit = m_oOCIBase.m_hOCI.bSrvInit;
	sThreadData.hOCI.bStmtInit = m_oOCIBase.m_hOCI.bStmtInit;
	sThreadData.hOCI.bSvcInit = m_oOCIBase.m_hOCI.bSvcInit;
	sThreadData.hOCI.phEnv = m_oOCIBase.m_hOCI.phEnv;
	sThreadData.hOCI.phErr = m_oOCIBase.m_hOCI.phErr;
    sThreadData.hOCI.phServer = m_oOCIBase.m_hOCI.phServer;
	sThreadData.hOCI.phService = m_oOCIBase.m_hOCI.phService;
	sThreadData.hOCI.phSession = m_oOCIBase.m_hOCI.phSession;
	sThreadData.hOCI.phStmt = m_oOCIBase.m_hOCI.phStmt;
	sThreadData.hOCI.pLdaDef = m_oOCIBase.m_hOCI.pLdaDef;

	CListCtrl *pList = (CListCtrl *)GetDlgItem(IDC_LIST2);
	sThreadData.hWnd = pList->GetSafeHwnd();
	
	CWinThread *pThread = ::AfxBeginThread(InsertUnNull,(LPVOID)(&sThreadData),THREAD_PRIORITY_BELOW_NORMAL,0,CREATE_SUSPENDED);
	pThread->ResumeThread();
}

UINT InsertUnNull(LPVOID lpParam)
{
	//从参数中获得数据信息
    int nRecordStart = sThreadData.nRecordStart;//((struct MyThreadData *)lpParam)->nRecordStart;
	int nRecordEnd = sThreadData.nRecordEnd;//((struct MyThreadData *)lpParam)->nRecordEnd;
	int nTranPos = sThreadData.nTranDot;//((struct MyThreadData *)lpParam)->nTranDot; //事务处理点 
	HWND hWnd = sThreadData.hWnd;//((struct MyThreadData *)lpParam)->hWnd;
	COraBase *pOra = sThreadData.pOra;//((struct MyThreadData *)lpParam)->pOra; 
	
	OCIHANDLE hOCI;
	hOCI.bEnvInit = sThreadData.hOCI.bEnvInit ;//((struct MyThreadData *)lpParam)->hOCI.bEnvInit;
	hOCI.bErrInit = sThreadData.hOCI.bErrInit  ;//((struct MyThreadData *)lpParam)->hOCI.bErrInit; 
	hOCI.bLdaDefInit = sThreadData.hOCI.bLdaDefInit  ;//((struct MyThreadData *)lpParam)->hOCI.bLdaDefInit;
	hOCI.bSessionInit = sThreadData.hOCI.bSessionInit  ;//((struct MyThreadData *)lpParam)->hOCI.bSessionInit;
	hOCI.bSrvInit = sThreadData.hOCI.bSrvInit  ;//((struct MyThreadData *)lpParam)->hOCI.bSrvInit;
	hOCI.bStmtInit = sThreadData.hOCI.bStmtInit  ;//((struct MyThreadData *)lpParam)->hOCI.bStmtInit;
	hOCI.bSvcInit = sThreadData.hOCI.bSvcInit  ;//((struct MyThreadData *)lpParam)->hOCI.bSvcInit;
	hOCI.phEnv = sThreadData.hOCI.phEnv  ;//((struct MyThreadData *)lpParam)->hOCI.phEnv;
	hOCI.phErr = sThreadData.hOCI.phErr  ;//((struct MyThreadData *)lpParam)->hOCI.phErr;
	hOCI.phServer = sThreadData.hOCI.phServer  ;//((struct MyThreadData *)lpParam)->hOCI.phServer;
	hOCI.phService = sThreadData.hOCI.phService  ;//((struct MyThreadData *)lpParam)->hOCI.phService;
	hOCI.phSession = sThreadData.hOCI.phSession  ;//((struct MyThreadData *)lpParam)->hOCI.phSession;
	hOCI.phStmt = sThreadData.hOCI.phStmt  ;//((struct MyThreadData *)lpParam)->hOCI.phStmt;
	hOCI.pLdaDef = sThreadData.hOCI.pLdaDef ;//((struct MyThreadData *)lpParam)->hOCI.pLdaDef; 

	//从句柄中获得控件的指针
	CListCtrl *pList = (CListCtrl *)CListCtrl::FromHandle(hWnd);

	//清空显示控件
	pList->DeleteAllItems(); 

	CString strSQL;
	CString str;
    int i = 0, j = 0;
	double dAge = 0;
	char szItemArr[30];
	int nErrCode;
	char szInfoStrArr[512], szErrInfoArr[512];
		
	//用于计算时间的变量
	DWORD dwStart = 0;
	DWORD dwEnd = 0;
	int rc = 0;
	//开始计算时间
	dwStart = ::GetTickCount(); //主要是获得毫秒级的计时 

	for(i = nRecordStart; i < nRecordEnd; i++)
	{
		//形成SQL语句
		strSQL = "insert into tab212(num,name,age,sex) values( ";
		str.Format("%d", i);
		strSQL += str;
		strSQL += ",'";
		strSQL += str;
		strSQL += "',";
		dAge = (i %100)/2.5 *2.68;
		if(0.0 == dAge)
			dAge = 1.0;
		str.Format("%f", dAge);
		strSQL += str;
		strSQL += ",'";
		str.Format("%d", i%2);
		strSQL += str;
		strSQL += "')";

		//strSQL.GetBuffer(strSQL.GetLength()) 
		sprintf(szItemArr, "%d", i);
		pList->InsertItem(j, szItemArr);
		pList->SetItemText(j, 1, (LPCTSTR)strSQL.GetBuffer(strSQL.GetLength())); 
		pList->EnsureVisible(j, FALSE);
		strSQL.ReleaseBuffer(); 

		rc = OCIStmtPrepare(hOCI.phStmt,
		                    hOCI.phErr,
						    (text *)(strSQL.GetBuffer(strSQL.GetLength())),
						    (ub4)strlen((char *)(strSQL.GetBuffer(strSQL.GetLength()))),
						    (ub4) OCI_NTV_SYNTAX, 
	  				        (ub4) OCI_DEFAULT);
		if(OCI_SUCCESS != rc)
		{
			//获取错误信息
			pOra->GetOCIErrInfo(hOCI.phErr, nErrCode, szInfoStrArr, 256);
			sprintf(szErrInfoArr,"在取临时表的记录时，无法解释SQL语句，错误号ORA-%d；错误信息为：%s", nErrCode, szInfoStrArr);
			sprintf(szItemArr, "%d", i + 1);
			pList->InsertItem(j + 1, szItemArr);
			pList->SetItemText(j + 1, 1, szErrInfoArr); 
			pList->EnsureVisible(j + 1, FALSE);
			return -1;
		}
		strSQL.ReleaseBuffer(); 

		rc = OCIStmtExecute(hOCI.phService,
			                hOCI.phStmt,
			                hOCI.phErr,
			                (ub4)1,
			                (ub4)0,
			                (CONST OCISnapshot*)0,
			                (OCISnapshot *)0,
			                (ub4)OCI_DEFAULT);
		if(OCI_SUCCESS != rc && OCI_SUCCESS_WITH_INFO != rc )
		{
			//获取错误信息
			pOra->GetOCIErrInfo(hOCI.phErr, nErrCode, szInfoStrArr, 256);
			sprintf(szErrInfoArr,"在取临时表的记录时，无法解释SQL语句，错误号ORA-%d；错误信息为：%s", nErrCode, szInfoStrArr);
			sprintf(szItemArr, "%d", i + 1);
			pList->InsertItem(j + 1, szItemArr);
			pList->SetItemText(j + 1, 1, szErrInfoArr); 
			pList->EnsureVisible(j + 1, FALSE);
			return -1;
		}

		if(0 == (i % nTranPos)) //每20000一提交事务
		{
			rc = OCITransCommit(hOCI.phService, hOCI.phErr, (ub4) 0);
			if(OCI_SUCCESS != rc && OCI_SUCCESS_WITH_INFO != rc )
			{
				//获取错误信息
				pOra->GetOCIErrInfo(hOCI.phErr, nErrCode, szInfoStrArr, 256);
				sprintf(szErrInfoArr,"在取临时表的记录时，无法解释SQL语句，错误号ORA-%d；错误信息为：%s", nErrCode, szInfoStrArr);
				sprintf(szItemArr, "%d", i + 1);
				pList->InsertItem(j + 1, szItemArr);
				pList->SetItemText(j + 1, 1, szErrInfoArr); 
				pList->EnsureVisible(j + 1, FALSE);
				return -1;
			}
		}
		if(0 == i % 3000 && 0 != i)
		{	
			pList->DeleteAllItems();
			j = -1;
		}
		j++;
	}

	rc = OCITransCommit(hOCI.phService, hOCI.phErr, (ub4) 0);
	if(OCI_SUCCESS != rc && OCI_SUCCESS_WITH_INFO != rc )
	{
		//获取错误信息
		pOra->GetOCIErrInfo(hOCI.phErr, nErrCode, szInfoStrArr, 256);
		sprintf(szErrInfoArr,"在取临时表的记录时，无法解释SQL语句，错误号ORA-%d；错误信息为：%s", nErrCode, szInfoStrArr);
		sprintf(szItemArr, "%d", i + 1);
		j = pList->GetItemCount(); 
		pList->InsertItem(j, szItemArr);
		pList->SetItemText(j, 1, szErrInfoArr); 
		pList->EnsureVisible(j, FALSE);
		return -1;
	}

	//插入记录结束
	dwEnd = ::GetTickCount();
	str.Format("线程共向数据库的表TAB212中插入了 %d 条记录,共耗费的时间为: %d 毫秒。", nRecordEnd - nRecordStart, dwEnd - dwStart);
	sprintf(szItemArr, "%d", i);
	j = pList->GetItemCount(); 
	pList->InsertItem(j, szItemArr);
	pList->SetItemText(j, 1, szErrInfoArr); 
	pList->EnsureVisible(j, FALSE);

	return 0;
}