블로그 이미지
baragi76

카테고리

분류 전체보기 (115)
생활 (10)
My Story (34)
My Album (1)
HomePage (0)
Hobbies (21)
Develop (42)
Utility (1)
Project (6)
Statistics Graph
Total236,739
Today3
Yesterday21

공지사항

달력

« » 2020.2
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29

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 형식으로 화면에 뿌려집니다~~~!!
다른 형태를 원하시면~ 잘~~ 수정해야 합니다~

Posted by baragi76

댓글을 달아 주세요

  1. 2010.10.08 18:54 신고 Favicon of https://baragi.kr BlogIcon baragi76  댓글주소  수정/삭제  댓글쓰기

    (Declarations) 부분의 DNS 설정, SQL_TABLE_FIELD, SQL_TABLE_FIELD_AROUND 상수만 손을 보시고 Query를 날려보세요~
    아마도 될것이라 생각이 됩니다~~~