Jump to content

ADODB command method - how to return results of query?


GeoA
 Share

Recommended Posts

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
Link to comment
Share on other sites

  • 3 weeks later...
  • 8 months later...

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
Link to comment
Share on other sites

  • 3 years later...

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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...