Can't connect to Oracle DB - TNS failure - ORA-12154

[Yaerox]

Hey everyone,

I'm using an Oracle 11-XE database and I'd like to connect to it using AutoIt. I almost get every Database-Client-Software running except doing this with AutoIt.

tnsping my_external_db is <OK> ... So TNS is working fine.

AutoIt is giving me the following:

err.description is: ORA-12154

err.windescription: unknown error

err.number is: 80020009

err.lastdllerror is : 0

err.scriptline is: 16

err.source is: OraOLEDB

err.helpfile is: 

err.helpcontext is: 0

 

What am I doing wrong? For me it seems like using the wrong Provider...

Using Provider=MSDAORA.1 gives me: ORA-06413 ... Where can I research, which one I have to use?

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
    ; 'Set data source - for OLEDB this is a tns alias, for ODBC it can be 'either a tns alias or a DSN.
    ; If "provider" is used this means that the ODBC connections is used via DSN.
    ; if Driver is used = "Driver={Microsoft ODBC for Oracle};Server=TNSnames_ora;Uid=demo;Pwd=demo;" then this is a DSN Less connector
    ; More Info for Oracle MS KB Q193332
    .ConnectionString =("Provider='ODBC';Data Source='my_external_db';User Id='myuser';Password='myuserpwd';")
    .Open
EndWith

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

With $adors
        .ActiveConnection = $ado
        ;.CursorLocation = "adUseClient"
        ;.LockType = "adLockReadOnly" ; Set ODBC connection read only
        .Source = "select count(*) from benutzer"
        .Open
EndWith

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

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

Source of this code can be found here: 

Regards

Edited April 16, 2015 by MikeWenzel

[Yaerox]

No one can help me out, or even got an idea what to research? 

[GreenCan]

No one can help me out, or even got an idea what to research? 

 

  

Try this DSN-Less connection

expandcollapse popup

#include <GUIConstants.au3>

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

Global $sDelimiterChr = ";"

; SQL statement
; Global $sSQL = "select * from benutzer WHERE ROWNUM <= 10"
Global $sSQL = "select count(*) from benutzer"

Global $adoCon = ObjCreate("ADODB.Connection") ; Create a COM ADODB Object

; DSN-less Connection string
; Driver NAME : The name of your Oracle installation
; DBQ as found in your TNSNAMES.ORA
Global $connection = "DRIVER={Oracle in OraClient11g_home1};DBQ=xxxxx.world;uid=xxxxx;pwd=xxxxx;"

$adoCon.Open($connection)
$adoRs = ObjCreate("ADODB.Recordset") ; Create a Record Set to handle the SELECT SQL
$adoRs.CursorType = 2
$adoRs.LockType = 3

; execute the single statement SQL
$adoRs.Open($sSQL, $adoCon)

ToolTip("Please wait", 10, 10, "Processing SQL") ;, 1, 2)

Global $sReportTitles = "", $iQueryCols, $iMsg

; set the report column titles
With $adoRs
    For $i_I = 0 To .Fields.Count - 1
        $sReportTitles = $sReportTitles & .Fields($i_I).Name & $sDelimiterChr
    Next
    ; remove the last $sDelimiterChr
    $sReportTitles = StringTrimRight($sReportTitles, 1)
EndWith
$iQueryCols = $adoRs.Fields.Count

ConsoleWrite("Columns: " & $iQueryCols & @CRLF)

ConsoleWrite($sReportTitles & " " & $iQueryCols & @CRLF)

; now get the rows
With $adoRs
    .MoveFirst
    While Not .EOF
        $sRow = ""
        For $i_I = 0 To .Fields.Count - 1
            $sRow = $sRow & .Fields($i_I).Value & $sDelimiterChr
        Next
        ; remove the last $sDelimiterChr
        $sRow = StringTrimRight($sRow, 1)
        .MoveNext

        ConsoleWrite($sRow & @CRLF)

    WEnd
EndWith

ToolTip("")
Exit


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

This is working perfectly on my PC

GreenCan

Edited April 28, 2015 by GreenCan

[Kinshima]

Hello,

Does the following code work? 

Maybe swap around with the provider a bit, msdaora, OraOLEDB.Oracle, MSDASQL, Microsoft ODBC Driver for Oracle, don't know what you have installed. 

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile_type=a3x
#AutoIt3Wrapper_UseX64=n
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

Global $odbc_objConnection = ObjCreate("ADODB.Connection")
Global $odbc_objRecordSet = ObjCreate("ADODB.Recordset")
Global $odbc_conn = False
Global $errADODB = ObjEvent("AutoIt.Error", "_ErrADODB")
Global $conn_error = False

#include <array.au3>
If _ODBC_OpenConnection() Then
    Local $records = _ODBC_GetRecords('SELECT * FROM all_tables',500,500)
    _ODBC_CloseConnection()
    _ArrayDisplay($records)
EndIf

Func _ExeSQL($sql)
    If Not IsObj($odbc_objConnection) Then Return -1
    $odbc_objConnection.Execute($sql)
    If $errADODB.number Then
        MsgBox(4 + 16 + 256, "_ExeSQL", $sql & @CRLF & @CRLF & 'Error. ' & @CRLF & 'Exiting.')
        Exit
    Else
        Return 1
    EndIf

EndFunc   ;==>_ExeSQL

Func _ODBC_CloseConnection()
    $odbc_conn = False
    Return $odbc_objConnection.Close
EndFunc   ;==>_ODBC_CloseConnection

Func _ODBC_OpenConnection()
    If Not IsObj($odbc_objConnection) Then Return -1
    $odbc_objConnection.ConnectionString = ("Provider='msdaora';Data Source='my_external_db';User Id='myuser';Password='myuserpwd';")
    $conn_error = False
    $odbc_objConnection.Open
    If $conn_error = True Then
        Return False
    EndIf
    $odbc_conn = True
    Return True
EndFunc   ;==>_ODBC_OpenConnection

Func _ODBC_GetRecords($sql, $ch = 250, $limit = False)
    If $odbc_conn = False Then
        MsgBox(0, 'luukwcs', 'connectie niet open')
        Return
    EndIf
    If Not IsObj($odbc_objConnection) Then Return -1
    If Not IsObj($odbc_objRecordSet) Then Return -2
    _ODBC_OpenRecordset($sql)
    Dim $arrRecords[1][1]
    If $odbc_objRecordSet.EOF = True Then
        _ODBC_CloseRecordSet()
        Return False
    EndIf
    $odbc_objRecordSet.MoveFirst
    Local $x = 0
    ReDim $arrRecords[1][$odbc_objRecordSet.Fields.Count]
    For $objField In $odbc_objRecordSet.Fields
        $arrRecords[0][$x] = $objField.Name
        $x += 1
    Next
    Local $chn = UBound($arrRecords) + $ch
    ReDim $arrRecords[$chn][$odbc_objRecordSet.Fields.Count]
    $odbc_objRecordSet.MoveFirst
    Local $y = 0
    Do
        $x = 0
        $y += 1
        For $objField In $odbc_objRecordSet.Fields
            $arrRecords[$y][$x] = $objField.Value
            $x += 1
        Next
        If $y = $chn - 1 Then
            $chn += $ch
            ReDim $arrRecords[$chn][$odbc_objRecordSet.Fields.Count]
        EndIf
        $odbc_objRecordSet.MoveNext
        If $limit = False Then
        Else
            If $y = $limit Then
                ReDim $arrRecords[$y + 1][$odbc_objRecordSet.Fields.Count]
                Return $arrRecords
            EndIf
        EndIf
    Until $odbc_objRecordSet.EOF
    ReDim $arrRecords[$y + 1][$odbc_objRecordSet.Fields.Count]
    _ODBC_CloseRecordSet()
    Return $arrRecords
EndFunc   ;==>_ODBC_GetRecords

Func _ODBC_OpenRecordset($sql);
    If Not IsObj($odbc_objConnection) Then Return -1
    If Not IsObj($odbc_objRecordSet) Then Return -2
    Return $odbc_objRecordSet.Open($sql, $odbc_objConnection, 0, 1)
EndFunc   ;==>_ODBC_OpenRecordset

Func _ODBC_CloseRecordSet()
    Return $odbc_objRecordSet.Close
EndFunc   ;==>_ODBC_CloseRecordSet

Func _ErrADODB()
    MsgBox(0, "ADODB COM Error", "We intercepted a COM Error !" & @CRLF & @CRLF & _
            "err.description is: " & @TAB & $errADODB.description & @CRLF & _
            "err.windescription:" & @TAB & $errADODB.windescription & @CRLF & _
            "err.number is: " & @TAB & Hex($errADODB.number, 8) & @CRLF & _
            "err.lastdllerror is: " & @TAB & $errADODB.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $errADODB.scriptline & @CRLF & _
            "err.source is: " & @TAB & $errADODB.source & @CRLF & _
            "err.helpfile is: " & @TAB & $errADODB.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $errADODB.helpcontext _
            )
    $conn_error = True
EndFunc   ;==>_ErrADODB

I based it on '?do=embed' frameborder='0' data-embedContent>>from spudw2k.

Getrecords is a mess because $odbc_objRecordSet.RecordCount does not seem to work with a orcale provider so can't predict the amount of records I am retrieving and redimming for every single record is just too slow.

[Yaerox]

I couldn't watch out this on monday, I had a lot of stuff to do the last days.

I'll take a look on it today. Thanks, I really apreciate your help.

REgards