Database connection

[niranjana]

Hello,

I am fairly new to AutoIt.

I am trying to connect to a oracle database server from AutoIt to access data. Could anyone please let me know how can i go about? I need to know the syntax and where the connection details such TNS entries need to be entered.

Thanks,

[Xenobiologist]

Hi,

this can be a start:

expandcollapse popup

#include<Array.au3>
#include<GuiListView.au3>

; Global Variables
Global $adUseServer = 2
Global $adUseClient = 3

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

Global $provider = 'IBMDADB2'
Global $IP = ''
Global $port = ''
Global $DSN = ''
Global $userID = ''
Global $password = ''

Global $connection_Obj = _connectDB($provider, $IP, $port, $DSN, $userID, $password)

_getColumns($connection_Obj, 'SELECT * FROM "BI1VSNR"."AGENTURMANDANT"')
_displayTable($connection_Obj, 'SELECT * FROM "BI1VSNR"."AGENTURMANDANT"')

Func _connectDB($provider, $IP, $port, $DSN, $userID, $password)
    Local $sqlCon = ObjCreate('ADODB.Connection')
    $sqlCon.Mode = 16 ; Erlaubt im MultiUser-Bereich das öffnen anderer Verbindungen ohne Beschränkungen [Lesen/Schreiben/Beides]
    $sqlCon.CursorLocation = $adUseClient ; client side cursor Schreiben beim Clienten

    $sqlCon.Open('Provider=' & $provider & ';IP=' & $IP & ';Port=' & $port & ';DSN=' & $DSN & ';User ID=' & $userID & ';Password=' & $password)
    If @error Then Return -1
    Return $sqlCon
EndFunc   ;==>_connectDB

Func _getColumns($sqlCon, $SQL)
    Local $sqlRs = ObjCreate('ADODB.Recordset')
    If Not @error Then
        $sqlRs.open($SQL, $sqlCon)
        If Not @error Then
            For $i = 0 To $sqlRs.Fields.Count - 1
                ConsoleWrite($sqlRs.Fields($i).Name & @CRLF)
            Next
            $sqlRs.close
        EndIf
    EndIf
EndFunc   ;==>_getColumns

Func _displayTable($sqlCon, $SQL)
    Local $sqlRs = ObjCreate('ADODB.Recordset')
    If Not @error Then
        $sqlRs.open($SQL, $sqlCon)
        If Not @error Then
            Local $header[$sqlRs.Fields.Count], $rows
            For $i = 0 To $sqlRs.Fields.Count - 1
                $header[$i] = $sqlRs.Fields($i).Name
            Next
            $rows = $sqlRs.GetRows()
            $sqlRs.close
        EndIf
    EndIf
    _ArrayDisplay_WithHeader($rows, $header)
EndFunc   ;==>_displayTable

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   ;==>MyErrFunc

;~ _executeSQLonDB2Recordset($Obj, "insert into " & "Autoit" & "values('Xenobiologist', 'User')")

Func _executeSQLonDB2Recordset($sqlCon, $SQL)
    Local $sqlRs = ObjCreate('ADODB.Recordset')
    $sqlRs.open('SELECT * FROM Autoit', $sqlCon)
    If Not @error Then
        $sqlRs.Source = "Select * From Autoit" ; ganze Tabelle
        $sqlRs.AddNew ;'Neuen Datensatz erzeugen
;~   'Beispiele für Zuweisung von Werten an Tabellenfelder
        $sqlRs.Fields("Name") = "MEGA"
        $sqlRs.Fields("Job") = "MEGAMAN"
        $sqlRs.Update
        $sqlRs.close
    EndIf
EndFunc   ;==>_executeSQLonDB2Recordset


;$OptionVal = $sqlRs.Fields ('LAST_NAME' ).Value
;~             MsgBox(0, 'Record Found', 'Name:  ' & $OptionName );& @CRLF & 'Value:  ' & $OptionVal)
;~             $sqlRs.FIELDS(''' & $OptionName & ''') = '.F.' ; ADDED THIS LINE
; $sqlRs.Update  ; ADDED THIS LINE
;~ Driver=IBMDADB2

Func _ArrayDisplay_WithHeader(Const ByRef $avArray, $header, $sTitle = "Array: ListView Display", $iItemLimit = -1, $iTranspose = 0, $sSeparator = "", $sReplace = "|")
    If Not IsArray($avArray) Then Return SetError(1, 0, 0)

    ; Dimension checking
    Local $iDimension = UBound($avArray, 0), $iUBound = UBound($avArray, 1) - 1, $iSubMax = UBound($avArray, 2) - 1
    If $iDimension > 2 Then Return SetError(2, 0, 0)

    ; Separator handling
;~     If $sSeparator = "" Then $sSeparator = Chr(1)
    If $sSeparator = "" Then $sSeparator = Chr(124)

    ; Declare variables
    Local $i, $j, $vTmp, $aItem, $avArrayText, $sHeader = "Row", $iBuffer = 64
    Local $iColLimit = 250, $iLVIAddUDFThreshold = 4000, $iWidth = 640, $iHeight = 480
    Local $iOnEventMode = Opt("GUIOnEventMode", 0), $sDataSeparatorChar = Opt("GUIDataSeparatorChar", $sSeparator)

    ; Swap dimensions if transposing
    If $iSubMax < 0 Then $iSubMax = 0
    If $iTranspose Then
        $vTmp = $iUBound
        $iUBound = $iSubMax
        $iSubMax = $vTmp
    EndIf

    ; Set limits for dimensions
    If $iSubMax > $iColLimit Then $iSubMax = $iColLimit
    If $iItemLimit = 1 Then $iItemLimit = $iLVIAddUDFThreshold
    If $iItemLimit < 1 Then $iItemLimit = $iUBound
    If $iUBound > $iItemLimit Then $iUBound = $iItemLimit
    If $iLVIAddUDFThreshold > $iUBound Then $iLVIAddUDFThreshold = $iUBound

    ; Set header up
    For $i = 0 To UBound($header) - 1
        $sHeader &= $sSeparator & $header[$i]
    Next

    ; Convert array into text for listview
    Local $avArrayText[$iUBound + 1]
    For $i = 0 To $iUBound
        $avArrayText[$i] = "[" & $i & "]"
        For $j = 0 To $iSubMax
            ; Get current item
            If $iDimension = 1 Then
                If $iTranspose Then
                    $vTmp = $avArray[$j]
                Else
                    $vTmp = $avArray[$i]
                EndIf
            Else
                If $iTranspose Then
                    $vTmp = $avArray[$j][$i]
                Else
                    $vTmp = $avArray[$i][$j]
                EndIf
            EndIf

            ; Add to text array
            $vTmp = StringReplace($vTmp, $sSeparator, $sReplace, 0, 1)
            $avArrayText[$i] &= $sSeparator & $vTmp

            ; Set max buffer size
            $vTmp = StringLen($vTmp)
            If $vTmp > $iBuffer Then $iBuffer = $vTmp
        Next
    Next
    $iBuffer += 1

    ; GUI Constants
    Local Const $_ARRAYCONSTANT_GUI_DOCKBORDERS = 0x66
    Local Const $_ARRAYCONSTANT_GUI_DOCKBOTTOM = 0x40
    Local Const $_ARRAYCONSTANT_GUI_DOCKHEIGHT = 0x0200
    Local Const $_ARRAYCONSTANT_GUI_DOCKLEFT = 0x2
    Local Const $_ARRAYCONSTANT_GUI_DOCKRIGHT = 0x4
    Local Const $_ARRAYCONSTANT_GUI_EVENT_CLOSE = -3
    Local Const $_ARRAYCONSTANT_LVIF_PARAM = 0x4
    Local Const $_ARRAYCONSTANT_LVIF_TEXT = 0x1
    Local Const $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH = (0x1000 + 29)
    Local Const $_ARRAYCONSTANT_LVM_GETITEMCOUNT = (0x1000 + 4)
    Local Const $_ARRAYCONSTANT_LVM_GETITEMSTATE = (0x1000 + 44)
    Local Const $_ARRAYCONSTANT_LVM_INSERTITEMA = (0x1000 + 7)
    Local Const $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE = (0x1000 + 54)
    Local Const $_ARRAYCONSTANT_LVM_SETITEMA = (0x1000 + 6)
    Local Const $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT = 0x20
    Local Const $_ARRAYCONSTANT_LVS_EX_GRIDLINES = 0x1
    Local Const $_ARRAYCONSTANT_LVS_SHOWSELALWAYS = 0x8
    Local Const $_ARRAYCONSTANT_WS_EX_CLIENTEDGE = 0x0200
    Local Const $_ARRAYCONSTANT_WS_MAXIMIZEBOX = 0x00010000
    Local Const $_ARRAYCONSTANT_WS_MINIMIZEBOX = 0x00020000
    Local Const $_ARRAYCONSTANT_WS_SIZEBOX = 0x00040000
    Local Const $_ARRAYCONSTANT_tagLVITEM = "int Mask;int Item;int SubItem;int State;int StateMask;ptr Text;int TextMax;int Image;int Param;int Indent;int GroupID;int Columns;ptr pColumns"

    Local $iAddMask = BitOR($_ARRAYCONSTANT_LVIF_TEXT, $_ARRAYCONSTANT_LVIF_PARAM)
    Local $tBuffer = DllStructCreate("char Text[" & $iBuffer & "]"), $pBuffer = DllStructGetPtr($tBuffer)
    Local $tItem = DllStructCreate($_ARRAYCONSTANT_tagLVITEM), $pItem = DllStructGetPtr($tItem)
    DllStructSetData($tItem, "Param", 0)
    DllStructSetData($tItem, "Text", $pBuffer)
    DllStructSetData($tItem, "TextMax", $iBuffer)

    ; Set interface up
    Local $hGUI = GUICreate($sTitle, $iWidth, $iHeight, Default, Default, BitOR($_ARRAYCONSTANT_WS_SIZEBOX, $_ARRAYCONSTANT_WS_MINIMIZEBOX, $_ARRAYCONSTANT_WS_MAXIMIZEBOX))
    Local $aiGUISize = WinGetClientSize($hGUI)
    Local $hListView = GUICtrlCreateListView($sHeader, 0, 0, $aiGUISize[0], $aiGUISize[1] - 26, $_ARRAYCONSTANT_LVS_SHOWSELALWAYS)
    Local $hCopy = GUICtrlCreateButton("Copy Selected", 3, $aiGUISize[1] - 23, $aiGUISize[0] - 6, 20)
    GUICtrlSetResizing($hListView, $_ARRAYCONSTANT_GUI_DOCKBORDERS)
    GUICtrlSetResizing($hCopy, $_ARRAYCONSTANT_GUI_DOCKLEFT + $_ARRAYCONSTANT_GUI_DOCKRIGHT + $_ARRAYCONSTANT_GUI_DOCKBOTTOM + $_ARRAYCONSTANT_GUI_DOCKHEIGHT)
    GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_GRIDLINES, $_ARRAYCONSTANT_LVS_EX_GRIDLINES)
    GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT)
    GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE)

    ; Fill listview
    For $i = 0 To $iLVIAddUDFThreshold
        GUICtrlCreateListViewItem($avArrayText[$i], $hListView)
    Next
    For $i = ($iLVIAddUDFThreshold + 1) To $iUBound
        $aItem = StringSplit($avArrayText[$i], $sSeparator)
        DllStructSetData($tBuffer, "Text", $aItem[1])

        ; Add listview item
        DllStructSetData($tItem, "Item", $i)
        DllStructSetData($tItem, "SubItem", 0)
        DllStructSetData($tItem, "Mask", $iAddMask)
        GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_INSERTITEMA, 0, $pItem)

        ; Set listview subitem text
        DllStructSetData($tItem, "Mask", $_ARRAYCONSTANT_LVIF_TEXT)
        For $j = 2 To $aItem[0]
            DllStructSetData($tBuffer, "Text", $aItem[$j])
            DllStructSetData($tItem, "SubItem", $j - 1)
            GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETITEMA, 0, $pItem)
        Next
    Next
    
    ; ajust window width
    $iWidth = 0
    For $i = 0 To $iSubMax + 1
        $iWidth += GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH, $i, 0)
    Next
    If $iWidth < 250 Then $iWidth = 230
    WinMove($hGUI, "", Default, Default, $iWidth + 20)

    ; Show dialog
    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $_ARRAYCONSTANT_GUI_EVENT_CLOSE
                ExitLoop

            Case $hCopy
                Local $sClip = ""

                ; Get selected indices [ _GUICtrlListView_GetSelectedIndices($hListView, True) ]
                Local $aiCurItems[1] = [0]
                For $i = 0 To GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMCOUNT, 0, 0)
                    If GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMSTATE, $i, 0x2) Then
                        $aiCurItems[0] += 1
                        ReDim $aiCurItems[$aiCurItems[0] + 1]
                        $aiCurItems[$aiCurItems[0]] = $i
                    EndIf
                Next

                ; Generate clipboard text
                If Not $aiCurItems[0] Then
                    For $sItem In $avArrayText
                        $sClip &= $sItem & @CRLF
                    Next
                Else
                    For $i = 1 To UBound($aiCurItems) - 1
                        $sClip &= $avArrayText[$aiCurItems[$i]] & @CRLF
                    Next
                EndIf
                ClipPut($sClip)
        EndSwitch
    WEnd
    GUIDelete($hGUI)

    Opt("GUIOnEventMode", $iOnEventMode)
    Opt("GUIDataSeparatorChar", $sDataSeparatorChar)

    Return 1
EndFunc   ;==>_ArrayDisplay_WithHeader

;~ _printDB2Recordset($Obj, 'SELECT * FROM "BI1VSNR"."AGENTURMANDANT"')

;~ Func _printDB2Recordset($sqlCon, $SQL)
;~     Local $sqlRs = ObjCreate('ADODB.Recordset')
;~     If Not @error Then
;~         $sqlRs.open($SQL, $sqlCon)
;~         If Not @error Then
;~             ;Loop until the end of file
;~             While Not $sqlRs.EOF
;~                 ;Retrieve data from the following fields
;~                 ConsoleWrite($sqlRs.Fields('Nr_Mandant' ).Value & @CRLF)
;~                 ConsoleWrite($sqlRs.Fields(1).Value & @CRLF)
;~                 ConsoleWrite($sqlRs.Fields('COUNTRY' ).Value & @CRLF)
;~                 $sqlRs.MoveNext
;~             WEnd
;~             $sqlRs.close
;~         EndIf
;~     EndIf
;~ EndFunc   ;==>_printDB2Recordset

[paz]

I know that, personally, I had some fun to figure out the proper connection string to access our oracle databases at work.

Depending on the driver you use, you may want to setup a DSN entry. In the code below, I have a function doing exactly that. I also commented-out an alternate connection string that uses the Microsoft's Oracle driver, for your reference.

This sample is much more bare-bones than the previous post (which I'm reading myself, to learn some more!), and I hope it can help you as well.

expandcollapse popup

#include "Array.au3"

;COM Error reporting. Will write every messages in a text file named like the autoit script, but with the .txt file type
Global $COMError = ObjEvent("AutoIt.Error","COMErrorFunc")


$DSN_Name = "MyFunkyDSN"
$userid="MyUserID"
$password = "MyPassword"

$sql_code = "select * from myTable"


;This will write the proper reg keys to create a DSN ODBC entry for my oracle server named "cdb1" (name from tsnnames.ora)
CreateOracleDSN($DSN_Name,"cdb1")


;Create the Connection object
$ORA_CONN = ObjCreate("ADODB.Connection")

;This connection string requires a DSN to be configured
$ORA_CONN.Open("DSN=" & $DSN_Name & ";UId=" & $userid & ";Pwd=" & $password & ";")


;This connection string works directly with the TSN name, no need to write reg keys... but I've read it's no longer maintained.
;$ORA_CONN.Open("Driver={Microsoft ODBC for Oracle};Server=" & $tsn_name & ";Uid="& $userid & ";Pwd=" & $password & ";")


;-----------------------------------------------------
;At this point, I'm supposed to be connected to Oracle
;-----------------------------------------------------


;Create a ADO Recordset object, and execute a SQL query
$RST = ObjCreate("ADODB.Recordset")
$RST.Open($sql_code, $ORA_CONN)


;This will save the data as a attribute-based XML.
$RST.Save(@ScriptDir & "\data.xml",1)


;This is a quick & dirty way to save the data as a header-less CSV text file
Const $adClipString = 2
$data = $RST.GetString($adClipString, -1,",",@crlf,"")
FileWriteLine(@ScriptDir & "\data.csv", $data)


;I can also get the data as a simple 2D array, and display it with AutoIT's UDF
$array = $RST.GetRows()
_ArrayDisplay($array)



;Close the recordset, once I'm finished
$RST.Close
$RST = 0


;-----------------------------------------------------
;End of script execution
;-----------------------------------------------------


Func COMErrorFunc()
    FileWriteLine(  StringTrimRight(@ScriptFullPath,3) & "txt", _
                    $COMError.Windescription & "/" & $COMError.description & _
                    $COMError.Scriptline _
                 )
    SetError(1)
Endfunc



Func CreateOracleDSN($dsn_name="", $tsn_name="", $user_id="", $password="")
;I will eventually code something to search for SQORA32.DLL instead of hardcoding the path...
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Driver","REG_SZ", "c:\\oracle\\102\\BIN\\SQORA32.DLL")
    
;Default values. Got them by looking at the registry after creating a DSN for Oracle
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Attributes","REG_SZ", "W")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FetchBufferSize","REG_SZ","64000")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "NumericSetting","REG_SZ","NLS")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ForceWCHAR","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverDelay","REG_SZ","10")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverRetryCount","REG_SZ","10")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "MetadataIdDefault","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BindAsDATE","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "CloseCursor","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSchemaOpt","REG_SZ","")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSyntax","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Application Attributes","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ResultSets","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "QueryTimeout","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Failover","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Lobs","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableMTS","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableDPM","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BatchAutocommitMode","REG_SZ","IfAllSuccessful")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DSN","REG_SZ",$dsn_name)
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Description","REG_SZ","AutoIT-Generated DSN")
    
;TSN Name for the oracle server
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ServerName","REG_SZ",$tsn_name)
;Default username - optional
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "UserID","REG_SZ",$user_id)
;Default password... not sure if it's safe to keep that in the registry;-)
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Password","REG_SZ",$password)
    
EndFunc

[niranjana]

Many thanks for your speedy response people..

I know that, personally, I had some fun to figure out the proper connection string to access our oracle databases at work.

Depending on the driver you use, you may want to setup a DSN entry. In the code below, I have a function doing exactly that. I also commented-out an alternate connection string that uses the Microsoft's Oracle driver, for your reference.

This sample is much more bare-bones than the previous post (which I'm reading myself, to learn some more!), and I hope it can help you as well.

expandcollapse popup

#include "Array.au3"

;COM Error reporting. Will write every messages in a text file named like the autoit script, but with the .txt file type
Global $COMError = ObjEvent("AutoIt.Error","COMErrorFunc")


$DSN_Name = "MyFunkyDSN"
$userid="MyUserID"
$password = "MyPassword"

$sql_code = "select * from myTable"


;This will write the proper reg keys to create a DSN ODBC entry for my oracle server named "cdb1" (name from tsnnames.ora)
CreateOracleDSN($DSN_Name,"cdb1")


;Create the Connection object
$ORA_CONN = ObjCreate("ADODB.Connection")

;This connection string requires a DSN to be configured
$ORA_CONN.Open("DSN=" & $DSN_Name & ";UId=" & $userid & ";Pwd=" & $password & ";")


;This connection string works directly with the TSN name, no need to write reg keys... but I've read it's no longer maintained.
;$ORA_CONN.Open("Driver={Microsoft ODBC for Oracle};Server=" & $tsn_name & ";Uid="& $userid & ";Pwd=" & $password & ";")


;-----------------------------------------------------
;At this point, I'm supposed to be connected to Oracle
;-----------------------------------------------------


;Create a ADO Recordset object, and execute a SQL query
$RST = ObjCreate("ADODB.Recordset")
$RST.Open($sql_code, $ORA_CONN)


;This will save the data as a attribute-based XML.
$RST.Save(@ScriptDir & "\data.xml",1)


;This is a quick & dirty way to save the data as a header-less CSV text file
Const $adClipString = 2
$data = $RST.GetString($adClipString, -1,",",@crlf,"")
FileWriteLine(@ScriptDir & "\data.csv", $data)


;I can also get the data as a simple 2D array, and display it with AutoIT's UDF
$array = $RST.GetRows()
_ArrayDisplay($array)



;Close the recordset, once I'm finished
$RST.Close
$RST = 0


;-----------------------------------------------------
;End of script execution
;-----------------------------------------------------


Func COMErrorFunc()
    FileWriteLine(  StringTrimRight(@ScriptFullPath,3) & "txt", _
                    $COMError.Windescription & "/" & $COMError.description & _
                    $COMError.Scriptline _
                 )
    SetError(1)
Endfunc



Func CreateOracleDSN($dsn_name="", $tsn_name="", $user_id="", $password="")
;I will eventually code something to search for SQORA32.DLL instead of hardcoding the path...
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Driver","REG_SZ", "c:\\oracle\\102\\BIN\\SQORA32.DLL")
    
;Default values. Got them by looking at the registry after creating a DSN for Oracle
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Attributes","REG_SZ", "W")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FetchBufferSize","REG_SZ","64000")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "NumericSetting","REG_SZ","NLS")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ForceWCHAR","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverDelay","REG_SZ","10")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverRetryCount","REG_SZ","10")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "MetadataIdDefault","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BindAsDATE","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "CloseCursor","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSchemaOpt","REG_SZ","")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSyntax","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Application Attributes","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ResultSets","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "QueryTimeout","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Failover","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Lobs","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableMTS","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableDPM","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BatchAutocommitMode","REG_SZ","IfAllSuccessful")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DSN","REG_SZ",$dsn_name)
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Description","REG_SZ","AutoIT-Generated DSN")
    
;TSN Name for the oracle server
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ServerName","REG_SZ",$tsn_name)
;Default username - optional
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "UserID","REG_SZ",$user_id)
;Default password... not sure if it's safe to keep that in the registry;-)
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Password","REG_SZ",$password)
    
EndFunc

[reva]

Thanks for the idea.

I'm new to Autoit Com objects. Like pointers in C, they seem like life and heart of Autoit.

I tried to modify your script for connecting to database (TNSNAMES is given below). Please help me !

(C:\oracle\ora92\network\admin\tnsnames.ora)

CU1CON =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.99)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = CU1CON)

)

)

I know that, personally, I had some fun to figure out the proper connection string to access our oracle databases at work.

Depending on the driver you use, you may want to setup a DSN entry. In the code below, I have a function doing exactly that. I also commented-out an alternate connection string that uses the Microsoft's Oracle driver, for your reference.

This sample is much more bare-bones than the previous post (which I'm reading myself, to learn some more!), and I hope it can help you as well.

expandcollapse popup

#include "Array.au3"

;COM Error reporting. Will write every messages in a text file named like the autoit script, but with the .txt file type
Global $COMError = ObjEvent("AutoIt.Error","COMErrorFunc")


$DSN_Name = "MyFunkyDSN"
$userid="MyUserID"
$password = "MyPassword"

$sql_code = "select * from myTable"


;This will write the proper reg keys to create a DSN ODBC entry for my oracle server named "cdb1" (name from tsnnames.ora)
CreateOracleDSN($DSN_Name,"cdb1")


;Create the Connection object
$ORA_CONN = ObjCreate("ADODB.Connection")

;This connection string requires a DSN to be configured
$ORA_CONN.Open("DSN=" & $DSN_Name & ";UId=" & $userid & ";Pwd=" & $password & ";")


;This connection string works directly with the TSN name, no need to write reg keys... but I've read it's no longer maintained.
;$ORA_CONN.Open("Driver={Microsoft ODBC for Oracle};Server=" & $tsn_name & ";Uid="& $userid & ";Pwd=" & $password & ";")


;-----------------------------------------------------
;At this point, I'm supposed to be connected to Oracle
;-----------------------------------------------------


;Create a ADO Recordset object, and execute a SQL query
$RST = ObjCreate("ADODB.Recordset")
$RST.Open($sql_code, $ORA_CONN)


;This will save the data as a attribute-based XML.
$RST.Save(@ScriptDir & "\data.xml",1)


;This is a quick & dirty way to save the data as a header-less CSV text file
Const $adClipString = 2
$data = $RST.GetString($adClipString, -1,",",@crlf,"")
FileWriteLine(@ScriptDir & "\data.csv", $data)


;I can also get the data as a simple 2D array, and display it with AutoIT's UDF
$array = $RST.GetRows()
_ArrayDisplay($array)



;Close the recordset, once I'm finished
$RST.Close
$RST = 0


;-----------------------------------------------------
;End of script execution
;-----------------------------------------------------


Func COMErrorFunc()
    FileWriteLine(  StringTrimRight(@ScriptFullPath,3) & "txt", _
                    $COMError.Windescription & "/" & $COMError.description & _
                    $COMError.Scriptline _
                 )
    SetError(1)
Endfunc



Func CreateOracleDSN($dsn_name="", $tsn_name="", $user_id="", $password="")
;I will eventually code something to search for SQORA32.DLL instead of hardcoding the path...
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Driver","REG_SZ", "c:\\oracle\\102\\BIN\\SQORA32.DLL")
    
;Default values. Got them by looking at the registry after creating a DSN for Oracle
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Attributes","REG_SZ", "W")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FetchBufferSize","REG_SZ","64000")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "NumericSetting","REG_SZ","NLS")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ForceWCHAR","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverDelay","REG_SZ","10")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverRetryCount","REG_SZ","10")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "MetadataIdDefault","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BindAsDATE","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "CloseCursor","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSchemaOpt","REG_SZ","")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSyntax","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Application Attributes","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ResultSets","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "QueryTimeout","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Failover","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Lobs","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableMTS","REG_SZ","T")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableDPM","REG_SZ","F")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BatchAutocommitMode","REG_SZ","IfAllSuccessful")
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DSN","REG_SZ",$dsn_name)
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Description","REG_SZ","AutoIT-Generated DSN")
    
;TSN Name for the oracle server
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ServerName","REG_SZ",$tsn_name)
;Default username - optional
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "UserID","REG_SZ",$user_id)
;Default password... not sure if it's safe to keep that in the registry;-)
    RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Password","REG_SZ",$password)
    
EndFunc