[Solved] Attempting to catch an error in sql query

[JLogan3o13]

I am currently working on a replacement console for SCCM 2007 for a customer. The console is almost ready for delivery, but I am having a hard time catching an error. If anyone could suggest any options, I would appreciate it greatly. Below are the relevant portions of the code:

Main GUI calls the three functions below to add machine to a collection, passing variables for the machine name and the collection ID. The problem I am encountering is generated in the _find function; if the machine name entered is not valid, the ResourceID cannot be obtained, and the $machine variable is also invalid. The GUI then crashes in the _add function with the message "Variable used without being declared". I have confirmed everything works correctly if the technician enters the machine name correctly.

The _connect function opens the connection to the SCCM server:

Func _connect()

$oLocator = ObjCreate("WbemScripting.SWbemLocator")
Global $oSMS = $oLocator.ConnectServer($sServer, "root\sms\site_" & $SCode)
If @error Then MsgBox(0, "", "Can't Connect")
$oSMS.Security_.ImpersonationLevel = 3
$oSMS.Security_.AuthenticationLevel = 6

EndFunc

The _find function then queries the SCCM db for the machine name, and converts the name to the ResourceID:

Func _find()

$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_R_System WHERE Name = '" & $pc & "'")

For $element In $oResults
  Global $machine = $element.ResourceID
Next

EndFunc

Finally the _add function adds the ResourceID to the Collection

Func _add()

$collection = $oSMS.Get("SMS_Collection.CollectionID=" & """" & $ColID & """")
$rule = $oSMS.Get("SMS_CollectionRuleDirect").SpawnInstance_()
$rule.ResourceClassName = "SMS_R_System"
$rule.ResourceID = $machine
$collection.AddMembershipRule($rule)

EndFunc

I have tried a number of methods to catch the error; checking @error, checking if $oResults = "", etc. Nothing seems to be working for me. My latest attempt is adding an if statement to the _find function, shown below. The if statement works, IF the ResourceID is successfully returned. If it is not, however, I don't get a Msgbox, it just errors out.

Func _find()

$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_R_System WHERE Name = '" & $pc & "'")
  For $element In $oResults
   Global $machine = $element.ResourceID

    If $machine = "" Then
     MsgBox(0, "Doublecheck", "$machine not valid.")       ;changed portion
    Else
     MsgBox(0, "DoubleCheck", $machine)
    EndIf
  Next

EndFunc

I'm thinking I've been staring at it too long, and that the answer is quite simple. If anyone has any thoughts, that would be great.

Edited May 30, 2012 by JLogan3o13

[Zedna]

You must use COM/OLE error handler.

Look at ObjEvent() in the helpfile.

[JLogan3o13]

Thanks, Zedna, I will try that out. I knew it had to be sitting right in front of me.

[JLogan3o13]

Ok, I must still be looking at this incorrectly; I am feeling decidedly thick. I tried adding in something like this, using the example in the help file, but I get "The requested action with this object has failed." Should I be looking at another method, rather than ExecQuery, to catch the error?

Modified _find function:

Func _find()

Local $oErrorHandler = ObjEvent($oSMS.ExecQuery, "_ErrFunc")

$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_R_System WHERE Name = '" & $pc & "'")
  If @error Then Return

For $element In $oResults
  Global $machine = $element.ResourceID
Next

EndFunc

Edited May 29, 2012 by JLogan3o13

[Spiff59]

Did you try the default of trapping "AutoIt.Error" ?

Edit: A Global ObjEvent() near the top of my script trapping the string above reports the details of any COM errors (SQL included) that I encounter.

Edited May 29, 2012 by Spiff59

[JLogan3o13]

I did indeed. Unfortunately when I use AutoIt.Error, it is as though it doesn't even try to capture the error. It goes back to erroring out on the usage of $machine, as if it skips the ObjEvent lines altogether.

[BrewManNH]

Did you try checking to see if $oResults is an object before trying to process what's in it?

[Zedna]

I did indeed. Unfortunately when I use AutoIt.Error, it is as though it doesn't even try to capture the error. It goes back to erroring out on the usage of $machine, as if it skips the ObjEvent lines altogether.

Post your script using AutoIt.Error and we will help you with it ...

[jchd]

Try the .EOF() function on the resultset returned by the query.

If $oResults.EOF() Then
  ; handle empty result set
Else
  ; process results
Endif

[JLogan3o13]

Did you try checking to see if $oResults is an object before trying to process what's in it?

I did, I put in IsObj like this. Unfortunately, it shows up as an object either way.

Func _find()

$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_R_System WHERE Name = '" & $pc & "'")


  If Not IsObj($oResults) Then
   MsgBox(0, "", "Not an object.")
  Else
   MsgBox(0, "", "I'm an object.")
  EndIf


#cs
For $element In $oResults
  Global $machine = $element.ResourceID
Next
#ce

EndFunc

[JLogan3o13]

Post your script using AutoIt.Error and we will help you with it ...

This is the _find function with the AutoIt.Error attempt. It is more or less culled from the example in the Help file:

Func _find()

Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_R_System WHERE Name = '" & $pc & "'")
  If @error Then Return

For $element In $oResults
  Global $machine = $element.ResourceID
Next

EndFunc

The _ErrFunc is just this:

Func _ErrFunc()

MsgBox(0, "", "Testing COM error handling.")

EndFunc

In this case it goes right on to the _add function, as if no @error is thrown.

Edited May 29, 2012 by JLogan3o13

[JLogan3o13]

Try the .EOF() function on the resultset returned by the query.

If $oResults.EOF() Then
  ; handle empty result set
Else
  ; process results
Endif

Thanks for the assistance, I did try this out. The error I get is "The requested action with this object has failed."

[jchd]

I hope you're using at least the latest release (the beta is even better). Then remove any obsolete COM error handler and simply test for @error at every AutoIt / Com instruction to catch where the error raises.

[JLogan3o13]

I'm using 3.3.8.1. I can certainly through the latest Beta on a test machine and give it a go.

[jchd]

Both should behave the same in this respect: old COM error handler is a thing of the past. If your resultset is not an object, then some error must have occured before. At least that sound logical. Maybe SCCM is special, distorded, perverse or otherwise trying to fool you.

[JLogan3o13]

It is all of those things Thanks for the assistance to everyone. I am still working through my options, and am open to any suggestions, but it is looking like I will have to find a workaround.

[Zedna]

Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

Func _find()
    $oResults = $oSMS.ExecQuery("SELECT * FROM SMS_R_System WHERE Name = '" & $pc & "'")
    If @error Then Return

    For $element In $oResults
        Global $machine = $element.ResourceID
    Next
EndFunc   ;==>_find

Func _ErrFunc()
    MsgBox(16, "Error", "Error in COM!" & @CRLF & @CRLF & _
            "Description: " & @TAB & $oErrorHandler.description & @CRLF & _
            "Win. description:" & @TAB & $oErrorHandler.windescription & @CRLF & _
            "Line number: " & @TAB & $oErrorHandler.scriptline & @CRLF & _
            "Error numberČíslo chyby: " & @TAB & Hex($oErrorHandler.number, 8) & @CRLF & _
            "Object: " & @TAB & $oErrorHandler.source)

    SetError(1)
EndFunc

[JLogan3o13]

Thanks, Zedna. I plugged this in and tested, but it does not catch the error. I still receive the following when it attempts to use the $machine variable.:

C:Users...DesktopTest.au3 (335) : ==> Variable used without being declared.:

$rule.ResourceID = $machine
$rule.ResourceID = ^ ERROR

->09:12:37 AutoIT3.exe ended.rc:1
>Exit code: 1    Time: 16.422

[Zedna]

You badly declared/used global variable.

Instead of:

Func _find()
$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_R_System WHERE Name = '" & $pc & "'")
For $element In $oResults
  Global $machine = $element.ResourceID
Next
EndFunc

use this:

Global $machine = ''

Func _find()
$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_R_System WHERE Name = '" & $pc & "'")
For $element In $oResults
  $machine = $element.ResourceID
Next
EndFunc

Edited May 30, 2012 by Zedna

[JLogan3o13]

Thanks, Zedna, I knew I needed to move that var up to the top, didn't think it would affect the error handling. I made the change, but it still wasn't catching the COM error. I did try this, though, and it is working. Zedna, jchd, Spiff59 and BrewManNH, thanks for all the help.

Global $machine = ''
Global $ErrLevel = ''

Func _find()

$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_R_System WHERE Name = '" & $pc & "'")
 
   For $element In $oResults
     $machine = $element.ResourceID
   Next

EndFunc

Func _add()

 If StringLen($machine) < 1 Then
   MsgBox(0, "", "Error")
   $ErrLevel = 1
 Else
   $collection = $oSMS.Get("SMS_Collection.CollectionID=" & """" & $ColID & """")
   $rule = $oSMS.Get("SMS_CollectionRuleDirect").SpawnInstance_()
   $rule.ResourceClassName = "SMS_R_System"
   $rule.ResourceID = $machine
   $collection.AddMembershipRule($rule)
 EndIf

EndFunc