Sign in to follow this  
Followers 0
Rishav

Oracle query with ado connection [ CLOSED ]

5 posts in this topic

#1 ·  Posted (edited)

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 by Rishav

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

Thanks Stefan. You were a great help as always. :D

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by 99ojo

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