ODBC 예제

ODBC (open database connectivity)

 

STMT - Statement

DBC - Database Connectivity

ENV - Environment

 

 

[OS]

 

1. ODBC 드라이버 설치
2. DSN 등록

 

 

[Programming]

 

- 접속

 

1. 환경 핸들 생성
2. 환경 속성 설정
3. DBC 핸들 생성
4. SQL 접속
5. STMT 핸들 생성

 

 

- 실행

 

1. 쿼리문 실행
2. 쿼리문 실행 결과와 변수 바인딩
3. op. 컬럼 정보 수집
4. 결과 가져오기
5. 출력

 

 

- 종료

 

1. STMT 핸들 해제
2. SQL 접속 종료
3. DBC 핸들 해제
4. 환경 핸들 해제

 

• #include <stdio.h>
• #include <Windows.h>
• #include <tchar.h>
• #include <string>
• #include <sql.h>
• #include <sqlext.h>
• SQLHENV hEnv;
• SQLHDBC hDBC;
• SQLHSTMT hSTMT;
• SQLCHAR* ODBC_Name = (SQLCHAR*)"SQLite3 DB";
• SQLCHAR* ODBC_ID = (SQLCHAR*)" ";
• SQLCHAR* ODBC_PW = (SQLCHAR*)" ";
• bool DBConnect();
• void DBDisconnect();
• bool DBExcuteSQL();
• int _tmain(int argc, TCHAR* argv[])
• {
•     try
•     {
•         if(!DBConnect()) throw std::string("접속 에러 - 종료");
•         if(!DBExcuteSQL()) throw std::string("실행쿼리 에러");
•     }
•     catch(std::string& ex)
•     {
•         printf("%s \n", ex.c_str());
•     }
•     DBDisconnect();
•     system("pause");
•     return 0;
• }
• bool DBConnect()
• {
•     if(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv)
•         != SQL_SUCCESS)
•     {
•         return false;
•     }
•     if(SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,
•         (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER)
•         != SQL_SUCCESS)
•     {
•         return false;
•     }
•     if(SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBC)
•         != SQL_SUCCESS)
•     {
•         return false;
•     }
•     if(SQLConnect(hDBC, ODBC_Name, SQL_NTS,
•         ODBC_ID, SQL_NTS, ODBC_PW, SQL_NTS)
•         != SQL_SUCCESS)
•     {
•         return false;
•     }
•     if(SQLAllocHandle(SQL_HANDLE_STMT, hDBC, &hSTMT)
•         != SQL_SUCCESS)
•     {
•         return false;
•     }
•     return true;
• }
• void DBDisconnect()
• {
•     if(hSTMT) SQLFreeHandle(SQL_HANDLE_STMT, hSTMT);
•     if(hDBC) SQLDisconnect(hDBC);
•     if(hDBC) SQLFreeHandle(SQL_HANDLE_DBC, hDBC);
•     if(hEnv) SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
• }
• bool DBExcuteSQL()
• {
•     SQLRETURN result;
•     SQLCHAR string[128] = {0,};
•     SQLINTEGER number;
•     //result = SQLExecDirect(hSTMT,
•     (SQLCHAR*)"create table mytable(first varchar(20), second int)", SQL_NTS);
•     //if(result != SQL_SUCCESS) return false;
•     //result = SQLExecDirect(hSTMT,
•     (SQLCHAR*)"insert into mytable values('string1', 10)", SQL_NTS);
•     //if(result != SQL_SUCCESS) return false;
•     //result = SQLExecDirect(hSTMT,
•     (SQLCHAR*)"insert into mytable values('string2', 20)", SQL_NTS);
•     //if(result != SQL_SUCCESS) return false;
•     // 쿼리문 실행
•     result = SQLExecDirect(hSTMT,
•         (SQLCHAR*)"select * from mytable", SQL_NTS);
•     if(result != SQL_SUCCESS) return false;
•     // 컬럼 바인딩
•     SQLBindCol(hSTMT, 1, SQL_CHAR, string, sizeof(SQLCHAR)*128, NULL);
•     SQLBindCol(hSTMT, 2, SQL_INTEGER, &number, sizeof(SQLINTEGER), NULL);
•     // 컬럼명과 타입 정보 획득
•     SQLPOINTER columnLabels[2] = {0,};
•     SQLPOINTER columnTypes[2] = {0,};
•     SQLSMALLINT usedBufferLength = 0;
•     SQLSMALLINT columnCount = 0;
•     SQLNumResultCols(hSTMT, &columnCount);
•     for (SQLSMALLINT i = 0 ; i < 2 ; i++ )
•     {
•         columnLabels[i] = (SQLPOINTER)malloc( 64*sizeof(char) );
•         columnTypes[i] = (SQLPOINTER)malloc( 32*sizeof(char) );
•         SQLColAttribute(
•             hSTMT,
•             (SQLUSMALLINT)i + 1,
•             SQL_DESC_LABEL,
•             columnLabels[i],
•             (SQLSMALLINT)64,
•             &usedBufferLength,
•             NULL );
•         SQLColAttribute(
•             hSTMT,
•             (SQLUSMALLINT)i + 1,
•             SQL_DESC_TYPE_NAME,
•             columnTypes[i],
•             (SQLSMALLINT)32,
•             &usedBufferLength,
•             NULL );
•         printf( "Column %d | %s | %s\n",
•             i,
•             (char*)columnLabels[i],
•             (char*)columnTypes[i] );
•     }
•     // 결과 출력
•     while(SQLFetch(hSTMT) != SQL_NO_DATA)
•     {
•         printf( "[%s] %s - [%s] %d \n",
•             (char*)columnLabels[0], string,
•             (char*)columnLabels[1], number );
•     }
•     if(hSTMT) SQLCloseCursor(hSTMT);
•     // 메모리 해제
•     for (int i = 0 ; i < 2 ; i++ )
•     {
•         free(columnLabels[i]);
•         free(columnTypes[i]);
•     }
•     return true;
• }