Autoit odbc 연결

Oracle ODBC 연결

assignment 3가지를 명심 할것

1. windows 가 64비트여도 orcle odbc는 32비트용을 깔아라(64비트 깔면 개고생)
2. TNS_ADMIN 환경변수에 등록 , tnsnames.ora 파일 경로
3. Provider ~ 쓰지말고 DSN ~ 으로 써야 된다

• 안됨 (삽질의 시작) Provider

   .ConnectionString =("Provider='OraOLEDB.Oracle';Data Source='TNS NAME HERE';User Id='XXXX';Password='XXXXX';")

• 수정 후 (삽질 2일차 해결) DSN

   .ConnectionString = ("DSN=orcl;DB=orcl;UID=dbcafe;PWD=xxxx;")

; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$sqlCon = ObjCreate("ADODB.Connection")

$sqlCon.Mode = 16  ; shared
$sqlCon.CursorLocation = 3 ; client side cursor

#cs
$sqlCon.Open ("Driver={DB2}; IP=[ip_address]; Port=[port_number]; Database=[database_name]; UID=[username]; PWD=[password]")
#ce
; DSN : TNS NAME (tnsnames.ora)
$sqlCon.Open ("DSN=orcl;DB=orcl;UID=dbcafe;PWD=xxxx;")


If @error Then
    MsgBox(0, "ERROR", "Failed to connect to the database")
    Exit
EndIf

; See also Catalog "ADOX Catalog Example.au3"

$sqlRs = ObjCreate("ADODB.Recordset")
If Not @error Then
    $sqlRs.open ("select * from Table", $sqlCon)
    If Not @error Then
        ;Loop until the end of file
        While Not $sqlRs.EOF
            ;Retrieve data from the following fields
            $OptionName = $sqlRs.Fields ('name' ).Value
            $OptionVal = $sqlRs.Fields ('value' ).Value
            MsgBox(0, "Record Found", "Name:  " & $OptionName & @CRLF & "Value:  " & $OptionVal)
            $sqlRs.FIELDS('"' & $OptionName & '"') = ".F." ; ADDED THIS LINE
           ; $sqlRs.Update  ; ADDED THIS LINE
            $sqlRs.MoveNext  
        WEnd
        $sqlRs.close
    EndIf
EndIf

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

Oracle OLEDB for Autoit

#include <GUIConstants.au3>

Dim $oMyError

; Initializes COM handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$ado = ObjCreate( "ADODB.Connection" )    ; Create a COM ADODB Object  with the Beta version

With $ado
    ; 'Set data source - for OLEDB this is a tns alias, for ODBC it can be 'either a tns alias or a DSN.
    ; If "provider" is used this means that the ODBC connections is used via DSN. 
    ; if Driver is used = "Driver={Microsoft ODBC for Oracle};Server=TNSnames_ora;Uid=demo;Pwd=demo;" then this is a DSN Less connector
    ; More Info for Oracle MS KB Q193332
    .ConnectionString = ("DSN=orcl;DB=orcl;UID=dbcafe;PWD=xxxx;")
;~    .ConnectionString =("Provider='OraOLEDB.Oracle';Data Source='TNS NAME HERE';User Id='XXXX';Password='XXXXX';") 
    .Open
EndWith

$adors = ObjCreate( "ADODB.RecordSet" )    ; Create a Record Set to handles SQL Records

With $adors
        .ActiveConnection = $ado
        ;.CursorLocation = "adUseClient"
        ;.LockType = "adLockReadOnly" ; Set ODBC connection read only
        .Source = "select * from TABLE NAME HERE"
        .Open 
EndWith

While not $adors.EOF
    For $i = 0 To $adors.Fields.Count - 1
        ConsoleWrite( $adors.Fields( $i ).Value & @TAB )    ; Columns in the AutoIt console use Column Name or Index
    Next
        ConsoleWrite(@CR)
    $adors.MoveNext                                                ; Go to the next record
WEnd

; This COM error Handler
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc