Jump to content
Sign in to follow this  
JLogan3o13

[Solved] Attempting to catch an error in sql query

Recommended Posts

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 by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
JLogan3o13

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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 by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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 by Spiff59

Share this post


Link to post
Share on other sites
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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
BrewManNH

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


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
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 ...

Share this post


Link to post
Share on other sites
jchd

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

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

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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 by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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."

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
JLogan3o13

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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 by Zedna

Share this post


Link to post
Share on other sites
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

√-1 2^3 ∑ π, and it was delicious!

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  

×