cociexampledlg.cpp

这个是用OCI接口在VC平台上对Oracle 数据库的操作.包括对数据库的连接,数据表的提取,查询,删除,修改等基本功能.这个代码已经编译实现.

	{
		OCIErrorGet((dvoid*)errhp,(ub4)1,NULL,&errcode,errbuf,
			        (ub4)sizeof(errbuf),OCI_HTYPE_ERROR);
		str.Format("error:%d\n error information:%s\n",errcode,errbuf);
		AfxMessageBox(str);
	}
}

void CCOCIExampleDlg::OnBTableselectok() 
{
	// TODO: Add your control notification handler code here
	TableName="";
	int item=m_listTablename.GetCurSel();
	m_listTablename.GetText(item,TableName);
	if(TableName=="")return;

	text textSQL[1024];
	sword status;
	status=wsprintf((char *)textSQL,"SELECT * FROM %s",TableName);
	if(OCI_SUCCESS!=OCIStmtPrepare(stmthp,errhp,textSQL,strlen((char *)textSQL),
		OCI_NTV_SYNTAX,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}
	if(OCI_SUCCESS!=OCIStmtExecute(svchp,stmthp,errhp,(ub4)0,0,NULL,NULL,OCI_DEFAULT))
	{
		MessageBox("SQL语句没有成功执行!");
		ErrorProc(errhp,status);
		return;
	}

	ub4 col_num;
    
	ErrorProc(errhp,OCIAttrGet(stmthp,OCI_HTYPE_STMT,&col_num,0,OCI_ATTR_PARAM_COUNT,errhp));
	ColumnNumbers=(int)col_num;
	text *namep;
	ub4   sizep;
	text tempText[100];

	for(int i=0;i<(int)col_num;i++)
	{
		ErrorProc(errhp,OCIParamGet(stmthp,OCI_HTYPE_STMT,errhp,(void**)&colhp,ub4(i+1)));
		ErrorProc(errhp,OCIAttrGet(colhp,OCI_DTYPE_PARAM,&collen[i],0,OCI_ATTR_DATA_SIZE,errhp));
		ErrorProc(errhp,OCIAttrGet(colhp,OCI_DTYPE_PARAM,&coltype[i],0,OCI_ATTR_DATA_TYPE,errhp));
		if(coltype[i]==SQLT_DAT) collen[i]=30;

		colbuf[i]=(text*)new text[(int)collen[i]+1];
		
		ErrorProc(errhp,OCIAttrGet(colhp,OCI_DTYPE_PARAM,(dvoid*)&namep,(ub4*)&sizep,OCI_ATTR_NAME,errhp));
		strncpy((char*)tempText,(char*)namep,(size_t)sizep);
		tempText[sizep]='\0';
		ColName[i].Format("%s",tempText);
		
	}

	for(i=0;i<(int)col_num;i++)
	{
		if(OCI_SUCCESS!=OCIDefineByPos(stmthp,&defhp[i],errhp,i+1,(ub1*)colbuf[i],
				collen[i]+1,SQLT_STR,&ind[i],0,0,OCI_DEFAULT))
			{
				ErrorProc(errhp,status);
				return;
			}	
	}

		int row=0;	
		
		while(OCI_SUCCESS==OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT))
		{
			for(i=0;i<(int)col_num;i++)
			{
				ColVal[row][i]=colbuf[i];				
			}	
			row++;	            
		}
		
		for(i=0;i<(int)col_num;i++)//problem has been solved!
			delete colbuf[i];
		
		m_listCtrl.ModifyStyle(NULL,LVS_REPORT);
		m_listCtrl.SetExtendedStyle(LVS_EX_FLATSB |LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES);
		m_listCtrl.SetFocus();
		
		int ColNumber=m_listCtrl.GetHeaderCtrl()->GetItemCount();

		for(i=0;i<ColNumber;i++)
			m_listCtrl.DeleteColumn(i);//change the original.(0->i)(my idea is right!)

		for(i=0;i<(int)col_num;i++)
			m_listCtrl.InsertColumn(i,ColName[i],LVCFMT_LEFT,60,0);

		m_listCtrl.DeleteAllItems();
		for(i=0;i<row;i++)
		{
			m_listCtrl.InsertItem(LVIF_TEXT|LVIF_STATE,i,ColVal[i][0],
				(i%2)==0?LVIS_SELECTED:0,LVIS_SELECTED,0,0);
			for(int j=1;j<(int)col_num;j++)
				m_listCtrl.SetItemText(i,j,ColVal[i][j]);
		}                                           //自己把参数写错了,以后遍尝的时候要特别小心!

	for(i=0;i<(int)ColumnNumbers;i++)
		m_comboCtrl.AddString(ColName[i]);
	
	for(i=0;i<(int)col_num;i++)
		m_comConFNameCtrl.AddString(ColName[i]);
	
}

void CCOCIExampleDlg::OnButQuery() 
{
	// TODO: Add your control notification handler code here

    UpdateData(TRUE);
	CString FieldName=m_strFieldName;
	CString strCondition=m_strCondition;
	CString strFieldVal=m_strFieldVal;
	
	sword status;
	text  textSQL[1024];
	wsprintf((char*)textSQL,"SELECT *FROM %s WHERE %s %s :CONDITION",
		TableName,FieldName,strCondition);
	if(status=OCIStmtPrepare(stmthp,errhp,textSQL,
		strlen((char*)textSQL),OCI_NTV_SYNTAX,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}
	
	text *textConVal;
	sb4   len;
	int strlength=strFieldVal.GetLength();
	textConVal=new text[strlength+1];
	wsprintf((char*)textConVal,"%s",strFieldVal);
	textConVal[strlength]='\0';
	len=strlen((const char*)textConVal)+1;

	if(status=OCIBindByName(stmthp,&bidhp[0],errhp,(text*) ":CONDITION",-1,
		(ub1 *)textConVal,len,SQLT_STR,0,0,0,0,0,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}
	if(status=OCIStmtExecute(svchp,stmthp,errhp,(ub4)0,0,NULL,NULL,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}
	ub4 col_num;

	ErrorProc(errhp,OCIAttrGet(stmthp,OCI_HTYPE_STMT,&col_num,0,OCI_ATTR_PARAM_COUNT,errhp));
	for(int i=0;i<(int)col_num;i++)
	{
		ErrorProc(errhp,OCIParamGet(stmthp,OCI_HTYPE_STMT,errhp,
			(void **)&colhp,ub4(i+1)));

		ErrorProc(errhp,OCIAttrGet(colhp,OCI_DTYPE_PARAM,&collen[i],0,
			OCI_ATTR_DATA_SIZE,errhp));
		ErrorProc(errhp,OCIAttrGet(colhp,OCI_DTYPE_PARAM,&coltype[i],
			0,OCI_ATTR_DATA_TYPE,errhp));

		if(coltype[i]==SQLT_DAT)
			collen[i]=30;

		colbuf[i]=(text*)new text [(int)collen[i]+1];
	}
	for(i=0;i<(int)col_num;i++)
	{
		if(status=OCIDefineByPos(stmthp,&defhp[i],
			errhp,i+1,(ub1*)colbuf[i],collen[i]+1,SQLT_STR,&ind[i],0,0,OCI_DEFAULT))
		{
			ErrorProc(errhp,status);
			return;
		}
	}
	
	int row=0;
	while((OCIStmtFetch(stmthp,errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT))!=OCI_NO_DATA)
	{
		for(i=0;i<(int)col_num;i++)
		{
			ColVal[row][i]="";
			if(ind[i]==-1)
				ColVal[row][i]="";
			else
				ColVal[row][i]=colbuf[i];
		}
	    row=row+1;
	}

	for(i=0;i<(int)col_num;i++)
		delete colbuf[i];
	
	m_listCtrl.DeleteAllItems();
	for(i=0;i<row;i++)
	{
		m_listCtrl.InsertItem(LVIF_TEXT|LVIF_STATE,i,ColVal[i][0],
			(i%2)==0?LVIS_SELECTED:0,LVIS_SELECTED,0,0);
		for(int j=1;j<(int)col_num;j++)
			m_listCtrl.SetItemText(i,j,ColVal[i][j]);
	}
	delete textConVal;

}

void CCOCIExampleDlg::OnButDelete() 
{
	// TODO: Add your control notification handler code here
	UpdateData(TRUE);
	sword status;
	text textSQL[1024];
	wsprintf((char*)textSQL,"DELETE FROM %s WHERE %s %s :DelFieldVal",TableName,
 		m_strFieldName,m_strCondition);
	if(status=OCIStmtPrepare(stmthp,errhp,textSQL,strlen((char*)textSQL),      //没有执行!
		OCI_NTV_SYNTAX,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}

	text textColVal[20];
	sb4 len;
	int strlength=m_strFieldVal.GetLength();
	wsprintf((char*)textColVal,"%s",m_strFieldVal);
	textColVal[strlength]='\0';
	len=strlen((const char *)textColVal)+1;

	if(status=OCIBindByName(stmthp,&bidhp[0],errhp,(text *) ":DelFieldVal",
		-1,(ub1 *)&textColVal,len,SQLT_STR,0,(ub2*)0,(ub2*)0,(ub4)0,(ub4*)0,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}

	if(status=OCIStmtExecute(svchp,stmthp,errhp,(ub4)1,0,NULL,NULL,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}

	OnBTableselectok();
	
}

void CCOCIExampleDlg::OnButUpdate() 
{
	// TODO: Add your control notification handler code here
    UpdateData(TRUE);
	sword status;
	text textSQL[1024];
	wsprintf((char*)textSQL,"UPDATE %s SET %s =:UpDFieldVal WHERE %s =:KeyFieldVal",
		TableName,m_strFieldName,m_strConFName);
	if(status=OCIStmtPrepare(stmthp,errhp,textSQL,strlen((char *)textSQL),
		OCI_NTV_SYNTAX,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}

	text textColVal[20];
	sb4  len;
	int  strlength=m_strFieldVal.GetLength();
	wsprintf((char*)textColVal,"%s",m_strFieldVal);
	textColVal[strlength]='\0';
	len=strlen((const char *)textColVal)+1;
	
    if(status=OCIBindByName(stmthp,&bidhp[0],errhp,(text*)":UpDFieldVal",-1,
		(ub1*)&textColVal,len,SQLT_STR,0,0,0,0,0,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}

	text textConColVal[20];
	strlength=m_strConFName.GetLength();
	wsprintf((char*)textConColVal,"%s",m_strConFVal);
	textConColVal[strlength]='\0';
	len=strlen((const char*)textConColVal)+1;
	if(status=OCIBindByName(stmthp,&bidhp[1],errhp,(text*)":KeyFieldVal",-1,
		(ub1*)&textConColVal,len,SQLT_STR,0,0,0,0,0,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}
    if(status=OCIStmtExecute(svchp,stmthp,errhp,(ub4)1,0,NULL,NULL,OCI_DEFAULT))
	{
		ErrorProc(errhp,status);
		return;
	}
	
	status=OCITransCommit(svchp,errhp,(ub4)0);

	OnBTableselectok();
		
}