#include "stdafx.h"
|
|
/*
|
* Access Êý¾Ý¿â½Ó¿Ú³ÌÐò
|
*
|
*
|
*
|
*
|
*/
|
|
int CreateMdb(CString MdbPathName)
|
{
|
//µ¼Èë¶ÔÓ¦Dll
|
|
|
//::CoInitialize(NULL);
|
ADOX::_CatalogPtr pCatalog(__uuidof(ADOX::Catalog));
|
CString s1,s2;
|
// s1=_T("Provider=Microsoft.Jet.OLEDB.4.0;data source=")+MdbPathName;
|
s1=_T("Provider=Microsoft.ACE.OLEDB.12.0;data source=")+MdbPathName;
|
// s1=_T("File Name=.\\A1.UDL");
|
_bstr_t str=s1;
|
|
try
|
{ s1.Format(_T("=> Try To Create Mdb %s \r\n"),MdbPathName);
|
OutputDebugString(s1);
|
pCatalog->Create(str); //´´½¨Êý¾Ý¿â
|
return 1;
|
}
|
catch(_com_error &e)
|
{
|
//´íÎó´¦Àí
|
// ::MessageBox(NULL,_T("Êý¾Ý¿â´´½¨Ê§°Ü¡£"), _T("Ìáʾ"),MB_OK | MB_ICONINFORMATION);
|
_bstr_t bt=e.Description();
|
s2=(LPTSTR)bt;
|
s1.Format(_T("=>LOG Catch e Êý¾Ý¿â %s ´´½¨Ê§°Ü %s \r\n %s \r\n"),MdbPathName,e.ErrorMessage(),s2);
|
// SysLog(s1);
|
//s1.AppendFormat(_T("%ws"),e.Description());
|
// ::MessageBox(NULL,s2+e.Description(),e.ErrorMessage(),MB_OK|MB_ICONINFORMATION);
|
|
//TRACE(s1);
|
OutputDebugString(s1);
|
|
return -1;
|
}
|
//CoUninitialize();
|
return 0;
|
}
|
int OpenMdb(CString MdbPathFileName,_ConnectionPtr & pConnectionPtr)
|
{
|
CString strConnection;
|
CString strMsg;
|
CString strTemp1, strTemp2,strAddField1£¬strAddField2;
|
CString s1;
|
// _ConnectionPtr pConnection;
|
|
|
// strTemp1 = _T("ÐÂ×Ö¶Î1");
|
// strTemp2 = _T("ÐÂ×Ö¶Î2");
|
|
strConnection.Format(_T("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s;Persist Security Info=False;"),MdbPathFileName);
|
HRESULT hr = pConnectionPtr.CreateInstance( __uuidof(Connection) );
|
if ( FAILED(hr) )
|
{
|
::MessageBox(NULL,_T("ÄúϵͳÖеÄÊý¾Ý¿âÇý¶¯ÓÐÎÊÌâ¡£"), _T("Ìáʾ"),MB_OK | MB_ICONINFORMATION);
|
// SysLog(_T("ÄúϵͳÖеÄÊý¾Ý¿âÇý¶¯ÓÐÎÊÌâ¡£\r\n"));
|
return 0;
|
}
|
try
|
{
|
pConnectionPtr->ConnectionTimeout = 18;
|
pConnectionPtr->ConnectionString =_bstr_t(strConnection);
|
pConnectionPtr->Open( _bstr_t(L""), _bstr_t(L""), _bstr_t(L""), -1 );
|
//½¨Á¢±í
|
}
|
catch( _com_error& e)
|
{
|
_bstr_t bstrError = e.Description();
|
strMsg=(LPCTSTR)bstrError;
|
// ::MessageBox(NULL,strMsg, e.ErrorMessage(),MB_OK | MB_ICONINFORMATION);
|
// _bstr_t bt=e.Description();
|
// s2=(LPTSTR)bt;
|
s1.Format(_T("=>LOG Êý¾Ý¿â %s Á¬½Óʧ°Ü %s \r\n %s \r\n"),MdbPathFileName,e.ErrorMessage(),strMsg);
|
//SysLog(s1);
|
OutputDebugString(s1);
|
// pConnectionPtr->Close();
|
pConnectionPtr.Release();
|
return 0;
|
}
|
// (* pConnectionPtr)=pConnection;
|
// pConnection->Close();
|
// pConnection.Release();
|
return 1;
|
//CoUninitialize();
|
}
|
|
int CloseMdb(_ConnectionPtr & pConnection)
|
{
|
pConnection->Close();
|
pConnection.Release();
|
return 0;
|
}
|
|
int RunSql1( _ConnectionPtr pConnection,CString SqlStr1)//ÔËÐÐûÓзµ»ØÖµµÄSQLÓï¾ä
|
{
|
_variant_t RecordsAffected;
|
CString s1,s2;
|
CString strMsg;
|
try
|
{
|
pConnection->Execute(_bstr_t(SqlStr1),&RecordsAffected,adCmdText);
|
|
}
|
catch(_com_error& e)
|
{
|
_bstr_t bstrError = e.Description();
|
strMsg=(LPCTSTR)bstrError;
|
s1.Format(_T("=>LOG RunSQL1 %s \r\n %s \r\n"),e.ErrorMessage(),strMsg);
|
OutputDebugString(s1);
|
// SysLog(s1);
|
}
|
return 0;
|
}
|
|
int RunSql2( _ConnectionPtr pConnection,CString sqlquery1,_RecordsetPtr & pRecordsetPtr)//ÔËÐÐÓзµ»ØÖµµÄSQLÓï¾ä
|
{
|
_variant_t RecordsAffected;
|
CString s1,s2;
|
CString strMsg;
|
int j;
|
int RowCount,FiledCount;
|
try
|
{
|
// pConnection->Execute(_bstr_t(SqlStr1),&RecordsAffected,adCmdText);
|
pRecordsetPtr.CreateInstance(__uuidof(Recordset));
|
j=pRecordsetPtr->Open(_variant_t(sqlquery1),pConnection.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);
|
s1.Format(_T("-mdbÊý¾Ý¿â¶ÁÈ¡³É¹¦ readok ״̬Âë %x\r\n"),j);
|
// ::SysLog(s1);
|
RowCount=pRecordsetPtr->GetRecordCount();
|
|
FieldsPtr fs2;
|
CString name2,value2;
|
// fs2.CreateInstance(__uuidof(Fields));
|
fs2=pRecordsetPtr->GetFields();
|
FiledCount=fs2->Count;
|
s1.Format(_T("result %x %d\r\n"),RowCount,FiledCount);
|
// SysLog(s1);
|
}
|
catch(_com_error& e)
|
{
|
_bstr_t bstrError = e.Description();
|
strMsg=(LPCTSTR)bstrError;
|
s1.Format(_T("=>LOG RunSQL2 %s \r\n %s \r\n"),e.ErrorMessage(),strMsg);
|
// SysLog(s1);
|
return -1;
|
}
|
return RowCount;
|
}
|
|
MyDataBase::MyDataBase()
|
{
|
//µ¼Èë¶ÔÓ¦Dll
|
// ::CoInitialize(NULL);
|
IsOpened=0;
|
}
|
|
MyDataBase::~MyDataBase()
|
{
|
if (IsOpened)
|
{
|
CloseMdb(m_ConnectionPtr);
|
//m_ConnectionPtr;
|
}
|
//CoUninitialize();
|
}
|
|
int MyDataBase::CreateMdb()
|
{
|
return ::CreateMdb(MdbPathName);
|
return 1;
|
}
|
|
int MyDataBase::CreateMdbStruct()
|
{
|
if (!IsOpened)
|
{
|
IsOpened=OpenMdb(MdbPathName,m_ConnectionPtr);
|
}
|
//½¨Á¢±í
|
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)");
|
//pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
|
int j=RunSql1(m_ConnectionPtr,strAddField1);
|
|
CString strAddField2= _T("Create Table [Results](ID Integer, Names1 Text(50),Value1 Text(120))");
|
//pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
|
// RunSql1(m_ConnectionPtr,strAddField2);
|
|
CString strAddField3= _T("Create Table [Counter](ID1 Integer, Names1 TEXT(50),vvv VarChar(200))");
|
//pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
|
// RunSql1(m_ConnectionPtr,strAddField3);
|
|
/*
|
//ÐèÒªÔö¼ÓµÄ×ֶμ°Êý¾ÝÀàÐÍ
|
strAddField1 = TEXT("ALTER TABLE Covers ADD COLUMN ") + strTemp1 + TEXT(" int");
|
pConnection->Execute(_bstr_t(strAddField1),&RecordsAffected,adCmdText);
|
|
//ÐèÒªÔö¼ÓµÄ×ֶμ°Êý¾ÝÀàÐÍ
|
CString strAddField2 = TEXT("ALTER TABLE Covers ADD COLUMN ") + strTemp2 + TEXT(" datetime");
|
pConnection->Execute(_bstr_t(strAddField2),&RecordsAffected,adCmdText);
|
*/
|
//::MessageBox(NULL,_T("Êý¾Ý¿â¹¹Ôì¸üгɹ¦¡£"), _T("Ìáʾ"),MB_OK | MB_ICONINFORMATION);
|
// SysLog(_T("Êý¾Ý¿â¹¹Ôì¸üгɹ¦¡£\r\n"));
|
return j;
|
//CoUninitialize();
|
}
|
|
int MyDataBase::AddToOutPutDataBase(int Index, CString ProductCode, CString ProductType, CString ProductDesc, int ColorNo,int Quality,int PowerNo,int ProductLineNo)
|
{
|
CString sqlstr1;
|
if (Index==0)
|
{
|
sqlstr1.Format(_T("Insert Into Products(ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo,time1) \
|
Values('%s','%s','%s','%d','%d','%d','%d',Now())"),
|
ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo);
|
|
}
|
else
|
{
|
sqlstr1.Format(_T("Insert Into Products(SN,ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo,time1) \
|
Values('%d','%s','%s','%s','%d','%d','%d','%d',Now())"),
|
Index,ProductCode,ProductType,ProductDesc,ColorNo,Quality,PowerNo,ProductLineNo);
|
}
|
int j;
|
j=RunSql1(this->m_ConnectionPtr,sqlstr1);
|
|
//ɾ³ý±íÖмǼ£¬Ö»±£Áô×îеÄ200Ìõ
|
sqlstr1.Format(_T("DELETE FROM Products WHERE SN NOT IN (SELECT TOP 200 SN FROM Products ORDER BY SN DESC)"));
|
j=RunSql1(this->m_ConnectionPtr,sqlstr1);
|
return j;
|
}
|
|
int MyDataBase::AddToMdb(int channel,CString SN)
|
{
|
// ::CoInitialize(NULL);
|
|
CString Sql1;
|
Sql1.Format(_T("INSERT INTO COUNTERS(CHANNEL,SN,TIME1) values('%d','%s',TIME())"),channel,SN);
|
// SysLog(Sql1+_T("\r\n"));
|
RunSql1(m_ConnectionPtr,Sql1);
|
// pConnection->Execute(_bstr_t(Sql1),&RecordsAffected,adCmdText);
|
|
return 1;
|
//CoUninitialize();
|
|
}
|
|
int MyDataBase::CheckMdb1(CString SerialNo)
|
{
|
int j,k;
|
CString s1;
|
int fieldcount;
|
int readok=0;
|
_variant_t v2;
|
CString namestr,valuestr;
|
_ConnectionPtr m_connection2;
|
_RecordsetPtr m_recordset2;
|
k=m_connection2.CreateInstance(__uuidof(Connection));
|
// m_DBConnection.CreateInstance("ADODB.Connection");
|
CString db2constr;
|
db2constr.Format(_T("Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;CharSet=gbk"),MdbPathName); //Uid=admin;Pwd=123;
|
|
// m_connection2->ConnectionString="Provider=MSDAORA.1;password=stream;user id=stream;Data Source=stream.bly.com;Persist Security Info=True";
|
m_connection2->ConnectionString=_bstr_t(db2constr);
|
// Logtxt(db2constr+"\r\n");
|
try
|
{
|
j=m_connection2->Open(_T(""),_T(""),_T(""),-1);
|
// DB2opened=1;
|
s1.Format(_T("-mdbÊý¾Ý¿âÁ¬½Ó³É¹¦ DBOpened ״̬Âë %x %x\r\n"),j,k);
|
//::SysLog(s1);
|
// m_DBConnection->Execute("Insert into workers(name) values('zhang')",&v1,0);
|
m_recordset2.CreateInstance(__uuidof(Recordset));
|
CString sqlquery;
|
sqlquery.Format(_T("Select * from COUNTERS Where SN='%s'"),SerialNo);
|
j=m_recordset2->Open(_variant_t(sqlquery),m_connection2.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);
|
readok=1;
|
s1.Format(_T("-mdbÊý¾Ý¿â¶ÁÈ¡³É¹¦ readok ״̬Âë %x\r\n"),j);
|
// ::SysLog(s1);
|
namestr.Format(_T("serial"));
|
valuestr.Format(_T("'%s'"),SerialNo);
|
int rowcount;
|
FieldsPtr fs2;
|
CString name2,value2;
|
fs2.CreateInstance(__uuidof(Fields));
|
fs2=m_recordset2->GetFields();
|
fieldcount=fs2->Count;
|
j=fieldcount;
|
k=m_recordset2->GetRecordCount();
|
|
|
s1.Format(_T("result %x %d\r\n"),j,k);
|
// SysLog(s1);
|
|
FieldPtr field2;
|
field2.CreateInstance(__uuidof(Field));
|
rowcount=0;
|
/*
|
for (i=0;i<fieldcount;i++)
|
{
|
field2=fs2->GetItem(_variant_t(long(i)));
|
// v1=field->GetValue();
|
name2=(LPCTSTR)field2->GetName();
|
// m_list1.InsertColumn(i,name2,LVCFMT_LEFT,80);
|
}
|
*/
|
/*
|
while (!m_recordset2->adoEOF)
|
{
|
// m_list1.InsertItem(rowcount,value2,0);
|
(*resultstr).Empty();
|
for (i=0;i<fieldcount;i++)
|
{
|
field2=fs2->GetItem(_variant_t(long(i)));
|
// v1=field->GetValue();
|
name2=(LPCTSTR)field2->GetName();
|
v2= m_recordset2->GetCollect(_variant_t(name2));
|
|
if (v2.vt==VT_NULL)
|
{
|
value2.Empty();
|
l=0;
|
}
|
*/ /*
|
else if (v2.vt==VT_R8||v2.vt==VT_R4)
|
{
|
double dval1;
|
v2.ChangeType(VT_R8);
|
dval1=v2.dblVal;
|
value2.Format(_T("%f"),dval1);
|
l=value2.GetLength();
|
}
|
*/
|
/* else
|
{
|
v2.ChangeType(VT_BSTR);
|
value2=(char *)_bstr_t(v2.bstrVal);
|
l=_bstr_t(v2.bstrVal).length();
|
}
|
// m_list1.SetItemText(rowcount,i,value2);
|
if (name2==_T("Serial")) {continue;}
|
if (name2==_T("DateTime"))
|
{
|
namestr+=_T(",")+name2;
|
valuestr+=_T(",to_date('")+value2+_T("','YYYY-mm-dd hh24:MI:ss')");
|
continue;
|
}
|
namestr+=_T(",")+name2;
|
valuestr+=_T(",'")+value2+_T("'");
|
s1.Format(_T("%s=%s;"),name2,value2);
|
(*resultstr)+=s1;
|
}
|
// if (m_pRecordset->GetadoEof
|
s1.Format(_T("%s %d \r\n"),(*resultstr),(*resultstr).GetLength());
|
// Logtxt(s1);
|
m_recordset2->MoveNext();
|
rowcount++;
|
}
|
*/
|
m_recordset2->Close();
|
m_connection2->Close();
|
return k;
|
}
|
catch (_com_error e1)
|
{
|
// DB2opened=0;
|
s1.Format(_T("-mdbÊý¾Ý¿âÁ¬½Óʧ°Ü ¾ßÌåÐÅÏ¢ÈçÏÂ\r\n %s\r\n"),(char *)(e1.Description()));
|
// ::SysLog(s1);
|
return -1;
|
}
|
return 0;
|
|
}
|
|
|
