Sign in to follow this  
Followers 0
Clark

Arrays are confusing me

4 posts in this topic

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.

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

Share this post


Link to post
Share on other sites



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

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.


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

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.

Share this post


Link to post
Share on other sites

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.


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

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