ODBC를 이용한 Select 쿼리 에이전트 원형 I

ODBC를 이용하여 RDB의 데이터를 쿼리해오는 경우가 빈번히 발생하고 있습니다.
아래의 코드는 반복적으로 Select 구문을 소모적으로 만들다 보니 시간도 비교적 많이 걸리게 되더군요.
앞으로 지속적으로 업데이트 해 나가야 겠지만...
1단계로 정리된 코드가 있어 기록해 둡니다.
---------------------------------------------------------------------------------------------
*********
(Options)
*********
Option Public
Uselsx "*LSXODBC"
*************
(Declarations)
*************
Dim ss As NotesSession
Dim curDb As NotesDatabase
Dim curDoc As NotesDocument
Dim curAgent As NotesAgent                                '// 현재 Agent
Dim con As ODBCConnection
Dim query As ODBCQuery
Dim result As ODBCResultSet
Const SQL_DNS                 = "Domino"                '// ODBC DNS 이름
Const SQL_ID                 = "UserID"                '// ODBC USER ID
Const SQL_PASSWORD         = "Password"        '//  ODBC USER PASSWORD
Const SQL_TABLE_SCHEMA        = "dbo"                '// Table 스키마명
Const SQL_TABLE                = "table"                '// TABLE 이름
'//★ 아래의 코드는 RDB Table과 같은 형식이어야 함. ( 코드마다 틀리게 구성해야 함. )
Public Type table
        r1 As String        '// 필드 1
        r2 As String        '// 필드 2
        r3 As String        '// 필드 3
        r4 As String        '// 필드 4
        r5 As String        '// 필드 5
        '// Query중 포함되지 않는 열을 지정하기 위한 멤버
        n_r1 As String        '// NOT 필드 1
        n_r2 As String        '// NOT 필드 2
        n_r3 As String        '// NOT 필드 3
        n_r4 As String        '// NOT 필드 4
        n_r5 As String        '// NOT 필드 5
End Type
'// 에이전트 Query_String으로 들어온 데이터를 정리하기 위한 객체
Dim sqlInfo As table
************
  Initialize
************
Sub Initialize
        On Error Goto ErrorHandle
        Dim qs As String
        Dim resultField As String                        '// 결과
        Set ss = New NotesSession
        Set curDb = ss.CurrentDatabase
        Set curAgent = ss.CurrentAgent
        Set curDoc = ss.DocumentContext
        qs                 = curDoc.Query_String_Decoded(0)
        '// 결과 코드 기본 *(값이 없으면), 특정 열만 가져오고자하면 해당 필드명, DISTINCT fieldname 하면 Domino 수식의 @Unique와 같은 동작을 하더군요... ^^
        resultField                = GetAgrumentValue( "result=", qs )
        '//★ 아래의 코드는 RDB Table과 동일해야 합니다. (Declarations)에 정의된 구조체 참조
        sqlInfo.r1                 = GetArgumentValue( "r1=", qs )
        sqlInfo.r2                = GetArgumentValue( "r2=", qs )
        sqlInfo.r3                = GetArgumentValue( "r3=", qs )
        sqlInfo.r4                = GetArgumentValue( "r4=", qs )
        sqlInfo.r5                = GetArgumentValue( "r5=", qs )
        sqlInfo.n_r1        = GetArgumentValue( "r1!=", qs )
        sqlInfo.n_r2        = GetArgumentValue( "r2!=", qs )
        sqlInfo.n_r3        = GetArgumentValue( "r3!=", qs )
        sqlInfo.n_r4        = GetArgumentValue( "r4!=", qs )
        sqlInfo.n_r5        = GetArgumentValue( "r5!=", qs )
        Print |Content-type: text/xml|
        Print |<?xml version='1.0' encoding='KSC5601'?>|
        '// Server 연결
        If( SQLConnection( SQL_DNS ) = False ) Then Exit Sub
        If( GetTableInfo(resultField) = False ) Then Goto ProcessEnd
ProcessEnd:
        con.Disconnect
        Exit Sub
ErrorHandle:
        Print |<Result>
                <msg>error</msg>
                <ermsg>| + curDb.Filepath + "/" + curAgent.Name + "::initialize ERROR[" + Cstr( Erl() ) + "] : " + Error() + |</ermsg>
                <erl>| + Cstr( Erl() ) + |</erl>
        </Result>|
        Msgbox curDb.Filepath + "/" + curAgent.Name + "::initialize ERROR[" + Cstr( Erl() ) + "] : " + Error()
        If( con.IsConnected ) Then
                con.Disconnect
        End If
        Exit Sub
End Sub
**************
  GetTableInfo
**************
Function GetTableInfo( res As String ) As Integer
%REM
함수명 :         GetTableInfo
인자        :        res                        - Query 결과
반환        :        Boolean                - Query 처리 결과
                                - True         : 정상처리
                                - False         : 비정상 종료
목적        :        테이블의 정보를 가져오기 위함
작성정보        :         v1.0 / 바라기 / 2006-05-23
%END REM
        On Error Goto ErrorHandle
        Dim index As Integer
        Dim whereFlag As Boolean
        whereFlag = False
        query.SQL = MakeQuery( res )
        If( query.SQL = "" ) Then
                Msgbox "Query를 조합하지 못하였습니다."
                GetEquipmentInfo = False
                Exit Function
        End If
        If Not result.Execute Then
                Messagebox result.GetExtendedErrorMessage + "/" + result.GetErrorMessage
                GetEquipmentInfo = False
                Exit Function
        End If
        '// 14000 라인 이상의 데이터를 쿼리해오면 오류가 뜨더군요... Out of memory 그래서...
        result.MaxRows = 14000
        If( result.NumRows = 0 ) Then
                Print "<Result>검색한 결과가 없습니다.</Result>"
                GetEquipmentInfo = False
                Exit Function
        End If
        Print |<Result>|
        index = 0
        Do
                result.NextRow
                Print "<data index='" & index & "'>"
                '//★ 아래의 If문 사이에 있는 코드는 RDB Table 구조와 동일해야 합니다. ( 코드마나 다르게 구성해야함. )
                If( res = "" Or Lcase( res ) = "all" ) Then
                        Print "<r1>" & result.GetValue(1) & "</r1>"
                        Print "<r2>" & result.GetValue(2) & "</r2>"
                        Print "<r3>" & result.GetValue(3) & "</r3>"
                        Print "<r4>" & result.GetValue(4) & "</r4>"
                        Print "<r5>" & result.GetValue(5) & "</r5>"
                Else
                        '// DISTINCT 와 같은 문자가 들어가는 것을 위해... ^^
                        Print "<" + Strrightback( res, " " ) + ">" + result.GetValue(1) + "</" + Strrightback( res, " " ) + ">"
                End If
                Print "</data>"
                index = index + 1
        Loop Until result.IsEndOfData
        Print |</Result>|
        GetTableInfo = True
        Exit Function
ErrorHandle:
        Call WriteLog( Err(), curDb.Filepath + "/" + curAgent.Name + "::GetTableInfo ERROR[" + Cstr( Erl() ) + "] : " + Error() )
        GetTableInfo = False
        Exit Function
End Function
***************
  MakeQuery
***************
Function MakeQuery( res As String )As String
%REM
함수명 :         MakeQuery
인자        :        res                - Query 결과
반환        :        String                - Query 조합 결과
목적        :        테이블의 데이터를 가져오기위한 Query 조합
작성정보        :         v1.0 / 바라기 / 2006-05-24
%END REM
        On Error Goto ErrorHandle
        Dim pluralValue As Variant
        Dim returnQuery As String
        Dim whereFlag As Boolean
        If( res = "" Or Lcase( res ) = "all" ) Then
                returnQuery = "Select * from " + SQL_TABLE
        Else
                returnQuery = "Select " + res + " from " + SQL_TABLE
        End If
        '//★ Query를 조합하는 구문으로 RDB Table 형식과 동일하게 처리해야 함. ( 코드마다 다르게 구성해야함.)
        returnQuery = returnQuery + MakeQueryElement( "r1", sqlInfo.r1, whereFlag )
        returnQuery = returnQuery + MakeQueryElement( "r2", sqlInfo.r2, whereFlag )
        returnQuery = returnQuery + MakeQueryElement( "r3", sqlInfo.r3, whereFlag )
        returnQuery = returnQuery + MakeQueryElement( "r4", sqlInfo.r4, whereFlag )
        returnQuery = returnQuery + MakeQueryElement( "r5", sqlInfo.r5, whereFlag )
        '// 특정 데이터가 들어가있지 않은 문서를 Query 해오기 위해서 사용
        returnQuery = returnQuery + MakeQueryNotElement( "r1", sqlInfo.n_r1, whereFlag )
        returnQuery = returnQuery + MakeQueryNotElement( "r2", sqlInfo.n_r2, whereFlag )
        returnQuery = returnQuery + MakeQueryNotElement( "r3", sqlInfo.n_r3, whereFlag )
        returnQuery = returnQuery + MakeQueryNotElement( "r4", sqlInfo.n_r4, whereFlag )
        returnQuery = returnQuery + MakeQueryNotElement( "r5", sqlInfo.n_r5, whereFlag )
        MakeQuery = returnQuery
        Exit Function
ErrorHandle:
        Call WriteLog( Err(), curDb.Filepath + "/" + curAgent.Name + "::MakeQuery ERROR[" + Cstr( Erl() ) + "] : " + Error() )
        MakeQuery = ""
        Exit Function
End Function
**********************
  MakeQueryElement
**********************
Function MakeQueryElement( itemName As String, source As String, whereFlag As Boolean )As String
%REM
함수명 :         MakeQueryElement
인자        :        itemName         - Query 필드
                        source                - Query 필드 값
                        whereFlag         - where 구문 삽입 유무
반환        :        String                - Query 조합 결과
목적        :        Query 부분 조합
작성정보        :         v1.0 / 바라기 / 2006-05-24
%END REM
        On Error Goto ErrorHandle
        Dim pluralValue As Variant
        Dim returnQuery As String
        If( Trim( source ) <> "" ) Then
                If( Strleft( source, "," ) <> "" ) Then
                        '// 복수
                        If( whereFlag ) Then
                                returnQuery = returnQuery + " AND "
                        End If
                        pluralValue = Evaluate(|@Unique( @Trim( @Explode("| & source & |";",") ) )|)
                        Forall upv In pluralValue
                                If( whereFlag ) Then
                                        If( Cstr( upv ) = Cstr( pluralValue(0) ) ) Then
                                                returnQuery = returnQuery + " ( " + itemName + "='" + Cstr( upv ) + "'"
                                        Else
                                                returnQuery = returnQuery + " OR " + itemName + "='" + Cstr( upv ) + "'"
                                        End If
                                Else
                                        returnQuery = returnQuery + " where ( " + itemName + "='" + Cstr( upv ) + "'"
                                        whereFlag = True
                                End If
                        End Forall
                        returnQuery = returnQuery + ")"
                Else
                        '// 단수
                        If( whereFlag ) Then
                                returnQuery = returnQuery + " AND " + itemName + "='" + source + "'"
                        Else
                                returnQuery = returnQuery + " where " + itemName + "='" + source + "'"
                        End If
                End If
                whereFlag = True
        End If
        MakeQueryElement = returnQuery
        Exit Function
ErrorHandle:
        Call WriteLog( Err(), curDb.Filepath + "/" + curAgent.Name + "::MakeQueryElement ERROR[" + Cstr( Erl() ) + "] : " + Error() )
        MakeQueryElement = ""
        Exit Function
End Function
*************************
  MakeQueryNotElement
*************************
Function MakeQueryNotElement( itemName As String, source As String, whereFlag As Boolean )As String
%REM
함수명 :         MakeQueryNotElement
인자        :        itemName         - Query 필드
                        source                - Query 필드 값
                        whereFlag         - where 구문 삽입 유무
반환        :        String                - Query 조합 결과
목적        :        Query 부분 (NOT) 조합
작성정보        :         v1.0 / 바라기 / 2006-05-25
%END REM
        On Error Goto ErrorHandle
        Dim pluralValue As Variant
        Dim returnQuery As String
        If( Trim( source ) <> "" ) Then
                If( Strleft( source, "," ) <> "" ) Then
                        '// 복수
                        If( whereFlag ) Then
                                returnQuery = returnQuery + " AND "
                        End If
                        pluralValue = Evaluate(|@Unique( @Trim( @Explode("| & source & |";",") ) )|)
                        Forall upv In pluralValue
                                If( whereFlag ) Then
                                        If( Cstr( upv ) = Cstr( pluralValue(0) ) ) Then
                                                returnQuery = returnQuery + " ( " + itemName + " <> '" + Cstr( upv ) + "'"
                                        Else
                                                returnQuery = returnQuery + " AND " + itemName + " <> '" + Cstr( upv ) + "'"
                                        End If
                                Else
                                        returnQuery = returnQuery + " where ( " + itemName + " <> '" + Cstr( upv ) + "'"
                                        whereFlag = True
                                End If
                        End Forall
                        returnQuery = returnQuery + ")"
                Else
                        '// 단수
                        If( whereFlag ) Then
                                returnQuery = returnQuery + " AND " + itemName + " <> '" + source + "'"
                        Else
                                returnQuery = returnQuery + " where " + itemName + " <> '" + source + "'"
                        End If
                End If
                whereFlag = True
        End If
        MakeQueryNotElement = returnQuery
        Exit Function
ErrorHandle:
        Call WriteLog( Err(), curDb.Filepath + "/" + curAgent.Name + "::MakeQueryNotElement ERROR[" + Cstr( Erl() ) + "] : " + Error() )
        MakeQueryNotElement = ""
        Exit Function
End Function
*********************
  SQLConnection
*********************
Function SQLConnection( dns As String )As Integer
'===========================================================================================        
'                함  수  명 : SQLConnection
'                목       적 : SQL Server Connection
'                버       젼 : 1.0
'                매개 변수 : dns       : DNS (ex: "hits")
'===========================================================================================
        On Error Goto ErrorHandle
        Dim status As String
        Set con = New ODBCConnection
        Set query = New ODBCQuery
        Set result = New ODBCResultSet
        Set query.Connection = con
        Set result.Query = query
        status = con.ConnectTo( dns, SQL_ID, SQL_PASSWORD )
        If( Ucase( status ) <> "TRUE" ) Then
                Print |<Result>
                                        <msg>error</msg>
                                        <ermsg>조회정보 서버에 연결할 수 없습니다.( DNS : | + SQL_DNS + | ) 관리자에게 서버의 ODBC 설정을 확인 요청을 하시기 바랍니다.</ermsg>
                                        <erl>| & Erl & |</erl>
                                </Result>|
                SQLConnection = False
                Exit Function
        End If
        SQLConnection = True
        Exit Function
ErrorHandle:
        Call WriteLog( Err(), curDb.Filepath + "/" + curAgent.Name + "::SQLConnection ERROR[" + Cstr( Erl() ) + "] : " + Error() )
        SQLConnection = False
        Exit Function
End Function
******************
  WriteLog
******************
Function WriteLog( ErrorOption As Integer, LogMessage As String )
%REM
함수명 :         WriteLog
인자        :        ErrorOption        - 오류코드 ( 0 : 정상로그, 이외 오류코드 )
반환        :        -
목적        :        로그 DB에 결과 기록
작성정보        :         v1.0 / 바라기 / 2006-04-07
%END REM
        On Error Goto ErrorHandle
        Msgbox LogMessage
%REM
        If( ErrorOption = 0 ) Then
                Call curLog.LogAction( |[| & curDb.FilePath & |] | & LogMessage )
        Else
                Call curLog.LogError( ErrorOption, |[| & curDb.FilePath & |] | & LogMessage )
        End If
%END REM
        Exit Function
ErrorHandle:
        Msgbox |[| & curDb.FilePath & |] | & curAgent.Name & |::WriteLog ERROR[| & Cstr( Erl() ) & |] : | & Error()
        Exit Function
End Function
**********************
  GetArgumentValue
**********************
Function GetArgumentValue(div As String, source As String )
'===========================================================================================        
'        함  수  명 : GetArgumentValue
'        목       적 : 웹 파라미터 값을  반환하는 함수
'        버       젼 : 1.0
'        매개 변수 : div       : 파라미터 (ex: "sv=")
'               source :  전체문자열
'===========================================================================================
        GetArgumentValue = ""
        Dim pos As Integer
        Dim valueItem As String
        pos = Instr( source, div )
        If pos > 0 Then
                valueItem = Mid( source, pos )                
                If Instr( valueItem , "&" ) > 0Then
                        GetArgumentValue = Mid( valueItem, Instr( valueItem , "=") + 1, Instr( valueItem , "&") -  Instr( valueItem , "=")-1)
                Else
                        GetArgumentValue = Mid( valueItem, Instr( valueItem , "=") + 1)                
                End If                
        End If
End Function

 

-----------------------------------------

에이전트 호출하는 방식...
1.
http://serverURL/db.nsf/agent?openagent&result=DISTINCTr1&r2=TEST&r3=1111,2222,3333,4444&r4!=AAAA,BBBB
--->
결과는...
r2 필드값이 TEST 이며, r3 필드값이 1111 또는 2222 또는 3333 또는 4444 이며, r4 필드값이 AAAA 가 아니며, r4 필드값이 BBBB가 아닌 레코드의 r1값 중... 중복값을 제외한 목록이 반환 됩니다.
2.
http://serverURL/db.nsf/agent?openagent&r2=TEST&r2=1111,2222,3333
---->
결과는...
r2 필드값이 TEST인 레코드의 모든 필드값을 반환합니다.
( r2라는 파라메터를 중복해서 넣어주면 두번째껀 무시해 버립니다... !! )
result 파라메타가 없는 경우 모든 필드값을 반환하져...
3.
http://serverURL/db.nsf/agent?openagent&r2=TEST&r2!=1111,2222,3333
----->
결과는...
r2 필드값이 TEST인 레코드의 모든 필드값을 반환합니다.
r2! 파라메터가 무의미해 집니다. ( Select 쿼리에는 들어 갑니다... )
결과는 XML 형식으로 화면에 뿌려집니다~~~!!
다른 형태를 원하시면~ 잘~~ 수정해야 합니다~