바라기의 이야기

ODBC를 이용한 Select 쿼리 에이전트 원형 II 본문

Develop/Domino

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

baragi76 2010. 10. 8. 18:53

업그레이드 1탄...
몇일만에 바로 업글이 되었네요~ 그만큼 초기 버전이 제약사항 및 오류가 있었다는 얘기겠죠~
기존껀 오로지 SELECT, LIKE 조건 등을 날리지 못했으며,
각 RDB Table에 맞춰주려면 여러 함수들을 돌아다니면서 수정해줘야하는 번거로움이 있었습니다.

이제 달라졌습니다~
1. Table의 형식이 달라져도 (Declarations)의 ODBC Connection 설정 사항과 2줄의 코드만 수정해주면 다른곳은 수정할 필요가 없습니다.
-> SQL_TABLE_FIELD, SQL_TABLE_AROUND 상수가 추가가 되었습니다.
* SQL_TABLE_FIELD 상수에는 받고자하는 필드를 순서대로 컴머(,)로 분류하여 입력하시면 됩니다. ( 공백은 날려버리니 상관 없구요...)
* SQL_TABLE_FIELD_AROUND 상수는 SQL_TABLE_FIELD상수와 같은 수만큼 컴머로 분리하서 꺽쇠(^)를 넣어주시면 됩니다. 이것을 넣은 이유는 XML을 반환시 특수문자 또는 공백이 들어가면 오류가 발생되므로 공백이 포함된 필드값을 받고자하는 경우엔 <![CDATA[~~~~~]]>로 감싸줘야 하기때문에 상수가 추가가 되었습니다.



2. SELECT 쿼리와 프로시져를 호출할 수 있도록 기능이 추가되었습니다.
-> 다음과 같이 URL을 구성하면...
http://server_url/db.nsf/agent?openagent&order=exec&param-i=2&param-1=1111&param-2=2222
결과는 exec SQL_TABLE(Declarations의 설정값) '1111','2222' 이렇게 RDB 서버로 Query를 전송합니다.

파라메터가 추가가 된것인데요~
order : 명령줄( select 생략가능 / 없으면 select로 인식 )
param-i : order를 exec와 같이 프로시저를 호출할 경우 파라메터를 넘겨주는데~ 그 파라메터의 수를 미리 지정해주는 옵션입니다.
param-1 ~ n : param-i로 지정한 값 만큼 설정해야 합니다. (글케하지 않으면 어떤 오류가 뜰지 모릅니다... ㅡ_ㅡ;; )



3. 오로지 A='b' 또는 A<>'b' 조건문 밖에 안되던것이... LIKE문을 넣을 수 있도록 추가가 되었습니다.
SQL을 마니 알지는 못하지만... LIKE문을 포함하여 쿼리를 구성할 수 있도록 수정하였습니다.
-> 기존엔 http://server_url/db.nsf/agent?openagent&aa=1111,2222&bb!=3333,4444 와 같이 호출하면...
SELECT * from table where ( aa = '1111' or aa = '2222' ) and ( bb <> '3333' and bb <> '4444' )라는 Query가 RDB 서버로 전송이됩니다. 이에 불만이 한가지 있었습니다.... LIKE 문이죠...

자 그럼~
http://server_url/db.nsf/agent?openagent&aaa=[LIKE]111%,[LIKE]222%&bb!=3333,4444 와 같이 호출하면~
SELECT * from table where ( aa LIKE '111%' or aa LIKE '222%' ) and ( bb <> '3333' and bb <> '4444' )라는 Query를 RDB 서버로 전송합니다.
조금더 확장된 기능을 제공한다라고 감히 말할수 있겠죠(?)


자 아래부터는 Full 코드입니다.
---------------------------------------------------------------------------------------------------


****************************
(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                 = "ODBC_DNS"
Const SQL_ID                 = "UserID"
Const SQL_PASSWORD         = "UserPassword"
Const SQL_TABLE_SCHEMA        = "dbo"
Const SQL_TABLE                = "table"


'//★ 아래의 코드는 RDB Table과 같은 형식이어야 함. ( 코드마다 틀리게 구성해야 함. )
Const SQL_TABLE_FIELD        = |a,b,c,d,e|        '// 결과 필드 목록
Const SQL_TABLE_FIELD_AROUND = |^,^,^,^,<![CDATA[^]]>|        '// 결과 필드 둘러싸기 옵션

Dim TableDoc As NotesDocument
Dim fieldAround As Variant



****************************
  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)
       
        Print |Content-type: text/xml|
        Print |<?xml version='1.0' encoding='KSC5601'?>|
       
        Call SetTableDoc( qs )        
       
        '// SYBASE Server 연결
        If( SQLConnection( SQL_DNS ) = False ) Then Exit Sub
       
        If( GetTableInfo(TableDoc.result(0)) = False ) Then Goto ProcessEnd
       
ProcessEnd:
        con.Disconnect
        Exit Sub
ErrorHandle:
        Print |<Result>
                                <msg>error</msg>
                                <ermsg>| + Error() + |</ermsg>
                                <erl>| + Cstr( Erl() ) + |</erl>
                        </Result>|
       
        Msgbox curDb.Filepath + "/" + curAgent.Name + "::initialize ERROR[" + Cstr( Erl() ) + "] : " + Error()
        Print "<error>"
        Print curDb.Filepath + "/" + curAgent.Name + "::initialize ERROR[" + Cstr( Erl() ) + "] : " + Error()
        Print "</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-25/2006-05-26
%END REM
        On Error Goto ErrorHandle
        Dim index As Integer
        Dim rowIndex As Integer
        Dim whereFlag As Boolean
        whereFlag = False
       
        query.SQL = MakeQuery( res )
       
        If( query.SQL = "" ) Then
                Msgbox "Query를 조합하지 못하였습니다."
                GetTableInfo = False
                Exit Function
        End If
       
        If Not result.Execute Then
                Messagebox result.GetExtendedErrorMessage + "/" + result.GetErrorMessage
                GetTableInfo = False
                Exit Function
        End If
       
        result.MaxRows = 14000
        If( result.NumRows = 0 ) Then
                Print "<Result>검색한 결과가 없습니다.</Result>"
                GetTableInfo = False
                Exit Function
        End If
       
        Print |<Result>|
        index = 0
        Do
                result.NextRow
               
                Print "<data index='" & index & "'>"
                If( res = "" Or Lcase( res ) = "all" ) Then
                        rowIndex = 1
                        Forall utdi In TableDoc.GetItemValue( "FieldList" )
                                Print "<" & Cstr( utdi ) & ">" & Strleft( fieldAround(rowIndex-1), "^" ) & result.GetValue( rowIndex ) & Strright( fieldAround(rowIndex-1), "^" ) & "</" & Cstr( utdi ) & ">"
                                rowIndex = rowIndex + 1
                        End Forall
                Else
                        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 / 2006-05-26
%END REM
        On Error Goto ErrorHandle
        Dim returnQuery As String                        '// Query
        Dim whereFlag As Boolean                        '// where절 추가상태 여부
        Dim curItemName As String                        '// Query 구성 Item명
        Dim separator As String                                '// 파라메터간 구분자
       
        If( Lcase( TableDoc.Order(0) ) = "select" Or Lcase( TableDoc.Order(0) ) = "" ) Then
                '// Select 구문인 경우
                If( res = "" Or Lcase( res ) = "all" ) Then
                        returnQuery = "Select * from " + SQL_TABLE
                Else
                        returnQuery = "Select " + res + " from " + SQL_TABLE
                End If
               
                Forall utdi In TableDoc.Items
                        curItemName = Lcase( utdi.Name )
                        '// result, fieldlist, order 필드는 Query에 포함되지 않음.
                        If( curItemName <> "result" And curItemName <> "fieldlist" And curItemName <> "order" And Left( curItemName, 6 ) <> "param-" ) Then
                                '// n_로 시작하는 필드는 n_ 다음 String으로 쿼리를 구성함.
                                If( Left( utdi.Name, 2 ) = "n_" ) Then
                                        returnQuery = returnQuery + MakeQueryElement( Rightbp( utdi.Name, Len(utdi.Name)-2 ), utdi.Values(0), False, whereFlag )
                                Else
                                        returnQuery = returnQuery + MakeQueryElement( utdi.Name, utdi.Values(0), True, whereFlag )
                                End If
                        End If                
                End Forall
        Else
                '// 프로시저 호출일 경우
                returnQuery = TableDoc.Order(0) + " " + SQL_TABLE
               
                For i%=1 To Cint( TableDoc.GetItemValue( "param-i" )(0) )
                        If( i% > 1 ) Then
                                separator = |,'|
                        Else
                                separator = | '|
                        End If
                        returnQuery = returnQuery + separator + TableDoc.GetItemValue( "param-" & Cstr(i%) )(0) + |'|
                Next
        End If
       
        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, equalOption As Boolean, whereFlag As Boolean )As String
%REM
함수명 :         MakeQueryElement
인자        :        itemName         - Query 필드
                        source                - Query 필드 값
                        equalOption        - 포함되는 값을 찾으려면 True, 포함되지 않는 값을 찾으려면 False
                        whereFlag         - where 구문 삽입 유무
반환        :        String                - Query 조합 결과
목적        :        Query 부분 조합
작성정보        :         v1.1 / 바라기 / 2006-05-24
%END REM
        On Error Goto ErrorHandle
        Dim pluralValue As Variant        '// source 값 복수시 Explode
        Dim returnQuery As String        '// 결과 반환값
       
        Const REF_CALCULATE_BLOCK = " AND "                '//조건 블럭간 조건
       
        Dim valueCompare As String                        '// 등호, 같음 또는 같지 않음 체크
        Dim bitCalculator As String                        '// 같음 일때는 OR, 다름 일때는 AND 여야 함.
        If( equalOption ) Then
                valueCompare = " = "
                bitCalculator = " OR "
        Else
                valueCompare = " <> "
                bitCalculator = " AND "
        End If
       
        If( Trim( source ) <> "" ) Then
                If( Strleft( source, "," ) <> "" ) Then
                        '// 복수
                        If( whereFlag ) Then
                                returnQuery = returnQuery + REF_CALCULATE_BLOCK
                        End If
                        pluralValue = Evaluate(|@Unique( @Trim( @Explode("| & source & |";",") ) )|)
                        Forall upv In pluralValue
                                If( whereFlag ) Then
                                        If( Cstr( upv ) = Cstr( pluralValue(0) ) ) Then
                                                '// 첫번째 조건값
                                                If( Left( Cstr( upv ), 1 ) = "[" And Strleft( Cstr( upv ), "]" ) <> "" ) Then
                                                        '// LIKE와 같은 구문이 있는 경우 (ex: [LIKE]AA%)
                                                        returnQuery = returnQuery + " ( " + itemName + " " + Strleft( Strright( Cstr( upv ) , "[" ), "]" ) + " '" + Strright( Cstr( upv ), "]" ) + "'"
                                                Else
                                                        returnQuery = returnQuery + " ( " + itemName + valueCompare + "'" + Cstr( upv ) + "'"
                                                End If
                                        Else
                                                '// 두번째 이후 조건값
                                                If( Left( Cstr( upv ), 1 ) = "[" And Strleft( Cstr( upv ), "]" ) <> "" ) Then
                                                        '// LIKE와 같은 구문이 있는 경우 (ex: [LIKE]AA%)
                                                        returnQuery = returnQuery + bitCalculator + itemName + " " + Strleft( Strright( Cstr( upv ) , "[" ), "]" ) + " '" + Strright( Cstr( upv ), "]" ) + "'"
                                                Else
                                                        returnQuery = returnQuery + bitCalculator + itemName + valueCompare + "'" + Cstr( upv ) + "'"
                                                End If
                                        End If
                                Else
                                        '// where절이 없는 경우 추가
                                        If( Left( Cstr( upv ), 1 ) = "[" And Strleft( Cstr( upv ), "]" ) <> "" ) Then
                                                '// LIKE와 같은 구문이 있는 경우 (ex: [LIKE]AA%)
                                                returnQuery = returnQuery + " where ( " + itemName + " " + Strleft( Strright( Cstr( upv ) , "[" ), "]" ) + " '" + Strright( Cstr( upv ), "]" ) + "'"
                                        Else
                                                returnQuery = returnQuery + " where ( " + itemName + valueCompare + "'" + Cstr( upv ) + "'"
                                        End If
                                       
                                        whereFlag = True
                                End If
                        End Forall
                       
                        returnQuery = returnQuery + ")"
                Else
                        '// 단수
                        If( whereFlag ) Then
                                '// where 절을 이미 삽입한 경우
                                If( Left( source, 1 ) = "[" And Strleft( source, "]" ) <> "") Then
                                        '// LIKE와 같은 구문이 있는 경우 (ex: [LIKE]AA%)
                                        returnQuery = returnQuery + REF_CALCULATE_BLOCK + itemName + " " + Strleft( Strright( source , "[" ), "]" ) + " '" + Strright( source, "]" ) + "'"
                                Else
                                        returnQuery = returnQuery + REF_CALCULATE_BLOCK + itemName + valueCompare + "'" + source + "'"
                                End If
                        Else
                                '// where 절이 없는 경우 추가
                                If( Left( source, 1 ) = "[" And Strleft( source, "]" ) <> "" ) Then
                                        '// LIKE와 같은 구문이 있는 경우 (ex: [LIKE]AA%)
                                        returnQuery = returnQuery + " where " + itemName + " " + Strleft( Strright( source , "[" ), "]" ) + " '" + Strright( source, "]" ) + "'"
                                Else
                                        returnQuery = returnQuery + " where " + itemName + valueCompare + "'" + source + "'"
                                End If
                               
                               
                        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() )
        Print source
        MakeQueryElement = ""
        Exit Function
End Function



****************************
  SetTableDoc
****************************

Sub SetTableDoc( source As String )
%REM
함수명 :         SetTableDoc
인자        :        source                        - Query_String_Decoded
반환        :        -
목적        :        Query_String_Decoded로 넘어온 값 중에서 원하는 Query를 잘라내어 TableDoc의 Item으로 생성함.
                        이를 MakeQuery, GetTableInfo 함수에서 사용함으로 각 코드에 Table 형식에 맞게끔 코딩하는 것을 방지할 수 있음.
작성정보        :         v1.0 / 바라기 / 2006-05-26
%END REM
        On Error Goto ErrorHandle
        Dim FieldList As Variant
       
        Set TableDoc = New NotesDocument( curDb )
        FieldList = Evaluate(|@Trim( @Explode("| & SQL_TABLE_FIELD & |";",") )|)
        fieldAround = Evaluate(|@Explode("| & SQL_TABLE_FIELD_AROUND & |";",")|)
       
        Forall ufl In FieldList
                Call TableDoc.ReplaceItemValue( Cstr( ufl ), GetArgumentValue( Cstr( ufl ) & "=", source ) )
                Call TableDoc.ReplaceItemValue( "n_" & Cstr( ufl ), GetArgumentValue( Cstr( ufl ) & "!=", source ) )
        End Forall
       
        Call TableDoc.ReplaceItemValue( "FieldList", FieldList        )                                                                                '// 결과 Field 목록
        Call TableDoc.ReplaceItemValue( "result", GetArgumentValue( "result=", source ) )                '// 결과
        Call TableDoc.ReplaceItemValue( "order", GetArgumentValue( "order=", source ) )                '// 명령( select, exec ... )
        Call TableDoc.ReplaceItemValue( "param-i", GetArgumentValue( "param-i=", source ) )        '// 파라메터 수
       
        If( TableDoc.GetItemValue("param-i")(0) <> "" ) Then
                For i%=1 To Cint( TableDoc.GetItemValue( "param-i" )(0) )
                        Call TableDoc.ReplaceItemValue( "param-" & Cstr(i%), GetArgumentValue( "param-" & Cstr(i%) & "=", source ) )
                Next
        End If
       
        Exit Sub
ErrorHandle:
        Call WriteLog( Err(), curDb.Filepath + "/" + curAgent.Name + "::SetTableDoc ERROR[" + Cstr( Erl() ) + "] : " + Error() )
        Exit Sub
End Sub



****************************
  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
        Print 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