[Solved] Looking for a more efficient way to populate 2D array

[JLogan3o13]

I have been working on a project that pulls information from a sql database (Microsoft SCCM), and then presents a GUI to the end user to manipulate that data. On startup, the application pulls a list of collections of computers the user has access to. Currently I pull in a web report, save it as a .csv, and then convert the sheet to a 2d array. This works very well, but is a bit cumbersome. It adds 5-10 seconds onto the startup, which to the end user seems to be a lag, since I cannot create the GUI until this array is created.

I can query the database directly, and get the same information far more quickly in just a few lines of code (see below). I'm just unsure how to easily convert this into a 2d array any other way than the Excel trick I am using above. From the Help file, _ArrayAdd is for a 1D array only. Am I overlooking something obvious, or is the Excel method the best option?

I can easily pull the query, and use the $element.Name and $element.CollectionID properties, but would like to have them in an Array for use in the rest of the application.

$sServer = "SCCMP02"
$SCode = "P02"

$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
$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_Collection WHERE Comment LIKE '%Site License%' ORDER by Name")

For $element In $oResults
  $name = $element.Name
  $colID = $element.CollectionID
  MsgBox(0, $name, $colID)
Next

Edited June 8, 2012 by JLogan3o13

[water]

Maybe something like this? I'm not sure if the name of the property where the recordcount is stored is count or recordcount

#include <Array.au3>
$sServer = "SCCMP02"
$SCode = "P02"

Global $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
$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_Collection WHERE Comment LIKE '%Site License%' ORDER by Name")

Global $aResult[$oResults.Count][2]
Global $iIndex = 1
For $element In $oResults
  $aResult[$iIndex][0] = $element.Name
  $aResult[$iIndex][1] = $element.CollectionID
Next
_ArrayDisplay($aResult)

[JLogan3o13]

Hi, Water. Thanks as always for the great suggestion. I tweaked what you have above just a little, and it is now displaying the array. Unfortunately, it displays only the last item in the array. I figure I have to find a way to increments the $iIndex variable but receive an error about the array dimensions being exceeded. Guess I'll be brushing up on UBound Thanks again for the nudge in the right direction.

#include <Array.au3>
Local $oResults
$sServer = "SCCMP02"
$SCode = "P02"

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

$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_Collection WHERE Comment LIKE '%Site License%' ORDER by Name")

Global $aResult[$oResults.Count][2]
Global $iIndex = 1

For $element In $oResults
   $name = $element.Name
   $col = $element.CollectionID
   $aResult[$iIndex][0] = $name
   $aResult[$iIndex][1] = $col
Next

_ArrayDisplay($aResult)

[water]

My bad The counter has to be incremented

#include <Array.au3>

$sServer = "SCCMP02"
$SCode = "P02"

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

Global $oResults = $oSMS.ExecQuery("SELECT * FROM SMS_Collection WHERE Comment LIKE '%Site License%' ORDER by Name")

Global $aResult[$oResults.Count][2]
Global $iIndex = 0

For $element In $oResults
   $aResult[$iIndex][0] = $element.Name
   $aResult[$iIndex][1] = $element.CollectionID
   $iIndex += 1
Next

_ArrayDisplay($aResult)

N.B. When an error occurres when connecting to the server I would exit the script because the following object related statements will crash. Edited June 8, 2012 by water

[JLogan3o13]

I knew what you meant I actually tried the same thing, but I get the error $aResult[$iIndex][0] = $name >> "Array varuable has incorrect number of subscripts or subscript dimension range exceeded". I am thinking I would have to loop through it somehow in order to increment $iIndex. I tried this, but it just takes the last element in the array and puts it on every line. I just have to figure out how to loop through the elements in $oResults outside the second For loop.

#include <Array.au3>
Local $oResults
$sServer = "SCCMP02"
$SCode = "P02"

$oLocator = ObjCreate("WbemScripting.SWbemLocator")
Global $oSMS = $oLocator.ConnectServer($sServer, "rootsmssite_" & $SCode)
If @error Then MsgBox(0, "", "Can't Connect")
$oSMS.Security_.ImpersonationLevel = 3
$oSMS.Security_.AuthenticationLevel = 6
$oResults = $oSMS.ExecQuery("SELECT * FROM SMS_Collection WHERE Comment LIKE '%Site License%' ORDER by Name")
 
Global $aResult[$oResults.Count][2]
Global $iIndex = 1
 
For $element In $oResults
   For $x = 1 To UBound($aResult) - 1
     $name = $element.Name
     $col = $element.CollectionID
     $aResult[$iIndex][0] = $name
     $aResult[$iIndex][1] = $col
   Next
Next

_ArrayDisplay($aResult)

Counts the number of elements correctly (124), but produces this on every line:

[0]||
[1]|WoundPath Shortcut 1.0|P020009F
[2]|WoundPath Shortcut 1.0|P020009F
[3]|WoundPath Shortcut 1.0|P020009F
[4]|WoundPath Shortcut 1.0|P020009F
[5]|WoundPath Shortcut 1.0|P020009F
[6]|WoundPath Shortcut 1.0|P020009F
[7]|WoundPath Shortcut 1.0|P020009F
[8]|WoundPath Shortcut 1.0|P020009F
[9]|WoundPath Shortcut 1.0|P020009F
[10]|WoundPath Shortcut 1.0|P020009F
[11]|WoundPath Shortcut 1.0|P020009F
[12]|WoundPath Shortcut 1.0|P020009F
[13]|WoundPath Shortcut 1.0|P020009F
[14]|WoundPath Shortcut 1.0|P020009F
[15]|WoundPath Shortcut 1.0|P020009F
[16]|WoundPath Shortcut 1.0|P020009F
[17]|WoundPath Shortcut 1.0|P020009F
[18]|WoundPath Shortcut 1.0|P020009F
[19]|WoundPath Shortcut 1.0|P020009F

Edited June 8, 2012 by JLogan3o13

[water]

Pleas test the (modified) code I posted above again. The index has to start with 0 not with 1 as we don't have the index in "row" 0 of the array.

Edited June 8, 2012 by water

[JLogan3o13]

D'oh! My turn . That works perfectly. Thanks a ton, just cut my startup time in half with this.

[water]

To make it (a bit) faster you can remove the unneccessary assigns (above code modified again) and alter the counter incrementing.

[JLogan3o13]

Thanks again, that helped more than I would have thought. From 5-10 seconds I'm down to less than 2 for launching the app. I'm sure the end users will appreciate that.

[water]

Tell them to throw coffee, beer and wine at you if they are happy with the result

[guinness]

Tell them to throw coffee, beer and wine at you if they are happy with the result

That's going to hurt!