ODBC (open database connectivity)

ODBC (open database connectivity) 로 Visual C++과 mysql 연동.

**. 샘플 정보

DSN 이름 : mysqld database : test UID/PWD : root/root drop table TEST; create table TEST ( t_str varchar(10), t_blob blob );   insert into TEST values ('hi~`, 'hi man~~~~');

ODBC

.mdb는 dynaset 가능하나 mysql 의 경우 지원하지 않으므로 snapshot 사용.

mysql용 connector는 mysql 설치시 customize 모드로 같이 설치 하던가

mysql.com 에서 Connector/ODBC 설치 후 

DSN 등록 (관리도구 > ODBC 관리자)

ODBC 샘플

transaction 처리(commit, rollback 지원 안됨.)

transaction 처리를 위해선 아래 CDatabase 클래스를 사용해야 함.

1. 직접 연결

SQLTCHAR InCon[1024] = _T("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=root;PWD=root1"); SQLTCHAR OutCon[1024]; SQLSMALLINT cbOutCon; SQLRETURN sqlReturn = SQLDriverConnect(hDbc, NULL, InCon, sizeof(InCon), OutCon, sizeof(OutCon), &cbOutCon, SQL_DRIVER_NOPROMPT); if (sqlReturn != SQL_SUCCESS && sqlReturn != SQL_SUCCESS_WITH_INFO) printError(SQL_HANDLE_DBC, hDbc);

2. ODBC dns사용

SQLRETURN sqlReturn = SQLConnect(hDbc, _T("mysqlds"), SQL_NTS, _T("root"), SQL_NTS, _T("root"), SQL_NTS); if (sqlReturn != SQL_SUCCESS && sqlReturn != SQL_SUCCESS_WITH_INFO) printError(SQL_HANDLE_DBC, hDbc);

3. 샘플

ODBCsample.cpp

**. 참고 

    ODBC DSN 등록 을 프로그래밍으로 할 수 있게 SQLConfigDataSource() 함수 지원하고 있음.

MFC ODBC 

CDatabase, CRecordset class

CDatabase의 BeginTrans, CommitTrans, Rollback 으로 Transaction 처리 가능.

void testDB(void) { CString strConn = _T("ODBC;DATABASE=mysql;DSN=mysqlds;UID=root;PWD=root"); CDatabase db; try { db.OpenEx(strConn, CDatabase::noOdbcDialog); if (db.CanTransact()) db.BeginTrans(); //트랜잭션 시작 db.ExecuteSQL(_T("insert into test.test (t_str) values ('111')")); db.ExecuteSQL(_T("insert into test.test (t_str) values ('222')")); if (db.CanTransact()) db.Rollback(); //트랜잭션 끝 //또는 db.CommitTrans(); } catch (CDBException* e) { CString errMsg; errMsg.Format(_T("[ERROR] retcode : %d \n%s \n%s"), e->m_nRetCode, e->m_strError, e->m_strStateNativeOrigin); AfxMessageBox(errMsg); e->Delete(); } if (db.IsOpen()) db.Close(); }

blob, text 같은 DB data type을 사용할 경우 제대로 지원이 안되는 것 같다.

blob select는 되는데 insert/update에서 에러 없이 안되어서 포기.

인터넷에 동작하는 예제도 못찾음.

1. MFC project 생성시 자동 생성

"Database Support" 에서 

Database support : "Database view without file support" 혹은 "Database view with file support" 선택

Client type : "ODBC" 선택

Data source : "Data Source..." 버튼에서 해당 DSN 선택

Type : "Snapshot" 선택 (.mdb 같은 경우 Dynaset 가능)

View class에서 m_pSet 변수를 통해 사용.

void CCTestRecordsetView::selectSQL(void) { CString output(_T("")); m_pSet->m_strFilter.Empty(); m_pSet->Requery(); if (m_pSet->IsBOF()) return; while (!m_pSet->IsEOF()) { output += _T("t_str\t: "); output += m_pSet->m_t_str; output += _T("\nt_blob\t: "); //blob select if (m_pSet->m_t_blob.m_dwDataLength != 0) { CLongBinary* blobTmp = &m_pSet->m_t_blob; char* blobChar = (LPSTR)GlobalLock(blobTmp->m_hData); TCHAR* blobTchar = new TCHAR[blobTmp->m_dwDataLength+1]; int len = MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, blobChar, blobTmp->m_dwDataLength, NULL, NULL);  MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, blobChar, blobTmp->m_dwDataLength, blobTchar, len); blobTchar[len] = 0; CString t_blobStr = blobTchar; delete blobTchar; GlobalUnlock(blobChar); output += t_blobStr; } output += _T("\n\n"); m_pSet->MoveNext(); } m_pSet->MoveLast(); AfxMessageBox(output); } void CCTestRecordsetView::updateSQL(void) { m_pSet->m_strFilter = _T("t_str like 'insert1%'"); m_pSet->Requery(); while (!m_pSet->IsEOF()) { m_pSet->Edit(); m_pSet->m_t_str = _T("insertA"); //blob update 방법 못찾음. m_pSet->Update(); m_pSet->MoveNext(); } m_pSet->Requery(); } void CCTestRecordsetView::insertSQL(void) { m_pSet->MoveLast(); m_pSet->AddNew(); m_pSet->SetFieldNull(NULL); m_pSet->m_t_str = _T("insert1"); //blob insert 방법 못찾음. m_pSet->Update(); m_pSet->Requery(); } void CCTestRecordsetView::deleteSQL(void) { m_pSet->m_strFilter = _T("t_str like 'insertA%'"); m_pSet->Requery(); while (!m_pSet->IsEOF()) { m_pSet->Delete(); m_pSet->MoveNext(); } } void CCTestRecordsetView::OnBnClickedButton1() { try { insertSQL(); selectSQL(); updateSQL(); selectSQL(); deleteSQL(); selectSQL(); } catch (CDBException* e) { CString errMsg; errMsg.Format(_T("[ERROR] retcode : %d \n%s \n%s"), e->m_nRetCode, e->m_strError, e->m_strStateNativeOrigin); AfxMessageBox(errMsg); e->Delete(); } }

2. MFC ODBC Consumer 생성 (wizard 사용)

Class View에서 오른쪽 마우스 클릭

"Add > Class..." 선택 후

"Visual C++ > MFC > MFC ODBC Consumer" 선택

Data source, Class 명, Type(snapshot) 등을 지정해주면 

MFC project 생성시 처럼 CRecordset을 상속한 파생 클래스가 생기며 이를 사용.

3. 직접 코딩 coding

간단한 select 샘플. (varchar(10), blob)

void test(void) { CDatabase database; CRecordset rs; CString connStr = _T("ODBC;DATABASE=mysql;DSN=mysqlds;"); try { database.OpenEx(connStr, CDatabase::noOdbcDialog); database.ExecuteSQL(_T("set names euckr")); rs.m_pDatabase = &database; //select CString query = _T("select t_str,t_blob from test.test"); rs.Open(CRecordset::forwardOnly, query ,CRecordset::readOnly); CString output = _T("[SELECT]\n"); while (!rs.IsEOF()) { CString t_str     = _T(""); CString t_blobStr = _T(""); CDBVariant t_blob; rs.GetFieldValue(_T("t_str"), t_str); rs.GetFieldValue(_T("t_blob"), t_blob); //blob if (t_blob.m_dwType != DBVT_NULL) { CLongBinary* blobTmp = t_blob.m_pbinary; char* blobChar = (LPSTR)GlobalLock(blobTmp->m_hData); TCHAR* blobTchar = new TCHAR[blobTmp->m_dwDataLength+1]; int len = MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, blobChar, blobTmp->m_dwDataLength, NULL, NULL);   MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, blobChar, blobTmp->m_dwDataLength, blobTchar, len); blobTchar[len] = 0; t_blobStr = blobTchar; delete blobTchar; GlobalUnlock(blobChar); } output += t_str; output += _T("\n"); output += t_blobStr; output += _T("\n\n"); rs.MoveNext(); } AfxMessageBox(output); if (rs.IsOpen()) rs.Close(); } catch (CDBException* e) { CString errMsg; errMsg.Format(_T("[ERROR] retcode : %d \n%s \n%s"), e->m_nRetCode, e->m_strError, e->m_strStateNativeOrigin); AfxMessageBox(errMsg); } if (rs.IsOpen()) rs.Close(); if (database.IsOpen()) database.Close(); }