Com2meNet

같이 일하시는 분을 통해서 어떤 기관의 회원관리 프로그램을 만들어달라는 요청이 들어왔습니다. 마침 엑셀로 사용할 수 있게 작업을 해달라고 해서 VBA도 활용할 겸 간단한 엑셀 기반의 CS프로그램을 만들어 볼까 합니다. 제 본업이 이게 아니기 때문에 하루 한시간씩 투자하여 일주일 동안 진행하기로 하고 기관장님께는 넉넉하게 이번 달 내로 해드리겠다고 했습니다. 저의 계획은 DB만 호스팅 받고 엑셀VBA에서 사용하여 회원관리가 이루어지도록 하는 것입니다. 오늘은 일단 1년짜리 MS-SQL 호스팅을 신청하고 셋팅을 완료하였습니다. (이거 하나 하는데 한시간을 다 소비했네요..) 내일은 이렇게 셋팅된 DB계정에 테이블들을 생성해보도록 하겠습니다.

지난 시간에 이어서 도메인등록 작업을 하겠습니다. 우선 DDNS서비스에 접속하여 네임서버 연동이 완료되었는지 확인해 보겠습니다.

자 네임서버설정 란을 보니 OK라고 나오는게 뭔가 잘 처리된 것 같습니다. 이로써 나의 도메인(redstream.co.kr)은 DDNS서비스의 제어상태로 들어갔습니다. 이제부터는 내가 원하는 IP주소에 나의 도메인을 마음대로 연결시킬 수가 있습니다. 따라서 지금 내가 구축하고 있는 서버의 IP로 DNS설정만 바꿔주면 그 즉시 전 세계 사람들이 내 도메인으로 나의 서버에 접속할 수 있는 거죠. 하지만 지난시간에 말씀드렸듯이 유동IP를 사용하는 상황에서는 언제 내 서버의 IP주소가 바뀔지 모르기 때문에 쉬지 않고 DNS설정을 바꿔주는 기능을 추가해야 합니다. DDNS사이트에서는 이러한 기능을 수행하는 클라이언트 유틸리티를 제공하고 있으므로 이 유틸리티를 설치해보도록 하겠습니다.

클라이언트 유틸은 서버에 설치해야 합니다.(연동될 도메인을 사용하여 앞으로는 원격으로 서버를 제어할 것이므로 서버에서 직접하는 작업은 이 작업 하나 뿐입니다.) 일반PC에서 다운받은 후 이동식저장소에 담아 서버로 옮긴 후 설치하면 됩니다. 클라이언트 유틸을 서버에 설치 후 실행하면 로그인 대화상자를 거친 후 아래와 같은 화면이 나옵니다.

밑에 갱신예정IP라고 해서 서버에서 현재 사용 중이 IP가 보입니다. 이 IP와 연동시킬 도메인을 고른 후 추가 버튼을 눌러 업데이트호스트 목록에 등록 시켜 놓습니다. 이제 서버에서 사용하는 유동IP가 어떻게 바뀌든 도메인명으로 연동되는 상황이 되었습니다. 시스템속성(내컴퓨터>우클릭>속성)을 열어서 아래와 같이 원격제어를 허용하도록 합니다.

자 이로써 서버에서 할 일은 더 이상 없을 것 같습니다. 앞으로는 다른 PC에서 서버를 원격으로 제어하며 그와 동시에 서버로부터 제공 받는 서비스들을 확인하는 방식으로 작업을 해 나갈 것입니다. 서버로 사용되는 PC는 이제 본체전원과 네트워크 상태만 점검하며 기타 모니터 등은 전원을 꺼 놓습니다. 다시 작업용PC에 돌아와 원격으로 서버에 접속하여 작업을 해 보도록 하겠습니다. 보조프로그램>원격데스크탑을 실행하여 컴퓨터주소에는 우리가 등록하여 서버IP와 연동시켜 놓은 도메인명을 입력합니다.

서버에서 사용한 계정으로 윈도우 로그인을 하면 아래와 같이 작업용PC화면에 서버의 화면이 나타나게 됩니다.

자 이로써 서버와 클라이언트, 이 두 가지 역할을 한 화면에서 수행하며 작업과 결과확인을 손쉽게 진행할 수가 있게 되었습니다. 간단한 작업을 통해 서버에서 작업을 하고 클라이언트에서 확인을 해 보도록 하겠습니다. 원격화면 속에 있는 서버에서 서버관리자를 실행하고 역할을 추가해 보겠습니다. 아래와 같이 서버관리자의 역할 항목을 열어서 IIS로써의 역할을 추가합니다.

정상적으로 IIS로써의 역할이 추가되었다면 클라이언트에서 IIS웹서버로의 접속이 가능해 져야합니다. 클라이언트화면에서 웹브라우저를 실행하고 이번에는 원격제어가 아닌 웹사이트로써의 도메인주소로 접속해보겠습니다. 웹브라우저 주소창에 내 서버의 도메인을 입력해 봅니다.

네 웹서버의 메인페이지가 열렸습니다. 성공입니다.

1. 포토샵을 실행하고 잘라내기 할 이미지를 포토샵 편집화면으로 드래그 하여 불러들입니다.

2. [잘라내기(crop)툴]을 선택하여 원하는 부분을 드래그 한 후 엔터(Enter)를 누릅니다.

3. ctrl + shift + s를 눌러 [다른이름으로저장]을 실행하고 원하는 파일명으로 저장합니다.

뭐 이론보다는 실습입니다. 웹에 대해 빠르게 이해하기 위해서는 직접 웹 사이트를 구축해서 운영해 보는 것에 가장 좋습니다. 웹 사이트를 구축하려면 뭐가 있어야 할까요? 일단 서버가 있어야겠죠. 방법은 두 가지입니다. 직접 서버를 구동하거나 또는 IDC업체에 있는 서버를 임대하여 사용하거나입니다. 첫 번째 방법은 월 사용요금을 지불하지 않아도 되는 장점이 있지만 웹 서버는 고가의 장비이고 서버를 관리한다는 것 자체만으로도 많은 기술을 필요로 하기 때문에 외부에 위탁하여 안전하게 운영하는 것이 좋습니다. 하지만 실습을 한다거나 개인적인 용도로 사용한다면 월 사용료를 내는 것이 사치일 수가 있고요 고성능을 필요로 하지 않는다면 집에서 놀고 있는 낡은 PC로도 얼마든지 서버를 돌려볼 수가 있습니다. (한때 네이버에서는 네티즌들에게 다락방서버를 운영하자는 운동을 하기도 했었죠.) 자 낡은 PC에 윈도우즈(윈도우즈는 프로페셔널버전 이상이어야 됨)만 깔려 있으면 됩니다. 리눅스서버도 많이 쓰입니다만 PC마다 기본적으로 깔려있고 모두에게 익숙한 윈도우즈 운영체제로 쉽게 설명을 해 보겠습니다. (윈도우즈는 서버용이 따로 있기는 하지만 기본 골격은 같고 업무용에 비해 불필요한 것들은 생략되고 서버로 활용되기 위한 기능들이 더 있다는 것만 다릅니다.)

자 낡은PC를 가지고 윈도우즈 서버를 구축했습니다.(저는 윈도우즈서버2008R2 라는 버전으로 설치를 했습니다. 윈도XP, 윈도7 등 개인용 윈도우즈도 상관없습니다만 가능하다면 윈도2008서버나 윈도2012서버 같은 서버용 운영체제를 사용하는게 더 낫습니다.) 새로 설치해서 그런지 화면이 황량해보입니다만 서버용으로만 사용된다면 바탕화면은 나중에도 거의 이 모습일 것입니다. 자 서버는 설치가 됐고요 이제 뭐가 필요할까요? 서버를 운영하려면 도메인이 필요합니다. 우리들은 서버에 접속하기 위해 고유의 주소값인 IP 이외에 'google.com' 이나 'naver.com' 같은 고유의 도메인명을 사용하지요. 그럼 도메인명을 하나 만들어서 내 서버의 IP주소와 연결해보도록 하겠습니다. 내 서버의 IP가 일정하다면 참 좋겠지만 ISP라고 하는 인터넷 회선을 제공하는 업체(KT나 SK 등)로부터 IP를 할당받아 사용하는 우리들의 현실은 그렇지 못합니다. ISP업체의 사정으로 IP주소가 계속 바뀌는 거죠. 언제 바뀔지 모르는 IP주소를 도메인명과 연결시키기 위해서 우리는 DDNS라는 서비스를 받도록 하겠습니다. 저는 kr.dnsever.com 이라는 사이트를 통해 DDNS서비스를 사용하도록 할 것입니다.

우선 kr.dnsever.com 에 접속하여 가입 후 로그인을 하면 아래와 같은 화면이 보입니다.

일단 우리가 필요한 것은 제가 붉은선으로 표시해 놓은 네임서버입니다. 1차부터 5차까지의 네임서버주소와 IP를 메모해 놓습니다. 그 다음 도메인명을 하나 등록해보겠습니다. 도메인명은 자유롭게 정하면 되지만 누군가 등록해 놓은 것은 사용할 수 없기에(웬만한 좋은 이름들은 거의 다 등록되어 있을 겁니다.) 약간의 고민이 필요한 부분입니다. 갑자기 도메인등록을 하려니 생각나는 것이 없네요. 저는 지금 홍천에 있으므로 그냥 redstream.com 이라고 이름을 지어보았습니다. 국제적으로 도메인명은 NIC라는 기관에서 관리하고 있고 우리는 국내 등록대행업체를 통해 도메인명을 등록할 수 있습니다. 저는 gabia.com이라는 등록대행사이트를 통해 도메인등록을 해보겠습니다.

www.gabia.com에 접속해 보면 도메인명을 찾아볼 수 있는 검색 창이 보입니다. 저는 redstream.com 이라는 도메인이 필요하므로 redsteam이라고 검색해 보겠습니다.

검색결과가 아래와 같이 나왔습니다.

역시 웬만한 이름은 다 사용 중이네요 redstream.com을 사용하지는 못할 것 같습니다. 계획을 수정해야겠습니다. 그래도 아래에 보니 다행스럽게 redstream.co.kr이라는 한국도메인은 사용가능합니다. 그럼 redstream.co.kr을 등록해 보겠습니다. redstream.co.kr에만 체크하시고 등록버튼은 누르면 등록의뢰를 할 수 있습니다. 회원가입을 하고 신청서 폼을 작성하면 되는데 폼에 네임서버정보를 입력하는 부분에서 자체네임서버 대신 타사 네임서버를 사용하겠다 하시고 아까 메모해 놓은 kr.dnsever.com 사이트에서 제공하는 네임서버 정보를 입력하시면 됩니다.

자 등록 신청서 작성을 완료하고 수수료를 결제하면 이 도메인명은 신청기간 동안 내 소유가 됩니다. 도메인등록은 완료되었지만 전 세계에 있는 DNS서버에 나의 도메인정보가 전달되어야 하므로 도메인명과 네임서버가 연동되려면 시간이 좀 걸립니다. 아쉽지만 오늘은 이만 마치고 네임서버가 연동되면 다시 공부를 하겠습니다.

개발 도중에는 쿼리를 검증하거나 중간결과를 확인하기가 좀 불편한 경우가 있습니다.

간단한 데이터들을 간편하게 가공할때는 엑셀이 참 편리하죠.

엑셀로 쿼리를 날려 결과물을 시트에 채워넣는 방법으로 DB를 조회할 수 있다면 참 편하곘죠.

그렇다고 거창하게 VSTO나 VBA로 코딩을 하면 오히려 일이 커집니다.

간편하게 사용자 함수를 정의해서 쿼리문을 인수로 넣고 결과물을 배열로 반환 받는다면 어떨까요?

자 티스토리 오픈기념으로 저만의 노하우를 하나 공개합니다.

분량에 맞게 미리 영역을 지정해 놓고 입력은 배열수식형태로 해야합니다.
(즉 함수식을 입력하고 ctrl + shift + Enter 를 누르셔야 합니다.)

예를 들면 =req_sql("SELECT * FROM table") 이런 식으로 활용하시면 됩니다.

제가 만든 req_sql() 사용자정의 함수의 코드는 아래와 같습니다.

컨넥션 스트링만 DB종류 및 계정에 맞게 설정해서 사용하시면 됩니다.
 (저는 ODBC를 통해 Mysql서버로 연결해봤습니다.)

Function req_sql(sql As String)

    '================= DB연결 및 Recordset정의 =======================
    Dim db_con As ADODB.Connection
    Set db_con = New ADODB.Connection
    Dim str_con As String
    str_con = "DSN=mydb;UID=root;PWD=admin;"
    db_con.Open str_con
    
    Dim adoRS As ADODB.Recordset
    Set adoRS = New ADODB.Recordset
    Set adoRS = db_con.Execute(sql)
    
    '================= Index변수 및 배열정의 =======================
    Dim r, c As Integer
    Dim r_cnt, c_cnt As Integer
    c_cnt = adoRS.Fields.Count
    adoRS.MoveFirst
    Do While adoRS.EOF = False
        r_cnt = r_cnt + 1
        adoRS.MoveNext
    Loop
    
    Dim var() As Variant
    ReDim var(0 To r_cnt, 0 To c_cnt - 1)
    
    '================= 배열에 필드명 및 데이터 채우기 =======================
    For c = 0 To c_cnt - 1
        var(0, c) = adoRS.Fields.Item(c).Name
    Next c
    
    adoRS.MoveFirst
    For r = 1 To r_cnt '행단위탐색
        For c = 0 To c_cnt - 1 '열단위 탐색
            If (IsError(adoRS(c).Value) = False) Then
                var(r, c) = adoRS(c).Value '배열 요소에 데이터 입력
            End If
        Next c
        adoRS.MoveNext
    Next r
    
    '================= 결과물 반환 =======================
    req_sql = var
    
    '================= DB연결 및 Recordset 반환 =======================
    adoRS.Close
    db_con.Close
    Set adoRS = Nothing
    Set db_con = Nothing
    
End Function