[DB] DAO test source

//참고
Data Programming with Microsoft Access 2010
http://msdn.microsoft.com/en-us/library/office/ff965871%28v=office.14%29.aspx

AC2010DataProgramming.zip

DAO(데이터 액세스 개체)
http://msdn.microsoft.com/ko-kr/library/cc438702%28v=VS.71%29.aspx

http://yamoe.tistory.com/m/post/view/id/185

//================================
void CSimpleTestDlg::DaoTest()
{
    int ret =0;
    CDaoDatabase* database = NULL;
    CDaoTableDef* tableDef = NULL;
    
    TCHAR mdb[256] = _T("d:\\test.mdb");
 
    TRY
    {
        //db 오픈
        database = new CDaoDatabase;
        database->Open(mdb);
 
        {
            //테이블 개수 확인
            int tableCnt = database->GetTableDefCount();
 
            CString strTableCnt;
            strTableCnt.Format(_T("테이블 개수 : %d"), tableCnt);
            _DbgStr(strTableCnt);
 
 
            //전체 테이블 목록보기
            CString tableNames;
            for (int i=0; i<tableCnt; i++)
            {
                //테이블 정보 가져오기
                CDaoTableDefInfo tableDefInfo;
                database->GetTableDefInfo(i, tableDefInfo, AFX_DAO_ALL_INFO);    //첫번째 테이블
 
                if (tableDefInfo.m_lAttributes & dbSystemObject)    //시스템 테이블인 경우
                    tableNames += _T("시스템 테이블 : ");
                else
                    tableNames += _T("사용자 테이블 : ");
 
                tableNames += tableDefInfo.m_strName;   //테이블 이름
                tableNames += _T("\n");
            }
            _DbgStr(tableNames);
        }
 
 
        //테이블 정보 보기
        {
            tableDef = new CDaoTableDef(database);
            tableDef->Open(_T("tbl_test"));    //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;
            _DbgStr(createTime);
 
            //테이블 마지막 수정일
            COleDateTime oleUTime = tableDef->GetDateLastUpdated();
            CString lastUpdatedTime;
            lastUpdatedTime.Format(_T("수정일: %04d-%02d-%02d\n"), oleUTime.GetYear(), oleUTime.GetMonth(), oleUTime.GetDay());
            info += lastUpdatedTime;
            _DbgStr(lastUpdatedTime);
            
            //테이블 레코드 수
            CString recordCnt;
            recordCnt.Format(_T("레코드수: %ld\n"), tableDef->GetRecordCount());
            info += recordCnt;
            _DbgStr(recordCnt);
 
            //테이블 필드 출력
            info += _T("필드정보\n");
            CDaoFieldInfo fieldInfo;
            int fieldCnt = tableDef->GetFieldCount();
            for (int i=0; i<fieldCnt; i++)
            {
                tableDef->GetFieldInfo(i, fieldInfo, AFX_DAO_PRIMARY_INFO   );
                _DbgStr(fieldInfo.m_strName);
                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");
            }
            //_DbgStr(info);
 
            //인덱스 필드 찾기
            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");
                }
            }
 
            //_DbgStr(info);
        }
        
        CString str, sTemp;
        _DbgStr(_T("쿼리실행"));
        //쿼리 실행
        {
            CDaoRecordset rs(database);
            //rs.Open(AFX_DAO_USE_DEFAULT_TYPE, _T("select count(*) as cnt from tbl_test"));    //쿼리 실행
            rs.Open(AFX_DAO_USE_DEFAULT_TYPE, _T("select * from tbl_test"));   

            COleVariant var;
            //rs.GetFieldValue(_T("cnt"), var);   //쿼리 값 획득
            //rs.GetFieldValue(_T("test_col1"), var);
            //CString value = convertString(var);            _DbgStr(value);

            while (!rs.IsEOF())
            {
                int cnt = rs.GetFieldCount();
                COleVariant var;
                str=_T("");
                for (int i=0; i<cnt; i++)
                {
                    rs.GetFieldValue(i, var);   
                    sTemp = var;
                    str += convertString(var);    str += _T("  ");
                    //rst += convertString(var);                    rst += _T(", ");
                }
                _DbgStr(str);    str =_T("");
                //rst += _T("\n");
                rs.MoveNext();  //scroll
            }

            rs.Close();
        }
 
        _DbgStr(_T("바인딩 쿼리실행"));
        //바인딩 쿼리 실행 : select
        {
            //바인딩 형식의 쿼리 저장
            CDaoQueryDef qd(database);
            /* qd.Create()의 첫번째 인수의 문자로 쿼리가 저장되며 qd.Append()로 저장이 완료됨.
            (영구저장되는 위치가 레지스트리 같긴한데 잘 모르겠습니다.)
            한번쓰는 임시 쿼리인 경우 NULL을 준후 qd.Append()를 안해주면 됩니다.*/
            qd.Create(NULL, _T("PARAMETERS [binding 1] INT; select * from tbl_test where 1 = [binding 1]"));
            //qd.Append();  //영구적인 쿼리 저장
            
            //바인딩
            COleVariant binding1((short)1);
            qd.SetParamValue(_T("[binding 1]"), binding1);
 
            //쿼리 실행
            CDaoRecordset rs(database);
            rs.Open(&qd);
 
            CString rst;
            while (!rs.IsEOF())
            {
                int cnt = rs.GetFieldCount();
                COleVariant var;
                str=_T("");
                for (int i=0; i<cnt; i++)
                {
                    rs.GetFieldValue(i, var);
                   
                    str += convertString(var);    str += _T("  ");

                    rst += convertString(var);
                    rst += _T(", ");
                }
                _DbgStr(str);
                str =_T("");
               

                rst += _T("\n");
                rs.MoveNext();  //scroll
            }
           // _DbgStr(rst);
 
            qd.Close();
            //qd.m_pDAOQueryDef->Close();
        }
    }
    CATCH (CException, e) //예외처리 : 정확히는 CDaoException이지만 어짜피 CException 상속 클래스임.
    {
        TCHAR szCause[256];
        e->GetErrorMessage(szCause, 256);
        _DbgStr(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;
    }
}
 
BOOL CSimpleTestDlg::DaoQuery()
{
    int ret =0;
    CDaoDatabase* database = NULL;
    //CDaoTableDef* tableDef = NULL;
    
    TCHAR mdb[256] = _T("d:\\test.mdb");
 
    TRY
    {
        //db 오픈
        database = new CDaoDatabase;
        database->Open(mdb);       
        
        CString str, sTemp;
        _DbgStr(_T("쿼리실행"));
        //쿼리 실행
        {
            CDaoRecordset rs(database);
            //rs.Open(AFX_DAO_USE_DEFAULT_TYPE, _T("select count(*) as cnt from tbl_test"));    //쿼리 실행
            rs.Open(AFX_DAO_USE_DEFAULT_TYPE, _T("select * from tbl_test"));   

            COleVariant var;
            //rs.GetFieldValue(_T("cnt"), var);   //쿼리 값 획득
            //rs.GetFieldValue(_T("test_col1"), var);
            //CString value = convertString(var);            _DbgStr(value);

            while (!rs.IsEOF())
            {
                int cnt = rs.GetFieldCount();
                COleVariant var;
                str=_T("");
                for (int i=0; i<cnt; i++)
                {
                    rs.GetFieldValue(i, var);   
                    sTemp = var;
                    str += convertString(var);    str += _T("  ");
                    //rst += convertString(var);                    rst += _T(", ");
                }
                _DbgStr(str);    str =_T("");
                //rst += _T("\n");
                rs.MoveNext();  //scroll
            }

            rs.Close();
        }
 
       
    }
    CATCH (CException, e) //예외처리 : 정확히는 CDaoException이지만 어짜피 CException 상속 클래스임.
    {
        TCHAR szCause[256];
        e->GetErrorMessage(szCause, 256);
        _DbgStr(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;
    }

    return 1;
}
 

 
 
//COleVariant를 CString으로 변환해줄 함수
CString CSimpleTestDlg::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;
}