niranjana Posted June 23, 2008 Share Posted June 23, 2008 Hello, I am fairly new to AutoIt. I am trying to connect to a oracle database server from AutoIt to access data. Could anyone please let me know how can i go about? I need to know the syntax and where the connection details such TNS entries need to be entered. Thanks, Link to comment Share on other sites More sharing options...
Xenobiologist Posted June 23, 2008 Share Posted June 23, 2008 Hi, this can be a start: expandcollapse popup#include<Array.au3> #include<GuiListView.au3> ; Global Variables Global $adUseServer = 2 Global $adUseClient = 3 ; Initialize COM error handler Global $oMyError = ObjEvent('AutoIt.Error', 'MyErrFunc') Global $provider = 'IBMDADB2' Global $IP = '' Global $port = '' Global $DSN = '' Global $userID = '' Global $password = '' Global $connection_Obj = _connectDB($provider, $IP, $port, $DSN, $userID, $password) _getColumns($connection_Obj, 'SELECT * FROM "BI1VSNR"."AGENTURMANDANT"') _displayTable($connection_Obj, 'SELECT * FROM "BI1VSNR"."AGENTURMANDANT"') Func _connectDB($provider, $IP, $port, $DSN, $userID, $password) Local $sqlCon = ObjCreate('ADODB.Connection') $sqlCon.Mode = 16 ; Erlaubt im MultiUser-Bereich das öffnen anderer Verbindungen ohne Beschränkungen [Lesen/Schreiben/Beides] $sqlCon.CursorLocation = $adUseClient ; client side cursor Schreiben beim Clienten $sqlCon.Open('Provider=' & $provider & ';IP=' & $IP & ';Port=' & $port & ';DSN=' & $DSN & ';User ID=' & $userID & ';Password=' & $password) If @error Then Return -1 Return $sqlCon EndFunc ;==>_connectDB Func _getColumns($sqlCon, $SQL) Local $sqlRs = ObjCreate('ADODB.Recordset') If Not @error Then $sqlRs.open($SQL, $sqlCon) If Not @error Then For $i = 0 To $sqlRs.Fields.Count - 1 ConsoleWrite($sqlRs.Fields($i).Name & @CRLF) Next $sqlRs.close EndIf EndIf EndFunc ;==>_getColumns Func _displayTable($sqlCon, $SQL) Local $sqlRs = ObjCreate('ADODB.Recordset') If Not @error Then $sqlRs.open($SQL, $sqlCon) If Not @error Then Local $header[$sqlRs.Fields.Count], $rows For $i = 0 To $sqlRs.Fields.Count - 1 $header[$i] = $sqlRs.Fields($i).Name Next $rows = $sqlRs.GetRows() $sqlRs.close EndIf EndIf _ArrayDisplay_WithHeader($rows, $header) EndFunc ;==>_displayTable Func MyErrFunc() $HexNumber = Hex($oMyError.number, 8) MsgBox(0, 'COM Test', 'We intercepted a COM Error !' & @CRLF & @CRLF & _ 'err.description is: ' & @TAB & $oMyError.description & @CRLF & _ 'err.windescription:' & @TAB & $oMyError.windescription & @CRLF & _ 'err.number is: ' & @TAB & $HexNumber & @CRLF & _ 'err.lastdllerror is: ' & @TAB & $oMyError.lastdllerror & @CRLF & _ 'err.scriptline is: ' & @TAB & $oMyError.scriptline & @CRLF & _ 'err.source is: ' & @TAB & $oMyError.source & @CRLF & _ 'err.helpfile is: ' & @TAB & $oMyError.helpfile & @CRLF & _ 'err.helpcontext is: ' & @TAB & $oMyError.helpcontext _ ) SetError(1) ; to check for after this function returns EndFunc ;==>MyErrFunc ;~ _executeSQLonDB2Recordset($Obj, "insert into " & "Autoit" & "values('Xenobiologist', 'User')") Func _executeSQLonDB2Recordset($sqlCon, $SQL) Local $sqlRs = ObjCreate('ADODB.Recordset') $sqlRs.open('SELECT * FROM Autoit', $sqlCon) If Not @error Then $sqlRs.Source = "Select * From Autoit" ; ganze Tabelle $sqlRs.AddNew ;'Neuen Datensatz erzeugen ;~ 'Beispiele für Zuweisung von Werten an Tabellenfelder $sqlRs.Fields("Name") = "MEGA" $sqlRs.Fields("Job") = "MEGAMAN" $sqlRs.Update $sqlRs.close EndIf EndFunc ;==>_executeSQLonDB2Recordset ;$OptionVal = $sqlRs.Fields ('LAST_NAME' ).Value ;~ MsgBox(0, 'Record Found', 'Name: ' & $OptionName );& @CRLF & 'Value: ' & $OptionVal) ;~ $sqlRs.FIELDS(''' & $OptionName & ''') = '.F.' ; ADDED THIS LINE ; $sqlRs.Update ; ADDED THIS LINE ;~ Driver=IBMDADB2 Func _ArrayDisplay_WithHeader(Const ByRef $avArray, $header, $sTitle = "Array: ListView Display", $iItemLimit = -1, $iTranspose = 0, $sSeparator = "", $sReplace = "|") If Not IsArray($avArray) Then Return SetError(1, 0, 0) ; Dimension checking Local $iDimension = UBound($avArray, 0), $iUBound = UBound($avArray, 1) - 1, $iSubMax = UBound($avArray, 2) - 1 If $iDimension > 2 Then Return SetError(2, 0, 0) ; Separator handling ;~ If $sSeparator = "" Then $sSeparator = Chr(1) If $sSeparator = "" Then $sSeparator = Chr(124) ; Declare variables Local $i, $j, $vTmp, $aItem, $avArrayText, $sHeader = "Row", $iBuffer = 64 Local $iColLimit = 250, $iLVIAddUDFThreshold = 4000, $iWidth = 640, $iHeight = 480 Local $iOnEventMode = Opt("GUIOnEventMode", 0), $sDataSeparatorChar = Opt("GUIDataSeparatorChar", $sSeparator) ; Swap dimensions if transposing If $iSubMax < 0 Then $iSubMax = 0 If $iTranspose Then $vTmp = $iUBound $iUBound = $iSubMax $iSubMax = $vTmp EndIf ; Set limits for dimensions If $iSubMax > $iColLimit Then $iSubMax = $iColLimit If $iItemLimit = 1 Then $iItemLimit = $iLVIAddUDFThreshold If $iItemLimit < 1 Then $iItemLimit = $iUBound If $iUBound > $iItemLimit Then $iUBound = $iItemLimit If $iLVIAddUDFThreshold > $iUBound Then $iLVIAddUDFThreshold = $iUBound ; Set header up For $i = 0 To UBound($header) - 1 $sHeader &= $sSeparator & $header[$i] Next ; Convert array into text for listview Local $avArrayText[$iUBound + 1] For $i = 0 To $iUBound $avArrayText[$i] = "[" & $i & "]" For $j = 0 To $iSubMax ; Get current item If $iDimension = 1 Then If $iTranspose Then $vTmp = $avArray[$j] Else $vTmp = $avArray[$i] EndIf Else If $iTranspose Then $vTmp = $avArray[$j][$i] Else $vTmp = $avArray[$i][$j] EndIf EndIf ; Add to text array $vTmp = StringReplace($vTmp, $sSeparator, $sReplace, 0, 1) $avArrayText[$i] &= $sSeparator & $vTmp ; Set max buffer size $vTmp = StringLen($vTmp) If $vTmp > $iBuffer Then $iBuffer = $vTmp Next Next $iBuffer += 1 ; GUI Constants Local Const $_ARRAYCONSTANT_GUI_DOCKBORDERS = 0x66 Local Const $_ARRAYCONSTANT_GUI_DOCKBOTTOM = 0x40 Local Const $_ARRAYCONSTANT_GUI_DOCKHEIGHT = 0x0200 Local Const $_ARRAYCONSTANT_GUI_DOCKLEFT = 0x2 Local Const $_ARRAYCONSTANT_GUI_DOCKRIGHT = 0x4 Local Const $_ARRAYCONSTANT_GUI_EVENT_CLOSE = -3 Local Const $_ARRAYCONSTANT_LVIF_PARAM = 0x4 Local Const $_ARRAYCONSTANT_LVIF_TEXT = 0x1 Local Const $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH = (0x1000 + 29) Local Const $_ARRAYCONSTANT_LVM_GETITEMCOUNT = (0x1000 + 4) Local Const $_ARRAYCONSTANT_LVM_GETITEMSTATE = (0x1000 + 44) Local Const $_ARRAYCONSTANT_LVM_INSERTITEMA = (0x1000 + 7) Local Const $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE = (0x1000 + 54) Local Const $_ARRAYCONSTANT_LVM_SETITEMA = (0x1000 + 6) Local Const $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT = 0x20 Local Const $_ARRAYCONSTANT_LVS_EX_GRIDLINES = 0x1 Local Const $_ARRAYCONSTANT_LVS_SHOWSELALWAYS = 0x8 Local Const $_ARRAYCONSTANT_WS_EX_CLIENTEDGE = 0x0200 Local Const $_ARRAYCONSTANT_WS_MAXIMIZEBOX = 0x00010000 Local Const $_ARRAYCONSTANT_WS_MINIMIZEBOX = 0x00020000 Local Const $_ARRAYCONSTANT_WS_SIZEBOX = 0x00040000 Local Const $_ARRAYCONSTANT_tagLVITEM = "int Mask;int Item;int SubItem;int State;int StateMask;ptr Text;int TextMax;int Image;int Param;int Indent;int GroupID;int Columns;ptr pColumns" Local $iAddMask = BitOR($_ARRAYCONSTANT_LVIF_TEXT, $_ARRAYCONSTANT_LVIF_PARAM) Local $tBuffer = DllStructCreate("char Text[" & $iBuffer & "]"), $pBuffer = DllStructGetPtr($tBuffer) Local $tItem = DllStructCreate($_ARRAYCONSTANT_tagLVITEM), $pItem = DllStructGetPtr($tItem) DllStructSetData($tItem, "Param", 0) DllStructSetData($tItem, "Text", $pBuffer) DllStructSetData($tItem, "TextMax", $iBuffer) ; Set interface up Local $hGUI = GUICreate($sTitle, $iWidth, $iHeight, Default, Default, BitOR($_ARRAYCONSTANT_WS_SIZEBOX, $_ARRAYCONSTANT_WS_MINIMIZEBOX, $_ARRAYCONSTANT_WS_MAXIMIZEBOX)) Local $aiGUISize = WinGetClientSize($hGUI) Local $hListView = GUICtrlCreateListView($sHeader, 0, 0, $aiGUISize[0], $aiGUISize[1] - 26, $_ARRAYCONSTANT_LVS_SHOWSELALWAYS) Local $hCopy = GUICtrlCreateButton("Copy Selected", 3, $aiGUISize[1] - 23, $aiGUISize[0] - 6, 20) GUICtrlSetResizing($hListView, $_ARRAYCONSTANT_GUI_DOCKBORDERS) GUICtrlSetResizing($hCopy, $_ARRAYCONSTANT_GUI_DOCKLEFT + $_ARRAYCONSTANT_GUI_DOCKRIGHT + $_ARRAYCONSTANT_GUI_DOCKBOTTOM + $_ARRAYCONSTANT_GUI_DOCKHEIGHT) GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_GRIDLINES, $_ARRAYCONSTANT_LVS_EX_GRIDLINES) GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT) GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE) ; Fill listview For $i = 0 To $iLVIAddUDFThreshold GUICtrlCreateListViewItem($avArrayText[$i], $hListView) Next For $i = ($iLVIAddUDFThreshold + 1) To $iUBound $aItem = StringSplit($avArrayText[$i], $sSeparator) DllStructSetData($tBuffer, "Text", $aItem[1]) ; Add listview item DllStructSetData($tItem, "Item", $i) DllStructSetData($tItem, "SubItem", 0) DllStructSetData($tItem, "Mask", $iAddMask) GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_INSERTITEMA, 0, $pItem) ; Set listview subitem text DllStructSetData($tItem, "Mask", $_ARRAYCONSTANT_LVIF_TEXT) For $j = 2 To $aItem[0] DllStructSetData($tBuffer, "Text", $aItem[$j]) DllStructSetData($tItem, "SubItem", $j - 1) GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETITEMA, 0, $pItem) Next Next ; ajust window width $iWidth = 0 For $i = 0 To $iSubMax + 1 $iWidth += GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH, $i, 0) Next If $iWidth < 250 Then $iWidth = 230 WinMove($hGUI, "", Default, Default, $iWidth + 20) ; Show dialog GUISetState(@SW_SHOW, $hGUI) While 1 Switch GUIGetMsg() Case $_ARRAYCONSTANT_GUI_EVENT_CLOSE ExitLoop Case $hCopy Local $sClip = "" ; Get selected indices [ _GUICtrlListView_GetSelectedIndices($hListView, True) ] Local $aiCurItems[1] = [0] For $i = 0 To GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMCOUNT, 0, 0) If GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMSTATE, $i, 0x2) Then $aiCurItems[0] += 1 ReDim $aiCurItems[$aiCurItems[0] + 1] $aiCurItems[$aiCurItems[0]] = $i EndIf Next ; Generate clipboard text If Not $aiCurItems[0] Then For $sItem In $avArrayText $sClip &= $sItem & @CRLF Next Else For $i = 1 To UBound($aiCurItems) - 1 $sClip &= $avArrayText[$aiCurItems[$i]] & @CRLF Next EndIf ClipPut($sClip) EndSwitch WEnd GUIDelete($hGUI) Opt("GUIOnEventMode", $iOnEventMode) Opt("GUIDataSeparatorChar", $sDataSeparatorChar) Return 1 EndFunc ;==>_ArrayDisplay_WithHeader ;~ _printDB2Recordset($Obj, 'SELECT * FROM "BI1VSNR"."AGENTURMANDANT"') ;~ Func _printDB2Recordset($sqlCon, $SQL) ;~ Local $sqlRs = ObjCreate('ADODB.Recordset') ;~ If Not @error Then ;~ $sqlRs.open($SQL, $sqlCon) ;~ If Not @error Then ;~ ;Loop until the end of file ;~ While Not $sqlRs.EOF ;~ ;Retrieve data from the following fields ;~ ConsoleWrite($sqlRs.Fields('Nr_Mandant' ).Value & @CRLF) ;~ ConsoleWrite($sqlRs.Fields(1).Value & @CRLF) ;~ ConsoleWrite($sqlRs.Fields('COUNTRY' ).Value & @CRLF) ;~ $sqlRs.MoveNext ;~ WEnd ;~ $sqlRs.close ;~ EndIf ;~ EndIf ;~ EndFunc ;==>_printDB2Recordset Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
paz Posted June 23, 2008 Share Posted June 23, 2008 I know that, personally, I had some fun to figure out the proper connection string to access our oracle databases at work. Depending on the driver you use, you may want to setup a DSN entry. In the code below, I have a function doing exactly that. I also commented-out an alternate connection string that uses the Microsoft's Oracle driver, for your reference. This sample is much more bare-bones than the previous post (which I'm reading myself, to learn some more!), and I hope it can help you as well. expandcollapse popup#include "Array.au3" ;COM Error reporting. Will write every messages in a text file named like the autoit script, but with the .txt file type Global $COMError = ObjEvent("AutoIt.Error","COMErrorFunc") $DSN_Name = "MyFunkyDSN" $userid="MyUserID" $password = "MyPassword" $sql_code = "select * from myTable" ;This will write the proper reg keys to create a DSN ODBC entry for my oracle server named "cdb1" (name from tsnnames.ora) CreateOracleDSN($DSN_Name,"cdb1") ;Create the Connection object $ORA_CONN = ObjCreate("ADODB.Connection") ;This connection string requires a DSN to be configured $ORA_CONN.Open("DSN=" & $DSN_Name & ";UId=" & $userid & ";Pwd=" & $password & ";") ;This connection string works directly with the TSN name, no need to write reg keys... but I've read it's no longer maintained. ;$ORA_CONN.Open("Driver={Microsoft ODBC for Oracle};Server=" & $tsn_name & ";Uid="& $userid & ";Pwd=" & $password & ";") ;----------------------------------------------------- ;At this point, I'm supposed to be connected to Oracle ;----------------------------------------------------- ;Create a ADO Recordset object, and execute a SQL query $RST = ObjCreate("ADODB.Recordset") $RST.Open($sql_code, $ORA_CONN) ;This will save the data as a attribute-based XML. $RST.Save(@ScriptDir & "\data.xml",1) ;This is a quick & dirty way to save the data as a header-less CSV text file Const $adClipString = 2 $data = $RST.GetString($adClipString, -1,",",@crlf,"") FileWriteLine(@ScriptDir & "\data.csv", $data) ;I can also get the data as a simple 2D array, and display it with AutoIT's UDF $array = $RST.GetRows() _ArrayDisplay($array) ;Close the recordset, once I'm finished $RST.Close $RST = 0 ;----------------------------------------------------- ;End of script execution ;----------------------------------------------------- Func COMErrorFunc() FileWriteLine( StringTrimRight(@ScriptFullPath,3) & "txt", _ $COMError.Windescription & "/" & $COMError.description & _ $COMError.Scriptline _ ) SetError(1) Endfunc Func CreateOracleDSN($dsn_name="", $tsn_name="", $user_id="", $password="") ;I will eventually code something to search for SQORA32.DLL instead of hardcoding the path... RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Driver","REG_SZ", "c:\\oracle\\102\\BIN\\SQORA32.DLL") ;Default values. Got them by looking at the registry after creating a DSN for Oracle RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Attributes","REG_SZ", "W") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FetchBufferSize","REG_SZ","64000") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "NumericSetting","REG_SZ","NLS") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ForceWCHAR","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverDelay","REG_SZ","10") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverRetryCount","REG_SZ","10") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "MetadataIdDefault","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BindAsDATE","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "CloseCursor","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSchemaOpt","REG_SZ","") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSyntax","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Application Attributes","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ResultSets","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "QueryTimeout","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Failover","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Lobs","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableMTS","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableDPM","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BatchAutocommitMode","REG_SZ","IfAllSuccessful") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DSN","REG_SZ",$dsn_name) RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Description","REG_SZ","AutoIT-Generated DSN") ;TSN Name for the oracle server RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ServerName","REG_SZ",$tsn_name) ;Default username - optional RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "UserID","REG_SZ",$user_id) ;Default password... not sure if it's safe to keep that in the registry;-) RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Password","REG_SZ",$password) EndFunc Link to comment Share on other sites More sharing options...
niranjana Posted June 23, 2008 Author Share Posted June 23, 2008 Many thanks for your speedy response people.. I know that, personally, I had some fun to figure out the proper connection string to access our oracle databases at work. Depending on the driver you use, you may want to setup a DSN entry. In the code below, I have a function doing exactly that. I also commented-out an alternate connection string that uses the Microsoft's Oracle driver, for your reference. This sample is much more bare-bones than the previous post (which I'm reading myself, to learn some more!), and I hope it can help you as well. expandcollapse popup#include "Array.au3" ;COM Error reporting. Will write every messages in a text file named like the autoit script, but with the .txt file type Global $COMError = ObjEvent("AutoIt.Error","COMErrorFunc") $DSN_Name = "MyFunkyDSN" $userid="MyUserID" $password = "MyPassword" $sql_code = "select * from myTable" ;This will write the proper reg keys to create a DSN ODBC entry for my oracle server named "cdb1" (name from tsnnames.ora) CreateOracleDSN($DSN_Name,"cdb1") ;Create the Connection object $ORA_CONN = ObjCreate("ADODB.Connection") ;This connection string requires a DSN to be configured $ORA_CONN.Open("DSN=" & $DSN_Name & ";UId=" & $userid & ";Pwd=" & $password & ";") ;This connection string works directly with the TSN name, no need to write reg keys... but I've read it's no longer maintained. ;$ORA_CONN.Open("Driver={Microsoft ODBC for Oracle};Server=" & $tsn_name & ";Uid="& $userid & ";Pwd=" & $password & ";") ;----------------------------------------------------- ;At this point, I'm supposed to be connected to Oracle ;----------------------------------------------------- ;Create a ADO Recordset object, and execute a SQL query $RST = ObjCreate("ADODB.Recordset") $RST.Open($sql_code, $ORA_CONN) ;This will save the data as a attribute-based XML. $RST.Save(@ScriptDir & "\data.xml",1) ;This is a quick & dirty way to save the data as a header-less CSV text file Const $adClipString = 2 $data = $RST.GetString($adClipString, -1,",",@crlf,"") FileWriteLine(@ScriptDir & "\data.csv", $data) ;I can also get the data as a simple 2D array, and display it with AutoIT's UDF $array = $RST.GetRows() _ArrayDisplay($array) ;Close the recordset, once I'm finished $RST.Close $RST = 0 ;----------------------------------------------------- ;End of script execution ;----------------------------------------------------- Func COMErrorFunc() FileWriteLine( StringTrimRight(@ScriptFullPath,3) & "txt", _ $COMError.Windescription & "/" & $COMError.description & _ $COMError.Scriptline _ ) SetError(1) Endfunc Func CreateOracleDSN($dsn_name="", $tsn_name="", $user_id="", $password="") ;I will eventually code something to search for SQORA32.DLL instead of hardcoding the path... RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Driver","REG_SZ", "c:\\oracle\\102\\BIN\\SQORA32.DLL") ;Default values. Got them by looking at the registry after creating a DSN for Oracle RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Attributes","REG_SZ", "W") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FetchBufferSize","REG_SZ","64000") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "NumericSetting","REG_SZ","NLS") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ForceWCHAR","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverDelay","REG_SZ","10") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverRetryCount","REG_SZ","10") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "MetadataIdDefault","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BindAsDATE","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "CloseCursor","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSchemaOpt","REG_SZ","") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSyntax","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Application Attributes","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ResultSets","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "QueryTimeout","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Failover","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Lobs","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableMTS","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableDPM","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BatchAutocommitMode","REG_SZ","IfAllSuccessful") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DSN","REG_SZ",$dsn_name) RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Description","REG_SZ","AutoIT-Generated DSN") ;TSN Name for the oracle server RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ServerName","REG_SZ",$tsn_name) ;Default username - optional RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "UserID","REG_SZ",$user_id) ;Default password... not sure if it's safe to keep that in the registry;-) RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Password","REG_SZ",$password) EndFunc Link to comment Share on other sites More sharing options...
reva Posted December 30, 2008 Share Posted December 30, 2008 Thanks for the idea. I'm new to Autoit Com objects. Like pointers in C, they seem like life and heart of Autoit. I tried to modify your script for connecting to database (TNSNAMES is given below). Please help me ! (C:\oracle\ora92\network\admin\tnsnames.ora) CU1CON = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.99)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = CU1CON) ) ) I know that, personally, I had some fun to figure out the proper connection string to access our oracle databases at work. Depending on the driver you use, you may want to setup a DSN entry. In the code below, I have a function doing exactly that. I also commented-out an alternate connection string that uses the Microsoft's Oracle driver, for your reference. This sample is much more bare-bones than the previous post (which I'm reading myself, to learn some more!), and I hope it can help you as well. expandcollapse popup#include "Array.au3" ;COM Error reporting. Will write every messages in a text file named like the autoit script, but with the .txt file type Global $COMError = ObjEvent("AutoIt.Error","COMErrorFunc") $DSN_Name = "MyFunkyDSN" $userid="MyUserID" $password = "MyPassword" $sql_code = "select * from myTable" ;This will write the proper reg keys to create a DSN ODBC entry for my oracle server named "cdb1" (name from tsnnames.ora) CreateOracleDSN($DSN_Name,"cdb1") ;Create the Connection object $ORA_CONN = ObjCreate("ADODB.Connection") ;This connection string requires a DSN to be configured $ORA_CONN.Open("DSN=" & $DSN_Name & ";UId=" & $userid & ";Pwd=" & $password & ";") ;This connection string works directly with the TSN name, no need to write reg keys... but I've read it's no longer maintained. ;$ORA_CONN.Open("Driver={Microsoft ODBC for Oracle};Server=" & $tsn_name & ";Uid="& $userid & ";Pwd=" & $password & ";") ;----------------------------------------------------- ;At this point, I'm supposed to be connected to Oracle ;----------------------------------------------------- ;Create a ADO Recordset object, and execute a SQL query $RST = ObjCreate("ADODB.Recordset") $RST.Open($sql_code, $ORA_CONN) ;This will save the data as a attribute-based XML. $RST.Save(@ScriptDir & "\data.xml",1) ;This is a quick & dirty way to save the data as a header-less CSV text file Const $adClipString = 2 $data = $RST.GetString($adClipString, -1,",",@crlf,"") FileWriteLine(@ScriptDir & "\data.csv", $data) ;I can also get the data as a simple 2D array, and display it with AutoIT's UDF $array = $RST.GetRows() _ArrayDisplay($array) ;Close the recordset, once I'm finished $RST.Close $RST = 0 ;----------------------------------------------------- ;End of script execution ;----------------------------------------------------- Func COMErrorFunc() FileWriteLine( StringTrimRight(@ScriptFullPath,3) & "txt", _ $COMError.Windescription & "/" & $COMError.description & _ $COMError.Scriptline _ ) SetError(1) Endfunc Func CreateOracleDSN($dsn_name="", $tsn_name="", $user_id="", $password="") ;I will eventually code something to search for SQORA32.DLL instead of hardcoding the path... RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Driver","REG_SZ", "c:\\oracle\\102\\BIN\\SQORA32.DLL") ;Default values. Got them by looking at the registry after creating a DSN for Oracle RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Attributes","REG_SZ", "W") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FetchBufferSize","REG_SZ","64000") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "NumericSetting","REG_SZ","NLS") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ForceWCHAR","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverDelay","REG_SZ","10") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "FailoverRetryCount","REG_SZ","10") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "MetadataIdDefault","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BindAsDATE","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "CloseCursor","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSchemaOpt","REG_SZ","") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "EXECSyntax","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Application Attributes","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ResultSets","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "QueryTimeout","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Failover","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Lobs","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableMTS","REG_SZ","T") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DisableDPM","REG_SZ","F") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "BatchAutocommitMode","REG_SZ","IfAllSuccessful") RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "DSN","REG_SZ",$dsn_name) RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Description","REG_SZ","AutoIT-Generated DSN") ;TSN Name for the oracle server RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "ServerName","REG_SZ",$tsn_name) ;Default username - optional RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "UserID","REG_SZ",$user_id) ;Default password... not sure if it's safe to keep that in the registry;-) RegWrite("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\" & $dsn_name, "Password","REG_SZ",$password) EndFunc Link to comment Share on other sites More sharing options...
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