Jump to content

[solved] sqlite rowid to combobox


Recommended Posts

Hi all

I am comfortable with SQL and doing okay with Lists, ListViews and Comboboxes.  What I need to do is this: READ row from SQL, place SOME of this row into a combo list, MODIFY data in the Combo, READ the Combo and WRITE back to SQL as an update.

So, I want to modify existing SQL data in a combo box and then update the SQL to reflect the combo changes.

My specific problem, is that I do not know how to handle the rowid in the combo environment.  I do not want to show it to the user.

I have another project where the ID is in fact displayed on screen in the combo, so there it is easy, read the value and run an update.  Now, I need to hide the ID from the user, but not lose it.

Help? Suggestions? Examples?  I have been searching for some time now, but can't find any related posts.

Thanks for reading

 

Edited by Skysnake

Skysnake

Why is the snake in the sky?

Link to post
Share on other sites

Skysnake,

Is this anything at all like what you are looking for?

#include <GUIConstantsEx.au3>
#include <array.au3>
#include <sqlite.au3>
#include <GuiComboBox.au3>

#AutoIt3Wrapper_Add_Constants=n

_SQLite_Startup()
_SQLite_Open()

Local $sql, $aRows, $iCols, $iRows, $LastSelected
$sql = 'create table T1 (Fname, MI, Lname);'
$sql &= 'insert into T1 values("Thomas","R","Jones");'
$sql &= 'insert into T1 values("William","X","Smith");'
$sql &= 'insert into T1 values("James","T","Kirk");'
$sql &= 'insert into T1 values("Rastus","P","Badass");'
_SQLite_Exec(-1, $sql)

Local $gui010 = GUICreate('SQLite _rowid_ Example')
GUICtrlCreateLabel('Middle Initial', 140, 15, 100, 20)
Local $cMI = GUICtrlCreateCombo('', 140, 30, 100, 50)
Local $dummyenter = GUICtrlCreateDummy()
GUICtrlCreateLabel('Hit [ENTER] to update DB entry', 110, 70, 200, 20)

_pop_combo()

GUISetState()

Local $aAcKeys[1][2] = [["{Enter}", $dummyenter]]
GUISetAccelerators($aAcKeys)

While 1
    Switch GUIGetMsg()
        Case $gui_event_close
            Exit
        Case $cMI
            $LastSelected = _GUICtrlComboBox_GetCurSel($cMI)
        Case $dummyenter
            If $LastSelected = '' Then ContinueLoop
            ConsoleWrite($aRows[$LastSelected][0] & @CRLF)
            _SQLite_Exec(-1, 'update T1 set MI = ' & _SQLite_FastEscape(GUICtrlRead($cMI)) & ' where _rowid_ = ' & $aRows[$LastSelected+1][0] & ';')
            _pop_combo()
    EndSwitch
WEnd

Func _pop_combo()

    _SQLite_GetTable2d(-1, 'select _rowid_, MI from T1', $aRows, $iRows, $iCols)

    _arraydisplay($aRows)

    Local $str = '|'

    For $i = 1 To UBound($aRows) - 1
        $str &= $aRows[$i][1] & '|'
    Next
    GUICtrlSetData($cMI, $str, $aRows[1][1])

    $LastSelected = ''

EndFunc   ;==>_pop_combo

kylomas 

Edit: Original posting deleted, corrected version posted (_rowid_ used incorrectly).

 

Edited by kylomas
Corrected code

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Link to post
Share on other sites

a slight modification. 

#include <GUIConstantsEx.au3>
#include <array.au3>
#include <sqlite.au3>
#include <GuiComboBox.au3>

#AutoIt3Wrapper_Add_Constants=n

_SQLite_Startup()
_SQLite_Open("sql2combo.db3")

Local $sql, $aRows, $iCols, $iRows, $LastSelected
$sql = 'create table T1 (Fname, MI, Lname);'
$sql &= 'insert into T1 values("Thomas","R","Jones");'
$sql &= 'insert into T1 values("William","X","Smith");'
$sql &= 'insert into T1 values("James","T","Kirk");'
$sql &= 'insert into T1 values("Rastus","P","Badass");'
_SQLite_Exec(-1, $sql)

Local $gui010 = GUICreate('SQLite _rowid_ Example')
GUICtrlCreateLabel('Middle Initial', 140, 15, 100, 20)
Local $cMI = GUICtrlCreateCombo('', 140, 30, 100, 50)
Local $dummyenter = GUICtrlCreateDummy()
GUICtrlCreateLabel('Hit [ENTER] to update DB entry', 110, 70, 200, 20)

_pop_combo()

GUISetState()

Local $aAcKeys[1][2] = [["{Enter}", $dummyenter]]
GUISetAccelerators($aAcKeys)

While 1
    Switch GUIGetMsg()
        Case $gui_event_close
            Exit
        Case $cMI
            ; add one to combo for SQL rowid
            $LastSelected = _GUICtrlComboBox_GetCurSel($cMI)+1
            ConsoleWrite("$LastSelected: " & $LastSelected & @CRLF)
            ;Local $showSelected=$LastSelected+1
        Case $dummyenter
            If $LastSelected = '' Then ContinueLoop
            ConsoleWrite("SQL Row: " & $aRows[$LastSelected][0] & @CRLF)
            _SQLite_Exec(-1, 'update T1 set MI = ' & _SQLite_FastEscape(GUICtrlRead($cMI)) & ' where _rowid_ = ' & $aRows[$LastSelected][0] & ';')
            _pop_combo()
    EndSwitch
WEnd

Func _pop_combo()

    _SQLite_GetTable2d(-1, 'select _rowid_, MI from T1', $aRows, $iRows, $iCols)

    _arraydisplay($aRows)

    Local $str = '|'

    For $i = 1 To UBound($aRows) - 1
        $str &= $aRows[$i][1] & '|'
    Next
    GUICtrlSetData($cMI, $str, $aRows[1][1])

    $LastSelected = ''

EndFunc   ;==>_pop_combo

 

Edit:

I have added this to the Wiki

https://www.autoitscript.com/wiki/Snippets_(_Combo_)

 

 

 

 

Edited by Skysnake
Added to the Wiki

Skysnake

Why is the snake in the sky?

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By AFrenchCroissant
      Hello,
      i would like to know why isnt anything displayed on the combo box. i used this example and added it to my code
      Autoit Example :
      #include <GuiConstantsEx.au3> #include <WindowsConstants.au3> Dim $sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Fonts" GUICreate("Test", 400, 100) $cCombo_Font = GUICtrlCreateCombo("", 10, 10, 170, 20, 2097474);HELPHERE GUICtrlSetData($cCombo_Font, _RegEnumVal($sRegKey)) $cLabel = GUICtrlCreateLabel("Font preview", 230, 10, 155, 20) GUISetState() While 1 $msg = GUIGetMsg() Switch $msg Case $GUI_EVENT_CLOSE Exit Case $cCombo_Font GUICtrlSetFont($cLabel, 10, Default, Default, GUICtrlRead($cCombo_Font)) EndSwitch WEnd Func _RegEnumVal($sKey) Local $i = 1, $sVal, $sResult While 1 $sVal = RegEnumVal($sKey, $i) If @error Then ExitLoop $i += 1 $sResult &= $sVal & "|" WEnd Return StringRegExpReplace($sResult, " \(.*?\)", "") EndFunc ;==>_RegEnumVal And my code:
      #NoTrayIcon #include <Misc.au3> #include <GuiConstantsEx.au3> #include <WindowsConstants.au3> #include <File.au3> #include <GuiEdit.au3> #include <String.au3> #include <GuiRichEdit.au3> #include <GuiToolbar.au3> #include <GuiImageList.au3> $WIDTH = 500 $HEIGHT = 380 $EXT1 = "|Rich Text File (*.rtf)" $EXT2 = "|ODF Text (*.odt)" $EXT3 = "|All Files(*.*)" $EXT = $EXT1 & $EXT2 $SETTINGS = "Settings.ini" Dim $FONTKEY = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Fonts" Global $FILESAVE, $FILEOPEN = "", $SAVES, $SAVE, $FILEOPENED = 0 Global Enum $e_idNew = 1000, $e_idOpen, $e_idSave, $e_idHelp $MAINGUI = GUICreate("Text Editor", $WIDTH, $HEIGHT, -1, -1, $WS_OVERLAPPEDWINDOW + $WS_VISIBLE) $TOOLBAR = _GUICtrlToolbar_Create($MAINGUI) _GUIImageList_AddIcon($TOOLBAR, @SystemDir & "\shell.dll", -1) $IMAGES = _GUIImageList_Create(24,24) _GUICtrlToolbar_SetImageList($TOOLBAR, $IMAGES) _GUICtrlToolbar_AddButton($TOOLBAR, $e_idNew, -1);AndHereIfYouCouldTHX _GUICtrlToolbar_AddButton($TOOLBAR, $e_idNew, -2) _GUICtrlToolbar_AddButton($TOOLBAR, $e_idNew, 3) _GUICtrlToolbar_AddButton($TOOLBAR, $e_idNew, 4) _GUICtrlToolbar_AddButtonSep($TOOLBAR) _GUICtrlToolbar_AddButton($TOOLBAR, $e_idNew, 5) _GUICtrlToolbar_AddButton($TOOLBAR, $e_idNew, 6) $EDITBOX = _GUICtrlRichEdit_Create($MAINGUI, "This is a test.", 10, 35, $WIDTH - 20, $HEIGHT - 70, _ BitOR($ES_MULTILINE, $WS_VSCROLL, $ES_AUTOVSCROLL)) $FONTCOMBOBOX = GUICtrlCreateCombo("", $WIDTH - 170, 10, 160, 20, 2097474);HELPMEHERE GUICtrlSetData($FONTCOMBOBOX, _RegEnumVal($FONTKEY));ALSOHERE $FONTSIZECOMBOBOX = GUICtrlCreateCombo("", $WIDTH - 200, 10, 20, 20, 2097474);FuncForPreviousLineAtTheEnd $FILE = GUICtrlCreateMenu("File") $NEW = GUICtrlCreateMenuItem("New", $FILE) $OPEN = GUICtrlCreateMenuItem("Open...", $FILE) $SAVE = GUICtrlCreateMenuItem("Save", $FILE) $SAVEAS = GUICtrlCreateMenuItem("Save As...", $FILE) $PRINT = GUICtrlCreateMenuItem("Print...", $FILE) GUICtrlCreateMenuItem("", $FILE) $EXIT = GUICtrlCreateMenuItem("Exit", $FILE) $EDIT = GUICtrlCreateMenu("Edit") $UNDO = GUICtrlCreateMenuItem("Undo", $EDIT) $REDO = GUICtrlCreateMenuItem("Redo", $EDIT) $SEPARATOR = GUICtrlCreateMenuItem("", $EDIT) $FONT = GUICtrlCreateMenuItem("Font test", $EDIT) $SEPARATOR1 = GUICtrlCreateMenuItem("", $EDIT) $CUT = GUICtrlCreateMenuItem("Cut", $EDIT) $COPY = GUICtrlCreateMenuItem("Copy", $EDIT) $PASTE = GUICtrlCreateMenuItem("Paste", $EDIT) GUISetState(@SW_SHOW & $GUI_ACCEPTFILES) While 1 $Msg = GUIGetMsg() Select Case $Msg = $GUI_EVENT_CLOSE If _GUICtrlEdit_CanUndo($EDITBOX) Then $SAVECHANGES = MsgBox(35, "Text Editor", "Your original file has been modified." & @CRLF & "Would you like to save changes to it?") If $SAVECHANGES = 6 And $FILEOPENED = 0 Then SaveAs() _GUICtrlRichEdit_Destroy($hRichEdit) Exit EndIf If $SAVECHANGES = 6 And $FILEOPENED = 1 Then FileDelete($FILEOPEN) FileWrite($FILEOPEN, GUICtrlRead($EDITBOX)) Exit EndIf If $SAVECHANGES = 7 Then ExitLoop Exit EndIf EndIf If Not _GUICtrlEdit_CanUndo($EDITBOX) Then Exit Case $Msg = $OPEN $FILEOPEN = FileOpenDialog("Open", $FILEOPEN, $EXT) $TEXT = _GUICtrlRichEdit_StreamFromFile($EDITBOX, $FILEOPEN) _GUICtrlRichEdit_ReplaceText($EDITBOX, $TEXT) $FILEOPENED = 1 Case $Msg = $NEW If _GUICtrlEdit_CanUndo($EDITBOX) Then $SAVECHANGES = MsgBox(35, "Text Editor", "Your original file has been modified." & @CRLF & "Would you like to save changes to it?") If $SAVECHANGES = 6 And $FILEOPENED = 0 Then SaveAs() EndIf If $SAVECHANGES = 6 And $FILEOPENED = 1 Then FileDelete($FILEOPEN) FileWrite($FILEOPEN, GUICtrlRead($EDITBOX)) EndIf If $SAVECHANGES = 7 Then GUICtrlSetData($EDITBOX, "") If $SAVECHANGES = 2 Then Sleep(1) EndIf If Not _GUICtrlEdit_CanUndo($EDITBOX) Then GUICtrlSetData($EDITBOX, "") $FILEOPENED = 0 Case $Msg = $EXIT If _GUICtrlEdit_CanUndo($EDITBOX) Then $SAVECHANGES = MsgBox(35, "Text Editor", "Your original file has been modified." & @CRLF & "Would you like to save changes to it?") If $SAVECHANGES = 6 And $FILEOPENED = 0 Then SaveAs() _GUICtrlRichEdit_Destroy($hRichEdit) Exit EndIf If $SAVECHANGES = 6 And $FILEOPENED = 1 Then FileDelete($FILEOPEN) FileWrite($FILEOPEN, GUICtrlRead($EDITBOX)) Exit EndIf If $SAVECHANGES = 7 Then ExitLoop If $SAVECHANGES = 2 Then Sleep(1) EndIf EndIf If Not _GUICtrlEdit_CanUndo($EDITBOX) Then Exit Case $Msg = $SAVEAS GUICtrlSetState($EDITBOX, $GUI_DISABLE) SaveAs() $FILEOPENED = 1 GUICtrlSetState($EDITBOX, $GUI_ENABLE) Case $Msg = $SAVE And $FILEOPENED = 1 FileDelete($FILEOPEN) FileWrite($FILEOPEN, GUICtrlRead($EDITBOX)) _GUICtrlEdit_EmptyUndoBuffer($EDITBOX) Case $Msg = $SAVE And $FILEOPENED = 0 GUICtrlSetState($EDITBOX, $GUI_DISABLE) SaveAs() $FILEOPENED = 1 GUICtrlSetState($EDITBOX, $GUI_ENABLE) Case $Msg = $SAVE And $FILEOPENED = 0 GUICtrlSetState($EDITBOX, $GUI_DISABLE) SaveAs() $FILEOPENED = 1 GUICtrlSetState($EDITBOX, $GUI_ENABLE) Case $Msg = $PRINT $PRINTMSGBOX = MsgBox(35, "Text Editor", "Are you sure you want to print this page?") Select Case $PRINTMSGBOX = 6 If FileExists("Print.txt") Then FileDelete("Print.txt") EndIf FileWrite("Print.txt", GUICtrlRead($EDITBOX)) $PRINTFILE = "Print.txt" _FilePrint($PRINTFILE) FileDelete($PRINTFILE) EndSelect Case $Msg = $UNDO _GUICtrlRichEdit_Undo($EDITBOX) Case $Msg = $REDO _GUICtrlRichEdit_Redo($EDITBOX) Case _GUICtrlEdit_CanUndo($EDITBOX) And $SAVES = 1 GUICtrlSetState($SAVE, $GUI_ENABLE) Case $Msg = $CUT _GUICtrlRichEdit_Cut ($EDITBOX) Case $Msg = $COPY _GUICtrlRichEdit_Copy ($EDITBOX) Case $Msg = $PASTE _GUICtrlRichEdit_Paste ($EDITBOX) Case Not _GUICtrlEdit_CanUndo($EDITBOX) And $SAVES = 0 GUICtrlSetState($SAVE, $GUI_DISABLE) $SAVES = 1 Case $Msg = $FONT _GUICtrlRichEdit_SetFont($EDITBOX, $FONTSIZECOMBOBOX, "Times New Roman") Case $FONTCOMBOBOX _GUICtrlRichEdit_SetFont($EDITBOX, $FONTSIZECOMBOBOX, GUICtrlRead($FONTCOMBOBOX)) EndSelect WEnd Exit Func SaveAs() $FILESAVEAS = FileSaveDialog("Save As", "", "Text Document (*.txt)|All Files (*.*)") If Not @error Then $STRING = StringSplit($FILESAVEAS, ".") If $STRING[0] = 1 Then FileDelete($FILESAVEAS) _GUICtrlRichEdit_StreamToFile($EDITBOX, $FILESAVEAS &".rtf") Else _GUICtrlRichEdit_StreamToFile($EDITBOX, $FILESAVEAS &".rtf") EndIf EndIf EndFunc Func _RegEnumVal($sKey) Local $i = 1, $sVal, $sResult While 1 $sVal = RegEnumVal($sKey, $i) If @error Then ExitLoop $i += 1 $sResult &= $sVal & "|" WEnd Return StringRegExpReplace($sResult, " \(.*?\)", "") EndFunc ;==>_RegEnumVal So the problem is that
      The second combo box is blank Icons in taskbar are not appearing (don't really care for now, .dll file for icon in attachement) problem with $FONTCOMBOBOX = GUICtrlCreateCombo("", 10, 10, 200, 20, Bitor($CBS_SORT,$CBS_DROPDOWN,$CBS_AUTOHSCROLL,$WS_VSCROLL) (BitOR gave me a weird error)
      Thanks !
      Autoit newbie
      ico.dll
    • By diepfeile
      I'm using the following:
      Autoit 3.3.14.5
      newly installed Beta 3.3.15.5
      SQlite version 3380000 aka 3.38.0
      I put sqlite3.dll and sqlite3_x64.dll in C:\Windows\System32 since many scripts depend on them.


      I extended the output of _SQLite_Startup()
      with:
      ConsoleWrite("@AutoItX64 " & @AutoItX64 & @CRLF) ConsoleWrite("$sDll_Filename " & $sDll_Filename & @CRLF) ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

      Also using the script from https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm for testing.

       
      >Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3.exe "R:\Download\aasdf.au3" @AutoItX64 0 $sDll_Filename sqlite3.dll _SQLite_LibVersion=0 >Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "R:\Download\aasdf.au3" @AutoItX64 1 $sDll_Filename sqlite3_x64.dll _SQLite_LibVersion=3.38.0 >Running:(3.3.15.5):C:\Program Files (x86)\AutoIt3\Beta\autoit3.exe "R:\Download\aasdf.au3" @AutoItX64 0 $sDll_Filename sqlite3.dll _SQLite_LibVersion=0 >Running:(3.3.15.5):C:\Program Files (x86)\AutoIt3\Beta\autoit3_x64.exe "R:\Download\aasdf.au3" @AutoItX64 1 $sDll_Filename sqlite3_x64.dll _SQLite_LibVersion=3.38.0


      Why doesn't it work in 32bit, despite me having the 32bit sqlite.dll? Autoit urges running scripts in 32bit mode and Scite starts scripts just in 32bit mode without the flag?
      With #AutoIt3Wrapper_UseX64=Y it just works, both normal Autoit and beta!
      sqlite3.dll sqlite3_x64.dll
    • By t0nZ
      I was searching for a way to highlight zones (regions, provinces, counties, etc) on a map, and I don't need  super precise maps so I wrote this script, based on picking up black and white maps (2 colors BW .png or .gif tested) and filling them with colors, writing down a sqlite database to associate zones with names (and other data as well), and reuse the map and the DB to display data, in my example reading a simple .txt file.
      It's all based on this thread  and this other thread.

      So I have two modes:
      The Map "creation mode" : you provide a map image and you start to pick up colors, set "upper level" region/state, and by clicking on a region you fill it and you name it, and all the data are saved on a sqlite DB (auto-created) when you have the map image and a DB with the correct associations, you can switch the "mode" to "show" (as by .ini file) and the script tries to read a "datafile" showing the zone names listed in datafile. The code:
      #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=Icone\mapFlooder.ico #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** ;MAP Flooder ;(C) NSC 2021 #include <GUIConstants.au3> #include <_GOLLOG.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <Misc.au3> #include <GDIPlus.au3> Opt("mousecoordmode", 2) Global $prgname = "MAP Flooder", $ver = "V.0.7", $Buttoncolor = "0xFF00FF", $MPini = @ScriptDir & "\MapFlooder.ini", $btest Global $dbfullpath, $dbtable, $dbFields, $mapfile, $FloodMode, $datafile Global $HDC, $hBrush, $hGraphics, $obj_orig Global $Pic1, $gui, $width, $height, $bColor, $realtimeCoords, $lastclickcoords, $inputSup, $zonecountNum,$labeltest #Region program Gollog(">>>>>> Start MAP Flooder " & $ver) ctrlini() Gui() SQLiteDBcreate() If $FloodMode = "createdb" Then Gollog("CreateDB Mode") DBFlooder() Else Gollog("Show MAP mode") MapShow("show") EndIf Close() #EndRegion program #Region funcS Func Gui() _GDIPlus_Startup() $Pic1 = _GDIPlus_BitmapCreateFromFile($mapfile) $width = _GDIPlus_ImageGetWidth($Pic1) $height = _GDIPlus_ImageGetHeight($Pic1) If $FloodMode = "createdb" Then $gui = GUICreate($prgname & " " & $ver, $width + 150, $height) $labelLoadedMap = GUICtrlCreateLabel("Loaded Map", $width + 10, 5) $labelLoadedMap2 = GUICtrlCreateLabel(_FileToFileName($mapfile), $width + 10, 25) $labeldim = GUICtrlCreateLabel("Width*Height", $width + 10, 45) $labeldim2 = GUICtrlCreateLabel($width & " * " & $height, $width + 10, 65) $lastclickcoordslabel = GUICtrlCreateLabel("Last Click Coords", $width + 10, 100) $lastclickcoords = GUICtrlCreateLabel("xx - xx", $width + 10, 120, 180, 20) $realtimeCoordslabel = GUICtrlCreateLabel("Real Time Coords", $width + 10, 140) $realtimeCoords = GUICtrlCreateLabel("Real Time Coords", $width + 10, 160, 80, 20) $SuPzonelabel = GUICtrlCreateLabel("Supzone (region-state)", $width + 10, 200) $inputSup = GUICtrlCreateInput("sup", $width + 10, 220, 80, 20) $bColor = GUICtrlCreateButton($Buttoncolor, $width + 10, 250, 130, 30) GUICtrlSetBkColor($bColor, $Buttoncolor) $zonecountlabel = GUICtrlCreateLabel("Done Zone Count:", $width + 10, 320, 100, 20) $zonecountNum = GUICtrlCreateLabel("x", $width + 10, 340, 100, 20) $btest = GUICtrlCreateButton("TEST MAP", $width + 10, 380, 130, 30) $labeltest = GUICtrlCreateLabel("", $width + 10, 420, 130, 30) Else $gui = GUICreate($prgname & " " & $ver, $width, $height) EndIf GUISetState() $HDC = _WinAPI_GetDC($gui) $hGraphics = _GDIPlus_GraphicsCreateFromHDC($HDC) _GDIPlus_GraphicsDrawImageRect($hGraphics, $Pic1, 0, 0, $width, $height) EndFunc ;==>Gui Func MapShow($showmode) ; reading a simple text file with zone names, searching for names in DB and fill the map using stored coordinates If $showmode = "show" Then Local $aLines = FileReadToArray($datafile) Local $iLineCount = @extended EndIf If $showmode = "test" Then $iLineCount = 1 If @error Then MsgBox(48, "MapFlooder", "There was an error reading the data file. @error: " & @error) ; Gollog("There was an error reading the data file. @error: " & @error) Close() Else Gollog("start filling zones") _SQLite_Startup() _SQLite_Open($dbfullpath) ; open Database with zone definitions Local $hQuery, $aRow For $i = 0 To $iLineCount - 1 If $showmode = "show" Then _SQLite_Query(-1, "SELECT * FROM " & $dbtable & " where zone = '" & $aLines[$i] & "' ORDER BY zone ASC;", $hQuery) ; the query EndIf If $showmode = "test" Then _SQLite_Query(-1, "SELECT * FROM " & $dbtable & " ORDER BY zone ASC;", $hQuery) ; the query EndIf While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", $aRow[2]) ; fill color read from DB $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) DllCall("gdi32.dll", "int", "FloodFill", "int", $HDC, "int", $aRow[3], "int", $aRow[4], "int", 0x000000) If $showmode = "test" Then GUICtrlSetData($labeltest,$aRow[0]) Sleep(200) GUISetState() EndIf WEnd _SQLite_QueryFinalize($hQuery) Next _SQLite_Close() _SQLite_Shutdown() EndIf While 1 $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then ExitLoop WEnd EndFunc ;==>MapShow Func DBFlooder() $zonecount = 0 While 1 $mp = MouseGetPos() GUICtrlSetData($realtimeCoords, $mp[0] & " - " & $mp[1]) $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then ExitLoop If $msg = $bColor Then colorP() If $msg = $btest Then MapShow("Test") If $mp[0] < $width And $mp[1] < $height And _IsPressed("01") And WinActive($gui) Then $mp = MouseGetPos() GUICtrlSetData($lastclickcoords, $mp[0] & " - " & $mp[1]) $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", $Buttoncolor) ; fill color ok $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) DllCall("gdi32.dll", "int", "FloodFill", "int", $HDC, "int", $mp[0], "int", $mp[1], "int", 0x000000) Local $Zone = InputBox("Map Floode", "Zone ?") If $Zone = "" Or @error = 1 Then ; when manage wrong click, possibility to repeat ; set 'temp' color to highlight the 'wrong' click $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", 0x4ccfc6) ; fill color wrong $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) DllCall("gdi32.dll", "int", "FloodFill", "int", $HDC, "int", $mp[0], "int", $mp[1], "int", 0x000000) ; restore color $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", $Buttoncolor) ; fill color ok $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) Else _SQLite_Startup() _SQLite_Open($dbfullpath) ; open Database Local $SupZone = GUICtrlRead($inputSup) Local $data = '"' & $Zone & '","' & $SupZone & '","' & $Buttoncolor & '",' & $mp[0] & "," & $mp[1] _SQLite_Exec(-1, "INSERT INTO " & $dbtable & "(" & $dbFields & ") VALUES (" & $data & ");") If @error = -1 Then GOLLOG("Error insert record") MsgBox(48, "Error", "insert record") EndIf $zonecount += 1 GUICtrlSetData($zonecountNum, $zonecount) _SQLite_Close() _SQLite_Shutdown() EndIf EndIf WEnd EndFunc ;==>DBFlooder Func Close() Gollog("<<<<<<< closing...") _WinAPI_ReleaseDC($gui, $HDC) _GDIPlus_GraphicsDispose($hGraphics) _GDIPlus_Shutdown() Exit EndFunc ;==>Close Func SQLiteDBcreate() ;complete path e filename If Not FileExists($dbfullpath) Then GOLLOG("perform SQLite DB creation") Local $dbfolder = _FileToFilePath($dbfullpath) ;Local $dbfile = _FileToFileName($dbfullpath) If Not FileExists($dbfolder) Then DirCreate($dbfolder) ; =====================>>>>> START SQL DLL _SQLite_Startup() _SQLite_Open($dbfullpath) ; open Database ; creating first table If _SQLite_Exec(-1, "CREATE TABLE " & $dbtable & " (" & $dbFields & ");") = $SQLITE_OK Then GOLLOG("DB table - " & $dbtable & " - creation ok") Else GOLLOG("Error creating DB table : " & @error) EndIf _SQLite_Close() _SQLite_Shutdown() Else Gollog("DB already exist") EndIf EndFunc ;==>SQLiteDBcreate ; #FUNCTION# ==================================================================================================================== ; Name ..........: _FileToFilePath ; Description ...: Returns a folder path from a FQPN (Fully Qualified Path Name) ; Syntax ........: _FileToFilePath($sPath) ; Parameters ....: $sPath - a string value. ; Return values .: Success - String ; Failure - Empty string as returned from StringLeft() ; Author ........: Sam Coates ; =============================================================================================================================== Func _FileToFilePath($sPath) Local $sReturn = StringLeft($sPath, StringInStr($sPath, "\", 0, -1) - 1) Return ($sReturn) EndFunc ;==>_FileToFilePath ; #FUNCTION# ==================================================================================================================== ; Name ..........: _FileToFileName ; Description ...: Returns a filename from a FQPN (Fully Qualified Path Name) ; Syntax ........: _FileToFileName($sPath[, $bIncludeExtension = True]) ; Parameters ....: $sPath - a string value. ; $bIncludeExtension - [optional] a boolean value. Default is True. ; Return values .: Success - String ; Failure - Empty string as returned from StringLeft() ; Author ........: Sam Coates ; =============================================================================================================================== Func _FileToFileName($sPath, $bIncludeExtension = True) Local $sReturn = StringTrimLeft($sPath, StringInStr($sPath, "\", 0, -1)) If $bIncludeExtension = False Then $sReturn = StringLeft($sReturn, StringInStr($sReturn, ".", 0, -1) - 1) Return ($sReturn) EndFunc ;==>_FileToFileName Func colorP() ; modified for BGR color GOLLOG("Color Picker") Local $color = _ChooseColor(2) If $color = -1 Then GOLLOG("no color selected") Else Local $sCr = Hex($color, 6) Local $RGB_Buttoncolor = '0x' & StringMid($sCr, 1, 2) & StringMid($sCr, 3, 2) & StringMid($sCr, 5, 2) GUICtrlSetBkColor($bColor, $RGB_Buttoncolor) ; BGR color $Buttoncolor = '0x' & StringMid($sCr, 5, 2) & StringMid($sCr, 3, 2) & StringMid($sCr, 1, 2) GUICtrlSetData($bColor, $Buttoncolor) GOLLOG("new color " & $Buttoncolor & " selected") EndIf EndFunc ;==>colorP Func ctrlini() ;ini read If FileExists($MPini) Then GOLLOG("found: " & $MPini) $mapfile = IniRead($MPini, "map", "mapfile", "") $datafile = IniRead($MPini, "map", "datafile", "") $dbfullpath = IniRead($MPini, "db", "dbfullpath", "") $dbtable = IniRead($MPini, "db", "dbtable", "") $dbFields = IniRead($MPini, "db", "dbfields", "") $FloodMode = IniRead($MPini, "mode", "mode", "") Else GOLLOG($MPini & " NOT found..") Close() EndIf EndFunc ;==>ctrlini #EndRegion funcS All the needed files plus some example (image maps and DBs)
      Link to all demo files
      To test, copy all in a single folder and adjust the mapflooder.ini, also you can add to you includes the _gollog.au3 (used for log, you can avoid it deleting all Gollog() lines)
       
       
       
       
       
    • By benners
      It seems  I'm always asking SQL questions in December,  must be a tradition. Here is the latest
      I have a database that is queried to return infomation about software installers and users. I can achieve what I want by calling two queries using _SQLite_QuerySingleRow, and adding the second query to the first returned array using _ArrayAdd. What I want to do is try to do it with one query.
      I don't have any code as I try the queries in SQLitexpert before to obtain the results and layout I require. The one below returns the information I want but the info is duplicated for every name that uses the program. I know this is down to my code
      SELECT [main].[platform].[id], [main].[installer].[path], [main].[package].[type], [main].[installer].[switches], [main].[minwinver].[version], [main].[category].[class], [main].[installer].[install_order], [main].[installer].[id] AS [id1], [main].[installer].[display_name], [main].[installer].[display_version], [main].[installer].[display_description], [main].[user].[username] FROM [main].[installer] INNER JOIN [main].[category] ON [main].[category].[id] = [main].[installer].[category_id] INNER JOIN [main].[installer_user] ON [main].[installer].[id] = [main].[installer_user].[installer_id] INNER JOIN [main].[user] ON [main].[user].[id] = [main].[installer_user].[user_id] INNER JOIN [main].[platform] ON [main].[platform].[id] = [main].[installer].[platform_id] INNER JOIN [main].[package] ON [main].[package].[id] = [main].[installer].[package_id] INNER JOIN [main].[minwinver] ON [main].[minwinver].[id] = [main].[installer].[minwinver_id] WHERE [main].[installer].[display_name] = 'SciTE4AutoIt3' AND [main].[installer].[id] = 5; What I have done is split the sql calls. This one gets the software
      SELECT [main].[platform].[id], [main].[installer].[path], [main].[package].[type], [main].[installer].[switches], [main].[minwinver].[version], [main].[category].[class], [main].[installer].[install_order], [main].[installer].[id] AS [id1], [main].[installer].[display_name], [main].[installer].[display_version], [main].[installer].[display_description] FROM [main].[installer] INNER JOIN [main].[category] ON [main].[category].[id] = [main].[installer].[category_id] INNER JOIN [main].[platform] ON [main].[platform].[id] = [main].[installer].[platform_id] INNER JOIN [main].[package] ON [main].[package].[id] = [main].[installer].[package_id] INNER JOIN [main].[minwinver] ON [main].[minwinver].[id] = [main].[installer].[minwinver_id] WHERE [main].[installer].[display_name] = 'SciTE4AutoIt3' AND [main].[installer].[id] = 5; This one gets the users. The users will be added to a combo so it doesn't matter if it's an array, but I would prefer a delimited string return.
      SELECT group_concat([main].[user].[username]) FROM [main].[installer] INNER JOIN [main].[installer_user] ON [main].[installer].[id] = [main].[installer_user].[installer_id] INNER JOIN [main].[user] ON [main].[user].[id] = [main].[installer_user].[user_id] WHERE [main].[installer].[id] = 5 ORDER BY [main].[installer_user].[user_id]; Is it possible to return the result in the second example along with the third in one array using SQLite. I have been googling and trying different things that are obviously incorrect. I have attached the database in question.
      Thanks
      Installers.dbc
    • By t0nZ
      Code to read a Spiceworks Database and export text data files in INI format.
      Focused on exporting data about PC inventory and useful to migrate to another inventory system.
      Tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019)
      It creates one  text file for every single machine.
       
      ;Spiceworks Db Exporter ; ;- NSC - t0nZ 2021 ;code to read a Spiceworks Database and export text data files in INI format. ;focused on exporting data about PC inventory. ;useful to migrate to another inventory system. ;tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019) ;It creates one text files for every single machine. ; --> please adapt paths to your environment. #include <SQLite.au3> #include <String.au3> #include <File.au3> dataINIfromDBspiceworks() #Region spiceworks immport Func dataINIfromDBspiceworks() ConsoleWrite("start import from db Spiceworks" & @CRLF) Local $dbspicepath = "C:\scambio" Local $dbspice = "spiceworks_prod.db" ; =====================>>>>> START SQL DLL _SQLite_Startup() ;======================<<<<<<<<<<<<<<<<<<< _SQLite_Open($dbspicepath & '\' & $dbspice) ;- Local $aCPdata Local $hQuery Local $recordcount = 0 Local $salvarec = 0 ;------------------------------------------------ Local $spiceQuery = "SELECT serial_number," & _ "server_name," & _ "manufacturer," & _ "(model || '-' || raw_model)," & _ "processor_type," & _ "raw_processor_type," & _ "processor_architecture," & _ "raw_processor_type," & _ "number_of_processors," & _ "'speed'," & _ "CAST (memory AS FLOAT) / 1073741824," & _ "current_user," & _ "domain," & _ "network_adapters.dns_domain," & _ "'logon'," & _ "operating_system," & _ "os_architecture," & _ "version," & _ "'lang'," & _ "('C:;Fixed;' || (CAST (disks.size AS FLOAT) / 1048576) || ';' || (CAST (disks.free_space AS FLOAT) / 1048576) )," & _ "devices.ip_address," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "network_adapters.gateway," & _ "(network_adapters.description ||' - '|| network_adapters.name)," & _ "devices.mac_address," & _ "devices.updated_on," & _ "user_tag " & _ "FROM devices " & _ "inner JOIN " & _ "network_adapters ON network_adapters.computer_id = devices.id " & _ "inner JOIN " & _ "disks ON disks.computer_id = devices.id " & _ "WHERE disks.name = 'C:' " & _ "ORDER BY devices.updated_on DESC;" _SQLite_Query(-1, $spiceQuery, $hQuery) While _SQLite_FetchData($hQuery, $aCPdata) = $SQLITE_OK writeINI($aCPdata) $recordcount += 1 If $recordcount = $salvarec + 10 Then $salvarec = $recordcount ConsoleWrite($recordcount & " processed records " & @CRLF) EndIf WEnd ;________________________________________________ _SQLite_Close() _SQLite_Shutdown() EndFunc ;==>dataINIfromDBspiceworks Func writeINI($aCPdata) Local $folderdataINI = "c:\scambio\ini" If Not FileExists($folderdataINI) Then DirCreate($folderdataINI) $aCPdata[28] = StringRegExpReplace($aCPdata[28], "[\D]", "") ; this "2021-04-17 02:34:16" to that "20210417023416 Local $cpini = $folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini" If FileExists($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") Then FileDelete($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") ; deleted previous files ! ConsoleWrite("deleted: " & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini" & @CRLF) EndIf ;Section Unique :serial, computer name Local $aSectionUnique[2][2] = [["serial", $aCPdata[0]], ["computername", $aCPdata[1]]] IniWriteSection($cpini, "UNIQUE", $aSectionUnique, 0) ;Section Machine :manufacturer,model,cpuname,cpuid,cpuarc,cpuvendor,cpucores,cpuspeed,RAM,disk1capacity,disk1used Local $aSectionMachine[9][2] = [["manufacturer", $aCPdata[2]], ["model", $aCPdata[3]], ["cpuname", $aCPdata[4]], ["cpuid", $aCPdata[5]], ["cpuarc", $aCPdata[6]], ["cpuvendor", $aCPdata[7]], ["cpucores", $aCPdata[8]], ["cpuspeed", $aCPdata[9]], ["ram", $aCPdata[10]]] IniWriteSection($cpini, "MACHINE", $aSectionMachine, 0) ;Section User : username,domain,DNSdomain,logonServer Local $aSectionUser[4][2] = [["username", $aCPdata[11]], ["domain", $aCPdata[12]], ["DNSdomain", $aCPdata[13]], ["logonServer", $aCPdata[14]]] IniWriteSection($cpini, "USER", $aSectionUser, 0) ;Section OS : OSver, OSarch, OSbuild, OSlang Local $aSectionOS[4][2] = [["OSver", $aCPdata[15]], ["OSarch", $aCPdata[16]], ["OSbuild", $aCPdata[17]], ["OSlang", $aCPdata[18]]] IniWriteSection($cpini, "OS", $aSectionOS, 0) ;Section Disks: username,domain,DNSdomain,logonServer Local $aSectionDrives[1][2] = [["drives", $aCPdata[19]]] IniWriteSection($cpini, "DRIVES", $aSectionDrives, 0) ;Section network:localIP1,localIP2,localIP3,localIP4,publicIP,GW,adapter,mac Local $aSectionNetwork[8][2] = [["localIP1", $aCPdata[20]], ["localIP2", $aCPdata[21]], ["localIP3", $aCPdata[22]], ["localIP4", $aCPdata[23]], ["publicIP", $aCPdata[24]], ["GW", $aCPdata[25]], ["adapter", $aCPdata[26]], ["mac", $aCPdata[27]]] IniWriteSection($cpini, "NETWORK", $aSectionNetwork, 0) ;Section PLUS :date, groupid Local $aSectionPlus[2][2] = [["date", $aCPdata[28]], ["groupid", $aCPdata[29]]] IniWriteSection($cpini, "PLUS", $aSectionPlus, 0) EndFunc ;==>writeINI #EndRegion spiceworks import  
      Spiceworks on premise, now pretty abadonware, has a non crypted SQLite DB usually located in:
      c:\Program Files (x86)\Spiceworks\db\spiceworks_prod.db The query was the difficult part (at least for me), and I export in INI format because it was part of my effort to migrate from Spiceworks to a custom made system (Computer Plucker see this post) where I already parse .INI files in a custom MySQL and/or SQLite DB.
       
×
×
  • Create New...