db.cpp

vc连接mysql范例。包括创建数据源

// STMSDateBaseDLL.cpp : Defines the initialization routines for the DLL.
//

#include "stdafx.h"
#include "db.h"
#include <afxdb.h>
#include "ODBCINST.H"

#include "icrsint.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

//
//	Note!
//
//		If this DLL is dynamically linked against the MFC
//		DLLs, any functions exported from this DLL which
//		call into MFC must have the AFX_MANAGE_STATE macro
//		added at the very beginning of the function.
//
//		For example:
//
//		extern "C" BOOL PASCAL EXPORT ExportedFunction()
//		{
//			AFX_MANAGE_STATE(AfxGetStaticModuleState());
//			// normal function body here
//		}
//
//		It is very important that this macro appear in each
//		function, prior to any calls into MFC.  This means that
//		it must appear as the first statement within the 
//		function, even before any object variable declarations
//		as their constructors may generate calls into the MFC
//		DLL.
//
//		Please see MFC Technical Notes 33 and 58 for additional
//		details.
//

/////////////////////////////////////////////////////////////////////////////
// CSTMSDateBaseDLLApp
/*
BEGIN_MESSAGE_MAP(CSTMSDateBaseDLLApp, CWinApp)
	//{{AFX_MSG_MAP(CSTMSDateBaseDLLApp)
		// NOTE - the ClassWizard will add and remove mapping macros here.
		//    DO NOT EDIT what you see in these blocks of generated code!
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()
*/
/////////////////////////////////////////////////////////////////////////////
// CSTMSDateBaseDLLApp construction

db::db()
{
	// TODO: add construction code here,
	// Place all significant initialization in InitInstance
}

/////////////////////////////////////////////////////////////////////////////
// The one and only CSTMSDateBaseDLLApp object

db *theApp = new db();

/*
	函数名:int sql_sentence_operate(char* tableName, char* sqlstr)
	函数功能：通过sql语句操作（建表，删表等），若有记录集的操作，则在当前目录下生成一个Record文件夹，并在地下生成一个record.ini的文件保存记录集。
	函数参数：
		admin_name：用户名
		sqlstr,：sql语言
	函数返回值：
		-3									数据库连接失败
		ERROR_OPEN_TABLE				-1	打开数据库表异常
		ERROR_SQL_OPERTATION			-8	sql语句操作数据库异常（sql语句是否正确？表记录是否重复？）
		CORRECT_RETURN_INTERFACE		1	接口函数正常返回

  */
int db::sql_sentence_operate(char* tableName,char* sqlsch)
{
	CString tableNamestr;	
	CString sqlstr;
	sqlstr.Format("%s",sqlsch);
	tableNamestr.Format("%s",tableName);

	try
	{
		_variant_t RecordsAffected;
		theApp->m_pRecordset = theApp->m_pConnection->Execute((_bstr_t)sqlstr,&RecordsAffected,adCmdText);
		
		if (sqlstr.Left(6) == "select" || sqlstr.Left(6) == "SELECT")
		{
			if ((theApp->m_pRecordset->adoEOF))
			{
				return ERROR_NOT_FOUND_RECORD;	//没有找到记录集
			}
			long l_record_count = 0;
			FILE *file;	
			//生成记录文件
			_TCHAR lpszCurrentDirectory[_MAX_PATH];
			GetCurrentDirectory(_MAX_PATH, lpszCurrentDirectory);
			_TCHAR lpszOutputFileName[_MAX_PATH];
			CreateDirectory("Record",NULL);	//设置src路径用于存放src文件
			_stprintf(lpszOutputFileName, _T("%s\\Record\\record.ini"),lpszCurrentDirectory);
			file = _tfopen(lpszOutputFileName, _T("w"));	
			int i_field_count = theApp->m_pRecordset->GetFields()->Count;//得到字段数;
			
			//得到字段名
			CString *str_field_name;
			str_field_name = new CString[i_field_count];
			long i = 0;
			for (i=0;i<i_field_count;i++)
			{
				str_field_name[i] = LPCTSTR(theApp->m_pRecordset->GetFields()->GetItem(_variant_t(i))->Name);
			}
			//字段值
			CString *str_field_value;
			str_field_value = new CString[i_field_count];

			//写入记录文件的头文件
			theApp->writeIntoRecordFile("[MENU]",file);
			theApp->writeIntoRecordFile("\n",file);
			theApp->writeIntoRecordFile("TABLENAME=",file);
			theApp->writeIntoRecordFile(tableName,file);
			theApp->writeIntoRecordFile("\n",file);
			theApp->writeIntoRecordFile("\n",file);
			int i_record_item = 0;

			while(!(theApp->m_pRecordset->adoEOF)){
				CString str_record_item;
				str_record_item.Format("%d",++i_record_item);
				theApp->writeIntoRecordFile("[RECORDITEM_"+str_record_item+"]\n",file);
				for (long j=0;j<i_field_count;j++)
				{
					
					str_field_value[j] = (char*)(_bstr_t)theApp->m_pRecordset->GetCollect(_variant_t(j));
					theApp->writeIntoRecordFile(str_field_name[j]+"="+str_field_value[j]+"\n",file);
				}
				l_record_count++;
				theApp->m_pRecordset->MoveNext();
			}
			
			CString str_record_count;
			str_record_count.Format("%ld",l_record_count);
			theApp->writeIntoRecordFile("[COUNT]\n",file);
			theApp->writeIntoRecordFile("RECORDCOUNT=",file);
			theApp->writeIntoRecordFile(str_record_count,file);
			fclose(file);
		}
	}
	
	catch(_com_error e)
	{
		return ERROR_SQL_OPERTATION;        //错误,程序退出
	}
	return CORRECT_RETURN_INTERFACE;
}

/*
	函数名:int initADOConnect()
	函数功能：初始化数据库连接
	函数参数：
		conIp：连接ip
		conPass：连接密码
		conPort：连接端口（8715）
		conUser：连接用户名
		conDS：	 连接数据源名
		conDB：  连接数据库名
	函数返回值：
		-3									数据库连接失败
		CORRECT_RETURN_INTERFACE		1	接口函数正常返回
  */

int db::initADOConnect(char* conIp , char* conPass , int conPort ,
				   char* conUser , char* conDS , char* conDB )
{
	
	::CoInitialize(NULL);
	
	//创建连接数据源
	CString sConfigDS;
	sConfigDS.Format("DSN=%s\0 UID=%s\0 PWD=%s\0 PORT=%d\0 SERVER=%s\0 DATABASE=%s\0\0",conDS,conUser,conPass,conPort,conIp,conDS);
	if (!SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN,"MySQL ODBC 3.51 Driver",sConfigDS))
	{
		return ERR_CON_DS;
	}
	
	try                 
	{
		HRESULT hr = theApp->m_pConnection.CreateInstance(__uuidof(Connection));//创建Connection对象
		
		// 打开本地Access库Demo.mdb
		if (SUCCEEDED(hr))
		{
			//连接数据库
			theApp->m_pConnection->CursorLocation = adUseClient;
			CString strConn;
			strConn.Format("DRIVER={MySQL ODBC 3.51 Driver};Provider=MSDASQL.1;server=%s;Password=%s;Port=%d;Persist Security Info=True;User ID=%s;Data Source=%s;Database=%s;OPTION=3",conIp,conPass,conPort,conUser,conDS,conDB);
			theApp->m_pConnection->CommandTimeout = 5;
			theApp->m_pConnection->ConnectionTimeout = 5;
			theApp->m_pConnection->ConnectionString = (_bstr_t)strConn;
			theApp->m_pConnection->Open(theApp->m_pConnection->ConnectionString,"","",adModeUnknown);
		}
		
	}
	catch(...)//COM错误取得,当执行COM功能的时候，如果出错，可以捕捉到_com_error的异常
	{
		
		return ERROR_CONNECT_IP;
	}
	//设置mysql字符编码
	theApp->m_pConnection->Execute((_bstr_t)"set character_set_connection=gbk",NULL,adCmdText);   
    theApp->m_pConnection->Execute((_bstr_t)"set character_set_results=gbk",NULL,adCmdText);   
    theApp->m_pConnection->Execute((_bstr_t)"set character_set_client=gbk",NULL,adCmdText);
	return CORRECT_RETURN_INTERFACE;
}
//关闭记录集和连接
void db::ExitConnect()
{
	//关闭记录集和连接
    //if(m_pRecordset!=NULL)
	//	m_pRecordset->Close();
	m_pConnection->Close();
}
//字段写入文件
/*
	函数名:void writeIntoRecordFile(CString content,FILE* file)
	函数功能：向文件中写入内容
	函数参数：
		content：要写入的内容
		file：文件指针
*/
void db::writeIntoRecordFile(CString content,FILE* file){
	BYTE* contentValue = (BYTE *)content.GetBuffer(content.GetLength());
	fwrite(contentValue, sizeof(BYTE), strlen((LPTSTR)contentValue), file);
	content.ReleaseBuffer();
}