Sign in to follow this  
Followers 0
GeoA

ADODB command method - how to return results of query?

5 posts in this topic

Can anybody give me a brief idea of how I can open a connection to a Oracle DB the run a select statement and get the results?

Share this post


Link to post
Share on other sites



This should give you a running start:

#include <GUIConstants.au3>

Const $adStateClosed = 0
Const $adStateOpen = 1

Global $oError
; Initializes COM handler
$oError = ObjEvent("AutoIt.Error", "ErrHandler")
Global $adoCn = ObjCreate( "ADODB.Connection" )
$adoCn.Properties("Prompt") = 2; 1=PromptAlways, 2=PromptComplete
Global $adoRs = ObjCreate( "ADODB.RecordSet" )
Local $Txt, $I, $J

; Set your Default Oracle Connection String Here
$Txt = "DRIVER={OraHome92};SERVER=ORASERV;DBQ=ORADB;UID=userid;PWD=password;"

$Form1 = GUICreate("Oracle ADO Test", 633, 447, 193, 115)
$ButtonConnect = GUICtrlCreateButton("&Connect", 8, 8, 89, 25, 0)
$InputConnect = GUICtrlCreateInput($Txt, 104, 8, 521, 21)
$ButtonSQL = GUICtrlCreateButton("&Query", 8, 40, 89, 25, 0)
$EditSQL = GUICtrlCreateEdit("SELECT * FROM <TABLE> WHERE <condition>", 104, 40, 521, 145)
$Label1 = GUICtrlCreateLabel("Results:", 8, 176, 42, 17)
$EditResults = GUICtrlCreateEdit("", 8, 200, 617, 241)
GUISetState(@SW_SHOW)
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
    Case $GUI_EVENT_CLOSE
        If $adoCn.State = $adStateOpen Then $adoCn.Close
        Exit
    Case $ButtonConnect
        If $adoCn.State = $adStateOpen Then $adoCn.Close
        $adoCn.ConnectionString = GUICtrlRead($InputConnect)
        $adoCn.Open
        $Txt = "Connected"
        If $adoCn.State <> $adStateOpen Then $Txt = "Not " & $Txt
        GUICtrlSetData($EditResults, $Txt & @CRLF & $adoCn.ConnectionString)
    Case $ButtonSQL
        $Txt = GUICtrlRead($EditSQL)
        If $adoRs.State = $adStateOpen Then $adoRs.Close
        GUICtrlSetData($EditResults, "Executing " & $Txt);"")
        $adoRs = $adoCn.Execute($Txt)
        If $adoRs.State = $adStateOpen Then
           $Txt = ""
           $J = $adoRs.Fields.Count - 1
           For $I = 0 To $J
                 $Txt = $Txt & $adoRs.Fields($I).Name & @TAB
           Next;$I
           $Txt = $Txt & @CRLF
           $Txt = $Txt & $adoRs.GetString(2, -1, @TAB, @CRLF, "Null")
           $adoRs.Close
           GUICtrlSetData($EditResults, $Txt)
        EndIf
    EndSwitch
WEnd

Func ErrHandler()
  $HexNumber=Hex($oError.number,8)
  Msgbox(0, StringReplace( $oError.windescription, "error", "COM Error #") & $oError.Number, _
            $oError.Description & @CRLF & _
            "Source: "       & @TAB & $oError.source         & @CRLF & _
            "at Line #: " & $oError.ScriptLine & @TAB & _
            "Last DllError: " & @TAB & $oError.lastdllerror   & @CRLF & _
             "Help File: " & @TAB & $oError.helpfile & @TAB & "Context: " & @TAB & $oError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
 ;Exit
Endfunc

Share this post


Link to post
Share on other sites

Very helpful tool :P

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Global $o_adoCon, $o_adoRs 

Global $strSQL

$strSQL = "Select * From Mytable;" 

_OracleConn ($o_adoCon)

$o_adoRs = ObjCreate("ADODB.Recordset") 

$o_adoRs =$o_adoCon.Execute($strSQL) 

if $o_adoRs.BOF = -1 And $o_adoRs.EOF = -1 Then

      Msgbox (16, $conProgName, "No Data to Display with the given conditions with this SQL " & @CRLF & @CRLF & $strSQL) 

      exit

     EndIf

$o_adoRs.MoveFirst

Msgbox (0,"", $o_adoRs.Fields(0).Value) ; you have to tube .Value which is not required in MS-Access !!!!

$o_adoCon.Close

;----------------------------

Func _OracleConn(ByRef $o_adoCon) 

Local $strCon = "Driver={Microsoft ODBC for Oracle}; " & _ "SERVER=mp2iprod

; USER id=myservrname

; password=mypassword;" 

; Local $strCon = "Driver={Microsoft ODBC for Oracle}

; " & _ ; "SERVER=" & $Server & "; " & _ 

; "USER=" & $USER & "; " & _ 

; "Password=" & $PWD & "; " 

 ;Msgbox (0,"Test", $strCon)

$o_adoCon = ObjCreate("ADODB.Connection")

$o_adoCon.Open ($strCon) ;$o_adoCon.Open ( "Driver={Microsoft ODBC for Oracle}

Return $o_adoCon

EndFunc ;==>_AccessConnectConnFunc

Edited by aymhenry

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

This should give you a running start:

#include <GUIConstants.au3>

Const $adStateClosed = 0
Const $adStateOpen = 1

Global $oError
; Initializes COM handler
$oError = ObjEvent("AutoIt.Error", "ErrHandler")
Global $adoCn = ObjCreate( "ADODB.Connection" )
$adoCn.Properties("Prompt") = 2; 1=PromptAlways, 2=PromptComplete
Global $adoRs = ObjCreate( "ADODB.RecordSet" )
Local $Txt, $I, $J

; Set your Default Oracle Connection String Here
$Txt = "DRIVER={OraHome92};SERVER=ORASERV;DBQ=ORADB;UID=userid;PWD=password;"

$Form1 = GUICreate("Oracle ADO Test", 633, 447, 193, 115)
$ButtonConnect = GUICtrlCreateButton("&Connect", 8, 8, 89, 25, 0)
$InputConnect = GUICtrlCreateInput($Txt, 104, 8, 521, 21)
$ButtonSQL = GUICtrlCreateButton("&Query", 8, 40, 89, 25, 0)
$EditSQL = GUICtrlCreateEdit("SELECT * FROM <TABLE> WHERE <condition>", 104, 40, 521, 145)
$Label1 = GUICtrlCreateLabel("Results:", 8, 176, 42, 17)
$EditResults = GUICtrlCreateEdit("", 8, 200, 617, 241)
GUISetState(@SW_SHOW)
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
    Case $GUI_EVENT_CLOSE
        If $adoCn.State = $adStateOpen Then $adoCn.Close
        Exit
    Case $ButtonConnect
        If $adoCn.State = $adStateOpen Then $adoCn.Close
        $adoCn.ConnectionString = GUICtrlRead($InputConnect)
        $adoCn.Open
        $Txt = "Connected"
        If $adoCn.State <> $adStateOpen Then $Txt = "Not " & $Txt
        GUICtrlSetData($EditResults, $Txt & @CRLF & $adoCn.ConnectionString)
    Case $ButtonSQL
        $Txt = GUICtrlRead($EditSQL)
        If $adoRs.State = $adStateOpen Then $adoRs.Close
        GUICtrlSetData($EditResults, "Executing " & $Txt);"")
        $adoRs = $adoCn.Execute($Txt)
        If $adoRs.State = $adStateOpen Then
           $Txt = ""
           $J = $adoRs.Fields.Count - 1
           For $I = 0 To $J
                 $Txt = $Txt & $adoRs.Fields($I).Name & @TAB
           Next;$I
           $Txt = $Txt & @CRLF
           $Txt = $Txt & $adoRs.GetString(2, -1, @TAB, @CRLF, "Null")
           $adoRs.Close
           GUICtrlSetData($EditResults, $Txt)
        EndIf
    EndSwitch
WEnd

Func ErrHandler()
  $HexNumber=Hex($oError.number,8)
  Msgbox(0, StringReplace( $oError.windescription, "error", "COM Error #") & $oError.Number, _
            $oError.Description & @CRLF & _
            "Source: "       & @TAB & $oError.source         & @CRLF & _
            "at Line #: " & $oError.ScriptLine & @TAB & _
            "Last DllError: " & @TAB & $oError.lastdllerror   & @CRLF & _
             "Help File: " & @TAB & $oError.helpfile & @TAB & "Context: " & @TAB & $oError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
 ;Exit
Endfunc

 

Hi Universalist, 

Thanks so much for that.

I'm wondering if you can help me with something?

When I * query a table I'm looking for all "Names" in the column that Name that contain a ">" sign.

A query result for Name that contains a ">" from my CaseNumber table would look something like this without the quotes: "FWD: 12345 > CA > DOCUMENTS > Some text with space here", always separated with the ">" like that, may or may not contain "FWD: " before the first few numbers and a space between the first few numbers and the ">". 

So what I'm trying to do is to get AutoIt to take that "12345" from that Name column value , and update another column in that Row with that number ("Number" column for example). 

Then take the "CA" between the next two carrots and update another column for that row with that text ("State" column, for example)

Then take the "DOCUMENTS" text and update another column for that row with that text ("Topic", for example). 

I am not sure if it is recommended or possible to do this with AutoIt or ADODB and writing back in this connection to SQL accordingly.

I figured if anyone knew, it'd be you, or someone else in this forum. 

Case $UpdateSQL
        If $adoRs.State = $adStateOpen Then $adoRs.Close
        GUICtrlSetData($EditResults, "Executing " & $Txt);"")
        $adoRs = $adoCn.Execute("SELECT * FROM CaseNumber Where Name like ('% >%')) ; I'm looking for all "Names" in the column that contain a > sign.

; A query result for Name would look something like this without the quotes: "FWD: 12345 > CA > DOCUMENTS > Some text with space here"
; So what I'm trying to do is to get AutoIt to take that "12345" and update another column in that Row with that number
; Then take the "CA" between the next two carrots and update another column for that row with that text
; Then take the "Documents" text and update another column for that row with that text. 



        If $adoRs.State = $adStateOpen Then
           $Txt = ""
           $J = $adoRs.Fields.Count - 1
           For $I = 0 To $J
                 $Txt = $Txt & $adoRs.Fields($I).Name & @TAB & @TAB & @TAB
           Next;$I
           $Txt = $Txt & @CRLF
           $Txt = $Txt & $adoRs.GetString(2, -1, @TAB & @TAB & @TAB, @CRLF, "Null")
           $adoRs.Close
           GUICtrlSetData($EditResults, $Txt)
        EndIf
Edited by jgq85

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