lobexampledlg.cpp

oracle 的 blob大字段类型操作例子

	{
		MessageBox("申请会话句柄失败!");
		Error_proc(errhp,status);
		return ;		
	}
	
	//申请语句句柄
	if(status=OCIHandleAlloc((dvoid*)envhp,(dvoid**)&stmthp,
		(ub4)OCI_HTYPE_STMT,(size_t)0,(dvoid**)0))
	{
		MessageBox("申请语句句柄失败!");
		Error_proc(errhp,status);
		return ;		
	}
	
	//申请LOB定位符
	if(status=OCIDescriptorAlloc((dvoid*)envhp,(dvoid**)&lob_loc,(ub4)OCI_DTYPE_LOB,
		(size_t)0,(dvoid**)0))
	{
		MessageBox("申请LOB定位符失败!");
		Error_proc(errhp,status);
		return ;		
	}
	
	//连接数据库
	if(status=OCIServerAttach(srvhp,errhp,(text*)"text",strlen("text"),OCI_DEFAULT))
	{
		MessageBox("连接数据库失败!");
		Error_proc(errhp,status);
		return ;		
	}

	//	设置服务环境的服务器属性	
	if(status= OCIAttrSet ((dvoid*)svchp, (ub4) OCI_HTYPE_SVCCTX, 
		(dvoid*)srvhp, (ub4) 0,OCI_ATTR_SERVER, errhp))
	{
		MessageBox("设置服务环境的服务器属性失败!");
		Error_proc(errhp,status);
		return ;		
	}
	
	//设置会话所使用的用户帐号
	if(status= OCIAttrSet((dvoid*)authp,OCI_HTYPE_SESSION,
		"system",(ub4)strlen("system"),OCI_ATTR_USERNAME,errhp))
	{
		MessageBox("设置会话所使用的用户帐号失败!");
		Error_proc(errhp,status);
		return ;		
	}	
	if(status= OCIAttrSet((dvoid*)authp,OCI_HTYPE_SESSION,
		"lxk",(ub4)strlen("lxk"),OCI_ATTR_PASSWORD,errhp))
	{
		MessageBox("设置会话所使用的用户帐号失败!");
		Error_proc(errhp,status);
		return ;		
	}
	if(status= OCISessionBegin(svchp,errhp,authp,OCI_CRED_RDBMS,OCI_DEFAULT))
	{
		MessageBox("设置会话所使用的用户帐号失败!");
		Error_proc(errhp,status);
		return ;		
	}
	
	//设置服务器环境的会话属性
	if(status= OCIAttrSet(svchp, OCI_HTYPE_SVCCTX,(dvoid*)authp, 0, 
		OCI_ATTR_SESSION, errhp))
	{
		MessageBox("设置服务器环境的会话属性失败!");
		Error_proc(errhp,status);
		return ;		
	}
*/
	wsprintf ((char*)filename, "c:\\2-4.img") ;
	fp=fopen((const char *)filename,(const char *)"rb");
	if(!fp)
	{
		tt.Format("无法打开%s文件,请重新输入文件名!",filename);
		MessageBox(tt);
	}
		
	//long ftell( FILE *stream );得到文件指针当前所指向的位置
	fseek(fp,0,SEEK_END);//将文件指针移到文件尾,读取文件长度
	tt.Format("n%s文件长度:%ld,读写所用的块长度:%d",filename,(filelen=ftell(fp)),MAXBUFLEN);
	MessageBox(tt);
	
	idcol=1;	//检索定位符	

	//准备SQL语句
	if(status= OCIStmtPrepare(stmthp,errhp,selectlocator,
		(ub4)strlen((char*)selectlocator),(ub4)OCI_NTV_SYNTAX,(ub4)OCI_DEFAULT))
	{
		MessageBox("准备SQL语句失败!");
		Error_proc(errhp,status);
		return ;		
	}
	
	//按位置绑定
	if(status= OCIBindByPos(stmthp,&bndhp,errhp,(ub4)1,(dvoid*)&idcol,
		(sb4)sizeof(idcol),SQLT_INT,(dvoid*)0,(ub2*)0,(ub2*)0,(ub4)0,(ub4*)0,(ub4)OCI_DEFAULT))
	{
		MessageBox("按位置绑定失败!");
		Error_proc(errhp,status);
		return ;		
	}
	
	//按位置定义
	if(status= OCIDefineByPos(stmthp,&defhp,errhp,(ub4)1,(dvoid*)&lob_loc,
		(sb4)-1,(ub2)SQLT_BLOB,(dvoid*)0,(ub2*)0,(ub2*)0,(ub4)OCI_DEFAULT))
	{
		MessageBox("按位置定义失败!");
		Error_proc(errhp,status);
		return ;		
	}
	

	//执行SQL语句
	if(status= OCIStmtExecute(svchp,stmthp,errhp,(ub4)1,(ub4)0,
		(CONST OCISnapshot*)0,(OCISnapshot*)0,(ub4)OCI_DEFAULT))
	
	{
		MessageBox("执行SQL语句失败!");
		Error_proc(errhp,status);
		return ;		
	}

//	OnButton1();
//	return;

	if(CallbackWriteToLob()) //回调函数方式写入LOB
		callback_error=1;
/*	
//	idcol=2;
	if(OCIStmtExecute(svchp,stmthp,errhp,(ub4)1,(ub4)0,(CONST OCISnapshot*)0,
		(OCISnapshot*)0,(ub4)OCI_DEFAULT))
		logoff();
	
	if(PollingWriteToLob())  //查询方式写入LOB
		polling_error=1;

	fp1=fopen("c:\\732.tif","wb"); //以查询方式将数据库表中LOB列的数据写入到外部新创建的文件中
	if(fp1 && !polling_error)
	{
		PollingReadLob();
		fclose(fp1);
	}
	
/*
	fp2=fopen("c:\\Calling.dwg","wb");
	if(fp2 && !callback_error)
	{
		CallbackReadLob();
		fclose(fp1);
	} 
	
	logoff();
	*/
}

//错误处理函数
void CLOBExampleDlg::Error_proc(dvoid *errhp, sword status)
{
	text errbuf[512];
	sb4 errcode;
	CString errinfor;
	
	switch(status)
	{
		case OCI_SUCCESS:
			break;
		case OCI_SUCCESS_WITH_INFO:
			MessageBox("OCI 错误代码: OCI_SUCCESS_WITH_INFO");
			break;
		case OCI_NEED_DATA:
			MessageBox("OCI 错误代码: OCI_NEED_DATA");
			break;
		case OCI_NO_DATA:
			MessageBox("OCI 错误代码: OCI_NO_DATA");
			break;
		case OCI_ERROR:
			OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, 
				&errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
			errinfor.Format ("OCI 错误号: %d\n错误信息:%s",errcode,errbuf);
			MessageBox(errinfor);
			break;
		case OCI_INVALID_HANDLE:
			MessageBox("OCI 错误代码: OCI_INVALID_HANDL");
			break;
		case OCI_STILL_EXECUTING:
			MessageBox("OCI 错误代码: OCI_STILL_EXECUTING");
			break;
		default:
			break;
	}
}

//回调函数方式LOB写入函数
int CLOBExampleDlg::CallbackWriteToLob()
{
	ub4 offset=1;loblen=0;
	ub4 bufp[MAXBUFLEN];
	
	ub4 amtp,piecelen;
	ub1 *piece=0;
	ub4 *lenp=0;

	amtp=filelen;
	CString tempstr;
	
	OCILobGetLength(svchp,errhp,lob_loc,&loblen);
	printf("\n/回调函数方式写入前LOB数据长度:%ld",loblen);
	
	fseek(fp,0,SEEK_SET);//将数据文件指针移到文件头
    if(filelen>MAXBUFLEN)
		piecelen=MAXBUFLEN;  //每次写入的数据长度
	else
		piecelen=filelen; //每次写入的数据长度
	
	if(fread((dvoid*)bufp,(size_t)piecelen,1,fp)!=1)
	{
		MessageBox("读取文件发生错误!");
		return -1;
	}
	if(filelen<MAXBUFLEN)   //只有一块数据需要写入
	{		
		if(status=OCILobWrite(svchp,errhp,lob_loc,&amtp,offset,	(dvoid*)bufp,(ub4)piecelen,
			OCI_ONE_PIECE,(dvoid*)0,(sb4(*)(dvoid*,dvoid*,ub4*,ub1*))0,(ub2)0,
			(ub1)SQLCS_IMPLICIT)!=OCI_SUCCESS)
		{
			Error_proc(errhp,status);
			return -1;
		}
		else
			m_progess.SetPos(100);
	}
	else   //有多块数据需要写入
	{
		if(status=OCILobWrite(svchp,errhp,lob_loc,&amtp,offset,(dvoid*)bufp,
			(ub4)piecelen,OCI_FIRST_PIECE,(dvoid*)0,callball_write,(ub2)0,SQLCS_IMPLICIT))
		{
			Error_proc(errhp,status);
			return -1;			
		}
	}
	
    OCILobGetLength(svchp,errhp,lob_loc,&loblen);
	CString tt;
	tt.Format("以回调函数方式写入后LOB数据长度=%ld\n",loblen);
	MessageBox(tt);
	if (status  =OCITransCommit(svchp, errhp, (ub4)0))
	{
		Error_proc (errhp, status);
		return -1;
	}
	EndWaitCursor( );
	return 0;

}

//断开与服务器和数据库连接,并释放句柄
void CLOBExampleDlg::logoff()
{
	fclose(fp);
	OCISessionEnd(svchp,errhp,authp,(ub4)0);
	OCIServerDetach(srvhp,errhp,(ub4)OCI_DEFAULT);
	OCIHandleFree((dvoid*)envhp,OCI_HTYPE_ENV);
}

//查询方式LOB数据读取函数
void CLOBExampleDlg::PollingReadLob()
{
	CString tempstr;
	ub4 offset=1,amtp=0;
	ub1 bufp[MAXBUFLEN];
	sword status;
	ub4 piece=0,remainder,len;
	loblen=0;
	//检索LOB数据长度
	OCILobGetLength(svchp,errhp,lob_loc,&loblen);
	amtp=loblen;
	tempstr.Format ("查询方式读取LOB数据长度,其长度=%ld",loblen);
	MessageBox(tempstr);
//	printf("\n查询方式读取LOB数据长度,其长度=%d.",amtp=loblen);
	memset(bufp,'\0',MAXBUFLEN);
	status=OCILobRead(svchp,errhp,lob_loc,&amtp,offset,(dvoid*)bufp,
		(loblen<MAXBUFLEN?loblen:MAXBUFLEN),(dvoid*)0,(sb4(*)(dvoid*,
		const dvoid*,ub4,ub1))0,(ub2)0,(ub1)SQLCS_IMPLICIT);
	switch(status)
	{
	case OCI_SUCCESS: //只有一块数据
		piece++;
		tempstr.Format("查询方式读取%d块数据,长度为:%ld",piece,amtp);
		MessageBox(tempstr);
		//printf("\n查询方式读取%d块数据,长度为:%d",++piece,amtp);
		fwrite((void*)bufp,(size_t)loblen,1,fp1);
		break;
	case OCI_ERROR:
		break;
	case OCI_NEED_DATA:
		remainder=loblen;
		fwrite((void*)bufp,MAXBUFLEN,1,fp1);
		len=amtp;
		do 
		{
			memset(bufp,'\0',MAXBUFLEN);
			amtp=0;
			remainder-=MAXBUFLEN;
			status=OCILobRead(svchp,errhp,lob_loc,&amtp,offset,(dvoid*)bufp,
				(ub4)MAXBUFLEN,(dvoid*)0,0,(ub2)0,(ub1)SQLCS_IMPLICIT);
			if(remainder<MAXBUFLEN)
				fwrite((void*)bufp,(size_t)remainder,1,fp1);
			else
				fwrite((void*)bufp,MAXBUFLEN,1,fp1);
			piece++;
			len+=amtp;
			m_progess.SetPos((int) (loblen-remainder)*1.0/loblen*100.0);
			
		}
		while(status==OCI_NEED_DATA);
		
		printf("\n查询方式读取%d块数据,实际读取长度为:%d",++piece,len);
		
		break;
	default:
		//printf("\nOCILobRead()函数错误,错误代码为%d",status);
		tempstr.Format("OCILobRead()函数错误,错误代码为%d",status);
		MessageBox(tempstr);
		Error_proc(errhp,status);
		break;
	}
	
	return;
	
}

//回调函数方式LOB数据读取函数
void CLOBExampleDlg::CallbackReadLob()
{
	ub4 offset=1,amtp=0;
	ub1 bufp[MAXBUFLEN];
	sword status;
	CString tt;
	loblen=0;
	OCILobGetLength(svchp,errhp,lob_loc,&loblen);
	tt.Format ("回调函数方式读取LOB数据,其长度=%d",amtp=loblen);
	MessageBox(tt);

//	printf("\n回调函数方式读取LOB数据,其长度=%d",amtp=loblen);
	if(status=OCILobRead(svchp,errhp,lob_loc,&amtp,offset,(dvoid*)bufp,
		(ub4)MAXBUFLEN,(dvoid*)0,callback_Read,(ub2)0,(ub1)SQLCS_IMPLICIT))
	{
		//printf("\nOCILobRead()函数错误,错误代码为%d.",status);
		Error_proc(errhp,status);
		
	}
//	printf("实际读取长度为%d",amtp);
	tt.Format ("实际读取长度为%d",amtp);
	MessageBox(tt);
	
}


//浏览外部文件,以用来读取到BLOB列中
void CLOBExampleDlg::OnButtonBrowse() 
{
	this->UpdateData ();
	CFileDialog dlg(TRUE,"打开文件",NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,"ERDAS遥感图像文件(*.img)|*.img|TIF图像文件(*.tif)|*.tif|所有文件(*.*)|*.*||",NULL);
	dlg.m_ofn.lpstrTitle="打开外部文件";
	
	if(dlg.DoModal()==IDCANCEL)
		return;
	
	m_strFilename=dlg.GetPathName();
	
	this->UpdateData (FALSE);
	
	
}

//向数据库表中添加记录,其中一列包含BLOB列,将外部文件读取过来填入到该BLOB列
void CLOBExampleDlg::OnButtonAdd() 
{
	CString tempStr;
	text *cp=NULL,filename[1024];
	
	text textSQL[1024];     
	
    this->UpdateData ();
		
	if(m_strFilename.IsEmpty())
	{
		MessageBox("外部文件不能为空");
		return;
	}

	if(m_ID<0)
	{
		MessageBox("ID号不能小于0");
		return;
	}

	nRecordCounts++;
	wsprintf ((char*)filename, m_strFilename) ;