GeoA Posted February 18, 2009 Posted February 18, 2009 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?
DaRam Posted February 18, 2009 Posted February 18, 2009 This should give you a running start: expandcollapse popup#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
aymhenry Posted December 3, 2009 Posted December 3, 2009 (edited) expandcollapse popupGlobal $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 December 3, 2009 by aymhenry
jgq85 Posted July 24, 2013 Posted July 24, 2013 (edited) This should give you a running start: expandcollapse popup#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 July 24, 2013 by jgq85
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now