Jump to content
Sign in to follow this  
FrancescoDiMuro

Creation of ListView Items takes a lot of time...

Recommended Posts

Good evening everyone :)
I am building a management for the company I work with, and I just imported a real amount of rows ( about 29000 ), in my SQLite DB.
The thing I am not understanding, is the time that the script takes to build this amount of rows in the ListView.
I didn't measure it, but I think it took 2 minutes or so to create each ListView item...
It is normal that it takes so much time?
What can I do to improve the creation of the items?

Here's the code I am using to query and to create ListView items...

; Articles ListView:
Global $lvwArticles = GUICtrlCreateListView("ID|Fornitore|Codice|Descrizione|EU|Prezzo|Sconto Applicato|Note", 14, 87, 1507, 660, BitOR($GUI_SS_DEFAULT_LISTVIEW,$LVS_SORTASCENDING,$LVS_SORTDESCENDING), BitOR($WS_EX_CLIENTEDGE,$LVS_EX_GRIDLINES,$LVS_EX_FULLROWSELECT))

; Query
$strQuery = "SELECT * FROM ARTICOLI;"

; Query Execution
_SQLite_GetTable2d($objDatabase, $strQuery, $arrResult, $intRows, $intColumns)
If @error Then
    ; Error Handling
Else
    ; Cleaning the ListView
    _GUICtrlListView_DeleteAllItems($lvwArticles)
    If @error Then
        ; Error Handling
    Else
        ; No records in the Table
        If UBound($arrResult) < 2 Then
            ; Error Handling
        Else
            _GUICtrlListView_BeginUpdate($lvwArticles)

            For $intCounter = 1 To UBound($arrResult) - 1

                $strListViewItem = $arrResult[$intCounter][0] & "|" & _
                                   $arrResult[$intCounter][1] & "|" & _
                                   $arrResult[$intCounter][2] & "|" & _
                                   $arrResult[$intCounter][3] & "|" & _
                                   $arrResult[$intCounter][4] & "|" & _
                                   $arrResult[$intCounter][5] & "|" & _
                                   $arrResult[$intCounter][6] & "|" & _
                                   $arrResult[$intCounter][7]

                $objListViewItem = GUICtrlCreateListViewItem($strListViewItem, $lvwArticles)
            Next

            _GUICtrlListView_EndUpdate($lvwArticles)

        EndIf
    EndIf
EndIf

Thanks in advance :)


Best Regards.


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

@AutoBert

I didn't watch closely in the Help file!

Didn't know about that function :)

I will try that tomorrow, and I'll let you know :)

Thanks for the suggestion.

PS: How could I measure the time since I start to create ListView Items and when the last ListView Item is displayed? 

Just for curiosity :)

Thanks again!

 

Best Regards.


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
2 hours ago, FrancescoDiMuro said:

PS: How could I measure the time since I start to create ListView Items and when the last ListView Item is displayed? 

The example _GUICtrlListView_AddArray.au3 show this too, using TimerInit and TimerDiff for this.

Share this post


Link to post
Share on other sites

To get an idea of how fast you can insert the rows in a listview, try displaying the array with _ArrayDisplay(). The code in _ArrayDisplay() is optimized code and It can probably not be done faster.

If it's not fast enough, then there is an ultimate solution. This solution consists of using a virtual listview. In a virtual listview, data is not inserted in the listview. Instead, the virtual listview reads data directly from the source. In this case either the array or the SQLite database.

Virtual listviews are fully and completely supported by AutoIt code.

To get an idea of how fast you can show the array in a virtual listview, try displaying the array with _ArrayDisplayEx().

There is also a function _SQLite_Display() that can display data from a SQLite database directly in a virtual listview. This function is very very fast. The total time spent reading the 29,000 rows from the database and displaying the first page in the listview will probably be less than one second.

Use this example to create your own virtual listview.

Share this post


Link to post
Share on other sites

@AutoBert Thanks for your suggestions :) I will check them tomorrow in the morning.

@LarsJ Wow! Didn't know anything about that... :) Let me take a look and bring you a feedback :)

Another little question ( for me you are like Masters in this programming language ): I'd like to improve my knowledge about AutoIt... Where can I take a look? 

Thanks! 

 

Best Regards.

 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

@AutoBert
Now it takes about 19 seconds to "fill" the ListView, but the "bug" is, that the ListView is not filled correctly, because, in the ListView, it is displayed only the first and the last element of the array.
I don't know why...


I'm going to see what kind of things are Virtual ListViews :)


Best Regards.


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

And you remembered to check the input parameters for _GUICtrlListView_AddArray() in the help file?

Another little answer: You can take a look in the Microsoft documentation.

Share this post


Link to post
Share on other sites
27 minutes ago, LarsJ said:

And you remembered to check the input parameters for _GUICtrlListView_AddArray() in the help file?

Sure :)

Isn't a Result array from _SQLite_GetTable2() already formatted in that way ( as _GUICtrlListView_ArrayAdd requests )? :)

The Help file is my Guide :D

 

Best Regards.


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

Yes, It's just a normal 2D array. And _GUICtrlListView_AddArray() is pretty simple code. And therefore it should work. And when simpel code isn't working, it should not be ignored. You have to investigate the problem.

Share this post


Link to post
Share on other sites

@LarsJ

I'll do some screenshot in order to let you see what is the issue I reported to AutoBert.

I didn't investigate on this, but If I use _SQLite_Display2DResult() or _ArrayDisplay, it works correctly.

Today, I spent my time studying on how Virtual ListView controls work, but, there were a lot information provided by your code, and not so much experience about that by my side.

So, I had to start from a previous step, or:

Since Virtual ListViews are created capturing WM_NOTIFY Windows Message, I had/have to understand how all Notification Codes and Structs and so on work.

So, I was asking where I can find those information.

I am 22 yo, with a big willingness to learn...

Every suggest is really appreciated.

Thanks again for your help :)

 

Best Regards.

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By UEZ
      This project has been discontinued!
       
      Here a small tool I wrote to update my Sysinternal tools collection without the need to download always the whole package or visiting the site to check for updates. I know that there are several tools available (also some tools written in AutoIt) but here another one for the collection. It was good exercise for me to code it.
       
       
        
       
       
      Some files from cannot be downloaded although they are visible on the web site!
       
      Here the download link of the source code only: AutoIt Sysinternal Tools Synchronizer v0.99.5 build 2020-07-04 beta.7z  (1509 downloads previously)
      -=> Requires AutoIt version 3.3.13.20 or higher / tested on Win8.1 real machine and some VMs: Win7 / Vista / Win10
       
      Compiled exe only: @MediaFire
       
      Just select the Sysinternal Tools folder or create one and press the synchronize button to download the selected items. Click on AutoIt label (near to left upper corner) to open menu.
       
      Special thanks to LarsJ, Melba23 and mesale0077 for their help. 
       
      I've still some ideas to implement which are more gimmick related, so it is not finished yet...
      If you want to add your language please check out #Region Language. Thanks. 
       
      Please report any bug or if you have any suggestions.
       
      The language of the tool tip from each of the executable in the left list view were automatically created using Google translator and weren't checked for correctness.
       
      Br,
      UEZ
    • By dmob
      So I am trying to implement an archive system of sorts for my (SQLite) DB app. I wrote a function to attach a separate (archive) DB and
      sync the columns with main DB. If archive DB file does not exist, create file with _SQLiteOpen then close the file (and thus connection) with SQLite_Close.
      This works as intended, however, after the create operation, all subsequent _SQLite_* functions returned a "Library misuse error".
      After a little digging I found the problem in the _SQLite_Close function: it clears the "last opened database" handle even when there still is a live
      DB connection open. All other functions then "think" there is no DB connection active. I hacked two functions in the UDF for a quick fix:

      In _SQLite_Close: Change
      ... $__g_hDB_SQLite = 0 __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] to:
      $__g_hDB_SQLite = __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] and in Func __SQLite_hDel changed
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) EndFunc ;==>__SQLite_hDel to:
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) Return $ahLists[UBound($ahLists)-1] ; Return last opened db EndIf Return 0 EndFunc ;==>__SQLite_hDel so it preserves last opened DB again.
       
      My archive function now works great
      I'm not sure if this should be classified as a bug, but I believe so...
      Hope this helps someone before
    • By Dan_555
      Hi.
      I'm using the FreeCommander XE file manager here, and i'v written a script, which will create an empty, new file, after choosing an extension out from the Listbox.
       
      The listview code  is not mine, iv found the scripts somewhere on this forum. I have no credits in it, because it was intended to be only for my personal use.
      But now, i have a lot of free time, so i remembered that i haven't posted anything on this forum, yet, so here is my first script:
      It should be compiled with the Autoit v3.3.14.3 .
      The compiled exe needs a folder passed as a parameter, so that it know where to create the new file.
      A Listbox is opened, with a selection of available extensions.
      After selecting and doubleclicking (or using the ok button), the script creates a filename (if specified in the config file) with increased numbers (up to 9999).
      If the filename exist, the counter is checking the next number, until the maximum is reached.
      This script uses an ini file for configuration, called "NewFile.ini" which should be in the same folder, as the compiled exe.
      NewFile.au3
      #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <GuiListView.au3> #include <String.au3> #include <WinAPIFiles.au3> #include <Array.au3> #include <EditConstants.au3> #include <GuiEdit.au3> #include <ScrollBarsConstants.au3> Global $test[0][2], $hFile, $cmd = "", $cmdtmp, $tmptxt, $tmpinidir, $filesetting, $MouseX, $MouseY, $UseMouseX, $UseMouseY, $Edit1 Global $aArray[1] = [] $cmdtmp = StringReplace($cmdlineraw, Chr(34), "") If StringLen($cmdtmp) = 2 Then     If StringRight($cmdtmp, 1) = ":" Then         $cmd = $cmdtmp & "\"     EndIf Else     If StringRight($cmdtmp, 1) <> "\" Or StringRight($cmdtmp, 1) <> "/" Then         $cmd = $cmdtmp & "\"     EndIf EndIf If Not FileExists($cmd) Then                    ;Check if the folder exists, display an error message if not !     DisplayReadMe(1)     Exit EndIf If StringLen(@ScriptDir) > 3 And StringRight(@ScriptDir, 1) <> "\" Then     $tmpinidir = @ScriptDir & "\" Else     $tmpinidir = @ScriptDir EndIf Local Const $sFilePath = $tmpinidir & "NewFile.ini" $filesetting = IniRead($sFilePath, "setting", "filename", "MyNewFile") $filesetting = StringReplace($filesetting, ">", " ") ;msgbox (0,"",$filesetting)  ;Debugging If FileExists($sFilePath) Then     $aArray = IniReadSectionNames($sFilePath) ; Read the INI section names. This will return a 1 dimensional array. EndIf $UseMouseX = IniRead($sFilePath, "setting", "UseMouseX", "1") $UseMouseY = IniRead($sFilePath, "setting", "UseMouseY", "0") ; Check if an error occurred. If Not @error Then     ; Enumerate through the array displaying the section names.     Local $count = 0     If FileExists($sFilePath) Then         For $i = 1 To $aArray[0]             $tmp1 = IniRead($sFilePath, $aArray[$i], "1", "none")             If $tmp1 <> "none" And StringLeft($tmp1, 1) = "." Then            ;Extension need to have a dot, or it will be ignored !                 $tmptxt = $tmptxt & $tmp1 & "|" & $aArray[$i] & @CRLF                 $count = $count + 1                                            ;Count how many extensions are added !             EndIf         Next     EndIf     If $count = 0 Then $tmptxt = ".au3|AutoIt 3" & @CRLF & ".txt|Text File" & @CRLF     ;MsgBox($MB_SYSTEMMODAL,"",$tmptxt)     _ArrayAdd($test, $tmptxt, 0, "|", @CRLF) Else                                                            ;Reading the ini failed, create a default array for the extensions     $tmptxt = ".au3|AutoIt 3" & @CRLF & ".txt|Text" & @CRLF     _ArrayAdd($test, $tmptxt, 0, "|", @CRLF) EndIf ;Local $test[5][2] = [['.au3', 'AutoIt'], ['.ahk', 'Auto Hotkey'], ['.txt', 'text'], ['.sdlbas', 'Sdl Basic'], ['.html', 'Webpage']] If $UseMouseX = 1 Then     $MouseX = MouseGetPos(0) Else     $MouseX = -1 EndIf If $UseMouseY = 1 Then     $MouseY = MouseGetPos(1) Else     $MouseY = -1 EndIf $Form1 = GUICreate("Create New File", 210, 247, $MouseX, $MouseY, $WS_CAPTION, $WS_EX_TOOLWINDOW) $List = GUICtrlCreateListView("", 5, 5, 200, 200) _GUICtrlListView_InsertColumn($List, 0, "Extension", 65) _GUICtrlListView_InsertColumn($List, 1, "Description", 115) _GUICtrlListView_AddArray($List, $test) GUICtrlCreateLabel("Example:" & $filesetting & "0000.ext", 5, 205) $Button1 = GUICtrlCreateButton("Ok", 16, 224, 45, 22) $Button3 = GUICtrlCreateButton("ReadMe", 80, 224, 55, 22) $Button2 = GUICtrlCreateButton("Cancel", 150, 224, 45, 22) $cDummy = GUICtrlCreateDummy() GUISetState(@SW_SHOW) GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY") While 1     $nMsg = GUIGetMsg()     Switch $nMsg         Case $GUI_EVENT_CLOSE, $Button2             Exit         Case $Button3             DisplayReadMe(0)         Case $Button1, $cDummy             Local $tmptxt = StringSplit(_GUICtrlListView_GetItemTextString($List), "|")[1]             If StringLen($tmptxt) > 0 Then                 Local $fnr = 0, $tmpfile = ""                 While $fnr < 10000                     $tmpfile = $cmd & $filesetting & _StringRepeat("0", 4 - StringLen($fnr)) & $fnr & $tmptxt                     ;MsgBox(0,"",$tmpfile) ; for debugging                     If Not (FileExists($tmpfile)) Then                         $hFile = _WinAPI_CreateFile($tmpfile, 0)                         _WinAPI_CloseHandle($hFile)                         Exit                     EndIf                     $fnr = $fnr + 1                 WEnd             EndIf     EndSwitch WEnd ;================================================================================ Func WM_NOTIFY($hWnd, $iMsg, $iwParam, $ilParam)     Local $hWndFrom, $iIDFrom, $iCode, $tNMHDR, $hWndListView     $hWndListView = $List     If Not IsHWnd($List) Then $hWndListView = GUICtrlGetHandle($List)     $tNMHDR = DllStructCreate($tagNMHDR, $ilParam)     $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))     $iCode = DllStructGetData($tNMHDR, "Code")     Switch $hWndFrom         Case $hWndListView             Switch $iCode                 Case $NM_DBLCLK                     ; Fire the dummy if the ListView is double clicked                     GUICtrlSendToDummy($cDummy)             EndSwitch     EndSwitch     Return $GUI_RUNDEFMSG EndFunc   ;==>WM_NOTIFY Func DisplayReadMe($err)     Local $Form1 = GUICreate("Read Me", 550, 400, 10, 10, BitOR($WS_CAPTION, $WS_THICKFRAME ,$WS_MAXIMIZEBOX))     $Edit1 = GUICtrlCreateEdit("", 0, 0, 549, 399)     GUICtrlSetData(-1, "")     GUISetState(@SW_SHOW)     If $err=1 then         local $txttmp01="Error: Path does not exist"         local $txttmp02="Commandline call was: "         AddText ($txttmp01)         Addtext ($txttmp02 & $cmd & @CRLF & @CRLF)     EndIf     AddText("Instructions:" & @CRLF)     AddText("Displays a selection of available extensions and then creates a new file at the Path's location." & @CRLF & @CRLF & "Usage: NewFile Path")     AddText("Example: NewFile c:\myfolder\" & @CRLF)     AddText("Result: A file with a name 'MyFile0000.ext' will be created at the Path's location.")     AddText("If the filename exists, the number counter will increase, until it finds a free number, up to 9999." & @CRLF)     AddText("Uses a configuration file in the .exe folder named NewFile.ini !" & @CRLF)     AddText("Example of a config file:" & @CRLF)     AddText("[setting]" & @CRLF & "filename=MyFile>")     AddText("UseMouseX=0                ;0 or 1 - position the dialog at the mouse x coordinate - usefull for multi monitor settings !")     AddText("UseMouseY=0                ;0 or 1 - Set this and UseMouseX to spawn the dialog at the mouse coordinates !" & @CRLF)     AddText(";Use > in filename as a space char ! (only needed if you want the space char at the beginning or at the end : in between filename and the number)" & @CRLF)     AddText(";Format for this ini file is:" & @CRLF & ";Name              e.g [Auto It]")     AddText(";extension       e.g 1=.au3" & @CRLF & "; p.s. only 1 extension per section ! the number must be 1" & @CRLF)     AddText("[AutoIt3]" & @CRLF & "1=.au3" & @CRLF & @CRLF & "[Text]" & @CRLF & "1=.txt")     _GUICtrlEdit_SetSel($Edit1, 0, 0)     _GUICtrlEdit_Scroll($Edit1, $SB_SCROLLCARET)     If $err=1 Then         _GUICtrlEdit_SetSel($Edit1, 0, StringLen($txttmp01 & $txttmp02 & $cmd)+5)     EndIf     While 1         $nMsg = GUIGetMsg()         Switch $nMsg             Case $GUI_EVENT_CLOSE                 GUIDelete($Form1)                 ExitLoop         EndSwitch     WEnd EndFunc   ;==>DisplayReadMe Func AddText($edittxt)     _GUICtrlEdit_AppendText($Edit1, $edittxt & @CRLF) EndFunc   ;==>AddText  
       NewFile.ini
      [setting] filename=MyNewFile_ UseMouseX=0                ;0 or 1 - position the dialog at the mouse x coordinate - usefull for multi monitor settings ! UseMouseY=0                ;0 or 1 - Set this and UseMouseX to spawn the dialog at the mouse coordinates ! ;Use > in filename as a space char ! (only needed if you want the space char at the beginning or at the end : in between filename and the number) ;Format for this ini file is: ;Name              e.g [Auto It] ;extension       e.g 1=.au3 ; p.s. only 1 extension per section ! the number must be 1 [AutoIt3] 1=.au3 [Auto Hotkey] 1=.ahk [Text] 1=.txt [Sdl Basic] 1=.sdlbas [Webpage] 1=.html [Basic] 1=.bas [Pascal] 1=.pas [Rich Text (RTF)] 1=.rtf [Word Document] 1=.doc [Hex File] 1=.hex
    • By argumentum
      #include <SQLite.au3> ;-- When SQLite is compiled with the JSON1 extensions it provides builtin tools ;-- for manipulating JSON data stored in the database. ;-- This is a gist showing SQLite return query as a JSON object. ;-- https://www.sqlite.org/json1.html Example() Func Example() _SQLite_Startup() ; "<your path>\sqlite3.dll", False, 1) ; https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm _SQLite_Open() ; ...if you can not run this due to errors, get the latest DLL from https://www.sqlite.org/ If _SQLite_Exec(-1, "CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, full_name TEXT NOT NULL, email TEXT NOT NULL, created DATE NOT NULL );") Then Return 4 If _SQLite_Exec(-1, 'INSERT INTO users VALUES ' & _ '(1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),' & _ '(2, "Angus O''Vader","angus.o@destroyers.com", "02-03-04"),' & _ '(3, "Imperator Colin", "c@c.c", "01-01-01");') Then Return 5 ; -- Get query data as a JSON object using the ; -- json_group_object() [1] and json_object() [2] functions. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_object(" & _ " email," & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS json_result" & _ " FROM (SELECT * FROM users WHERE created > ""02-01-01"");") ; {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}} ; -- Get query data as a JSON object using the ; -- json_group_array() function to maintain order. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_array(" & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS my_json_result_OrAnythingReally" & _ " FROM (SELECT * FROM users ORDER BY created);") ; [{"full_name":"Imperator Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}] ;-- Links ;-- [1] https://www.sqlite.org/json1.html#jgroupobject ;-- [2] https://www.sqlite.org/json1.html#jobj ; example found at https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2 EndFunc ;==>Example Func _SQLite_GetTable2d_ArrayToConsole($sSQL, $hDB = -1) Local $aResult, $iRows, $iColumns If _SQLite_GetTable2d($hDB, $sSQL, $aResult, $iRows, $iColumns) Then ConsoleWrite("! SQLite Error: " & _SQLite_ErrCode($hDB) & @CRLF & "! " & _SQLite_ErrMsg($hDB) & @CRLF) Else _SQLite_Display2DResult($aResult) EndIf ConsoleWrite(@CRLF) EndFunc ;==>_SQLite_GetTable2d_ArrayToConsole Based on this example, you can build your own query. 
      The code has all the explanations.
      Enjoy  
    • By argumentum
      #include <SQLite.au3> ;~ #include <SQLite.dll.au3> Local $hQuery, $aRow, $aNames _SQLite_Startup() ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) _SQLite_Open() ; open :memory: Database _SQLite_Exec(-1, "CREATE TABLE aTest (A,B int not null unique ,C text);") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") _SQLite_Query(-1, "SELECT _ROWID_,* FROM aTest ORDER BY a;", $hQuery) _SQLite_FetchTypes($hQuery, $aNames) ; Read out Column Types ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) _SQLite_FetchNames($hQuery, $aNames) ; Read out Column Names ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CRLF) WEnd _SQLite_Exec(-1, "DROP TABLE aTest;") _SQLite_Close() _SQLite_Shutdown() ; Output: ; INTEGER int text ; rowid A B C ; 3 a 1 Hello ; 2 b 3 ; 1 c 2 World Func _SQLite_FetchTypes($hQuery, ByRef $aTypes) Dim $aTypes[1] If __SQLite_hChk($hQuery, 3, False) Then Return SetError(@error, 0, $SQLITE_MISUSE) Local $avDataCnt = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_count", "ptr", $hQuery) If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error If $avDataCnt[0] <= 0 Then Return SetError(-1, 0, $SQLITE_DONE) ReDim $aTypes[$avDataCnt[0]] Local $avColName For $iCnt = 0 To $avDataCnt[0] - 1 $avColName = DllCall($__g_hDll_SQLite, "wstr:cdecl", "sqlite3_column_decltype16", "ptr", $hQuery, "int", $iCnt) If @error Then Return SetError(2, @error, $SQLITE_MISUSE) ; DllCall error $aTypes[$iCnt] = $avColName[0] Next Return $SQLITE_OK EndFunc ;==>_SQLite_FetchTypes  If you wanna build a proper JSON string, you may want to know if is {"int":123} or {"text":"123"}
      and for that, this can help, obviously only when declared in the SQLite table.
×
×
  • Create New...