Sign in to follow this  
Followers 0
niranjana

Database connection

5 posts in this topic

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,

Share this post


Link to post
Share on other sites



Hi,

this can be a start:

#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

Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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.

#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

Share this post


Link to post
Share on other sites

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.

#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

Share this post


Link to post
Share on other sites

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.

#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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0