MultiTerminal1 - Gitblit

QuakeGod

2022-01-16 326d3e312c74726814c39c9d112faab03c4a167c

提交 \| 用户 \| age
4b03ae	1	#include "stdafx.h"
Q	2
	3	/*
	4	* Access 数据库接口程序
	5	*
	6	*
	7	*
	8	*
	9	*/
	10
	11	int CreateMdb(CString MdbPathName)
	12	{
	13	//导入对应Dll
	14
	15
	16	//::CoInitialize(NULL);
	17	ADOX::_CatalogPtr pCatalog(__uuidof(ADOX::Catalog));
	18	CString s1,s2;
	19	// s1=_T("Provider=Microsoft.Jet.OLEDB.4.0;data source=")+MdbPathName;
	20	s1=_T("Provider=Microsoft.ACE.OLEDB.12.0;data source=")+MdbPathName;
	21	// s1=_T("File Name=.\\A1.UDL");
	22	_bstr_t str=s1;
	23
	24	try
	25	{ s1.Format(_T("=> Try To Create Mdb %s \r\n"),MdbPathName);
	26	OutputDebugString(s1);
	27	pCatalog->Create(str); //创建数据库
	28	return 1;
	29	}
	30	catch(_com_error &e)
	31	{
	32	//错误处理
	33	// ::MessageBox(NULL,_T("数据库创建失败。"), _T("提示"),MB_OK \| MB_ICONINFORMATION);
	34	_bstr_t bt=e.Description();
	35	s2=(LPTSTR)bt;
	36	s1.Format(_T("=>LOG Catch e 数据库 %s 创建失败 %s \r\n %s \r\n"),MdbPathName,e.ErrorMessage(),s2);
	37	// SysLog(s1);
	38	//s1.AppendFormat(_T("%ws"),e.Description());
	39	// ::MessageBox(NULL,s2+e.Description(),e.ErrorMessage(),MB_OK\|MB_ICONINFORMATION);
	40
	41	//TRACE(s1);
	42	OutputDebugString(s1);
	43
	44	return -1;
	45	}
	46	//CoUninitialize();
	47	return 0;
	48	}
	49	int OpenMdb(CString MdbPathFileName,_ConnectionPtr & pConnectionPtr)
	50	{
	51	CString strConnection;
	52	CString strMsg;
	53	CString strTemp1, strTemp2,strAddField1，strAddField2;
	54	CString s1;
	55	// _ConnectionPtr pConnection;
	56
	57
	58	// strTemp1 = _T("新字段1");
	59	// strTemp2 = _T("新字段2");
	60
	61	strConnection.Format(_T("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;Persist Security Info=False;"),MdbPathFileName);
	62	HRESULT hr = pConnectionPtr.CreateInstance( __uuidof(Connection) );
	63	if ( FAILED(hr) )
	64	{
	65	::MessageBox(NULL,_T("您系统中的数据库驱动有问题。"), _T("提示"),MB_OK \| MB_ICONINFORMATION);
	66	// SysLog(_T("您系统中的数据库驱动有问题。\r\n"));
	67	return 0;
	68	}
	69	try
	70	{
	71	pConnectionPtr->ConnectionTimeout = 18;
	72	pConnectionPtr->ConnectionString =_bstr_t(strConnection);
	73	pConnectionPtr->Open( _bstr_t(L""), _bstr_t(L""), _bstr_t(L""), -1 );
	74	//建立表
	75	}
	76	catch( _com_error& e)
	77	{
	78	_bstr_t bstrError = e.Description();
	79	strMsg=(LPCTSTR)bstrError;
	80	// ::MessageBox(NULL,strMsg, e.ErrorMessage(),MB_OK \| MB_ICONINFORMATION);
	81	// _bstr_t bt=e.Description();
	82	// s2=(LPTSTR)bt;
	83	s1.Format(_T("=>LOG 数据库 %s 连接失败 %s \r\n %s \r\n"),MdbPathFileName,e.ErrorMessage(),strMsg);
	84	//SysLog(s1);
	85	OutputDebugString(s1);
	86	// pConnectionPtr->Close();
	87	pConnectionPtr.Release();
	88	return 0;
	89	}
	90	// (* pConnectionPtr)=pConnection;
	91	// pConnection->Close();
	92	// pConnection.Release();
	93	return 1;
	94	//CoUninitialize();
	95	}
	96
	97	int CloseMdb(_ConnectionPtr & pConnection)
	98	{
	99	pConnection->Close();
	100	pConnection.Release();
	101	return 0;
	102	}
	103
	104	int RunSql1( _ConnectionPtr pConnection,CString SqlStr1)//运行没有返回值的SQL语句
	105	{
	106	_variant_t RecordsAffected;
	107	CString s1,s2;
	108	CString strMsg;
	109	try
	110	{
	111	pConnection->Execute(_bstr_t(SqlStr1),&RecordsAffected,adCmdText);
	112
	113	}
	114	catch(_com_error& e)
	115	{
	116	_bstr_t bstrError = e.Description();
	117	strMsg=(LPCTSTR)bstrError;
	118	s1.Format(_T("=>LOG RunSQL1 %s \r\n %s \r\n"),e.ErrorMessage(),strMsg);
	119	OutputDebugString(s1);
	120	// SysLog(s1);
	121	}
	122	return 0;
	123	}
	124
	125	int RunSql2( _ConnectionPtr pConnection,CString sqlquery1,_RecordsetPtr & pRecordsetPtr)//运行有返回值的SQL语句
	126	{
	127	_variant_t RecordsAffected;
	128	CString s1,s2;
	129	CString strMsg;
	130	int j;
	131	int RowCount,FiledCount;
	132	try
	133	{
	134	// pConnection->Execute(_bstr_t(SqlStr1),&RecordsAffected,adCmdText);
	135	pRecordsetPtr.CreateInstance(__uuidof(Recordset));
	136	j=pRecordsetPtr->Open(_variant_t(sqlquery1),pConnection.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);
	137	s1.Format(_T("-mdb数据库读取成功 readok 状态码 %x\r\n"),j);
	138	// ::SysLog(s1);
	139	RowCount=pRecordsetPtr->GetRecordCount();
	140
	141	FieldsPtr fs2;
	142	CString name2,value2;
	143	// fs2.CreateInstance(__uuidof(Fields));
	144	fs2=pRecordsetPtr->GetFields();
	145	FiledCount=fs2->Count;
	146	s1.Format(_T("result %x %d\r\n"),RowCount,FiledCount);
	147	// SysLog(s1);
	148	}
	149	catch(_com_error& e)
	150	{
	151	_bstr_t bstrError = e.Description();
	152	strMsg=(LPCTSTR)bstrError;
	153	s1.Format(_T("=>LOG RunSQL2 %s \r\n %s \r\n"),e.ErrorMessage(),strMsg);
	154	// SysLog(s1);
	155	return -1;
	156	}
	157	return RowCount;
	158	}
	159
	160	MyDataBase::MyDataBase()
	161	{
	162	//导入对应Dll
	163	// ::CoInitialize(NULL);
	164	IsOpened=0;
	165	}
	166
	167	MyDataBase::~MyDataBase()
	168	{
	169	if (IsOpened)
	170	{
	171	CloseMdb(m_ConnectionPtr);
	172	//m_ConnectionPtr;
	173	}
	174	//CoUninitialize();
	175	}
	176
	177	int MyDataBase::CreateMdb()
	178	{
	179	return ::CreateMdb(MdbPathName);
	180	return 1;
	181	}
	182
	183	int MyDataBase::CreateMdbStruct()
	184	{
	185	if (!IsOpened)
	186	{
	187	IsOpened=OpenMdb(MdbPathName,m_ConnectionPtr);
	188	}
	189	//建立表
	190	CString strAddField1= _T("Create Table [Products](SN COUNTER, ProductCode Text(32), ProductType Text(32),ProductDesc Text(120), ColorNo Integer, Quality Integer, PowerNo Integer,ProductLineNo Integer,Time1 DATETIME)");
	191	//pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
	192	int j=RunSql1(m_ConnectionPtr,strAddField1);
	193
	194	CString strAddField2= _T("Create Table [Results](ID Integer, Names1 Text(50),Value1 Text(120))");
	195	//pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
	196	// RunSql1(m_ConnectionPtr,strAddField2);
	197
	198	CString strAddField3= _T("Create Table [Counter](ID1 Integer, Names1 TEXT(50),vvv VarChar(200))");
	199	//pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
	200	// RunSql1(m_ConnectionPtr,strAddField3);
	201
	202	/*
	203	//需要增加的字段及数据类型
	204	strAddField1 = TEXT("ALTER TABLE Covers ADD COLUMN ") + strTemp1 + TEXT(" int");
	205	pConnection->Execute(_bstr_t(strAddField1),&RecordsAffected,adCmdText);
	206
	207	//需要增加的字段及数据类型
	208	CString strAddField2 = TEXT("ALTER TABLE Covers ADD COLUMN ") + strTemp2 + TEXT(" datetime");
	209	pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
	210	*/
	211	//::MessageBox(NULL,_T("数据库构造更新成功。"), _T("提示"),MB_OK \| MB_ICONINFORMATION);
	212	// SysLog(_T("数据库构造更新成功。\r\n"));
	213	return j;
	214	//CoUninitialize();
	215	}
	216
	217	int MyDataBase::AddToOutPutDataBase(int Index, CString ProductCode, CString ProductType, CString ProductDesc, int ColorNo,int Quality,int PowerNo,int ProductLineNo)
	218	{
	219	CString sqlstr1;
	220	if (Index==0)
	221	{
	222	sqlstr1.Format(_T("Insert Into Products(ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo,time1) \
	223	Values('%s','%s','%s','%d','%d','%d','%d',Now())"),
	224	ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo);
	225
	226	}
	227	else
	228	{
	229	sqlstr1.Format(_T("Insert Into Products(SN,ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo,time1) \
	230	Values('%d','%s','%s','%s','%d','%d','%d','%d',Now())"),
	231	Index,ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo);
	232	}
	233	int j;
	234	j=RunSql1(this->m_ConnectionPtr,sqlstr1);
	235
	236	//删除表中记录，只保留最新的200条
	237	sqlstr1.Format(_T("DELETE FROM Products WHERE SN NOT IN (SELECT TOP 200 SN FROM Products ORDER BY SN DESC)"));
	238	j=RunSql1(this->m_ConnectionPtr,sqlstr1);
	239	return j;
	240	}
	241
	242	int MyDataBase::AddToMdb(int channel,CString SN)
	243	{
	244	// ::CoInitialize(NULL);
	245
	246	CString Sql1;
	247	Sql1.Format(_T("INSERT INTO COUNTERS(CHANNEL,SN,TIME1) values('%d','%s',TIME())"),channel,SN);
	248	// SysLog(Sql1+_T("\r\n"));
	249	RunSql1(m_ConnectionPtr,Sql1);
	250	// pConnection->Execute(_bstr_t(Sql1),&RecordsAffected,adCmdText);
	251
	252	return 1;
	253	//CoUninitialize();
	254
	255	}
	256
	257	int MyDataBase::CheckMdb1(CString SerialNo)
	258	{
	259	int j,k;
	260	CString s1;
	261	int fieldcount;
	262	int readok=0;
	263	_variant_t v2;
	264	CString namestr,valuestr;
	265	_ConnectionPtr m_connection2;
	266	_RecordsetPtr m_recordset2;
	267	k=m_connection2.CreateInstance(__uuidof(Connection));
	268	// m_DBConnection.CreateInstance("ADODB.Connection");
	269	CString db2constr;
	270	db2constr.Format(_T("Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;CharSet=gbk"),MdbPathName); //Uid=admin;Pwd=123;
	271
	272	// m_connection2->ConnectionString="Provider=MSDAORA.1;password=stream;user id=stream;Data Source=stream.bly.com;Persist Security Info=True";
	273	m_connection2->ConnectionString=_bstr_t(db2constr);
	274	// Logtxt(db2constr+"\r\n");
	275	try
	276	{
	277	j=m_connection2->Open(_T(""),_T(""),_T(""),-1);
	278	// DB2opened=1;
	279	s1.Format(_T("-mdb数据库连接成功 DBOpened 状态码 %x %x\r\n"),j,k);
	280	//::SysLog(s1);
	281	// m_DBConnection->Execute("Insert into workers(name) values('zhang')",&v1,0);
	282	m_recordset2.CreateInstance(__uuidof(Recordset));
	283	CString sqlquery;
	284	sqlquery.Format(_T("Select * from COUNTERS Where SN='%s'"),SerialNo);
	285	j=m_recordset2->Open(_variant_t(sqlquery),m_connection2.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);
	286	readok=1;
	287	s1.Format(_T("-mdb数据库读取成功 readok 状态码 %x\r\n"),j);
	288	// ::SysLog(s1);
	289	namestr.Format(_T("serial"));
	290	valuestr.Format(_T("'%s'"),SerialNo);
	291	int rowcount;
	292	FieldsPtr fs2;
	293	CString name2,value2;
	294	fs2.CreateInstance(__uuidof(Fields));
	295	fs2=m_recordset2->GetFields();
	296	fieldcount=fs2->Count;
	297	j=fieldcount;
	298	k=m_recordset2->GetRecordCount();
	299
	300
	301	s1.Format(_T("result %x %d\r\n"),j,k);
	302	// SysLog(s1);
	303
	304	FieldPtr field2;
	305	field2.CreateInstance(__uuidof(Field));
	306	rowcount=0;
	307	/*
	308	for (i=0;i<fieldcount;i++)
	309	{
	310	field2=fs2->GetItem(_variant_t(long(i)));
	311	// v1=field->GetValue();
	312	name2=(LPCTSTR)field2->GetName();
	313	// m_list1.InsertColumn(i,name2,LVCFMT_LEFT,80);
	314	}
	315	*/
	316	/*
	317	while (!m_recordset2->adoEOF)
	318	{
	319	// m_list1.InsertItem(rowcount,value2,0);
	320	(*resultstr).Empty();
	321	for (i=0;i<fieldcount;i++)
	322	{
	323	field2=fs2->GetItem(_variant_t(long(i)));
	324	// v1=field->GetValue();
	325	name2=(LPCTSTR)field2->GetName();
	326	v2= m_recordset2->GetCollect(_variant_t(name2));
	327
	328	if (v2.vt==VT_NULL)
	329	{
	330	value2.Empty();
	331	l=0;
	332	}
	333	/ /
	334	else if (v2.vt==VT_R8\|\|v2.vt==VT_R4)
	335	{
	336	double dval1;
	337	v2.ChangeType(VT_R8);
	338	dval1=v2.dblVal;
	339	value2.Format(_T("%f"),dval1);
	340	l=value2.GetLength();
	341	}
	342	*/
	343	/* else
	344	{
	345	v2.ChangeType(VT_BSTR);
	346	value2=(char *)_bstr_t(v2.bstrVal);
	347	l=_bstr_t(v2.bstrVal).length();
	348	}
	349	// m_list1.SetItemText(rowcount,i,value2);
	350	if (name2==_T("Serial")) {continue;}
	351	if (name2==_T("DateTime"))
	352	{
	353	namestr+=_T(",")+name2;
	354	valuestr+=_T(",to_date('")+value2+_T("','YYYY-mm-dd hh24:MI:ss')");
	355	continue;
	356	}
	357	namestr+=_T(",")+name2;
	358	valuestr+=_T(",'")+value2+_T("'");
	359	s1.Format(_T("%s=%s;"),name2,value2);
	360	(*resultstr)+=s1;
	361	}
	362	// if (m_pRecordset->GetadoEof
	363	s1.Format(_T("%s %d \r\n"),(resultstr),(resultstr).GetLength());
	364	// Logtxt(s1);
	365	m_recordset2->MoveNext();
	366	rowcount++;
	367	}
	368	*/
	369	m_recordset2->Close();
	370	m_connection2->Close();
	371	return k;
	372	}
	373	catch (_com_error e1)
	374	{
	375	// DB2opened=0;
	376	s1.Format(_T("-mdb数据库连接失败具体信息如下\r\n %s\r\n"),(char *)(e1.Description()));
	377	// ::SysLog(s1);
	378	return -1;
	379	}
	380	return 0;
	381
	382	}
	383
	384