Oracle query with ado connection [ CLOSED ]

[Rishav]

Hi all

I have a simple connection to the oracle database on which i want to run a simple query. But I don't know how to get the output of that query in a msgbox. Some help will be appreciated.

thanks and regards

Rishav

$SID = "Rishavs"
$Uid = "ccas"
$Pass = "ccas"

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

With $OraOBJConn
.ConnectionString =("Provider='OraOLEDB.Oracle';Data Source=" & $SID & ";User Id='" & $Uid & "';Password='"& $Pass & "';")
.Open
EndWith

$OraOBJRecSet = ObjCreate( "ADODB.RecordSet" ) ; Create a Record Set to handles SQL Records
With $OraOBJRecSet
.ActiveConnection = $OraOBJConn
.Source = "select Destination from Destinations where dest_id = 0"
.Open
EndWith

MsgBox(0,"RESULTS","

$OraOBJConn.Close

Edited September 16, 2009 by Rishav

[99ojo]

Hi all

I have a simple connection to the oracle database on which i want to run a simple query. But I don't know how to get the output of that query in a msgbox. Some help will be appreciated.

thanks and regards

Rishav

$SID = "Rishavs"
$Uid = "ccas"
$Pass = "ccas"

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

With $OraOBJConn
.ConnectionString =("Provider='OraOLEDB.Oracle';Data Source=" & $SID & ";User Id='" & $Uid & "';Password='"& $Pass & "';")
.Open
EndWith

$OraOBJRecSet = ObjCreate( "ADODB.RecordSet" ) ; Create a Record Set to handles SQL Records
With $OraOBJRecSet
.ActiveConnection = $OraOBJConn
.Source = "select Destination from Destinations where dest_id = 0"
.Open
EndWith

MsgBox(0,"RESULTS","

$OraOBJConn.Close

Hi,

i try to transform an Access example and it is not tested:

Add Code after your EndWith for $OraOBJRecSet

$OraOBJRecSet.MoveFirst
While Not $OraOBJRecSet.EOF
    For $i = 0 $OraOBJRecSet.Fields.Count - 1
        $result &= " " & $OraOBJRecSet.Fields.Value
    Next
    $OraOBJRecSet.MoveNext
WEnd

MsgBox (0,"Results", $result)

Hope it runs! ;-))

Stefan

[Rishav]

Thanks Stefan. You were a great help as always.

[mastmaal]

Hi Everyone,

I'm a newbie with AutoIt and oracle, the code above is similar to what I am trying to achieve, so that is why I am replying to this.

I wanted to know how to send anonymous blocks to oracle?

For example, from the code above,

With $OraOBJRecSet

.ActiveConnection = $OraOBJConn

.Source = "select Destination from Destinations where dest_id = 0"

.Open

EndWith

This sections sends one query to the db and returns the result. How do I attach more queries to it?

I have tried concatenating queries, which doesn't work, I have tried using " ; " after my query ends, but I always get an ORA invalid character error, and I'm not really sure how to do this.

Any help would be greatly appreciated.

[99ojo]

Hi,

based on the code snippets above try this:

1) Create a select.txt with several select statements and put in the !!! Scriptfolder !!!, e.g:

select Destination from Destinations where dest_id = 0

select Destination from Destinations where dest_id = 1

select Destination from Destinations where dest_id = 2

2) This might works, have no oracle to test with:

$SID = "Rishavs"
$Uid = "ccas"
$Pass = "ccas"

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

With $OraOBJConn
    .ConnectionString =("Provider='OraOLEDB.Oracle';Data Source=" & $SID & ";User Id='" & $Uid & "';Password='"& $Pass & "';")
    .Open
EndWith

$OraOBJRecSet = ObjCreate( "ADODB.RecordSet" ) ; Create a Record Set to handles SQL Records
;open file (remember: Sciptdirectory), otherwise change path, in read mode
$file = FileOpen (@ScriptDir & "\select.txt", 0)
While 1
    With $OraOBJRecSet
        .ActiveConnection = $OraOBJConn
        ;reading line after line your select sstatements, further information: see helpfile FileReadLine ()
        .Source = FileReadLine ($file)
        ;if error exit this and the while loop, further information: see helpfile ExitLoop
        If @error = -1 Then ExitLoop 2
        .Open
    EndWith
    $result = ""
    $OraOBJRecSet.MoveFirst
    While Not $OraOBJRecSet.EOF
        For $i = 0 $OraOBJRecSet.Fields.Count - 1
            $result &= " " & $OraOBJRecSet.Fields.Value
        Next
        $OraOBJRecSet.MoveNext
    WEnd

    MsgBox (0,"Results", $result)
WEnd
$OraOBJConn.Close

;-))

Stefan

Edited April 28, 2010 by 99ojo