void CTXTTEST00Dlg::OnBnClickedButton3()
{
//CADOConnect file;
//file.OpenMDB(L"D:\\검수\\20201105mdb대소문자변경");
CDaoDatabase* database = NULL;
CDaoTableDef* tableDef = NULL;
TCHAR mdb[256] = _T("D:\\검수\\20201105mdb대소문자변경\\Camera.mdb");
TRY
{
//db 오픈
database = new CDaoDatabase;
database->Open(mdb);
{
//테이블 개수 확인
int tableCnt = database->GetTableDefCount();
CString strTableCnt;
strTableCnt.Format(_T("테이블 개수 : %d"), tableCnt);
//AfxMessageBox(strTableCnt);
//전체 테이블 목록보기
CString tableNames;
for (int i = 0; i < tableCnt; i++)
{
//테이블 정보 가져오기
CDaoTableDefInfo tableDefInfo;
database->GetTableDefInfo(i, tableDefInfo, AFX_DAO_ALL_INFO); //첫번째 테이블
//CDaoQueryDefInfo quryinfo;
//database->GetQueryDefInfo(i, quryinfo, AFX_DAO_PRIMARY_INFO);
if (tableDefInfo.m_lAttributes & dbSystemObject) //시스템 테이블인 경우
tableNames += _T("시스템 테이블 : ");
else
tableNames += _T("사용자 테이블 : ");
tableNames += tableDefInfo.m_strName; //테이블 이름
tableNames += _T("\n");
}
AfxMessageBox(tableNames);
}
//테이블 정보 보기
{
tableDef = new CDaoTableDef(database);
tableDef->Open(_T("file1")); //album이라는 테이블
CString info = _T("");
//테이블 이름
info += tableDef->GetName();
info += _T("\n");
//테이블 생성일
COleDateTime oleCTime = tableDef->GetDateCreated();
CString createTime;
createTime.Format(_T("생성일: %04d-%02d-%02d\n"), oleCTime.GetYear(), oleCTime.GetMonth(), oleCTime.GetDay());
info += createTime;
//테이블 마지막 수정일
COleDateTime oleUTime = tableDef->GetDateLastUpdated();
CString lastUpdatedTime;
lastUpdatedTime.Format(_T("수정일: %04d-%02d-%02d\n"), oleUTime.GetYear(), oleUTime.GetMonth(), oleUTime.GetDay());
info += lastUpdatedTime;
//테이블 레코드 수
CString recordCnt;
recordCnt.Format(_T("레코드수: %ld\n"), tableDef->GetRecordCount());
info += recordCnt;
//테이블 필드 출력
info += _T("필드정보\n");
CDaoFieldInfo fieldInfo;
int fieldCnt = tableDef->GetFieldCount();
for (int i = 0; i < fieldCnt; i++)
{
tableDef->GetFieldInfo(i, fieldInfo, AFX_DAO_ALL_INFO);
info += _T("\t");
info += fieldInfo.m_strName; //필드 이름
info += _T(" : ");
short nType = fieldInfo.m_nType; //필드 타입
switch (nType)
{
case dbBoolean: info += _T("BOOLEAN"); break;
case dbByte: info += _T("BYTE"); break;
case dbInteger: info += _T("INTEGER"); break;
case dbLong: info += _T("LONG"); break;
case dbSingle: info += _T("SINGLE"); break;
case dbCurrency: info += _T("CURRENCY"); break;
case dbDate: info += _T("DATE"); break;
case dbDouble: info += _T("DOUBLE"); break;
case dbText: info += _T("TEXT"); break;
case dbLongBinary: info += _T("LONGBINARY"); break;
case dbMemo: info += _T("MEMO"); break;
}
info += _T("\n");
}
//인덱스 필드 찾기
info += _T("인덱스 필드\n");
CDaoIndexInfo indexInfo;
int indexCnt = tableDef->GetIndexCount();
for (int i = 0; i < indexCnt; i++)
{
tableDef->GetIndexInfo(i, indexInfo);
for (int j = 0; j < indexInfo.m_nFields; j++)
{
CString indexField = indexInfo.m_pFieldInfos[j].m_strName;
info += _T("\t");
info += indexField;
info += _T("\n");
}
}
AfxMessageBox(info);
}
//쿼리 실행
{
CDaoRecordset rs(database);
rs.Open(AFX_DAO_USE_DEFAULT_TYPE, _T("select count(*) as cnt from file1")); //쿼리 실행
COleVariant var;
rs.GetFieldValue(_T("cnt"), var); //쿼리 값 획득
rs.Close();
CString value = convertString(var);
AfxMessageBox(value);
}
//바인딩 쿼리 실행 : select
{
//바인딩 형식의 쿼리 저장
CDaoQueryDef qd(database);
/* qd.Create()의 첫번째 인수의 문자로 쿼리가 저장되며 qd.Append()로 저장이 완료됨.
(영구저장되는 위치가 레지스트리 같긴한데 잘 모르겠습니다.)
한번쓰는 임시 쿼리인 경우 NULL을 준후 qd.Append()를 안해주면 됩니다.*/
qd.Create(NULL, _T("PARAMETERS [binding 1] INT; select * from file1 where 1 = [binding 1]"));
//qd.Append(); //영구적인 쿼리 저장
//바인딩
COleVariant binding1((short)1);
qd.SetParamValue(_T("[binding 1]"), binding1);
//쿼리 실행
CDaoRecordset rs(database);
rs.Open(&qd);
CString rst;
int tempcntz = 0;
while (!rs.IsEOF())
{
tempcntz++;
if (tempcntz > 20000)
{
string zztemp = "123";
}
int cnt = rs.GetFieldCount();
COleVariant var;
for (int i = 0; i < cnt; i++)
{
rs.GetFieldValue(i, var);
rst += convertString(var);
rst += _T(", ");
}
rst += _T("\n");
rs.MoveNext(); //scroll
}
AfxMessageBox(rst);
qd.Close();
//qd.m_pDAOQueryDef->Close();
}
}
CATCH(CException, e) //예외처리 : 정확히는 CDaoException이지만 어짜피 CException 상속 클래스임.
{
TCHAR szCause[256];
e->GetErrorMessage(szCause, 256);
AfxMessageBox(szCause, MB_ICONERROR);
}
END_CATCH
if (tableDef != NULL)
{
if (tableDef->IsOpen()) tableDef->Close();
delete tableDef;
tableDef = NULL;
}
if (database != NULL)
{
if (database->IsOpen()) database->Close();
delete database;
database = NULL;
}
}
- 중간에 mdb에서 가져온 COlecVariant를 CString으로 바꿔주는 함수
//COleVariant를 CString으로 변환해줄 함수
CString convertString(COleVariant& var)
{
CString value;
switch (var.vt) //COleVariant를 CString으로 변경
{
case VT_EMPTY:
case VT_NULL: value = _T("NULL"); break;
case VT_I2: value.Format(_T("%hd"), V_I2(&var)); break;
case VT_I4: value.Format(_T("%d"), V_I4(&var)); break;
case VT_R4: value.Format(_T("%e"), (double)V_R4(&var)); break;
case VT_R8: value.Format(_T("%e"), V_R8(&var)); break;
case VT_CY: value = COleCurrency(var).Format(); break;
case VT_DATE: value = COleDateTime(var).Format(_T("%m %d %y")); break;
case VT_BSTR: value = V_BSTRT(&var); break;
case VT_DISPATCH: value = _T("VT_DISPATCH"); break;
case VT_ERROR: value = _T("VT_ERROR"); break;
case VT_BOOL: V_BOOL(&var) ? value = _T("TRUE") : value = _T("FALSE"); break;
case VT_VARIANT: value = _T("VT_VARIANT"); break;
case VT_UNKNOWN: value = _T("VT_UNKNOWN"); break;
case VT_I1: value = _T("VT_I1"); break;
case VT_UI1: value.Format(_T("0x%02hX"), (unsigned short)V_UI1(&var)); break;
case VT_UI2: value = _T("VT_UI2"); break;
case VT_UI4: value = _T("VT_UI4"); break;
case VT_I8: value = _T("VT_I8"); break;
case VT_UI8: value = _T("VT_UI8"); break;
case VT_INT: value = _T("VT_INT"); break;
case VT_UINT: value = _T("VT_UINT"); break;
case VT_VOID: value = _T("VT_VOID"); break;
case VT_HRESULT: value = _T("VT_HRESULT"); break;
case VT_PTR: value = _T("VT_PTR"); break;
case VT_SAFEARRAY: value = _T("VT_SAFEARRAY"); break;
case VT_CARRAY: value = _T("VT_CARRAY"); break;
case VT_USERDEFINED: value = _T("VT_USERDEFINED"); break;
case VT_LPSTR: value = _T("VT_LPSTR"); break;
case VT_LPWSTR: value = _T("VT_LPWSTR"); break;
case VT_FILETIME: value = _T("VT_FILETIME"); break;
case VT_BLOB: value = _T("VT_BLOB"); break;
case VT_STREAM: value = _T("VT_STREAM"); break;
case VT_STORAGE: value = _T("VT_STORAGE"); break;
case VT_STREAMED_OBJECT: value = _T("VT_STREAMED_OBJECT"); break;
case VT_STORED_OBJECT: value = _T("VT_STORED_OBJECT"); break;
case VT_BLOB_OBJECT: value = _T("VT_BLOB_OBJECT"); break;
case VT_CF: value = _T("VT_CF"); break;
case VT_CLSID: value = _T("VT_CLSID"); break;
}
WORD vt = var.vt;
if (vt & VT_ARRAY)
{
vt = vt & ~VT_ARRAY;
value = _T("Array of ");
}
if (vt & VT_BYREF)
{
vt = vt & ~VT_BYREF;
value += _T("Pointer to ");
}
if (vt != var.vt)
{
switch (vt)
{
case VT_EMPTY: value += _T("VT_EMPTY"); break;
case VT_NULL: value += _T("VT_NULL"); break;
case VT_I2: value += _T("VT_I2"); break;
case VT_I4: value += _T("VT_I4"); break;
case VT_R4: value += _T("VT_R4"); break;
case VT_R8: value += _T("VT_R8"); break;
case VT_CY: value += _T("VT_CY"); break;
case VT_DATE: value += _T("VT_DATE"); break;
case VT_BSTR: value += _T("VT_BSTR"); break;
case VT_DISPATCH: value += _T("VT_DISPATCH"); break;
case VT_ERROR: value += _T("VT_ERROR"); break;
case VT_BOOL: value += _T("VT_BOOL"); break;
case VT_VARIANT: value += _T("VT_VARIANT"); break;
case VT_UNKNOWN: value += _T("VT_UNKNOWN"); break;
case VT_I1: value += _T("VT_I1"); break;
case VT_UI1: value += _T("VT_UI1"); break;
case VT_UI2: value += _T("VT_UI2"); break;
case VT_UI4: value += _T("VT_UI4"); break;
case VT_I8: value += _T("VT_I8"); break;
case VT_UI8: value += _T("VT_UI8"); break;
case VT_INT: value += _T("VT_INT"); break;
case VT_UINT: value += _T("VT_UINT"); break;
case VT_VOID: value += _T("VT_VOID"); break;
case VT_HRESULT: value += _T("VT_HRESULT"); break;
case VT_PTR: value += _T("VT_PTR"); break;
case VT_SAFEARRAY: value += _T("VT_SAFEARRAY"); break;
case VT_CARRAY: value += _T("VT_CARRAY"); break;
case VT_USERDEFINED: value += _T("VT_USERDEFINED"); break;
case VT_LPSTR: value += _T("VT_LPSTR"); break;
case VT_LPWSTR: value += _T("VT_LPWSTR"); break;
case VT_FILETIME: value += _T("VT_FILETIME"); break;
case VT_BLOB: value += _T("VT_BLOB"); break;
case VT_STREAM: value += _T("VT_STREAM"); break;
case VT_STORAGE: value += _T("VT_STORAGE"); break;
case VT_STREAMED_OBJECT: value += _T("VT_STREAMED_OBJECT"); break;
case VT_STORED_OBJECT: value += _T("VT_STORED_OBJECT"); break;
case VT_BLOB_OBJECT: value += _T("VT_BLOB_OBJECT"); break;
case VT_CF: value += _T("VT_CF"); break;
case VT_CLSID: value += _T("VT_CLSID"); break;
}
}
return value;
}