hdapi.cpp

来自「深入浅出Visual C++入门进阶与应用实例 随书光盘 作者 何志丹」· C++ 代码 · 共 411 行

#include "stdafx.h"
#include "odbcinst.h"
#include "HDAPI.h"

extern "C" int WINAPI HDCreateDBFile(CString strFileName)
{
	if(strFileName.IsEmpty())
		return 0;

	if(strFileName.GetLength() < 4 )
		return 0; //没有指定扩展名
	CString strFileExt = strFileName.Right(4);//文件扩展名

	enum HDBType type;
	if(strFileExt == ".mdb")
		type = HDBType_ACCESS;
	else if(strFileExt == ".xls")
		type = HDBType_EXCEL;
	else if(strFileExt == ".dbf")
		type = HDBType_FOXPRO;
	else
	{
		return 0;//错误类型
	}

	//同名的文件存在
	if(IsDirectoryOrFile(strFileName) > 0)
	{
		const CString strMess = "同名文件(或文件夹)已经存在.你想覆盖吗?点击是覆盖,否追加.";
		if(IDYES != AfxMessageBox(strMess,MB_YESNO))
			return  1;		
	}
	
	CreateDBFile(type,strFileName);
	return 1;	
}

extern "C" int WINAPI HDConfigFileTypeDataSource(CString strDSN,CString strFileName)
{
	enum HDBType type = GetDBTypeByFileName(strFileName);
	if( HDBType_UNKNOWN == type )
		return 0;

	char pszAttributes[1000];
	if(HDBType_ACCESS == type || HDBType_EXCEL == type)
		sprintf(pszAttributes,"DSN=%s;DBQ=%s;",strDSN,strFileName);
	else if(HDBType_FOXPRO == type)
		sprintf(pszAttributes,"DSN=%s;SourceType=dbf;SourceDB=%s;",strDSN,strFileName);

	//";"==>"\0"
	for(char * p = pszAttributes ; '\0' != *p ; p++)
		if(';' == *p )
			*p = '\0';
	
	int nRet =  SQLConfigDataSource(NULL,ODBC_ADD_DSN,szDBDriver[type],pszAttributes);	
	return nRet ;
}


extern "C" int WINAPI HDGetTable(CString strDSN,CString &strTableName,int SelectType,bool bReadOnly)
{
	strTableName.Empty();

	if(SelectType < 0 || SelectType > 2 )
	{
		ASSERT(false);
		return 0 ;	
	}

	HSelectTableByDSN dlg(strDSN);
	if(2 == SelectType)//显示全部
	{
		const int nCount = dlg.m_arTables.GetSize();
		for(int i = 0 ; i < nCount ; i++ )
		{
			if(0 != i)
				strTableName += ' ';
			strTableName += dlg.m_arTables[i];
		}
		return 1;
	}

	
	if(1 == SelectType)
		dlg.m_style |= CSelectTables::ST_MUIT;
	if(bReadOnly)
		dlg.m_style |= CSelectTables::ST_READONLY;
	strTableName = dlg.GetTables();
	if(strTableName.IsEmpty())
		return 0;
	else
		return 1;
}



extern "C"  int  WINAPI HDWriteRecordContents(CString strFileName, HFieldInfos& tFieldInfos, HRecordInfos& tRecordInfos)
{
	CString strErr;
	if(!IsLegalName(strFileName,strErr))
	{
		strErr += "\n继续吗?";
		if(IDYES != AfxMessageBox(strErr,MB_YESNO))
			return 0 ;
	}

	const HDBType nType = GetDBTypeByFileName(strFileName);
	
	HDCreateDBFile(strFileName);
	CString strDSN = gszTempDSN;
	HDConfigFileTypeDataSource(strDSN,strFileName);

	CDatabase db;
	try
	{
		db.OpenEx("DSN=" + strDSN);
		CStringArray arSql;
		CString      strTableName;
		if(HDBType_FOXPRO == nType)
			strTableName = strFileName ;
		else
		{
			HDGetTable(strDSN,strTableName,0,false);
			if(strTableName.IsEmpty())
			{
				if(db.IsOpen())
					db.Close();
				return 0;
			}
		}
		CStringArray arTables;
		GetTables(strDSN,arTables);		
		if(-1 != FindStrFromStrArr(arTables,strTableName))
		{
			if(IDYES != AfxMessageBox("已经存在同名的表,你想覆盖它吗?",MB_YESNO))
			{
				if(db.IsOpen())
					db.Close();
				return 0;				
			}
			CString strSql ;
			GetDeleteTableSql(strTableName,strSql);
			db.ExecuteSQL(strSql);
		}
		
		DoIllegalColName(nType,tFieldInfos);
		GetCreateTableSql(nType,strTableName,tFieldInfos,arSql);
		for(int i = 0 ; i < arSql.GetSize(); i++)
		{
			CString strSql = arSql[i];
			db.ExecuteSQL(strSql);
		}
		
		//填写数据
	
		
    }
	catch(CDBException &e)
	{
		ASSERT(false);
		e.m_strError;
	}
	if(db.IsOpen())
		db.Close();
	
	return 1;
}


extern "C"  int  WINAPI HDGetDataFromTableByODBC(CString strDSN,CString strTableName, HFieldInfos& tFieldInfos, HRecordInfos& tRecordInfos)
{
	int nRowCount = 1 ;//行和列都是以1起始
	const int nColCount = tFieldInfos.GetSize();

	SQLPOINTER pTok1;
	// 定义环境句柄、连接句柄、语句句柄
	SQLHENV henv;
	SQLHDBC hdbcSource;
	SQLHSTMT hstmt,hstmtSource;
	// 定义返回值
	SQLRETURN rtcode,rtcodeSource;
    SQLINTEGER cbPhotoParam,cbPhotoParamSource;
	// 分配环境句柄
	rtcode = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
	if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
	{
		// 设置环境属性（ODBC版本号）
		rtcode = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
		if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
		{
			// 分配连接句柄
			rtcode = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbcSource);
			if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
			{
				// 设置连接属性（登录超时 = 10s）
				SQLSetConnectAttr(hdbcSource,SQL_ATTR_LOGIN_TIMEOUT,(void*)10,0);
				// 连接数据源				
				rtcode = SQLConnect(hdbcSource,(SQLCHAR*)(LPCTSTR)strDSN,SQL_NTS,
					NULL,SQL_NTS,
					NULL,SQL_NTS);
                rtcode = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
				
				SQLCHAR NAME[100];
				SQLINTEGER oddcbID,oddcbName;
				if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
				{
					// 分配语句句柄
					rtcode = SQLAllocHandle(SQL_HANDLE_STMT,hdbcSource,&hstmtSource);
					if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
					{
						unsigned char connectStrSource[100];
						sprintf((char*)connectStrSource,"select * from %s",strTableName);
						if (rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO) 
						{
							SQLUINTEGER ID;
							cbPhotoParam = SQL_LEN_DATA_AT_EXEC(0);
							rtcode = SQLExecDirect(hstmtSource,connectStrSource,SQL_NTS);
							//绑定表的列名
							SQLCHAR tempch[1000000] = ""; 
							int nCount =   1000000;
							rtcode = SQLFetch(hstmtSource);
							//判断是否获得数据成功
							while (rtcode== SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO || rtcode == SQL_NEED_DATA)
							{						
								for(int i = 0 ; i < nColCount ; i++ )
								{
									rtcode = SQLGetData(hstmtSource,i+1,SQL_C_CHAR,tempch,nCount,&cbPhotoParamSource);
									HRecordInfo * pRecordInfo = new HRecordInfo ;
									pRecordInfo->iRow = nRowCount ;
									pRecordInfo->iCol = i + 1 ;
									if(cbPhotoParamSource >= 0)//非NULL
										pRecordInfo->sContent = tempch;
									tRecordInfos.Add(pRecordInfo);
								}
								//可能会进行多次
								nRowCount ++ ;
								rtcode = SQLFetch(hstmtSource);
							}		

							SQLFreeHandle(SQL_HANDLE_STMT,hstmtSource);
							// 断开连接
							SQLDisconnect(hdbcSource);
							// 释放连接句柄
							SQLFreeHandle(SQL_HANDLE_DBC,hdbcSource);
							// 释放环境句柄
							SQLFreeHandle(SQL_HANDLE_ENV,henv);
						}
					}
				}
			}
		}
		
	}

	return 1 ;	
}


extern "C"  int  WINAPI HDSetDataToTableByODBC(CString strDSN,CString strTableName, HFieldInfos& tFieldInfos, HRecordInfos& tRecordInfos)
{
	SQLINTEGER  cbStatus = SQL_NTS;

	const int nColCount = tFieldInfos.GetSize() ;
	int nRowCount = 0 ;
	for(int i = 0 ; i < tRecordInfos.GetSize() ; i++ )
	{
		HRecordInfo * pRecordInfo = tRecordInfos[i];
		if(pRecordInfo->iRow > nRowCount)
			nRowCount = pRecordInfo->iRow ;
	}

	CString ***pStr = new CString**[nRowCount];
	for(i = 0 ; i < nRowCount ; i++ )
		pStr[i] = new CString*[nColCount];

	for(i = 0 ; i < tRecordInfos.GetSize() ; i++ )
	{
		int nRow = tRecordInfos[i]->iRow - 1;
		int nCol = tRecordInfos[i]->iCol - 1;
		pStr[nRow][nCol] = &tRecordInfos[i]->sContent ;
	}
	
	CString strSql = "INSERT INTO " + strTableName ;
	CString strColName=" (",strValue = "VALUES(";
	for(i = 0 ; i < nColCount ; i++ )
	{
		if(0 != i)
		{
			strColName +=",";
			strValue   +=",";
		}
		strColName += tFieldInfos[i]->sFieldName;
		strValue   += "?";
	}
	strColName += ") ";
	strValue   += ")";
    strSql += (strColName + strValue);	

	SQLPOINTER pTok1;
	// 定义环境句柄、连接句柄、语句句柄
	SQLHENV henv;
	SQLHDBC hdbc;
	SQLHSTMT hstmt,hstmtSource;
	// 定义返回值
	SQLRETURN rtcode,rtcodeSource;
    SQLINTEGER cbPhotoParam,cbPhotoParamSource;
	// 分配环境句柄
	rtcode = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
	if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
	{
		// 设置环境属性（ODBC版本号）
		rtcode = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
		if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
		{
			// 分配连接句柄
			rtcode = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc);
			if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
			{
				// 设置连接属性（登录超时 = 10s）
				SQLSetConnectAttr(hdbc,SQL_ATTR_LOGIN_TIMEOUT,(void*)10,0);
				// 连接数据源				
				
				rtcode = SQLConnect(hdbc,(SQLCHAR*)(LPCTSTR)strDSN,SQL_NTS,
					(SQLCHAR*)NULL,SQL_NTS,
					(SQLCHAR*)NULL,SQL_NTS);				
				
                //rtcode = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
				
				SQLCHAR NAME[100];
				SQLINTEGER oddcbID,oddcbName;
				if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
				{
					// 分配语句句柄
					rtcode = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
					if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
					{						
						rtcode = SQLPrepare(hstmt,(unsigned char * )(LPCTSTR)strSql,SQL_NTS);
						if (rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO) 
						{				
							char ** pData	 = new char*[nColCount];
							int	 *  pLen	 = new int[nColCount];
							for(int nCol = 0 ; nCol < nColCount ; nCol ++ )
							{
								pData[nCol] = new char[10000];
								strcpy(pData[nCol],"A");
								pLen[nCol]  = strlen(pData[nCol]) +1;						
							}
							nCol = 0;
							for(nCol = 0; nCol < nColCount; nCol++)
								rtcodeSource =	SQLBindParameter(hstmt,nCol + 1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,pLen[nCol],0,pData[nCol],0,&cbStatus);
							for(int nRow = 0 ; nRow  < nRowCount ; nRow++)
							{	
								for(nCol = 0; nCol < nColCount ; nCol ++)
								{									
									strcpy(pData[nCol], *pStr[nRow][nCol]);
									pLen[nCol]  = strlen(pData[nCol]) +1;
								}

								nCol = 0 ;
								rtcodeSource = SQLExecute(hstmt);
							}

							delete [] pLen;
							delete [] pData;
						}
						
												
							SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
							// 断开连接
							SQLDisconnect(hdbc);
							// 释放连接句柄
							SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
							// 释放环境句柄
							SQLFreeHandle(SQL_HANDLE_ENV,henv);
						}
					}
				}
		}
	}	

	for(i = 0 ; i < nRowCount ; i++ )
		delete [] pStr[i] ;
	delete [] pStr ;
	
	return 1 ;
}

extern "C"  int  WINAPI HDGetColInfoFromTableByODBC(CString strDSN,CString strTableName, HFieldInfos& tFieldInfos)
{
	tFieldInfos.RemoveAll();
	
	CDatabase db;
	db.OpenEx("DSN=" + strDSN);
	CString strSql = "select * from " + strTableName ;
	CRecordset rs(&db);
	rs.Open(CRecordset::snapshot,strSql);
	const int nColCount = rs.GetODBCFieldCount();
	for(int i = 0 ; i < nColCount ; i++ )
	{
		CODBCFieldInfo fileldinfo ;
		rs.GetODBCFieldInfo(i,fileldinfo);
		HFieldInfo * pFieldInfo = new HFieldInfo();
		pFieldInfo->sFieldName = fileldinfo.m_strName;
		tFieldInfos.Add(pFieldInfo);
	}
	rs.Close();
	db.Close();

	return 1 ;
}