gcue Posted July 31, 2012 Share Posted July 31, 2012 Hello. In windows xp I am able to run 3 different queries each querying 3 different tables. here's the part that's killing me: usng the ADODB connection, I can run the same queries in win 7 but only 2 of them work. the one that doesn't work crashes the script and this is the error I get when I run $oConn.Execute($sQuery) the requested action on this object has failed I tried using both N and Y on this #AutoIt3Wrapper_UseX64=n and also tried using this #RequireAdmin Here's how I establish the ADODB.Connection Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}") Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2) Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp) If @error Or $sVal = "" Then Return SetError(2, 0, 0) $oConn = ObjCreate("ADODB.Connection") If Not IsObj($oConn) Then Return SetError(3, 0, 0) If $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";ARSERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";ARAuthentication=;ARUseUnderscores=1") If Not $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";ARSERVER=" & $sServer & ";DATABASE=" & $sDatabase) If @error Then Return SetError(1, 0, 0) Return $oConn EndFunc ;==>_SQLConnect here is the query function expandcollapse popupFunc _SQLQuery($oConn, $sQuery, $desired_fields) If IsObj($oConn) Then $incident = $oConn.Execute($sQuery) If $incident.EOF Then Local $results[2][1] $fields = StringSplit($desired_fields, ",") $iColumns = UBound($fields) - 1 ReDim $results[2][$iColumns] For $x = 0 To $iColumns - 1 $results[0][$x] = $fields[$x + 1] $results[1][$x] = "TICKET_NOT_FOUND" Next Return $results EndIf With $incident $results = .GetRows() If IsArray($results) Then $iColumns = UBound($results, 2) $iRows = UBound($results) ReDim $results[$iRows + 1][$iColumns];Adjust the array to fit the column names and move all data down 1 row For $x = $iRows To 1 Step -1 For $y = 0 To $iColumns - 1 $results[$x][$y] = $results[$x - 1][$y] Next Next ;Add the coloumn names For $i = 0 To $iColumns - 1 ;get the column names and put into 0 array element $results[0][$i] = .Fields($i).Name Next Else MsgBox(0, "", "Unable to retreive data") EndIf;IsArray() EndWith $incident = 0 Return $results Else MsgBox(0, "", "connection not valid") EndIf EndFunc ;==>_SQLQuery and here is the actual query that fails $desired_fields = "XMS_Task.Status" SQLQuery($remedy_sql_db, "SELECT " & $desired_fields & " FROM XMS_Task WHERE (XMS_Task.Task_ID='" & $ticket & "')", $desired_fields) any help or leads is GREATLY appreciated! Link to comment Share on other sites More sharing options...
water Posted July 31, 2012 Share Posted July 31, 2012 When working with COM you need a COM error handler to get more information about what happened. Check function ObjEvent or add the following functions to your script and insert _COMError_Notify(2)at the top of your script. expandcollapse popup; Sets or queries the debug level. Func _COMError_Notify($iNewDebugState, $sNewDebugFile = Default) Static Local $avDebugState[3] = [0, "", 0] ; Debugstate, Debugfile and AutoIt.Error object If $sNewDebugFile = Default Or $sNewDebugFile = "" Then $sNewDebugFile = @ScriptDir & "\COMError_Debug.txt" If Not IsInt($iNewDebugState) Or $iNewDebugState < -1 Or $iNewDebugState > 3 Then Return SetError(1, 0, 0) Switch $iNewDebugState Case -1 Return $avDebugState Case 0 $avDebugState[0] = 0 $avDebugState[1] = "" $avDebugState[2] = 0 Case Else If $iNewDebugState = 2 And $sNewDebugFile = "" Then Return SetError(4, 0, 0) ; A COM error handler will be initialized only if one does not exist If ObjEvent("AutoIt.Error") = "" Then $avDebugState[2] = ObjEvent("AutoIt.Error", "__COMError_Handler") ; Creates a custom error handler If @error <> 0 Then Return SetError(2, @error, 0) $avDebugState[0] = $iNewDebugState $avDebugState[1] = $sNewDebugFile Return SetError(0, 1, 1) ElseIf ObjEvent("AutoIt.Error") = "__COMError_Handler" Then Return SetError(0, 0, 1) ; COM error handler already set by a previous call to this function Else Return SetError(3, 0, 0) ; COM error handler already set to another function EndIf EndSwitch Return EndFunc ;==>_COMError_Notify ; Called if a COM error occurs and writes the error message to the Console, a MsgBox or a File. Func __COMError_Handler($oCOMError) Local $sTitle = "AutoIt COM error handler" Local $avDebugState = _COMError_Notify(-1) Local $sError = "Error encountered in " & @ScriptName & ":" & @CRLF & _ " @AutoItVersion = " & @AutoItVersion & @CRLF & _ " @AutoItX64 = " & @AutoItX64 & @CRLF & _ " @Compiled = " & @Compiled & @CRLF & _ " @OSArch = " & @OSArch & @CRLF & _ " @OSVersion = " & @OSVersion & @CRLF & _ " Scriptline = " & $oCOMError.Scriptline & @CRLF & _ " NumberHex = " & Hex($oCOMError.Number, 8) & @CRLF & _ " Number = " & $oCOMError.Number & @CRLF & _ " WinDescription = " & StringStripWS($oCOMError.WinDescription, 2) & @CRLF & _ " Description = " & StringStripWS($oCOMError.Description, 2) & @CRLF & _ " Source = " & $oCOMError.Source & @CRLF & _ " HelpFile = " & $oCOMError.HelpFile & @CRLF & _ " HelpContext = " & $oCOMError.HelpContext & @CRLF & _ " LastDllError = " & $oCOMError.LastDllError Switch $avDebugState[0] Case 1 ConsoleWrite($sTitle & " - " & $sError & @CRLF) Case 2 MsgBox(64, $sTitle, $sError) Case 3 FileWrite($avDebugState[1], @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sTitle & _ " - " & $sError & @CRLF) EndSwitch EndFunc ;==>__COMError_Handler My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki  Link to comment Share on other sites More sharing options...
gcue Posted July 31, 2012 Author Share Posted July 31, 2012 (edited) thanks for the lead.. but the error isnt very indicativehttp://postimage.org/image/u7icrmbz9/(error: none)i get this over and over again. Edited August 1, 2012 by gcue Link to comment Share on other sites More sharing options...
water Posted July 31, 2012 Share Posted July 31, 2012 That's strange. You run the script from SciTe and the COM error handler isn't able to provide the line number and other data? Will have to think about .... My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki  Link to comment Share on other sites More sharing options...
jdelaney Posted July 31, 2012 Share Posted July 31, 2012 silly question, but you do have the sql server driver installed on your windows 7 station, right IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
water Posted July 31, 2012 Share Posted July 31, 2012 As two out of three queries work I assume: yes My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki  Link to comment Share on other sites More sharing options...
gcue Posted July 31, 2012 Author Share Posted July 31, 2012 (edited) however objevent gives me an indicative error:http://postimage.org/image/8od36pu8j/(error: no data found)but the query should render results knowing the data also - the same query renders results in windows xp!arrrggg Edited August 1, 2012 by gcue Link to comment Share on other sites More sharing options...
gcue Posted July 31, 2012 Author Share Posted July 31, 2012 (edited) silly question, but you do have the sql server driver installed on your windows 7 station, right yes, and as water mentioned 2 other queries work off the same $oconn Edited July 31, 2012 by gcue Link to comment Share on other sites More sharing options...
jdelaney Posted July 31, 2012 Share Posted July 31, 2012 (edited) Probably won't help, but maybe it will get you thinking in diff directions? Try an ADODB.RecordSet.Open to run the query: $adors = ObjCreate("ADODB.RecordSet") $adors.Open( "SELECT " & $desired_fields & " FROM XMS_Task WHERE (XMS_Task.Task_ID='" & $ticket & "')", $remedy_sql_db) Also, the () seem unecessary, since you are not doing sub queries Edited July 31, 2012 by jdelaney IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
gcue Posted July 31, 2012 Author Share Posted July 31, 2012 (edited) here is the error i get when trying to establish the connectionhttp://postimage.org/image/i3omxmdtr/(error: the connection cannot be used to perform this operation. it is either closed or invalid in this context) Edited August 1, 2012 by gcue Link to comment Share on other sites More sharing options...
gcue Posted August 1, 2012 Author Share Posted August 1, 2012 this is very strange - has to be something with the way windows 7 interacts with script - i run the same query using microsoft query from the same machine and it works. again, the script works in windows xp Link to comment Share on other sites More sharing options...
BrewManNH Posted August 1, 2012 Share Posted August 1, 2012 Can I make a suggestion for the future? When you get an error message box, click the box, hit CTRL-C to copy the text in that box, and then paste it here instead of posting links to screenshots. It will allow more people to be able to help you if you stop posting screenshots of message boxes. Just a bit of advice. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator Link to comment Share on other sites More sharing options...
gcue Posted August 1, 2012 Author Share Posted August 1, 2012 ah sorry about that - wasnt aware of people's hesitance to view screenshots from websites. thanks for the tip. any ideas on the sql query issue? Link to comment Share on other sites More sharing options...
BrewManNH Posted August 1, 2012 Share Posted August 1, 2012 No clue, the website where the screenshots are is blocked by my company. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator Link to comment Share on other sites More sharing options...
gcue Posted August 1, 2012 Author Share Posted August 1, 2012 ahh thats the reason you were talking about. ok i just updated all the posts with error messages below the link thanks again for the heads up Link to comment Share on other sites More sharing options...
gcue Posted August 21, 2012 Author Share Posted August 21, 2012 strangely enough, i added 2 other queries to 2 additional tables and they work fine in windows 7 and xp but the same query in question still doesn't work in windows 7 but works in xp. it's killing me! =/ Link to comment Share on other sites More sharing options...
dany Posted August 21, 2012 Share Posted August 21, 2012 (edited) I have never worked with ADODB but a quickie through the manual learns that ADODB like most SQL-languages does support (named) parameters in queries and 'binding' them to values. This is the preferred way of inserting values into queries as the DB engine takes care of sanitizing (escaping, quoting etc.) the value making sure it's safe and legit to use and doesn't break syntax. Afaik this can be done through the Command and Command.Parameters objects. In pseudocode, so do not assume the method names or syntax are correct: Command.Prepare("SELECT @Desired_Field FROM XMS_Task WHERE XMS_Task.Task_ID='@Ticket'") paramA = Command.CreateParameter('@Desired_Field', 'XMS_Task.Status') Command.Parameters.Append(paramA) paramB = Command.CreateParameter('@Ticket', 'ticket value') Command.Parameters.Append(paramB) Result = Command.Execute() Or something along those lines, you'll have to search MSDN yourself for details, I just quickly glanced over it. Also, in some DB languages you also have to select the column you want to 'WHERE'-compare on. SELECT @Desired_Field, XMS_Task.Task_ID FROM XMS_Task WHERE XMS_Task.Task_ID='@Ticket' That's about all I can think of. Hope it helps and good luck. [EDIT] Maybe you should also check if there are any ADODB implementation differences on XP and 7 that could be borking your query. [/EDIT] Edited August 21, 2012 by dany [center]Spiderskank Spiderskank[/center]GetOpt Parse command line options UDF | AU3Text Program internationalization UDF | Identicon visual hash UDF Link to comment Share on other sites More sharing options...
gcue Posted August 21, 2012 Author Share Posted August 21, 2012 thanks for the response. im using that syntax already - strange thing though: in windows 7, the same adodb connection works for the 4 other queries (4 other tables) also using the same syntax. >_< Link to comment Share on other sites More sharing options...
gcue Posted August 21, 2012 Author Share Posted August 21, 2012 works now! i think something changed in the backend bc i didnt change anything and its working yipeeeeeeeeeeeeeeeeeeee i can now sleep soundly thank you everyone for your help 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