Sign in to follow this  
Followers 0
gcue

Sql query works in XP but not windows 7

19 posts in this topic

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

Func _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!

Share this post


Link to post
Share on other sites



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.

; 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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

thanks for the lead.. but the error isnt very indicative

http://postimage.org/image/u7icrmbz9/

(error: none)

i get this over and over again.

Edited by gcue

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

As two out of three queries work I assume: yes


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (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 Posted Image

Edited by gcue

Share this post


Link to post
Share on other sites

#8 ·  Posted (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 by gcue

Share this post


Link to post
Share on other sites

#9 ·  Posted (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 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.

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

here is the error i get when trying to establish the connection

http://postimage.org/image/i3omxmdtr/

(error: the connection cannot be used to perform this operation. it is either closed or invalid in this context)

Posted Image

Edited by gcue

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 Gude
How 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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 Gude
How 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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

=/

Share this post


Link to post
Share on other sites

#17 ·  Posted (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 by dany

[center]Spiderskank Spiderskank[/center]GetOpt Parse command line options UDF | AU3Text Program internationalization UDF | Identicon visual hash UDF

Share this post


Link to post
Share on other sites

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.

>_<

Share this post


Link to post
Share on other sites

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

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