Accessing An Oracle Database

[uberis]

Hi everybody,

does anybody of you ever successfully accessed an Oracle database out of an AutoIt3 script (connect, running an sql, etc.)? If so, does anybody have an example available of how to manage this stuff? Any other 3rd party tools needed?

Thx for your help in advance.

Harald

[ptrex]

@Uberis

I see that you have found the ODBCQuery yourselve

http://www.autoitscript.com/forum/index.php?showtopic=11147

This is the best I found so far.

expandcollapse popup

; ==================================================================================
; ODBCquery
;
; History:
;  - 1.0  - menu items functional
;           why not name it 1.0
;  - 0.3  - Cleanup of code, variable renaming
;        - added more comments
;  - 0.2  - Catalog tab added for database,table and column overview
;        - trap COM errors
;        - Com object error function
;        - optional userid, password, database for ODBC connection
;        - use of accelerator keys
;        - About box and statusbar update
;         - copy to clipboard of catalog/query results
;        - window resize supported
;  - 0.1  - "Release" / given a version number
;
; Forum Threads:
;  - http://www.autoitscript.com/forum/index.php?showtopic=7547&view=getnewpost
;
; ==================================================================================
#NoTrayIcon
#include <GUIConstants.au3>
#include <GUIListView.au3>

; ========================================
; Variables
; ========================================
Global Const $s_Title   = 'ODBCquery'
Global Const $s_Version = '0.3'
Global     $s_filenm  = ""

; ADO  QueryType                              ; Criteria
Global Const $i_ADOadSchemaCatalogs   = 1  ; table_catalog
Global Const $i_ADOadSchemaColumns     = 4  ; table_catalog - table_schema - table_name - column_name
Global Const $i_ADOadSchemaIndexes     = 12 ; table_catalog - table_schema - index_name - type - table_name
Global Const $i_ADOadSchemaProcedures   = 16 ; procedure_catalog - procedure_schema - procedure_name - procedure_type
Global Const $i_ADOadSchemaTables       = 20 ; table_catalog - table_schema - table_name - table_type
Global Const $i_ADOadSchemaProviderTypes = 22 ; data_type - best_match
Global Const $i_ADOadSchemaViews         = 23 ; table_catalog - table_schema - table_name
Global       $o_ADOconn, $o_ADOrs, $b_ADOiscon=0
; ADO cursortype and locktype
Global Const $i_adOpenDynamic   = 2
Global Const $i_adLockOptimistic = 3

; DSN 
Global Const $s_ODBCregUser   = "HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
Global Const $s_ODBCregSystem = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
Global Const $s_DSNHdr      = "DSN|Type|Description"
Global       $s_DSNfull, $s_DSNname, $s_DSNuid, $s_DSNpwd, $s_DSNdb, $i_DSNcount=0

; Listview headers
Global     $s_SchemaHdr  = "Catalog information"
Global       $s_ResultHdr    = "Query result" 

; ========================================
; COM object error handler
; ========================================
Global $i_ObjectDebug = 0                               ; 1 = show msgbox for every error
Global $s_ObjectErrorMsg                                ; Buffered message
$o_ComError = ObjEvent("AutoIt.Error","Object_Error")   ; Install a custom error handler 


; ========================================
; GUI controls
; ========================================
$h_Gui = GuiCreate($s_Title, 704, 488, 490,360 ,BitOR($WS_OVERLAPPEDWINDOW, $WS_CLIPSIBLINGS))

; --- File Menu ---
$mn_file = GUICtrlCreateMenu ("&File")
GUICtrlSetResizing (-1,$GUI_DOCKMENUBAR)
$mn_open   = GUICtrlCreateMenuitem ("Open",$mn_file)
GUICtrlSetState(-1,$GUI_DISABLE)
$mn_recent = GUICtrlCreateMenu ("Recent Files",$mn_file)
GUICtrlSetState(-1,$GUI_DISABLE)
$mn_saveas = GUICtrlCreateMenuitem ("Save as",$mn_file)
GUICtrlSetState(-1,$GUI_DISABLE)
$mn_exit   = GUICtrlCreateMenuitem ("E&xit",$mn_file)
GUICtrlCreateMenuitem ("",$mn_file,2); create a separator line
; --- View Menu ---
$mn_view = GUICtrlCreateMenu("View")
$mn_status = GUICtrlCreateMenuitem ("Statusbar",$mn_view)
GUICtrlSetState(-1,$GUI_CHECKED)
$mn_odbc = GUICtrlCreateMenuitem ("ODBC messages",$mn_view)
; --- Help Menu ---
$mn_help = GUICtrlCreateMenu ("Help")
$mn_about = GUICtrlCreateMenuitem ("About",$mn_help)

; --- Statusbar ---
$sb_connect = GUICtrlCreateLabel('Disconnected',2,448,200,16,BitOr($SS_SIMPLE,$SS_SUNKEN))
GUICtrlSetResizing (-1,$GUI_DOCKSTATEBAR)
$sb_info = GUICtrlCreateLabel ('',205,448,495,16,BitOr($SS_SIMPLE,$SS_SUNKEN))
GUICtrlSetResizing (-1,$GUI_DOCKSTATEBAR)
; sb_progress - future use

; --- Tab ---
$tab=GUICtrlCreateTab (2,2, 700,440)
    GUICtrlSetResizing (-1,$GUI_DOCKBORDERS)

$tab_conn=GUICtrlCreateTabitem (" Connection ")
    $lst_dsn = GUICtrlCreateListView($s_DSNHdr, 20, 50, 550, 380, -1, BitOR( $LVS_EX_FULLROWSELECT , $LVS_EX_GRIDLINES))
    ODBCsources($lst_dsn,$s_ODBCregUser  , "USER"  )
    ODBCsources($lst_dsn,$s_ODBCregSystem, "SYSTEM")
    _GUICtrlListViewSetItemSelState ($lst_dsn, 0)   ; select the first row

    GUICtrlCreateLabel("Optional:", 580, 50, 100, 20, -1, $WS_EX_TRANSPARENT )
    GUICtrlCreateLabel("Uid:", 580,  85, 30, 20, -1, $WS_EX_TRANSPARENT )
    GUICtrlCreateLabel("Pwd:", 580, 120, 30, 20, -1, $WS_EX_TRANSPARENT )
    GUICtrlCreateLabel("DB :", 580, 155, 30, 20, -1, $WS_EX_TRANSPARENT )
    $inp_uid   = GUICtrlCreateInput("", 610,  85, 80, 20)
    $inp_pwd   = GUICtrlCreateInput("", 610, 120, 80, 20, $ES_PASSWORD)
    $inp_db = GUICtrlCreateInput("", 610, 155, 80, 20)
    $bt_conn   = GUICtrlCreateButton("&Connect"   , 600, 200, 80, 20, $BS_DEFPUSHBUTTON)
    $bt_discon = GUICtrlCreateButton("&Disconnect", 600, 240, 80, 20)
    GUICtrlSetState(-1, $GUI_DISABLE )

$tab_schema=GUICtrlCreateTabitem (" Catalog ")
GUICtrlSetState($tab_schema,$GUI_DISABLE)
    $lst_schema = GUICtrlCreateListView($s_SchemaHdr, 20, 50, 550, 380)
    $bt_db    = GUICtrlCreateButton("&Databases", 600,  70, 80, 20)
    $bt_tbl  = GUICtrlCreateButton("&Tables"   , 600, 110, 80, 20)
    $bt_col  = GUICtrlCreateButton("&Columns"  , 600, 150, 80, 20)
    $bt_scopy   = GUICtrlCreateButton("&Clipboard", 600, 210, 80, 20)
    $bt_ssaveas = GUICtrlCreateButton("Save &as"  , 600, 250, 80, 20)

$tab_query=GUICtrlCreateTabitem (" Query ")
GUICtrlSetState($tab_query,$GUI_DISABLE)
    $ed_qry  = GUICtrlCreateEdit("", 20, 50, 550, 380)
    $bt_run  = GUICtrlCreateButton("&Run"   , 600,  70, 80, 20)
    $bt_clr  = GUICtrlCreateButton("Cl&ear"  , 600, 110, 80, 20)
    $bt_open    = GUICtrlCreateButton("&Open"   , 600, 180, 80, 20)
    $bt_saveas  = GUICtrlCreateButton("Save &as", 600, 220, 80, 20)

$tab_result=GUICtrlCreateTabitem (" Result ")
GUICtrlSetState($tab_result,$GUI_DISABLE)
    $lst_result = GUICtrlCreateListView($s_ResultHdr, 20, 50, 550, 350)
    $bt_new  = GUICtrlCreateButton("&New query"  , 600,  70, 80, 20)
    $bt_rcopy   = GUICtrlCreateButton("&Clipboard"  , 600, 140, 80, 20)
    $bt_rsaveas = GUICtrlCreateButton("Save &as"    , 600, 180, 80, 20)
GUICtrlCreateTabitem (""); end of Tab control

; --- Show Gui ---
GUICtrlSetState($lst_dsn,$GUI_FOCUS); Focus on dsn list
GUISetState (@SW_SHOW,$h_Gui)               


While 1
    $msg = GUIGetMsg()
    if $msg <> 0 then
        Select
        Case $msg = $GUI_EVENT_CLOSE or $msg=$mn_exit
            ExitLoop
        Case $msg = $mn_open OR $msg = $bt_open
            SQLopen()
          Case $msg = $mn_saveas OR $msg = $bt_saveas
            SQLsave()
        Case $msg = $mn_status
            If BitAnd(GUICtrlRead($mn_status),$GUI_CHECKED) = $GUI_CHECKED Then
                GUICtrlSetState($mn_status ,$GUI_UNCHECKED)
                GUICtrlSetState($sb_connect,$GUI_HIDE)
                GUICtrlSetState($sb_info   ,$GUI_HIDE)
            Else
                GUICtrlSetState($mn_status ,$GUI_CHECKED)
                GUICtrlSetState($sb_connect,$GUI_SHOW)
                GUICtrlSetState($sb_info   ,$GUI_SHOW)
            EndIf
        Case $msg = $mn_odbc
            If BitAnd(GUICtrlRead($mn_odbc),$GUI_CHECKED) = $GUI_CHECKED Then
                GUICtrlSetState($mn_odbc ,$GUI_UNCHECKED)
                $i_ObjectDebug = 0
            Else
                GUICtrlSetState($mn_odbc ,$GUI_CHECKED)
                $i_ObjectDebug = 1
            EndIf
        Case $msg = $mn_about
            About()
        Case $msg = $bt_conn
            ODBCconnect()
        Case $msg = $bt_discon
            $o_ADOconn.Close
            GUICtrlSetState($bt_conn    , $GUI_ENABLE  )
            GUICtrlSetState($bt_discon  , $GUI_DISABLE )
            GUICtrlSetState($mn_open    , $GUI_DISABLE )
            GUICtrlSetState($mn_recent  , $GUI_DISABLE )
            GUICtrlSetState($mn_saveas  , $GUI_DISABLE )
            GUICtrlSetState($tab_schema , $GUI_DISABLE )
            GUICtrlSetState($tab_query  , $GUI_DISABLE )
            GUICtrlSetState($tab_result , $GUI_DISABLE )
            $b_ADOiscon=0
            StatusBar("Disconnected")
        Case $msg = $bt_db
            ODBCschema($i_ADOadSchemaCatalogs)
        Case $msg = $bt_tbl
            ODBCschema($i_ADOadSchemaTables)
        Case $msg = $bt_col
            ODBCschema($i_ADOadSchemaColumns)
        Case $msg = $bt_scopy
            SAVElist($lst_schema,$s_SchemaHdr)          ; clipboard copy
        Case $msg = $bt_ssaveas
            SAVElist($lst_result,$s_ResultHdr, "file")  ; save schema to file
        Case $msg = $bt_run
            ODBCquery(GUICtrlRead($ed_qry))
        Case $msg = $bt_clr
            GUICtrlSetData($ed_qry, "" )                ; clear data
            GUICtrlSetState($lst_result,$GUI_FOCUS)
        Case $msg = $bt_new
            GUICtrlSetState($tab_query, $GUI_SHOW )     ; show query tab
            GUICtrlSetState($ed_qry, $GUI_FOCUS )       ; select edit field
        Case $msg = $bt_rcopy
            SAVElist($lst_result,$s_ResultHdr)          ; clipboard copy
        Case $msg = $bt_rsaveas
            SAVEList($lst_result,$s_ResultHdr, "file")  ; save result to file
        Case $msg = $tab_query
            GUICtrlSetState($tab_query, $GUI_SHOW  )    ; show query tab
            GUICtrlSetState($ed_qry   , $GUI_FOCUS )    ; select edit field
      EndSelect
    EndIf
WEnd
GUIDelete()
Exit

Func SAVElist($l_list, $s_hdr="", $filenm="")
    Local $a_indices = _GUICtrlListViewGetSelectedIndices($l_list,1)
    If(IsArray($a_indices))Then
        Local $i, $buf
        $buf=$s_hdr & @LF
        For $i = 1 To $a_indices[0]
            $buf &= _GUICtrlListViewGetItemText ($l_list, $a_indices[$i], -1)  & @LF 
        Next
        if $filenm <> "" Then
            $filenm =  FileSaveDialog( "Save as...", '.', "All files (*.*)", 3)
            If @error <> 1 Then 
                FileWrite($filenm,$buf)
                GUICtrlSetData($sb_info, "Selected rows saved to file")
            EndIf
        Else
            ClipPut($buf)
            GUICtrlSetData($sb_info, "Selected rows copied to clipboard")
        Endif
    Else
        GUICtrlSetData($sb_info, "Not Items Selected")
    EndIf
EndFunc

Func SQLopen()
    if $b_ADOiscon = 1 then
        $s_filenm = FileOpenDialog("Choose file...",'.',"SQLfiles (*.sql)|All (*.*)")
        If @error <> 1 Then 
            GUICtrlCreateMenuitem ($s_filenm,$mn_recent)
            GUICtrlSetData($ed_qry,FileRead($s_filenm,4096))
        Endif
    Endif
EndFunc

Func SQLsave()
    if $b_ADOiscon = 1 then
        $s_filenm =  FileSaveDialog( "Save as...", $s_filenm, "SQLfiles (*.sql)", 3)
        If @error <> 1 Then 
            FileWrite($s_filenm,GUICtrlRead($ed_qry))
        Endif
    Endif
EndFunc


Func ODBCsources($h_controlID, $s_RegEntry, $s_Type)
    Local $s_List, $i_dsncount, $s_VarNm, $s_Value, $i
    $i = 1
    $s_VarNm = RegEnumVal($s_RegEntry,  $i)
    $s_Value = RegRead($s_RegEntry, $s_Varnm)
    While $s_VarNm <> ""
        $s_VarNm = RegEnumVal($s_RegEntry,  $i)
        $s_Value = RegRead($s_RegEntry, $s_Varnm)
        If $s_Varnm <> "" Then
            GUICtrlCreateListViewItem($s_VarNm & "|" & $s_Type & "|" & $s_Value, $h_controlID)
            $i += 1
            $i_DSNcount += 1
        EndIf
    Wend
EndFunc

Func ODBCconnect()
    $dsnarray = StringSplit((GUICtrlRead(GUICtrlRead($lst_dsn), 2)), "|")   ; retrieve selected row
    if $dsnarray[1] <> "" Then
        $s_DSNName = $dsnarray[1]
        $s_DSNuid  = GUICtrlRead($inp_uid)
        $s_DSNpwd  = GUICtrlRead($inp_pwd)
        $s_DSNdb   = GUICtrlRead($inp_db)
        $s_DSNfull = "DSN=" & $s_DSNName & ";"
        if $s_DSNuid <> "" then $s_DSNfull &= "Uid=" & $s_DSNuid & ";"
        if $s_DSNpwd <> "" then $s_DSNfull &= "Pwd=" & $s_DSNpwd & ";"
        if $s_DSNdb  <> "" then $s_DSNfull &= "database=" & $s_DSNdb & ";"
        $o_ADOconn = ObjCreate ("ADODB.Connection")
        $o_ADOconn.Open ($s_DSNfull)
        if @error then 
            msgbox(64, "Connection error", "Error connecting to database DSN=["&$s_DSNfull &"]")
        else
            GUICtrlSetState($bt_conn  , $GUI_DISABLE )
            GUICtrlSetState($bt_discon, $GUI_ENABLE  )
            Statusbar("","Connected to "&$s_DSNName  )
            GUICtrlSetState($tab_schema , $GUI_ENABLE )
            GUICtrlSetState($tab_query  , $GUI_ENABLE )
            GUICtrlSetState($tab_result , $GUI_ENABLE )
            GUICtrlSetState($tab_query  , $GUI_SHOW   )     ; show query tab
            GUICtrlSetState($ed_qry  , $GUI_FOCUS  )        ; select edit field
            GUICtrlSetState($mn_open    , $GUI_ENABLE )
            GUICtrlSetState($mn_recent  , $GUI_ENABLE )
            GUICtrlSetState($mn_saveas  , $GUI_ENABLE )
            $b_ADOiscon=1
        Endif   
    Endif
EndFunc
        
Func StatusBar($info, $conn="")
   if $conn <> "" Then  GUICtrlSetData($sb_connect, $conn)
   if $info <> "" Then  GUICtrlSetData($sb_info   , $info)
EndFunc
        
        
Func ODBCquery($s_Qry)
    Local $cmboVal, $n
    StatusBar("Starting query...")

    $o_ADOrs = ObjCreate ("ADODB.Recordset")
    $o_ADOrs.CursorType = $i_adOpenDynamic
    $o_ADOrs.LockType = $i_adLockOptimistic
    $o_ADOrs.Open ($s_Qry, $o_ADOconn)
    if @error Then
        msgbox(64,"Info","Error in query !",2);
        StatusBar("Error in query !");
        Return
    Endif
        
    GUICtrlSetState($tab_result, $GUI_SHOW )        ; show result tab
    GuiSwitch($h_Gui,$tab_result)
    $a_size = ControlGetPos("", "", $lst_schema)    ; retrieve controls current postion and size
    GuiCtrlDelete($lst_result)
    With $o_ADOrs
      $s_ResultHdr=""                               ; Get information about Fields collection
      For $n = 0 To .Fields.Count - 1
         $s_ResultHdr &= .Fields($n).Name & "|"
      Next
      $lst_result = GUICtrlCreateListView($s_ResultHdr, $a_size[0], $a_size[1], $a_size[2], $a_size[3],  $LVS_REPORT, BitOR( $LVS_EX_FULLROWSELECT , $LVS_EX_GRIDLINES) )
      GUICtrlSetResizing (-1,$GUI_DOCKAUTO)

      If .RecordCount Then
         While Not .EOF
            $cmboVal = ""
            For $n = 0 To .Fields.Count - 1
                $cmboVal &= .Fields($n).Value & "|"
            Next
            $cmboVal = StringTrimRight($cmboVal, 1)
            GUICtrlCreateListViewItem($cmboVal, $lst_result)
            .MoveNext
         WEnd
      EndIf
   EndWith
   GUICtrlCreateTabItem("")
   GUICtrlSetState($lst_result,$GUI_FOCUS)
   StatusBar("Done")
   GUISetState (@SW_SHOW,$h_Gui)
EndFunc


Func ODBCschema($i_qrytype)
    Local $n
    GUICtrlSetState($tab_schema, $GUI_SHOW )        ; show result tab
    GuiSwitch($h_Gui,$tab_schema)

    $a_size = ControlGetPos("", "", $lst_schema)       ; retrieve controls current postion and size
    GuiCtrlDelete($lst_schema)

    StatusBar("Retrieving schema..")
    $o_ADOrs = $o_ADOconn.OpenSchema ($i_qrytype)
    With $o_ADOrs
        $s_SchemaHdr=""                             ; Get information about Fields collection
        For $n = 0 To .Fields.Count - 1
            $s_SchemaHdr &= .Fields($n).Name & "|"
        Next
        $lst_schema = GUICtrlCreateListView($s_SchemaHdr, $a_size[0], $a_size[1], $a_size[2], $a_size[3],  $LVS_REPORT, BitOR( $LVS_EX_FULLROWSELECT , $LVS_EX_GRIDLINES) )
          GUICtrlSetResizing (-1,$GUI_DOCKAUTO)
        While Not .EOF
            $cmboVal = ""
            For $n = 0 To .Fields.Count - 1
               $cmboVal &= .Fields($n).Value & "|"
            Next
            $cmboVal = StringTrimRight($cmboVal, 1)
            GUICtrlCreateListViewItem($cmboVal, $lst_schema)
            .MoveNext
        WEnd
   EndWith
   GUICtrlCreateTabItem("")
   GUICtrlSetState($lst_schema,$GUI_FOCUS)
   StatusBar("Done")
   GUISetState (@SW_SHOW,$h_Gui)
EndFunc


; ========================================
; GUI - About
; ========================================
Func About()
    $gui_About = GuiCreate('About', 315, 200, -1, -1, BitOr($WS_CAPTION, $WS_SYSMENU), -1, $h_Gui)
    GUICtrlCreateIcon ("shell32.dll", 175, 5,8)
    GuiCtrlCreateLabel($s_Title & ' v' & $s_Version & '        -           The Simple Query Utility' & @LF & _
                   @LF & _
                   'This application is a simple program for easily retrieving  ' & _
                   'information from a database using ODBC. ' & @LF & _
                   @LF & _
                   'It was written in AutoIt3 script by Hans Harder.' & @LF & _
                   @LF & _
                   ' ' , 45, 15, 290, 140)
    $lb_Ab_VisitSite     = _GuiCtrlCreateHyperlink('Visit the AutoIt Website', 5, 155, 165, 15, 0x0000ff, 'http://www.autoitscript.com')
    $lb_Ab_ContactAuthor = _GuiCtrlCreateHyperlink('Contact Hans Harder', 5, 175, 165, 15, 0x0000ff, 'mailto:hans@atabas.org')
    $bt_Ab_Close         = GuiCtrlCreateButton('&Close', 225, 165, 75, 25)
    GuiSetState(@SW_SHOW, $gui_About)

    While 1
        $msg = GUIGetMsg()
        if $msg <> 0 then
           Select
            Case $msg = $lb_Ab_VisitSite
                _Start('http://www.autoitscript.com')
            Case $msg = $lb_Ab_ContactAuthor
                _Start('"mailto:hans@atbas.org?Subject=AutoIt3 QDBCquery Application"')
            Case $msg = $GUI_EVENT_CLOSE Or $msg = $bt_Ab_Close
                ExitLoop
            EndSelect
        Endif
    WEnd
    GuiSetState(@SW_HIDE, $gui_About)
    GuiDelete($gui_About)
EndFunc

;===============================================================================
;
; Function Name:    _GuiCtrlCreateHyperlink()
; Description:    Creates a label that acts as a hyperlink
;
; Parameter(s):     $s_Text    - Label text
;                           $i_Left       - Label left coord
;                           [$i_Top]      - Label top coord
;                           [$i_Width]    - Label width
;                           [$i_Height]   - Label height
;                           [$i_Color]    - Text Color
;                           [$s_ToolTip]  - Hyperlink ToolTip
;                           [$i_Style]    - Label style
;                           [$i_ExStyle]  - Label extended style
;
; Requirement(s):   None
; Return Value(s):  Control ID
;
; Author(s):        Saunders <krawlie@hotmail.com>
;
;===============================================================================

Func _GuiCtrlCreateHyperlink($S_TEXT, $I_LEFT, $I_TOP, _
        $I_WIDTH = -1, $I_HEIGHT = -1, $I_COLOR = 0x0000ff, $S_TOOLTIP = '', $I_STYLE = -1, $I_EXSTYLE = -1)
    Local $I_CTRLID
    $I_CTRLID = GUICtrlCreateLabel($S_TEXT, $I_LEFT, $I_TOP, $I_WIDTH, $I_HEIGHT, $I_STYLE, $I_EXSTYLE)
    If $I_CTRLID <> 0 Then
        GUICtrlSetFont($I_CTRLID, -1, -1, 4)
        GUICtrlSetColor($I_CTRLID, $I_COLOR)
        GUICtrlSetCursor($I_CTRLID, 0)
    EndIf
    Return $I_CTRLID
EndFunc ;==>_GuiCtrlCreateHyperlink

Func _Start($s_StartPath)
    If @OSType = 'WIN32_NT' Then
        $s_StartStr = @ComSpec & ' /c start "" '
    Else
        $s_StartStr = @ComSpec & ' /c start '
    EndIf
    Run($s_StartStr & $s_StartPath, '', @SW_HIDE)
EndFunc

Func Object_Error() 
   $HexNumber=hex($o_ComError.number,8) 
   $s_ObjectError="COM Error = " & $HexNumber & @CRLF & _
                  "Windescription is: " & $o_ComError.windescription 
   if $i_ObjectDebug <> 0 Then msgbox(64, "Database error", "Database Error : "& $s_ObjectError &"")
   SetError(1); something to check for when this function returns 
Endfunc

Search in Scripts & Scraps for ODBC and/or Query and you'll find al lot.

Also see my signature to see some other SQL possibilities, like the SQL LogParser.

Enjoy !!

Edited May 3, 2006 by ptrex

[uberis]

@ptrex

Thnx a lot for your fast reply. yes, I found that stuff you mentioned in the meantime. I will give it a try to see if it will work with an Oracle database so far. I only thought that one guy might be outside that have had a successful connection to an Oracle database so far.

[radefeld]

Hey uberis - Any luck on the Oracle connectivity - I have a project that would be so much easier if I could connect with Oracle through AutoIT (and wondered if anyone had created something that could get me that much closer)

Thanks!

[ptrex]

I have made a succesful connection to Oracle.

I use the script as displayed in the 2nd post.

You will have to setup an ODBC connection. Than run this tool to connect.

Very Easy !!

[Geert]

ODBCquery does not work correctly on dutch version op Windows XP Pro. (100 percent CPU load and no GUI)

I changed the ODBCsources function. Now it's working perfectly!

Func ODBCsources($h_controlID, $s_RegEntry, $s_Type)
    Local $s_List, $i_dsncount, $s_VarNm, $s_Value, $i
    $i = 1
    While 1
        $s_VarNm = RegEnumVal($s_RegEntry, $i)
        If @error <> 0 Then ExitLoop
        $s_Value = RegRead($s_RegEntry, $s_VarNm)
        GUICtrlCreateListViewItem($s_VarNm & "|" & $s_Type & "|" & $s_Value, $h_controlID)
        $i += 1
        $i_dsncount += 1
    WEnd
EndFunc  ;==>ODBCsources

[ptrex]

@Geert

This is because as of Beta 120 the behaviour of the REGENUM has changed.

see here for more info http://www.autoitscript.com/forum/index.php?showtopic=25873

Sorry that I did not mention it before.

Thanks for changing the routine.

Regards / Groeten,

ptrex

[noleghair]

i have a peice of VBA im using in access to retrieve information from an Oracle ERP database and dump it into a "divisional" database

The VBA is:

expandcollapse popup

Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Dim objComm As ADODB.Command
Dim objParam1 As ADODB.Parameter
Dim objParam2 As ADODB.Parameter
Dim objParam3 As ADODB.Parameter
Dim objParam4 As ADODB.Parameter
Dim objParam5 As ADODB.Parameter
Dim objParam6 As ADODB.Parameter
Dim strid As String

Set objConn = New ADODB.Connection
objConn.Open "Provider=MSDAORA.1;Password=XXXXX;User ID=XXXXX;Data Source=XXXX;Persist Security Info=True"

Set objComm = New ADODB.Command
Set objComm.ActiveConnection = objConn
objComm.CommandText = "GET_DEPT_PROD_CNT"
objComm.CommandType = adCmdStoredProc

Set objParam6 = objComm.CreateParameter("RETURN_VALUE", adNumeric, adParamReturnValue)
objComm.Parameters.Append objParam6

Set objParam1 = objComm.CreateParameter("P_CELL", adVarChar, adParamInput, 4000, "266070")
objComm.Parameters.Append objParam1

Set objParam2 = objComm.CreateParameter("P_ICNT", adVarChar, adParamInput, 4000, "10")
objComm.Parameters.Append objParam2

Set objParam3 = objComm.CreateParameter("P_PLIN", adVarChar, adParamInput, 4000, "1")
objComm.Parameters.Append objParam3

Set objParam4 = objComm.CreateParameter("P_SHIFT", adVarChar, adParamInput, 4000, "D")
objComm.Parameters.Append objParam4

ProdDate = Format(ProdDate, "d mmm yyyy")
Set objParam5 = objComm.CreateParameter("P_DATE", adVarChar, adParamInput, 4000, lblDate.txt)
objComm.Parameters.Append objParam5

objComm.Execute

Label1.Caption = objParam6

Set objParam1 = Nothing
Set objParam2 = Nothing
Set objParam3 = Nothing
Set objParam4 = Nothing
Set objParam5 = Nothing
Set objParam6 = Nothing
Set objComm = Nothing
Set objConn = Nothing

Exit Sub

errHandler:

End Sub

Where im using a stored procedure with some parameters to get the info that i need. Can anyone help me with exactly how i can do this in auto it? I tried setting up a connection in the administrative tools and using this GUI, but i failed miserably. Im not an autoit pro, but i am trying to learn

Thank you for any help you can give

[ptrex]

@noleghair

This is an example of a succesful connect to an Oracle DB

expandcollapse popup

#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
    .ConnectionString =("Provider='OraOLEDB.Oracle';Data Source='xxx';User Id='system';Password='xxx';") 
    .Open
EndWith

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

With $adors
        .ActiveConnection = $ado
        .Source = "select * from xxx where xxx like 'xxx%'"
        .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
    $adors.MoveNext                                            ; Go to the next record
WEnd

; This is Sven P's custom error handler added by ptrex
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

It has a COM error handler, this gives feedback in case of wrong syntax

Most of the connection errors are because of wrong SID parameters passed to oracle.

If you get it to work let me know and share the code.

Edited June 20, 2006 by ptrex

[uberis]

Hi,

I have connected to an oracle db successfully, but I am still struggling with calling stored procedures, especially when there is a need to supply it with parameters (input as well as output). When I try it with the command object, no record sets are retrieved. Has anybody has had success in accessing and providing a stored procedure with parameters?

Greetings Harald

[ptrex]

@uberis

I don' t have experience with it myself, but this should get you started !!

Oracle® Provider for OLE DB Developer's Guide

Regards,

ptrex

[uberis]

@ptrex

Thank you for your support and the link for that documentation. I will give it a try to see if I get my task running.

uberis

[uberis]

Hi,

I attach an example of a successful connect to an oracle database, calling a stored procedure with 1 return flag, 3 input parameters as variables and also 3 output parameters. Hope that it can give you some idea of working with oracle databases.

expandcollapse popup

; -------------------------------------------------------------------
; function: Access_DB
;          Access oracle database and get required information
; -------------------------------------------------------------------
Func Access_Oracle_DB()
    
    Local $objconn, $objCmd, $objRS, $objPara

; we set up the required oracle environment
    $aOraPath = '\\(server)\(ora_instdir)\bin;\\\\(server)\(ora_instdir)\network\admin;'
    $aOraHome = '\\\\(server)\(ora_instdir)'
    $aOraTNS  = $aOraHome & '\network\admin'
    $aOraNLS  = 'GERMAN_GERMANY.WE8ISO8859P1'
    $aPath = EnvGet("Path")
    EnvSet("Path", $aPath & ';' & $aOraPath)
    EnvSet("ORACLE_HOME", $aOraHome)
    EnvSet("TNS_ADMIN", $aOraTNS)
    EnvSet("NLS_LANG", $aOraNLS)
    EnvUpdate()

; some variable definition for accessing the oracle com objects
    $adUseServer = 2
    $adUseClient = 3
    $adCmdText = 1
    $adCmdStoredProc = 4
    $adBSTR = 8; null terminated chr string
    $adVariant = 12
    $adChar = 8
    $adVarChar = 200

    $adIN = 1
    $adOUT = 2
    $adINOUT = 3
    $adParamReturnValue = 4 ;direction of variables that return a e.g. a flag

; definition of the database connection
; pls exchange all values in square brackets with real values out of you environment !!!!
;$DBDsn =   "Driver={Microsoft ODBC for Oracle};" & _
;           "Connectstring=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])(PORT=[portno])) " & _
;           "(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=[name of the oracle db])));" & _
;           "Uid=[uid];Pwd=[pw];"
;$DBDsn = "Driver={Microsoft ODBC for Oracle};Server=[name of the oracle db];Uid=[uid];Pwd=[pw];"
    $DBDsn = "Provider=MSDAORA;User ID=[uid];Password=[pw];Data Source=[name of the oracle db];"

    $objConn    = ObjCreate("ADODB.Connection")
    $objCmd  = ObjCreate("ADODB.Command")
    $objRS    = ObjCreate("ADODB.Recordset")
    $objParam   = ObjCreate("ADODB.Parameter")

; we connect to the database
    With $objConn
        .ConnectionString = $DBDsn
        .CursorLocation = $adUseClient
        .Open
    Endwith

; we build up the oracle command to be executed
    $var1_in = 'name of my pc'
    $var2_in = 'Adobe Acrobat Professional'
    $var3_in  = '7.0'
    
    With $objCmd
        .ActiveConnection = $objConn
        .CommandText = "[name of your stored procedure/package]"
        .CommandType = $adCmdStoredProc
    ;.Parameters.Refresh
    ;.Properties("PLSQLRSet") = TRUE
    ; syntax: (variable_name, variable_typ, direction, length)
        $objParm = .CreateParameter("retflag", $adVarChar, $adParamReturnValue, 1); in this case it returns Y or N
        .Parameters.Append ($objParm)
        
        $objParm = .CreateParameter("var1_in", $adVarChar, $adIN, 20)
        $objParm.Value = $ora_pc_name_in    
        .Parameters.Append ($objParm)
        
        $objParm = .CreateParameter("var2_in", $adVarChar, $adIN, 60)
        $objParm.Value = $ora_sw_name_in
        .Parameters.Append ($objParm)
        
        $objParm = .CreateParameter("var3_in", $adVarChar, $adIN, 15)
        $objParm.Value = $ora_sw_rev_in
        .Parameters.Append ($objParm)
        
        $objParm = .CreateParameter("var1_out", $adVarChar, $adOUT, 60)
        .Parameters.Append ($objParm)
            
        $objParm = .CreateParameter("var2_out", $adVarChar, $adOUT, 60)
        .Parameters.Append ($objParm)
        
        $objParm = .CreateParameter("var3_out", $adVarChar, $adOUT, 60)
        .Parameters.Append ($objParm)
    ;.NamedParameters
        .Execute
        $ret = @error       
        
        MsgBox(0,"",'Parm0: N=' & .Parameters(0).name & '/V=' & .Parameters(0).Value & '/D=' & .Parameters(0).Direction & @CRLF & _
                    'Parm1: N=' & .Parameters(1).name & '/V=' & .Parameters(1).Value & '/D=' & .Parameters(1).Direction & @CRLF & _
                    'Parm2: N=' & .Parameters(2).name & '/V=' & .Parameters(2).Value & '/D=' & .Parameters(2).Direction & @CRLF & _
                    'Parm3: N=' & .Parameters(3).name & '/V=' & .Parameters(3).Value & '/D=' & .Parameters(3).Direction & @CRLF & _
                    'Parm4: N=' & .Parameters(4).name & '/V=' & .Parameters(4).Value & '/D=' & .Parameters(4).Direction & @CRLF & _
                    'Parm5: N=' & .Parameters(5).name & '/V=' & .Parameters(5).Value & '/D=' & .Parameters(5).Direction & @CRLF & _                 
                    'Parm6: N=' & .Parameters(6).name & '/V=' & .Parameters(6).Value & '/D=' & .Parameters(6).Direction)
    ;.Properties("PLSQLRSet") = FALSE
    Endwith
    $objConn.Close
    
EndFunc  ;==>Access_DB

Greetings uberis