adaptation of ArrayDisplay function

Richard · January 9, 2010

I am using an adapted ArrayDisplay function for sqlite table display.

Is it possible to make the font bigger? (I tried GUICtrlSetFont($sHeader, 11, 700) after creating listview - does not work)

Can the first column be eliminated.

The heading of the listview should display the fields of row 0

Thanks

Richard

jchd · January 9, 2010

Is it possible to make the font bigger? (I tried GUICtrlSetFont($sHeader, 11, 700) after creating listview - does not work)

Wrong controlID ! Replace $Header by $hListview

Try inserting the line below after line 436 which is Local $hListView = GUICtrlCreateListView(...

GUICtrlSetFont($hListView, 11, 700, 0, "DejaVu Sans Mono") ;; or replace "DejaVu Sans Mono" by your favorite font, or leave as default

Can the first column be eliminated.
The heading of the listview should display the fields of row 0

Yes, you can use the following the same way you use _SQLite_GetTable2d:

Func _SQLite_GetData2d($hDB, $sql, ByRef $rows, ByRef $nrows, ByRef $ncols)
    Local $rtn = _SQLite_GetTable2d($hDB, $sql, $rows, $nrows, $ncols)
    If $rtn = $SQLITE_OK Then
        _ArrayDelete($rows, 0)
    EndIf
    Return (SetError(@error, 0, $rtn))
EndFunc ;==>_SQLite_GetData2d

But then, be sure to check for error condition. The reason is that if your query returns no row, then the $rows variable will be set to '' (not an array) because it won't hold the column names any more and there is no way in AutoIt to keep or declare an array with any dimension set to zero.

Richard · January 11, 2010

Wrong controlID ! Replace $Header by $hListview

Try inserting the line below after line 436 which is Local $hListView = GUICtrlCreateListView(...

GUICtrlSetFont($hListView, 11, 700, 0, "DejaVu Sans Mono") ;; or replace "DejaVu Sans Mono" by your favorite font, or leave as default

Yes, you can use the following the same way you use _SQLite_GetTable2d:
Func _SQLite_GetData2d($hDB, $sql, ByRef $rows, ByRef $nrows, ByRef $ncols)
    Local $rtn = _SQLite_GetTable2d($hDB, $sql, $rows, $nrows, $ncols)
    If $rtn = $SQLITE_OK Then
        _ArrayDelete($rows, 0)
    EndIf
    Return (SetError(@error, 0, $rtn))
EndFunc ;==>_SQLite_GetData2d
But then, be sure to check for error condition. The reason is that if your query returns no row, then the $rows variable will be set to '' (not an array) because it won't hold the column names any more and there is no way in AutoIt to keep or declare an array with any dimension set to zero.

-----------------------

I think there is a misunderstanding.

I want the 1st column (called row) removed and the name of the columns displayed in the heading in stead of

col 0 - col1 ......

All the function seems to do is remove row 0 (the column headings)

Richard

jchd · January 11, 2010

-----------------------
I think there is a misunderstanding.
I want the 1st column (called row) removed and the name of the columns displayed in the heading in stead of
col 0 - col1 ......
All the function seems to do is remove row 0 (the column headings)

Richard

You're right, I didn't read in your mind.

Here's something else. Indeed, it makes more sense this way.

I didn't use parameters for some options of _ArrayDisplay; adapt to your needs.

Func __SQLite_GetDisplayArray2d($hDB, $sql, ByRef $rows, ByRef $nrows, ByRef $ncols, $Title = 'Display of SQLite table using headers from query')
    Local $rtn = _SQLite_GetTable2d($hDB, $sql, $rows, $nrows, $ncols)
    If Not @error Then
        If $rtn = $SQLITE_OK And $ncols > 0 Then
            Local $Header = "Row"
            For $i = 0 to $ncols - 1
                $Header &= '|' & $rows[0][$i]
            Next
            _ArrayDelete($rows, 0)
            _ArrayDisplay($rows, $Title, -1, 0, '', '|', $Header)
        EndIf
    EndIf
    Return (SetError(@error, 0, $rtn))
EndFunc ;==>_SQLite_GetDisplayArray2d

Richard · January 11, 2010

That's much better , thanks

But it still displays the first column (the array subscripts)

----------

You're right, I didn't read in your mind.

Here's something else. Indeed, it makes more sense this way.

I didn't use parameters for some options of _ArrayDisplay; adapt to your needs.

Func __SQLite_GetDisplayArray2d($hDB, $sql, ByRef $rows, ByRef $nrows, ByRef $ncols, $Title = 'Display of SQLite table using headers from query')
    Local $rtn = _SQLite_GetTable2d($hDB, $sql, $rows, $nrows, $ncols)
    If Not @error Then
        If $rtn = $SQLITE_OK And $ncols > 0 Then
            Local $Header = "Row"
            For $i = 0 to $ncols - 1
                $Header &= '|' & $rows[0][$i]
            Next
            _ArrayDelete($rows, 0)
            _ArrayDisplay($rows, $Title, -1, 0, '', '|', $Header)
        EndIf
    EndIf
    Return (SetError(@error, 0, $rtn))
EndFunc ;==>_SQLite_GetDisplayArray2d

jchd · January 11, 2010

That's much better , thanks
But it still displays the first column (the array subscripts)

Correct. For removing that, you'll have to change _ArrayDisplay. Copy the std _ArrayDisplay into __My_ArrayDisplay then

Comment out line 373 of UDF version 3.3.3.3 (was $avArrayText[$i] = "[" & $i & "]")

Insert at line 398 (between the two nested Next) $avArrayText[$i] = StringTrimLeft($avArrayText[$i], 1)

and use this:

Func __SQLite_GetDisplayArray2d($hDB, $sql, ByRef $rows, ByRef $nrows, ByRef $ncols, $Title = 'Display of SQLite table using headers from query')
    Local $rtn = _SQLite_GetTable2d($hDB, $sql, $rows, $nrows, $ncols)
    If Not @error Then
        If $rtn = $SQLITE_OK And $ncols > 0 Then
            Local $Header
            For $i = 0 to $ncols - 1
                $Header &= '|' & $rows[0][$i]
            Next
            $Header = StringTrimLeft($Header, 1)
            _ArrayDelete($rows, 0)
            __My_ArrayDisplay($rows, $Title, -1, 0, '', '|', $Header)
        EndIf
    EndIf
    Return (SetError(@error, 0, $rtn))
EndFunc ;==>__SQLite_GetDisplayArray2d

jchd · January 11, 2010

Do either of you happen to have an example of how to use this function?

Probably, but _which_ function?

jchd · January 11, 2010

The one you just posted, __SQLite_GetDisplayArray2d().

Simple, as the post goes.

It performs some SQL query on the given SQLite database and then array-displays the result with headers = the resulting query headers and (last version) no "Row" column.

Highly similar to __SQLite_GetTable2d, but displays the result as stated above as well.

See help file for SQLite help.

__SQLite_GetDisplayArray2d($hDB, "select * from mytable;", $rows, $nrows, $ncols, 'Display of mytable content')

jchd · January 11, 2010

Wow, that is cool. I use a SQLite database in a few of my scripts, and I sometimes have trouble visualizing exactly what my database looks like. I usually use the SQLite Database Browser to view them, but I can't open a database with it and AutoIt at the same time, so it's quite a pain going back and forth.

Then you should use a real, complete SQLite manager!

Go check this one. Already the free version does a lot (and doesn't expire).

But beware that if you happen to write to the same base with two or more processes, then you better wrap your read/modify/write sections in "begin immediate;" ... "commit;" transactions and set ample timeout to avoid getting $SQLITE_BUSY and/or deadlocks. If you do that consistently, then you won't run into problems. Anyway, do _not_ use shared bases over NFS/SMB: weird locking problems can occur, possibly destroying your base.

Richard · January 12, 2010

Func __SQLite_GetDisplayArray2d($hDB, $sql, ByRef $rows, ByRef $nrows, ByRef $ncols, $Title = 'Display of SQLite table using headers from query')
    Local $rtn = _SQLite_GetTable2d($hDB, $sql, $rows, $nrows, $ncols)
    If Not @error Then
        If $rtn = $SQLITE_OK And $ncols > 0 Then
            Local $Header = "Row"
            For $i = 0 to $ncols - 1
                $Header &= '|' & $rows[0][$i]
            Next
            _ArrayDelete($rows, 0)
            _ArrayDisplay($rows, $Title, -1, 0, '', '|', $Header)
        EndIf
    EndIf
    Return (SetError(@error, 0, $rtn))
EndFunc ;==>_SQLite_GetDisplayArray2d

This function is fine but it displays 0 for the 1st column/ 1st row and it should display 1 (record number)...

Can this be adapted?

Richard

jchd · January 12, 2010

This function is fine but it displays 0 for the 1st column/ 1st row and it should display 1 (record number)...
Can this be adapted?

I'm unsure what you mean. Isn't this 0 part of your table?

Anyway, try this couple of functions. There is now one more parameter to make the array display column names as header and as in the std UDF (Row column) or along your line (no Row column). Tell me if this is OK.

Func _SQLite_GetDisplayArray2d($hDB, $sql, ByRef $rows, ByRef $nrows, ByRef $ncols, $Title = 'Display of SQLite table, headers from query option', $fStdHead = 0)
Local $rtn = _SQLite_GetTable2d($hDB, $sql, $rows, $nrows, $ncols)
If Not @error Then
If $rtn = $SQLITE_OK And $ncols > 0 Then
Local $Header
For $i = 0 to $ncols - 1
$Header &= '|' & $rows[0][$i]
Next
$Header = StringTrimLeft($Header, 1)
_ArrayDelete($rows, 0)
__ArrayDisplay($rows, $Title, -1, 0, '|', '|', $Header, $fStdHead)
SetError(0)
EndIf
EndIf
Return (SetError(@error, 0, $rtn))
EndFunc ;==>__SQLite_GetDisplayArray2d

Func __ArrayDisplay(Const ByRef $avArray, $sTitle = "Array: ListView Display", $iItemLimit = -1, $iTranspose = 0, $sSeparator = "", $sReplace = "|", $sHeader = "", $fShowRows = 1)
If Not IsArray($avArray) Then Return SetError(1, 0, 0)
; Dimension checking
Local $iDimension = UBound($avArray, 0), $iUBound = UBound($avArray, 1) - 1, $iSubMax = UBound($avArray, 2) - 1
If $iDimension > 2 Then Return SetError(2, 0, 0)

; Separator handling
If $sSeparator = "" Then $sSeparator = '|'

; Check the separator to make sure it's not used literally in the array
If _ArraySearch($avArray, $sSeparator, 0, 0, 0, 1) <> -1 Then
For $x = 1 To 255
If $x >= 32 And $x <= 127 Then ContinueLoop
Local $sFind = _ArraySearch($avArray, Chr($x), 0, 0, 0, 1)
If $sFind = -1 Then
$sSeparator = Chr($x)
ExitLoop
EndIf
Next
EndIf

; Declare variables
Local $vTmp, $iBuffer = 64
Local $iColLimit = 250
Local $iOnEventMode = Opt("GUIOnEventMode", 0), $sDataSeparatorChar = Opt("GUIDataSeparatorChar", $sSeparator)

; Swap dimensions if transposing
If $iSubMax < 0 Then $iSubMax = 0
If $iTranspose Then
$vTmp = $iUBound
$iUBound = $iSubMax
$iSubMax = $vTmp
EndIf

; Set limits for dimensions
If $iSubMax > $iColLimit Then $iSubMax = $iColLimit
If $iItemLimit < 1 Then $iItemLimit = $iUBound
If $iUBound > $iItemLimit Then $iUBound = $iItemLimit

; Set header up
If $sHeader = "" Then
$sHeader = "Row " ; blanks added to adjust column size for big number of rows
For $i = 0 To $iSubMax
$sHeader &= $sSeparator & "Col " & $i
Next
EndIf

; Convert array into text for listview
Local $avArrayText[$iUBound + 1]
For $i = 0 To $iUBound
If $fShowRows Then
$avArrayText[$i] = "[" & $i & "]"
EndIf
For $j = 0 To $iSubMax
; Get current item
If $iDimension = 1 Then
If $iTranspose Then
$vTmp = $avArray[$j]
Else
$vTmp = $avArray[$i]
EndIf
Else
If $iTranspose Then
$vTmp = $avArray[$j][$i]
Else
$vTmp = $avArray[$i][$j]
EndIf
EndIf

; Add to text array
$vTmp = StringReplace($vTmp, $sSeparator, $sReplace, 0, 1)
$avArrayText[$i] &= $sSeparator & $vTmp

; Set max buffer size
$vTmp = StringLen($vTmp)
If $vTmp > $iBuffer Then $iBuffer = $vTmp
Next
If Not $fShowRows Then
$avArrayText[$i] = StringTrimLeft($avArrayText[$i], 1)
EndIf
Next
$iBuffer += 1

; GUI Constants
Local Const $_ARRAYCONSTANT_GUI_DOCKBORDERS = 0x66
Local Const $_ARRAYCONSTANT_GUI_DOCKBOTTOM = 0x40
Local Const $_ARRAYCONSTANT_GUI_DOCKHEIGHT = 0x0200
Local Const $_ARRAYCONSTANT_GUI_DOCKLEFT = 0x2
Local Const $_ARRAYCONSTANT_GUI_DOCKRIGHT = 0x4
Local Const $_ARRAYCONSTANT_GUI_EVENT_CLOSE = -3
Local Const $_ARRAYCONSTANT_LVIF_PARAM = 0x4
Local Const $_ARRAYCONSTANT_LVIF_TEXT = 0x1
Local Const $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH = (0x1000 + 29)
Local Const $_ARRAYCONSTANT_LVM_GETITEMCOUNT = (0x1000 + 4)
Local Const $_ARRAYCONSTANT_LVM_GETITEMSTATE = (0x1000 + 44)
Local Const $_ARRAYCONSTANT_LVM_INSERTITEMW = (0x1000 + 77)
Local Const $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE = (0x1000 + 54)
Local Const $_ARRAYCONSTANT_LVM_SETITEMW = (0x1000 + 76)
Local Const $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT = 0x20
Local Const $_ARRAYCONSTANT_LVS_EX_GRIDLINES = 0x1
Local Const $_ARRAYCONSTANT_LVS_SHOWSELALWAYS = 0x8
Local Const $_ARRAYCONSTANT_WS_EX_CLIENTEDGE = 0x0200
Local Const $_ARRAYCONSTANT_WS_MAXIMIZEBOX = 0x00010000
Local Const $_ARRAYCONSTANT_WS_MINIMIZEBOX = 0x00020000
Local Const $_ARRAYCONSTANT_WS_SIZEBOX = 0x00040000
Local Const $_ARRAYCONSTANT_tagLVITEM = "int Mask;int Item;int SubItem;int State;int StateMask;ptr Text;int TextMax;int Image;int Param;int Indent;int GroupID;int Columns;ptr pColumns"

Local $iAddMask = BitOR($_ARRAYCONSTANT_LVIF_TEXT, $_ARRAYCONSTANT_LVIF_PARAM)
Local $tBuffer = DllStructCreate("wchar Text[" & $iBuffer & "]"), $pBuffer = DllStructGetPtr($tBuffer)
Local $tItem = DllStructCreate($_ARRAYCONSTANT_tagLVITEM), $pItem = DllStructGetPtr($tItem)
DllStructSetData($tItem, "Param", 0)
DllStructSetData($tItem, "Text", $pBuffer)
DllStructSetData($tItem, "TextMax", $iBuffer)

; Set interface up
Local $iWidth = 640, $iHeight = 480
Local $hGUI = GUICreate($sTitle, $iWidth, $iHeight, Default, Default, BitOR($_ARRAYCONSTANT_WS_SIZEBOX, $_ARRAYCONSTANT_WS_MINIMIZEBOX, $_ARRAYCONSTANT_WS_MAXIMIZEBOX))
Local $aiGUISize = WinGetClientSize($hGUI)
Local $hListView = GUICtrlCreateListView($sHeader, 0, 0, $aiGUISize[0], $aiGUISize[1] - 26, $_ARRAYCONSTANT_LVS_SHOWSELALWAYS)
GUICtrlSetFont($hListView, 9, 400, 0, "DejaVu Sans Mono")
Local $hCopy = GUICtrlCreateButton("Copy Selected", 3, $aiGUISize[1] - 23, $aiGUISize[0] - 6, 20)
GUICtrlSetResizing($hListView, $_ARRAYCONSTANT_GUI_DOCKBORDERS)
GUICtrlSetResizing($hCopy, $_ARRAYCONSTANT_GUI_DOCKLEFT + $_ARRAYCONSTANT_GUI_DOCKRIGHT + $_ARRAYCONSTANT_GUI_DOCKBOTTOM + $_ARRAYCONSTANT_GUI_DOCKHEIGHT)
GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_GRIDLINES, $_ARRAYCONSTANT_LVS_EX_GRIDLINES)
GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT)
GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE)

; Fill listview
Local $aItem
For $i = 0 To $iUBound
If GUICtrlCreateListViewItem($avArrayText[$i], $hListView) = 0 Then
; use GUICtrlSendMsg() to overcome AutoIt limitation
$aItem = StringSplit($avArrayText[$i], $sSeparator)
DllStructSetData($tBuffer, "Text", $aItem[1])

; Add listview item
DllStructSetData($tItem, "Item", $i)
DllStructSetData($tItem, "SubItem", 0)
DllStructSetData($tItem, "Mask", $iAddMask)
GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_INSERTITEMW, 0, $pItem)

; Set listview subitem text
DllStructSetData($tItem, "Mask", $_ARRAYCONSTANT_LVIF_TEXT)
For $j = 2 To $aItem[0]
DllStructSetData($tBuffer, "Text", $aItem[$j])
DllStructSetData($tItem, "SubItem", $j - 1)
GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_SETITEMW, 0, $pItem)
Next
EndIf
Next

; adjust window width
$iWidth = 0
For $i = 0 To $iSubMax + 1
$iWidth += GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH, $i, 0)
Next
If $iWidth < 250 Then $iWidth = 230
$iWidth += 20

If $iWidth > @DesktopWidth Then $iWidth = @DesktopWidth - 100

WinMove($hGUI, "", (@DesktopWidth - $iWidth)/2, Default, $iWidth)

; Show dialog
GUISetState(@SW_SHOW, $hGUI)

While 1
Switch GUIGetMsg()
Case $_ARRAYCONSTANT_GUI_EVENT_CLOSE
ExitLoop

Case $hCopy
Local $sClip = ""

; Get selected indices [ _GUICtrlListView_GetSelectedIndices($hListView, True) ]
Local $aiCurItems[1] = [0]
For $i = 0 To GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMCOUNT, 0, 0)
If GUICtrlSendMsg($hListView, $_ARRAYCONSTANT_LVM_GETITEMSTATE, $i, 0x2) Then
$aiCurItems[0] += 1
ReDim $aiCurItems[$aiCurItems[0] + 1]
$aiCurItems[$aiCurItems[0]] = $i
EndIf
Next

; Generate clipboard text
If Not $aiCurItems[0] Then
For $sItem In $avArrayText
$sClip &= $sItem & @CRLF
Next
Else
For $i = 1 To UBound($aiCurItems) - 1
$sClip &= $avArrayText[$aiCurItems[$i]] & @CRLF
Next
EndIf
ClipPut($sClip)
EndSwitch
WEnd
GUIDelete($hGUI)

Opt("GUIOnEventMode", $iOnEventMode)
Opt("GUIDataSeparatorChar", $sDataSeparatorChar)

Return 1
EndFunc ;==>_ArrayDisplay

jchd · January 12, 2010

The first Column Header was still labeled "Row", but these new functions fixed the problem.

Sorry for inconvenience. I hacked things a bit too fast and was being lazy/busy, which is a bad thing [being lazy].

Richard · January 13, 2010

Sorry for inconvenience. I hacked things a bit too fast and was being lazy/busy, which is a bad thing [being lazy].

Thanks, everything seems to be ok now.

Just one more thing.

The columns adapt their width automatically in function of the length of the data, but when the heading is longer than the data, only part of

that header is shown.

Richard

jchd · January 13, 2010

The columns adapt their width automatically in function of the length of the data, but when the heading is longer than the data, only part of
that header is shown.

That's the behavior of the UDF as it is currently. Of course it could be fixed somehow but this isn't that easy.

The function should scan the wole array, column after column to determine what is the largest header/row width but also has to deal with very large columns that won't fit on any display, and also resolve the various cases where the added width of columns is bigger than the width of the listview. For large arrays, this is very expensive in time and the results are likely to make everyone unhappy, some wanting "this" behavior, others wanting "that" one, both with good personal reasons. A strategy that would fit everyone's wishes in all cases is beyond hope, from this point of view.

Given that the headers of this function are a direct consequence of your query, you can adapt the query to mitigate the display inconvenience. For instance, say you have a query like this:

select column_one, very_large_column_native_name_much_larger_than_the_data_it_holds, date from mytable where ...;

rewrite it as:

select column_one as MyHead1, very_large_column_native_name_much_larger_than_the_data_it_holds as "My Header 2", Date as [ʉɸɐȱʖʘʄϠϗ] from mytable where ...;

and the listview headers will be (the strange characters are just to illustrate the possibility):

MyHead1 | My Header 2 | ʉɸɐȱʖʘʄϠϗ

Instead, you still can drag the column separation to adjust the column width to your visual needs, or double click on it to have Windows do the adjustment for you.

Edited January 13, 2010 by jchd

Richard · January 13, 2010

You're right

thanks

That's the behavior of the UDF as it is currently. Of course it could be fixed somehow but this isn't that easy.

The function should scan the wole array, column after column to determine what is the largest header/row width but also has to deal with very large columns that won't fit on any display, and also resolve the various cases where the added width of columns is bigger than the width of the listview. For large arrays, this is very expensive in time and the results are likely to make everyone unhappy, some wanting "this" behavior, others wanting "that" one, both with good personal reasons. A strategy that would fit everyone's wishes in all cases is beyond hope, from this point of view.

Given that the headers of this function are a direct consequence of your query, you can adapt the query to mitigate the display inconvenience. For instance, say you have a query like this:

select column_one, very_large_column_native_name_much_larger_than_the_data_it_holds, date from mytable where ...;

rewrite it as:

select column_one as MyHead1, very_large_column_native_name_much_larger_than_the_data_it_holds as "My Header 2", Date as [ʉɸɐȱʖʘʄϠϗ] from mytable where ...;

and the listview headers will be (the strange characters are just to illustrate the possibility):

MyHead1 | My Header 2 | ʉɸɐȱʖʘʄϠϗ

Instead, you still can drag the column separation to adjust the column width to your visual needs, or double click on it to have Windows do the adjustment for you.

adaptation of ArrayDisplay function

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members