Arrays are confusing me

[Clark]

Hi all

I've been working around my lack of understanding of arrays for a while now, but it is starting to irk me that I don't know why certain things give me errors.

Take the below function from a program I have written.

expandcollapse popup

Func _Do_Reporting($type)
; 0 = Screen report
; 1 = Excel report
local $arArray[25][14],$iRows,$iColumns
for $index = 6 to 0 step -1
  if GUICtrlRead($Report[$index])=$GUI_CHECKED Then
   exitloop
  EndIf
Next
if $index = 6 Then
  $rSQL = "SELECT * FROM et WHERE Sched_imp_date LIKE """ & GUICtrlRead($Date7) & """;"
else
  $rSQL = "SELECT * FROM et WHERE Status = " & $index & ";"
EndIf
$iRval= _Sql_GetTable2D($hDBHandle,$rSQL,$arArray,$iRows,$iColumns)
if $iRval <> $SQL_OK then
  Msgbox(0 + 16 +262144,"Error","Unable to retrieve audit data")
  Return
EndIf
for $cntr = 1 to $iRows
  $arArray[$cntr][5]=_Fix_Date($arArray[$cntr][5])
  for $newcntr = 8 to 12
   $arArray[$cntr][$newcntr]=_Fix_Date($arArray[$cntr][$newcntr])
  Next
  $sSQL = "SELECT status " & _
    "FROM xstudio_db.table_defect_integrated " & _
    "WHERE defect_id = " & $arArray[$cntr][1] & ";"
  $iRval = _SQL_QuerySingleRow($xDBHandle, $sSQL, $aResult)
  $arArray[$cntr][13] = $aResult                                                                ; <- gets an array out of range error here
Next
if $type = 0 then
  _ArrayDisplay($arArray)
Else
  $sFilePath = @TempDir & "\Temp.xls"
  If Not _FileCreate($sFilePath) Then
   MsgBox(4096, "Error", " Error Creating File - " & @error)
   return 1
  EndIf
  _ExcelBookOpen($sFilePath)
  $oExcel = _ExcelBookAttach("Temp.xls", "FileName")
  _ExcelWriteSheetFromArray($oExcel, $arArray, 1, 1, 0, 0)
EndIf
EndFunc

(omg, what happened to the formatting!?!? Anway, I shall fix the formatting after I post)

I'm confused as to why I get an array error in the line indicated above. I tried locally dimensioning the array via dim $arArray[25][14], but I get the same result. Obviously my understanding of arrays is flawed but I have been unable to figure out where.

Previously I just worked around this sort of issue but now I want to really understand it.

Clark

[jchd]

I guess that your GetTable2D doesn't actually return the array dimensions you expect. What does this show?

expandcollapse popup

Func _Do_Reporting($type)
; 0 = Screen report
; 1 = Excel report
Local $arArray, $iRows, $iColumns
For $index = 6 To 0 Step -1
  If GUICtrlRead($Report[$index]) = $GUI_CHECKED Then
   ExitLoop
  EndIf
Next
If $index = 6 Then
  $rSQL = "SELECT * FROM et WHERE Sched_imp_date LIKE """ & GUICtrlRead($Date7) & """;"
Else
  $rSQL = "SELECT * FROM et WHERE Status = " & $index & ";"
EndIf
$iRval = _Sql_GetTable2D($hDBHandle, $rSQL, $arArray, $iRows, $iColumns) ; overwrites $arArray anyway
If $iRval <> $SQL_OK Then
  MsgBox(0 + 16 + 262144, "Error", "Unable to retrieve audit data")
  Return
EndIf
For $cntr = 1 To $iRows
  $arArray[$cntr][5] = _Fix_Date($arArray[$cntr][5])
  For $newcntr = 8 To 12
   $arArray[$cntr][$newcntr] = _Fix_Date($arArray[$cntr][$newcntr])
  Next
  $sSQL = "SELECT status " & _
    "FROM xstudio_db.table_defect_integrated " & _
    "WHERE defect_id = " & $arArray[$cntr][1] & ";"
  $iRval = _SQL_QuerySingleRow($xDBHandle, $sSQL, $aResult)
_ArrayDisplay($arArray, " $iRows = " & $iRows) ;; look at what you actually have!
  $arArray[$cntr][13] = $aResult ; <- gets an array out of range error here
Next
If $type = 0 Then
  _ArrayDisplay($arArray)
Else
  $sFilePath = @TempDir & "Temp.xls"
  If Not _FileCreate($sFilePath) Then
   MsgBox(4096, "Error", " Error Creating File - " & @error)
   Return 1
  EndIf
  _ExcelBookOpen($sFilePath)
  $oExcel = _ExcelBookAttach("Temp.xls", "FileName")
  _ExcelWriteSheetFromArray($oExcel, $arArray, 1, 1, 0, 0)
EndIf
EndFunc   ;==>_Do_Reporting

Note the comment about $arArray: the GetTable2D will overwrite it (and redimension it) every time, ignoring your previous dimensions declaration.

Cheap advice: beware of SELECT * from... as it's unduly fragile to schema changes (ordering of columns, number of columns). Prefer always explicit column list for outer selects. SELECT * from... is most often fine for subselects.

[Clark]

Hi jchd and thanks for that helpful post

I didn't know that GetTable2D would redim the array. Basically all I need do then is redim it again just before I want to write that extra column.

Fully understand about Select *, and normally wouldn't use it, but in this case I control the schema also so it shouldn't be an issue.

[jchd]

Thr result array parameter from _SQL[ite]_GetTable[2d] is passed ByRef, so that the function can dimension it to what's needed, fill it without having to copy a local copy (like we do when we use Return($Result). See that I've removed the dimensions in the local $arArray declaration and that part will still run fine. If it wasn't the case, tailoring the correct row dimension for an unknow SELECT result would be a gamble!

Ah, you want to add a column! I was suspecting that the schema had changed under your feet and that the * returned one column less than usual.

No way to add a column to an existing array without copying everything it contains into a new, correctly dimensionned array.

But wait, _you_ control the number of columns of the result array. SELECT *FROM ... returns 12 colums, so make it SELECT *, '' FROM ... to have your extra column in the result.