lobexampledlg.cpp

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

	fp=fopen((const char *)filename,(const char *)"rb");
	if(!fp)
	{
		tempStr.Format("无法打开%s文件,请重新输入文件名!",filename);
		MessageBox(tempStr);
	}
	
	//long ftell( FILE *stream );得到文件指针当前所指向的位置
	fseek(fp,0,SEEK_END);//将文件指针移到文件尾,读取文件长度
	filelen=ftell(fp);

	m_grid.SetRows(nRecordCounts+1); 
	tempStr.Format ("%d",m_ID);
	m_grid.SetTextMatrix(nRecordCounts,0,tempStr);
	m_grid.SetTextMatrix(nRecordCounts,1,m_strFilename);
	tempStr.Format ("%ld",filelen);
	m_grid.SetTextMatrix(nRecordCounts,2,tempStr);
	
	wsprintf ((char*)textSQL, "INSERT INTO lobtable2 VALUES(%d,EMPTY_BLOB(),'%s',%ld)",m_ID,m_strFilename,filelen);
	if (status =OCIStmtPrepare(stmthp, errhp,textSQL, strlen((char*) textSQL ),OCI_NTV_SYNTAX,OCI_DEFAULT))
	{
		Error_proc(errhp, status);
		return;
	}
	
	// 代码处理SQL帅第三步，执行
	if (status =OCIStmtExecute(svchp, stmthp, errhp, (ub4 )1,0, NULL, NULL, OCI_DEFAULT))
	{
		Error_proc(errhp, status);
		return;
	}
	
	if (status =OCITransCommit(svchp, errhp, (ub4)0))
	{
		Error_proc(errhp, status);
		return;
	}
	

	//读取外部文件写入BLOB列里.
	BeginWaitCursor(); 

	text *selectlocator=(text*)"SELECT lobcol  FROM lobtable2 WHERE id= :m_ID FOR UPDATE";
	int idcol,callback_error=0,polling_error=0;
    
	

//	wsprintf ((char*)filename, m_strFilename) ;
//	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);//将文件指针移到文件尾,读取文件长度
	tempStr.Format("n%s文件长度:%ld,读写所用的块长度:%d",filename,filelen,MAXBUFLEN);
//	MessageBox(tt);
	
	idcol=m_ID;	//检索定位符	

	//准备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 ;		
	}

	switch(m_style)
	{
		case 0:
			if(PollingWriteToLob())  //查询方式写入LOB
				polling_error=1;
			break;
		case 1:
			if(CallbackWriteToLob()) //回调函数方式写入LOB
				callback_error=1;
			break;			
	}
	
}

//初始化表格
void CLOBExampleDlg::Init_Grid()
{
	m_grid.SetCols(3);
	m_grid.SetColWidth (0,700);
	m_grid.SetColWidth (1,4100);
	m_grid.SetColWidth (2,1600);
	m_grid.SetTextMatrix (0,0,"ID号");
	m_grid.SetTextMatrix (0,1,"外部文件(BFILE)名");
	m_grid.SetTextMatrix (0,2,"文件长度(字节)");
	
}

//初始化OIC
void CLOBExampleDlg::Init_OCI()
{
	//创建OCI环境
	if(status=OCIEnvCreate(&envhp,OCI_DEFAULT,(dvoid *)0, 
		(dvoid* (*)(dvoid*,size_t))0,(dvoid* (*)(dvoid*,dvoid*,size_t))0,
		(void (*)(dvoid *, dvoid *)) 0, (size_t) 0,(dvoid **) 0 ))
	{
		MessageBox("创建OCI环境失败!");
		Error_proc(errhp,status);
		return ;		
	}

	//申请错误句柄
	if(status=OCIHandleAlloc((dvoid*)envhp,(dvoid**)&errhp,(ub4)OCI_HTYPE_ERROR,
		(size_t)0,(dvoid**)0))
	{
		MessageBox("申请错误句柄失败!");
		Error_proc(errhp,status);
		return ;		
	}

	//申请服务器句柄
	if(status=OCIHandleAlloc((dvoid*)envhp,(dvoid**)&srvhp,
		(ub4)OCI_HTYPE_SERVER,(size_t)0,(dvoid**)0))
	{
		MessageBox("申请服务器句柄失败!");
		Error_proc(errhp,status);
		return ;		
	}
	
	//申请服务环境句柄
	if(status=OCIHandleAlloc((dvoid*)envhp,(dvoid**)&svchp,
		(ub4)OCI_HTYPE_SVCCTX,(size_t)0,(dvoid**)0))
	{
		MessageBox("申请服务环境句柄失败!");
		Error_proc(errhp,status);
		return ;		
	}
	
	//申请会话句柄
	if(status=OCIHandleAlloc((dvoid*)envhp,(dvoid**)&authp,
		(ub4)OCI_HTYPE_SESSION,(size_t)0,(dvoid**)0))
	{
		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 ;		
	}

}

//填充记录数据
void CLOBExampleDlg::GetRecordCounts()
{
	_ConnectionPtr m_pConnection;
	_RecordsetPtr  m_pRecordset;
	
	CString strsql,tempstr;
	BSTR  bstrSQL;
	_variant_t TheValue; //VARIANT数据类型
	CString m_servername,m_username,m_password;
	m_servername="text";m_username="system";m_password="lxk";
	//设置连接字符串
	strsql="Provider=OraOLEDB.Oracle;Data Source="+m_servername +";User Id="+m_username +";Password="+m_password +";";
	_bstr_t Sql=strsql;
	m_pConnection.CreateInstance("ADODB.Connection");	//初始化Connection指针  
	m_pConnection->Open(Sql,"","",-1);
	m_pRecordset.CreateInstance(_uuidof(Recordset));	//初始化Recordset指针  
	CString strSql="select * from lobtable2";  
	bstrSQL= strSql.AllocSysString();    
	
	try
	{
		int nn=m_pRecordset->Open(bstrSQL,(IDispatch*)(m_pConnection),adOpenDynamic,adLockOptimistic,adCmdText);    
	}
	
	catch(_com_error& e)	//捕捉异常
	{
		CString errormessage;
		errormessage.Format("连接数据库失败!\r\n错误信息:%s",e.ErrorMessage());
		AfxMessageBox(errormessage);///显示错误信息
		return;
	} 
	int TotalCounts=0;
	while(!m_pRecordset->adoEOF)//遍历所有记录
	{ 
		TotalCounts++;
		m_grid.SetRows (TotalCounts+1);
		TheValue = m_pRecordset->GetCollect("ID"); 
		tempstr.Format ("%d",(long)TheValue);
		m_grid.SetTextMatrix(TotalCounts,0, tempstr);
		
		TheValue = m_pRecordset->GetCollect("fileName"); 
		tempstr=TheValue.bstrVal;
		m_grid.SetTextMatrix(TotalCounts,1, tempstr);
	
		TheValue = m_pRecordset->GetCollect("filelength"); 
		tempstr.Format ("%ld",(long)TheValue);
		m_grid.SetTextMatrix(TotalCounts,2, tempstr);
		m_pRecordset->MoveNext();//转到下一条纪录
	}
	nRecordCounts=TotalCounts;
}

//查询模式
void CLOBExampleDlg::OnRadioPolling() 
{
	m_style=0;	
}

//回调模式
void CLOBExampleDlg::OnRadioCallback() 
{
	m_style	=1;
}

//将BLOB列中的数据输出到外部文件中
void CLOBExampleDlg::OnButtonOut() 
{
	CFileDialog dlg(FALSE,"输出文件",NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,"ERDAS遥感图像文件(*.img)|*.img|TIF图像文件(*.tif)|*.tif|所有文件(*.*)|*.*||",NULL);
	dlg.m_ofn.lpstrTitle="输出到外部文件";
	
	if(dlg.DoModal()==IDCANCEL)
		return;
	
	m_stroutfilename=dlg.GetPathName();
	
	this->UpdateData (FALSE);

    int nRowIndex=m_grid.GetRow();
	if(nRowIndex<1)
	{
		MessageBox("请选择要输出的记录!");
		return;
	}
    m_ID=atoi(m_grid.GetTextMatrix (nRowIndex,0));
	
	text *cp=NULL,filename[1024];
	text *selectlocator=(text*)"SELECT lobcol  FROM lobtable2 WHERE id= :m_ID FOR UPDATE";
	int idcol,callback_error=0,polling_error=0;
    CString tt;

	idcol=m_ID;	//检索定位符	
	
	//准备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 ;		
	}

	//选择写入模式
	switch(m_style)
	{
		case 0:
			fp1=fopen(m_stroutfilename,"wb"); //以查询方式将数据库表中LOB列的数据写入到外部新创建的文件中
			if(fp1 && !polling_error)
			{
				PollingReadLob();
				fclose(fp1);
				MessageBox("输出成功!");
			}
			break;
		case 1:
			fp2=fopen(m_stroutfilename,"wb");//以回调方式将数据库表中LOB列的数据写入到外部新创建的文件中
			if(fp2)
			{
				CallbackReadLob();
				fclose(fp2);
				MessageBox("输出成功!");
			} 
		break;
	}

}