Slow walking man

ODBC는 아마도.. Open Data Base Capability 의 약자...로?

윈도우 상에서 돌아가는 프로그램들이 DB프로그램에 종속적이지 않도록 DB와 프로그램 사이에서 인터페이스를 해주는 하나의 계층정도의 기능을 한다.

이 ODBC를 통해서 개발자는 동일한 방식으로 코딩을 해도, 변화하는 여러 DBMS에 따라 소스코드를 변경할 필요가 없어지게 되었다. 즉, 나는 ODBC계층에 명령을 내리고 ODBC는 각 DBMS 프로그램에 따라 그 명령을 해석에서 DBMS에 적합한 명령을 내려준다.

다시 말해, ODBC라는 하인에게 "가서 SELECT * FROM table 하고 와라" 라고 시키면 ODBC는 DBMS에게 가서 각 DBMS가 알아 먹을 수 있는 언어로 "주인님이 이거 하라는데요" 라고 말하는 구조다.

어찌 되었든 간에 postgresql또한 이런 ODBC계층의 업무를 수행할 드라이버를 제공하는데 이름이 psqlODBC이다. ODBC의 실체는 dll파일로 이 dll 파일에 들어 있는 바이너리 코드가 사용자의 명령을 해석하여 postgresql서버에 명령을 전송한다.

이 드라이버는 msi파일로 제공되어 그냥 더블클릭만 하면 드라이버 설치가 되고, 심지어 뭐 설정할 것도 없다. 멍하니 Next만 누르다 보면 언제깔았는지도 모를 이상한 프로그램들이 깔리는 다른 로그램과 달리 딸로 깔리는 것도 없어 Next만 눌러도 된다.

psqlOBDC는 아래 링크에서 다운 받을 수 있다.

http://www.postgresql.org/ftp/odbc/

현재 2014년 10월 버전까지 나왔는데, 그냥 최신버전을 받아 설치하면 된다.

참고로 프로그램 작성시 32비트로 코딩 했는데, 64비트 OBDC만 설치 되어 있다면 에러가 발생하니 주의하자. 64비트 ODBC만 설치 되긴 하던데 이 문제를 어떻게 해결할지는 또 열심히 찾아서 포스트 해야지...

OBDC 사용방법

OBDC를 사용함에 있어서 일단 프로그래밍 언어는 C#을 기준으로 삼겠다. 별 이유 없다. C#이 C++보다 프로그래밍을 많이 해봐서 그런지 좀더 편한것 같다. 그런데 뭐 C++로 해도 뭐 별것 없다. 필요한 헤더 포함시키고, 라이브러리 링크시키고 비슷한 함수 가지고 와서 쓰는거니 별 차이 없다.

뭐 일단, OBDC를 사용하는 방법은 크게 2가지 이다.

1번째는 DSN (Data Source Name). 즉, 데이터 원본이름을 설정하여 사용하는 방법

2번째는 DSN을 이용하지 않고 코드상에서 직접 postgresql에 접근하는 방법

1번째 방법은 윈도우 상에서 DSN을 설정해 주어야 한다.

제어판 -> 관리도구 -> 데이터 원본 (ODBC)를 더블 클릭 해보면 아래와 같이 DSN을 설정 할 수 있는 대화상자가 나타난다.

ODBC 드라이버가 정상적으로 깔려있는 상태에서 추가 버튼을 누르면, 아래와 같이 현재 컴퓨터상에 깔려 있는 Postgresql의 ODBC드라이버가 보인다.

Postgresql DB설정할 때 ANSI로 했으면 ANSI를, UNICODE로 했으면 UNICODE를 선택하면 된다. 선택이 끝나면 해당 Postgresql DB에 접근하기 위한 설정값들을 입력해야 한다.

이것도 뭐.. 직관적이다. Data Source는 DSN을 분별하는 이름으로, 이걸 가지고 DSN을 코드상에서 구분한다. 자기가 쓰기 쉬운 이름으로 정하면 되고, Description은 이 DSN에 대한 설명, Database는 Postgresql에서 설정한 DB의 이름이다. Server는 Postgresql서버의 주소이고 User Name은 해당 DB에 접근 가능한 계정정보로 Password와 함께 써 넣으면 된다. 마지막으로 port는 DB생성할 때 사용하기로 한 포트 정보를 입력해 주면 된다.

나는 envisiontest_postgres라는 이름으로 DSN을 설정했는데, 이 DSN을 가지고 이제 코드 상에서 해당 DB를 향해서 어떤 명령을 내릴 수 있다.

string connectionString = "DSN=envisiontest_postgres;"; OdbcConnection ocn = new OdbcConnection(connectionString); ocn.Open(); #region UPDATE table SET col=value WHERE criteria OdbcCommand updateCommand = new OdbcCommand("UPDATE usrTable SET usrID = 5 where usrName='아이우';", ocn); updateCommand.ExecuteNonQuery(); #endregion #region SELECT * FROM table OdbcCommand selectCommand = new OdbcCommand("SELECT * FROM usrTable", ocn); OdbcDataReader selectReader = selectCommand.ExecuteReader(); if (selectReader.HasRows) { for (int i = 0; i < selectReader.FieldCount; i++) { Console.Write(selectReader.GetName(i)+"\t"); } Console.WriteLine(""); while (selectReader.Read()) //read one record. { Console.WriteLine("{0}\t{1}\t{2}", selectReader.GetInt32(0), selectReader.GetString(1), selectReader.GetString(2)); } } else { Console.WriteLine("No rows found."); } selectReader.Close(); #endregion

그냥 ConnectionString을 "DSN=[DSN이름]"으로 해놓고 OdbcConnection 클래스의 객체를 만들 때 ConnectionString넣어 open만 해주면 미리 지정해 놓은 DSN의 DB로 접속이 된다.

참 쉽죠잉?

이 ConnectionString은 Odbc가 여러 DBMS를 관리하기 위해 여러 옵션을 두어 설정 할 수 있는데, 이 DSN을 이용한 방법은 사용자가 윈도우 상에서 DSN을 설정하고 만들어 두면 단순히 DSN이름만 가지고 어떤 DB에 접근 할 수 있다는 장점이 있지만, DSN에 설정해 놓은 DB에만 접근 할 수 있다는 단점이 있어 여러 DB에 접근해야 하는 상황이라면, 해당 DB에 대한 DSN을 일일히 구성 해 두어야 한다는 단점 또한 가지고 있다.

그럼 두번째 방법을 알아볼까?

DSN을 이용하지 않고서도 어떤 DB에 접근 할 수 있는 방법이 있다.

아까도 말했는데 바로 ConnectionString을 이용하는 방법이다.

임의의 DB에 접근해야 하므로 각 DBMS 프로그램마다 약간씩의 Connection 스트링에 차이가 있으나, 일단은 Postgresql만 살펴보자면..

"Driver={ODBC드라이버이름};

 Server=Postgresql 서버주소;

 Port=DB포트;

 Database=DB이름;

 UID=DB유저 ID;

 PWD=DB유저 PW";

먼저 Driver는 ODBC 드라이버 이름을 말한다. 아까 DSN설정할 때 봤던 ODBC 드라이버의 이름을 그대로 넣어주면 되겠다. 요번경우에는 PostgreSQL Unicode (x64)

그리고 아래 항목들은 모두 직관적인 파라미터 이니까 그냥 보면 되겠다.

이렇게 ConnectionString을 바꾸면, 그 이후 과정은 모두 동일하다. OdbcConnection을 이 ConnectionString을 인자로 생성하여 open하고 query 날리고..

복잡한 sql 쿼리 구문은 앞으로 차차 공부하여 사용하면 되고, 일단은 DB접속과 간단한 소스코드상의 사용은 이것으로 마무리 하고자 한다. 최종 목표로 pgAdmin III와 같은 매니지먼트 프로그램을 직접 작성해 보면 재미있을 것 같다.

[LEFT,RIGHT,FULL] OUTER JOIN은 각각 OUTER JOIN을 기점으로 왼쪽 오른쪽, 두가지 테이블 모두의 데이터 행중에서 ON이후에 오는 조건에 부합하지 않아도 일단은 출력행에 포함시키겠다는 의미이다.

다음을 보자.. 아참 그리고 편의상 이 테스트에 사용한 테스트용 테이블을 다시 첨부한다. 

아래 쿼리는 usrTable.usrID와 buyTable.usrID가 같은 데이터 행을 결합하여 새로운 행을 만들어 출력하되 LEFT OUTER JOIN 명령어를 기준으로 왼쪽에 있는 테이블 usrTable의 데이터 행은 무조건 다 출력하겠다는 의미이다. (붙일 다른 테이블의 데이터 행이 없으니까 그 열을 null로 열을 채워서라도..) 

따라서 아래의 출력결과와 같듯이 주문하지 않은 김언아, 손니은도 같이 출력이 되었다.

물론 나머지 컬럼은 NULL인채로..

그리고 한가지 더 유념해서 볼 것은 JOIN의 결과로 탄생한 출력 데이터 행의 갯수가 두 테이블의 조건을 만족하는 데이터 행 이 각각 a,b 라고 했을 때 max(a,b)만큼 생성된다는 것이다. 어찌보면 당연한 이야기 이지만 처음엔 좀 의아한 부분이라 적어 놓고 넘어간다.  

OUTER JOIN또한 INNER JOIN과 마찬가지로 복수개의 조건이 결합되어 사용될 수 있는데, 기본적인 방식은 INNER JOIN과 동일하다. 방법이 동일하니까 굳이 여기에 다시 적지는 않겠고, 대신에 회원 가입은 해놓고 주문은 안한 사람들의 명단을 뽑는 것으로 대신 예를 보이겠다. 여기서는 WHERE prdTable.prdID is NULL이라는 새로운 구문이 들어가 있는데 NULL인 칼럼 값을 가진 데이터 행을 찾는 구문이다.

김언아와 손니은이 주문을 안했다.

먼저, join에 관련된 학습을 하기 위해서 아래와 같은 테스트용 테이블을 작성 했다.

왜 psql.exe를 사용하지 않느냐고?

콘솔은 멋있기는 한데 사용하기가 역시 불편하다.. 테이블 확인하기도 어렵고 해서

일단은 pgAdmin III를 이용해서 간단한 sql문법은 테스트해 볼 예정이다.

뭐 어쨋든 위와 같은 테이블을 작성 해 보았다.

카페에서 음료를 판매하는 상황이라고 가정하고 아래와 같은 3개의 테이블을 작성했다.

이름, 주소등의 간단한 유저정보를 가지고 있는 usrTable,

판매하는 음료 상품정보를 가지고 있는 prdTable,

어떤 유저가 어떤 음료를 주문했는지 정보를 가지고 있는 buyTable.

INNER JOIN의 기능은 두 테이블을 합치는 기능이란다.

기본 문법은 아래와 같다.

INNER JOIN되어 형성된 테이블이 FROM다음에 들어가게 된다고 생각해도 좋을 것 같다.

그리고 INNER JOIN은 중첩되어 요청되도 된다.

위 문법에 보면 조건이 이것 저것 여러개 있는데, 적용되는 조건의 순서로 따지면

조건 1 -> 조건 2 -> 조건 3

순서로 조건이 적용된다.

조건 1, 2를 가지고 테이블을 조인하여 테이블을 만들고 이 테이블에서 where의 조건 3을

이용하여 마지막 select 문장이 실행되는 거라고 생각하면 된다.

예를 들어 김태흐가 뭘 시켜 먹었는지 알고 싶은 경우에 다음과 같은 쿼리를 요청 할 수 있다.

결과는 아래와 같다..

또 하나의 예로, 서울사람들은 어떤 음료를 마시는지 알고 싶다면..

아래와 같은 구문을 써서 결과를 알아 볼 수 도 있다.

서울 사람들은 카페모카와 아포가토를 주문했다.

이렇듯 INNER JOIN은 두 테이블이 가진 데이터 행중에 특정 조건을 만족하는 행을 이어 붙여 새로운 데이터 행을 만들어 내는 용도라고 생각하면 좋을 것 같다.