Search the Community

Since foreign keys are supported by default by pretty much all important relational database engines it would be nice to have it by default in SQLite as well. SQLite introduced support for foreign key constraints since SQLite v.3.6.19 but you have to manually enable foreign keys support at runtime. I know it's just a single line of code but should we consider to add this by default when _SQLite_Open() it's called? It might be a parameter as such: Func _SQLite_Open($sDatabase_Filename = Default, $iAccessMode = Default, $iEncoding = Default, $bFKEnable = True) If Not $__g_hDll_SQLite Then Return SetError(3, $SQLITE_MISUSE, 0) If $sDatabase_Filename = Default Or Not IsString($sDatabase_Filename) Then $sDatabase_Filename = ":memory:" Local $tFilename = __SQLite_StringToUtf8Struct($sDatabase_Filename) If @error Then Return SetError(2, @error, 0) If $iAccessMode = Default Then $iAccessMode = BitOR($SQLITE_OPEN_READWRITE, $SQLITE_OPEN_CREATE) Local $bOldBase = FileExists($sDatabase_Filename) ; encoding cannot be changed if base already exists If $iEncoding = Default Then $iEncoding = $SQLITE_ENCODING_UTF8 EndIf Local $avRval = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_open_v2", "struct*", $tFilename, _ ; UTF-8 Database filename "ptr*", 0, _ ; OUT: SQLite db handle "int", $iAccessMode, _ ; database access mode "ptr", 0) If @error Then Return SetError(1, @error, 0) ; DllCall error If $avRval[0] <> $SQLITE_OK Then __SQLite_ReportError($avRval[2], "_SQLite_Open") _SQLite_Close($avRval[2]) Return SetError(-1, $avRval[0], 0) EndIf $__g_hDB_SQLite = $avRval[2] __SQLite_hAdd($__g_ahDBs_SQLite, $avRval[2]) If Not $bOldBase Then Local $aEncoding[3] = ["8", "16", "16be"] _SQLite_Exec($avRval[2], 'PRAGMA encoding="UTF-' & $aEncoding[$iEncoding] & '";') EndIf If $bFKEnable Then _SQLite_Exec($avRval[2], 'PRAGMA foreign_keys = ON;') ; <<< --- added just this line to original _SQLite_Open() Return SetExtended($avRval[0], $avRval[2]) EndFunc ;==>_SQLite_Open If we really want to stick with the previous behavior of _SQLite_Open() then $bFKEnable can be set as False by default. Or we can add to UDF a new function like below to enable/disable foreign keys, but for me it makes more sense to enable the support when connection starts and you can disable support with _SQLite_Exec(). Func _SQLite_FK_Enable($hDB = Default, $bFKEnable = True) If __SQLite_hChk($hDB, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE) If _SQLite_Exec($hDB, 'PRAGMA foreign_keys = ' & ($bFKEnable ? 'ON' : 'OFF') & ';') == $SQLITE_OK Then Return SetError(0, 0, $bFKEnable) Else Return SetError(2, 0, Null) EndIf EndFunc ;==>_SQLite_FK_Enable Thoughts on this?

This is a replication of an old game called Bulldozer created by John 'FlyMan' Hattan (The Code Zone). There is another implementation in AutoHotkey made by Weston Campbell so I made one in AutoIt. In Weston's github repository you can check the game objective and all valid movements and objects. The controls are: UP - Move bulldozer up DOWN - Move bulldozer down RIGHT - Move bulldozer right LEFT - Move bulldozer left R - Restart current level U - Undo last move J - Jump to a specific level PAUSE - Pause the game Currently I added just first 30 40 60 levels but the original game have 180 levels (eventually I will add all of them). Since Weston's code is on github I suppose it's under some kind of creative license so I didn't bother to create other sprites but if someone have time and inspiration to create new sprites, I am open to add new sprites. To do: Maybe some music Hall of fame / Score table Add all original game levels Add more custom levels Provide access to settings (fonts, colors, sprites, hotkeys, etc) In the attachment is a compiled executable and also the source code. #NoTrayIcon #include-once #include <GDIPlus.au3> #include <WinAPI.au3> #include <SQLite.au3> If Not FileExists(@ScriptDir & '\Bulldozer.sqlite') Then MsgBox(0x10, 'Error', 'Database could not be located!', 10) Exit EndIf _SQLite_Startup(@ScriptDir & '\sqlite3.dll') Global $hDB = _SQLite_Open(@ScriptDir & '\Bulldozer.sqlite') Global Const $TileSize = Number(ReadProperty('TileSize', 32)) Global Const $XTiles = Number(ReadProperty('XTiles', 35)) Global Const $YTiles = Number(ReadProperty('YTiles', 25)) If @DesktopWidth < $XTiles * $TileSize Or @DesktopHeight < $YTiles * $TileSize Then MsgBox(0x30, 'Warning', 'For a better experience you need a display with a resolution ' & String($XTiles * $TileSize) & 'x' & String($YTiles * $TileSize) & '.', 10) EndIf Global $X, $Y, $Direction Global $mResources[] Global $mTiles[] Global $aTiles = GetTiles() If IsArray($aTiles) Then For $Index = 1 To UBound($aTiles) - 1 If $aTiles[$Index][1] Then $mTiles[$aTiles[$Index][0]] = $aTiles[$Index][1] Next Else _SQLite_Close() _SQLite_Shutdown() MsgBox(0x10, 'Error', 'Cannot retrieve game tiles from database!', 10) Exit EndIf Global $CurrentLevel = 1 Global $MaxLevel = Number(ReadProperty('MaxLevel', 1)) Global $AutoRestart = Number(ReadProperty('AutoRestart', 0)) Global $Font = ReadProperty('Font', 'Segoe UI') Global $FontSize = Number(ReadProperty('FontSize', 40)) Global $SecFontSize = Number(ReadProperty('SMFontSize', 20)) Global $MessageColor = ReadProperty('MessageColor', 0xFFFFFFFF) Global $SecMessageColor = ReadProperty('SMColor', 0xFF00A000) Global $Start, $PlayTime = 0, $Pause = False, $MsgShow = False Global $LastMove = Null, $PrevLevel = Null, $NumOfMoves = 0 Global $hMain, $aLevel[$YTiles][$XTiles] Global $ClearColor = ReadProperty('ClearColor', '0xFF000000') Global $KeyboardEnabled = False _GDIPlus_Startup() $mResources['Bitmap'] = _GDIPlus_BitmapCreateFromScan0($XTiles * $TileSize, $YTiles * $TileSize) $mResources['Graphics'] = _GDIPlus_ImageGetGraphicsContext($mResources['Bitmap']) _GDIPlus_GraphicsSetCompositingMode($mResources['Graphics'], 0) _GDIPlus_GraphicsSetCompositingQuality($mResources['Graphics'], 2) _GDIPlus_GraphicsSetInterpolationMode($mResources['Graphics'], 2) _GDIPlus_GraphicsSetSmoothingMode($mResources['Graphics'], 2) _GDIPlus_GraphicsSetTextRenderingHint($mResources['Graphics'], 3) For $Index = 1 To UBound($aTiles) - 1 If $aTiles[$Index][2] Then $mResources[$aTiles[$Index][0]] = _GDIPlus_BitmapCreateFromMemory(Unpack($aTiles[$Index][2])) Next $hMain = GUICreate('Bulldozer', $XTiles * $TileSize, $YTiles * $TileSize) $hPic = GUICtrlCreatePic('', 0, 0, $XTiles * $TileSize, $YTiles * $TileSize) GUISetState(@SW_SHOW, $hMain) LoadLevel() DrawLevel() DrawBulldozer($mResources['Bulldozer' & $Direction]) PushToScreen() While True If GUIGetMsg() = -3 Then Quit() If LevelDone() Then NextLevel() If WinActive($hMain) Then If $KeyboardEnabled = False Then KeyboardInput(True) Else If $KeyboardEnabled Then KeyboardInput(False) EndIf Sleep(10) WEnd Func Quit() Local $aKeys = MapKeys($mResources) For $Index = 0 To UBound($aKeys) - 1 $aKeys[$Index] = 'Graphics' ? _GDIPlus_GraphicsDispose($mResources[$aKeys[$Index]]) : _GDIPlus_BitmapDispose($mResources[$aKeys[$Index]]) Next _GDIPlus_Shutdown() _SQLite_Close() _SQLite_Shutdown() Exit EndFunc Func KeyboardInput($Set = True) Local $aKeys = GetKeyboard() If IsArray($aKeys) Then For $Index = 1 To UBound($aKeys) - 1 HotKeySet($aKeys[$Index][0], $Set ? $aKeys[$Index][1] : Null) Next EndIf $KeyboardEnabled = $Set EndFunc Func MoveRight() If $Pause Or $MsgShow Then Return $PrevLevel = $aLevel IsMovable($X + 1, $Y) If $X + 1 < $XTiles And IsMovable($X + 1, $Y) Then If IsRock($X + 1, $Y) And (IsEmpty($X + 2, $Y) Or IsEmptySocket($X + 2, $Y)) Then $aLevel[$Y][$X + 1] = IsSocket($X + 1, $Y) ? $mTiles['Socket'] : $mTiles['None'] $aLevel[$Y][$X + 2] = IsEmpty($X + 2, $Y) ? $mTiles['Rock'] : $mTiles['RockSocket'] $X += 1 ElseIf IsRock($X + 1, $Y) And (Not IsMovable($X + 2, $Y) Or IsRock($X + 2, $Y)) Then $X = $X Else $X += 1 EndIf $NumOfMoves += 1 EndIf $LastMove = 'R' DrawLevel() DrawBulldozer($mResources['BulldozerR']) PushToScreen() EndFunc Func MoveLeft() If $Pause Or $MsgShow Then Return $PrevLevel = $aLevel If $X - 1 > 0 And IsMovable($X - 1, $Y) Then If IsRock($X - 1, $Y) And (IsEmpty($X - 2, $Y) Or IsEmptySocket($X - 2, $Y)) Then $aLevel[$Y][$X - 1] = IsSocket($X - 1, $Y) ? $mTiles['Socket'] : $mTiles['None'] $aLevel[$Y][$X - 2] = IsEmpty($X - 2, $Y) ? $mTiles['Rock'] : $mTiles['RockSocket'] $X -= 1 ElseIf IsRock($X - 1, $Y) And (Not IsMovable($X - 2, $Y) Or IsRock($X - 2, $Y)) Then $X = $X Else $X -= 1 EndIf $NumOfMoves += 1 EndIf $LastMove = 'L' DrawLevel() DrawBulldozer($mResources['BulldozerL']) PushToScreen() EndFunc Func MoveUp() If $Pause Or $MsgShow Then Return $PrevLevel = $aLevel If $Y - 1 > 0 And IsMovable($X, $Y - 1) Then If IsRock($X, $Y - 1) And (IsEmpty($X, $Y - 2) Or IsEmptySocket($X, $Y - 2)) Then $aLevel[$Y - 1][$X] = IsSocket($X, $Y - 1) ? $mTiles['Socket'] : $mTiles['None'] $aLevel[$Y - 2][$X] = IsEmpty($X, $Y - 2) ? $mTiles['Rock'] : $mTiles['RockSocket'] $Y -= 1 ElseIf IsRock($X, $Y - 1) And (Not IsMovable($X, $Y - 2) Or IsRock($X, $Y - 2)) Then $Y = $Y Else $Y -= 1 EndIf $NumOfMoves += 1 EndIf $LastMove = 'U' DrawLevel() DrawBulldozer($mResources['BulldozerU']) PushToScreen() EndFunc Func MoveDown() If $Pause Or $MsgShow Then Return $PrevLevel = $aLevel If $Y + 1 < $YTiles And IsMovable($X, $Y + 1) Then If IsRock($X, $Y + 1) And (IsEmpty($X, $Y + 2) Or IsEmptySocket($X, $Y + 2)) Then $aLevel[$Y + 1][$X] = IsSocket($X, $Y + 1) ? $mTiles['Socket'] : $mTiles['None'] $aLevel[$Y + 2][$X] = IsEmpty($X, $Y + 2) ? $mTiles['Rock'] : $mTiles['RockSocket'] $Y += 1 ElseIf IsRock($X, $Y + 1) And (Not IsMovable($X, $Y + 2) Or IsRock($X, $Y + 2)) Then $Y = $Y Else $Y += 1 EndIf $NumOfMoves += 1 EndIf $LastMove = 'D' DrawLevel() DrawBulldozer($mResources['BulldozerD']) PushToScreen() EndFunc Func IsMovable($CX, $CY) If $CX < 0 Or $CX >= $XTiles Then Return False If $CY < 0 Or $CY >= $YTiles Then Return False Switch $aLevel[$CY][$CX] Case $mTiles['None'], $mTiles['Rock'], $mTiles['RockSocket'], $mTiles['Socket'] Return True Case Else Return False EndSwitch EndFunc Func IsRock($CX, $CY) If $CX < 0 Or $CX >= $XTiles Then Return False If $CY < 0 Or $CY >= $YTiles Then Return False Switch $aLevel[$CY][$CX] Case $mTiles['Rock'], $mTiles['RockSocket'] Return True Case Else Return False EndSwitch EndFunc Func IsEmpty($CX, $CY) If $CX < 0 Or $CX >= $XTiles Then Return False If $CY < 0 Or $CY >= $YTiles Then Return False Switch $aLevel[$CY][$CX] Case $mTiles['None'] Return True Case Else Return False EndSwitch EndFunc Func IsEmptySocket($CX, $CY) If $CX < 0 Or $CX >= $XTiles Then Return False If $CY < 0 Or $CY >= $YTiles Then Return False Switch $aLevel[$CY][$CX] Case $mTiles['Socket'] Return True Case Else Return False EndSwitch EndFunc Func IsSocket($CX, $CY) If $CX < 0 Or $CX >= $XTiles Then Return False If $CY < 0 Or $CY >= $YTiles Then Return False Switch $aLevel[$CY][$CX] Case $mTiles['RockSocket'], $mTiles['Socket'] Return True Case Else Return False EndSwitch EndFunc Func JumpToLevel() Local $iLevel = InputBox('Jump to level', 'Please type the level that you want to play') If $iLevel And Int($iLevel) > 0 And Int($iLevel) <= $MaxLevel Then $CurrentLevel = $iLevel LoadLevel() DrawLevel() DrawBulldozer($mResources['Bulldozer' & $Direction]) PushToScreen() EndIf EndFunc Func LevelDone() For $j = 0 To $YTiles - 1 For $i = 0 To $XTiles - 1 If $aLevel[$j][$i] = $mTiles['Socket'] Then Return False Next Next Return True EndFunc Func NextLevel() $PlayTime += Int(TimerDiff($Start) / 1000) ShowMessage('Level ' & $CurrentLevel & ' completed.' & @CRLF & ' ', 'Solved in ' & $NumOfMoves & ' moves.' & @CRLF & 'Time: ' & FormatTime($PlayTime), 4000) If $CurrentLevel + 1 > $MaxLevel Then GameEnd() Else $CurrentLevel += 1 LoadLevel() DrawLevel() DrawBulldozer($mResources['Bulldozer' & $Direction]) PushToScreen() EndIf EndFunc Func LoadLevel() Local $Data = SQLite_Query($hDB, 'SELECT Data FROM levels WHERE Level = ' & $CurrentLevel) If @extended Then $Data = BinaryToString(Unpack($Data[1][0])) $Data = StringSplit($Data, @CRLF, 1) For $Line = 1 To $YTiles Local $Row = StringSplit($Data[$Line], '') For $Index = 1 To $Row[0] $aLevel[$Line - 1][$Index - 1] = $Row[$Index] Next Next $X = $Data[26] $Y = $Data[27] $Direction = $Data[28] ShowMessage('Level ' & $CurrentLevel) $NumOfMoves = 0 $PlayTime = 0 $Start = TimerInit() EndIf EndFunc Func RestartLevel() If $Pause Then Return LoadLevel() DrawLevel() DrawBulldozer($mResources['Bulldozer' & $Direction]) PushToScreen() EndFunc Func GameEnd() ShowMessage('Congratulations!' & @CRLF & @CRLF & 'You have finished the game.', Null, 4000) If $AutoRestart Then $CurrentLevel = 1 RestartLevel() Else Quit() EndIf EndFunc Func FormatTime($Sec) If $Sec < 60 Then Return $Sec & ' seconds' Local $Min = Int($Sec / 60) $Sec -= $Min * 60 If $Min > 60 Then Local $Hours = Int($Sec / 60) $Min -= $Hours * 60 Return $Hours & ' hour' & ($Hours > 1 ? 's' : '') & ($Min <> 0 ? ', ' & $Min & ' minute' & ($Min > 1 ? 's' : '') : '') & ($Sec <> 0 ? ', ' & $Sec & ' second' & ($Sec > 1 ? 's' : '') : '') Else Return $Min & ' minute' & ($Min > 1 ? 's' : '') & ($Sec <> 0 ? ', ' & $Sec & ' second' & ($Sec > 1 ? 's' : '') : '') EndIf EndFunc Func ShowMessage($Message, $SecMessage = Null, $iDelay = 1500) $MsgShow = True Local $hFamily = _GDIPlus_FontFamilyCreate($Font) Local $hFont = _GDIPlus_FontCreate($hFamily, $FontSize, 1) Local $tLayout = _GDIPlus_RectFCreate(0, 0, $XTiles * $TileSize, ($SecMessage ? ($YTiles * $TileSize / 2) : ($YTiles * $TileSize))) Local $hBrush = _GDIPlus_BrushCreateSolid($MessageColor) Local $hFormat = _GDIPlus_StringFormatCreate() _GDIPlus_StringFormatSetAlign($hFormat, 1) _GDIPlus_StringFormatSetLineAlign($hFormat, ($SecMessage ? 2 : 1)) _GDIPlus_GraphicsClear($mResources['Graphics'], $ClearColor) _GDIPlus_GraphicsDrawStringEx($mResources['Graphics'], $Message, $hFont, $tLayout, $hFormat, $hBrush) If $SecMessage Then _GDIPlus_StringFormatSetLineAlign($hFormat, 0) Local $hSecFont = _GDIPlus_FontCreate($hFamily, $SecFontSize, 1) Local $tSecLayout = _GDIPlus_RectFCreate(0, ($YTiles * $TileSize / 2) , $XTiles * $TileSize, $YTiles * $TileSize / 2) Local $hSecBrush = _GDIPlus_BrushCreateSolid($SecMessageColor) _GDIPlus_GraphicsDrawStringEx($mResources['Graphics'], $SecMessage, $hSecFont, $tSecLayout, $hFormat, $hSecBrush) EndIf PushToScreen() _GDIPlus_StringFormatDispose($hFormat) _GDIPlus_BrushDispose($hBrush) _GDIPlus_FontDispose($hFont) If $SecMessage Then _GDIPlus_BrushDispose($hSecBrush) _GDIPlus_FontDispose($hSecFont) EndIf _GDIPlus_FontFamilyDispose($hFamily) Local $DelayTimer = TimerInit() Do If GUIGetMsg() = -3 Then Quit() Sleep(10) Until TimerDiff($DelayTimer) >= $iDelay $MsgShow = False EndFunc Func UndoLastMove() If $PrevLevel = Null Then Return If $LastMove = Null Then Return If $Pause Then Return $aLevel = $PrevLevel DrawLevel() Switch $LastMove Case 'R' $X -= 1 DrawBulldozer($mResources['BulldozerR']) Case 'L' $X += 1 DrawBulldozer($mResources['BulldozerL']) Case 'U' $Y += 1 DrawBulldozer($mResources['BulldozerU']) Case 'D' $Y -= 1 DrawBulldozer($mResources['BulldozerD']) EndSwitch PushToScreen() $PrevLevel = Null $NumOfMoves -= 1 EndFunc Func Pause() $Pause = Not $Pause If $Pause Then $PlayTime += Int(TimerDiff($Start) / 1000) ShowMessage('Game is paused.', 'Press {Pause} button to resume your game.', 10) Else DrawLevel() DrawBulldozer($mResources['Bulldozer' & $Direction]) PushToScreen() $Start = TimerInit() EndIf Do Sleep(10) Until $Pause = False EndFunc Func DrawLevel() _GDIPlus_GraphicsClear($mResources['Graphics'], $ClearColor) For $j = 0 To $YTiles - 1 For $i = 0 To $XTiles - 1 Switch $aLevel[$j][$i] Case $mTiles['Wall1'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Wall1'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['Wall2'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Wall2'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['Wall3'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Wall3'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['Wall4'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Wall4'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['Wall5'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Wall5'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['Wall6'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Wall6'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['Wall7'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Wall7'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['Wall8'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Wall8'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['Rock'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Rock'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['RockSocket'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['RockSocket'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) Case $mTiles['Socket'] _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $mResources['Socket'], $i * $TileSize, $j * $TileSize, $TileSize, $TileSize) EndSwitch Next Next EndFunc Func DrawBulldozer($hImage) _GDIPlus_GraphicsDrawImageRect($mResources['Graphics'], $hImage, $X * $TileSize, $Y * $TileSize, $TileSize, $TileSize) EndFunc Func PushToScreen() Local $hHBITMAP = _GDIPlus_BitmapCreateHBITMAPFromBitmap($mResources['Bitmap']) _WinAPI_DeleteObject(GUICtrlSendMsg($hPic, 0x0172, 0, $hHBITMAP)) _WinAPI_DeleteObject($hHBITMAP) EndFunc Func ReadProperty($sProperty, $vFallback = Null) Local $aQuery = SQLite_Query($hDB, "SELECT Value FROM settings WHERE Property = " & _SQLite_FastEscape($sProperty)) If @extended Then Return $aQuery[1][0] Else Return $vFallback EndIf EndFunc Func GetTiles($vFallback = Null) Local $aQuery = SQLite_Query($hDB, 'SELECT Tile, Symbol, Data FROM tiles') If @extended Then Return $aQuery Else Return $vFallback EndIf EndFunc Func GetKeyboard($vFallback = Null) Local $aQuery = SQLite_Query($hDB, 'SELECT Key, Function FROM keyboard') If @extended Then Return $aQuery Else Return $vFallback EndIf EndFunc Func SQLite_Query($hDB, $sQuery) Local $aResult, $iRows, $iColumns _SQLite_GetTable2d($hDB, $sQuery, $aResult, $iRows, $iColumns) If @error Then Return SetError(1, 0, False) Else Return SetError(0, UBound($aResult, 1) - 1, $aResult) EndIf EndFunc Func Unpack($bData) Local $tData = DllStructCreate('byte Data[' & BinaryLen($bData) & ']') Local $bCode = Binary('0x8B7424048B4C2408AC347F8846FF4975F7C20800') Local $iSize = BinaryLen($bCode) Local $tCode = DllStructCreate('byte Code[' & $iSize & ']') DllStructSetData($tCode, 'Code', $bCode) DllStructSetData($tData, 'Data', $bData) DllCallAddress('int', DllStructGetPtr($tCode), 'ptr', DllStructGetPtr($tData), 'int', DllStructGetSize($tData)) Return DllStructGetData($tData, 'Data') EndFunc Have fun! Bulldozer.zip

Just a fun exercise using SQLite: (inspired by this post: (https://www.autoitscript.com/forum/topic/209982-datediff_workdaysonly/) find a past or future "target" date starting from a certain date and counting a number of days, excluding any number of days of the week from the count. formally, a WorkDay function by default only Saturday and Sunday are considered non-working, but you can pass a string with the numbers corresponding to the days of the week to be considered non-working according to this scheme: (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4 =Thursday, 5=Friday, 6=Saturday) for example, to consider Thursday, Saturday and Sunday as non-working, pass the following string as the third parameter to the _WorkDay() function: "4,6,0" To run the script you need to download the "precompiled binaries for Windows" sqlite3.dll (32 or 64 according to your AutoIt version) from this link (https://www.sqlite.org/download.html) and save it in the same folder as this script. (hope there are no bugs. Suggestions for improvement and bug report are welcome). Have fun. #include <SQLite.au3> ; -- Start the SQLite engine ------------------- Global Static $g__sSQliteDll = _SQLite_Startup(".\sqlite3.dll", False, True) Global Static $hDb = _SQLite_Open() ; by default will open a memory database. ; ---------------------------------------------- ; by default only Saturday and Sunday are considered non-working, ; but you can pass a string with the numbers corresponding to the days of the week ; to be considered non-working according to this scheme: ; (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4 =Thursday, 5=Friday, 6=Saturday) ; for example, to consider Thursday, Saturday and Sunday as non-working, ; pass the following string as the third parameter to the _WorkDay() function: "4,6,0" ; MsgBox(64, 'example', "in four working days from today it will be the " & _WorkDay(@YEAR & "-" & @MON & "-" & @MDAY, 4)) MsgBox(64, 'example', "nine working days ago was the " & _WorkDay(@YEAR & "-" & @MON & "-" & @MDAY, -9)) _SQLite_Shutdown() Func _WorkDay($StartDate, $iDays = 0, $sHolidays = "0,6") ; $sHolidays: (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday) If Not $iDays Then Return $StartDate Local Static $asOrder[] = ['DESC', 'ASC'] ; $iDirection: if negative, search backward, if positive, search forward Local $iDirection = ($iDays < 0 ? -1 : 1) ; we generate a week for each requested day, ; this in order to be able to manage even the worst case scenario, ; where only one day per week could be selected (only one working day per week). Local $iWeeks = Abs($iDays) + 1 ; weeks involved Local $iNumDays = (7 * $iWeeks) * $iDirection ; toal number of days to be generated Local $aMyRcordset, $iMyRows, $iMyColumns, $Date1, $Date2, $StartRange, $EndRange ; shift StartDate by +/- 1 day (exclude starting day from the search) _SQLite_GetTable(-1, "SELECT date('" & $StartDate & "','" & $iDirection & " day');", $aMyRcordset, $iMyRows, $iMyColumns) $Date1 = $aMyRcordset[2] ; fetch the data from the array resulting from the previous query ; Generate EndDate (add $iNumDays to start date) _SQLite_GetTable(-1, "SELECT date('" & $Date1 & "','" & $iNumDays & " days');", $aMyRcordset, $iMyRows, $iMyColumns) $Date2 = $aMyRcordset[2] ; fetch the data from the array resulting from the previous query ; adjust Start / End date according to direction If $iDirection < 0 Then $StartRange = $Date2 $EndRange = $Date1 Else $StartRange = $Date1 $EndRange = $Date2 EndIf ; https://www.geekytidbits.com/date-range-table-sqlite/ ; a bit intricate (messed up) query (but maybe it can be simplified) Local $Query = "WITH RECURSIVE " & _ "cnt(x) AS ( " & _ "SELECT julianday('" & $StartRange & "') " & _ "UNION ALL " & _ "SELECT x+1 FROM cnt " & _ "LIMIT ((julianday('" & $EndRange & "') - julianday('" & $StartRange & "'))+1)) " & _ "SELECT date(x) as date FROM cnt where strftime('%w', date) NOT IN (" & _ "WITH RECURSIVE split(value, str) AS ( " & _ "SELECT null, '" & $sHolidays & "' || ',' " & _ ; list of non-working days of the week "UNION ALL " & _ "SELECT " & _ "substr(str, 0, instr(str, ',')), substr(str, instr(str, ',')+1) " & _ "FROM split WHERE str!='') SELECT value FROM split WHERE value IS NOT NULL) " & _ " ORDER BY date " & $asOrder[$iDirection = 1] & ";" _SQLite_GetTable(-1, $Query, $aMyRcordset, $iMyRows, $iMyColumns) ; execute above query ; _ArrayDisplay($aMyRcordset) Return $aMyRcordset[Abs($iDays) + 1] ; return the target date EndFunc ;==>_WorkDay

Topic Closed, please go here SQLite demonstration of native recognition of BLOB object in Listview Following an idea that was raised in a discussion with water and jchd, I have been working during the past week on a ListView SQLite demo that integrates BLOB (Binary large Object) fields. Download link at the bottom of this post This is what I have until now, version sero sero sero … one – to use the words of a well know Spanish biker . It is a fully workable example and I would be happy if you could test it and give me your comments. Pre-requisites: 1. You have to download the SQLite Database here: http://users.telenet.be/GreenCan/AutoIt/GreenCan_SQLite_Database_demo.zip 2. You have to download trancexx’ great GIFAnimation udf here: GIFAnimation or http://code.google.com/p/gif-animation/downloads/list For your comfort, I included the latest version in the above zip file. I decided to use GIFAnimation.au3 for the Image display because it works with several kinds of graphic types (except multipage tiff) including animated GIFs J . GIFAnimation? What’s in a name… 3. SQLite3.dll (also included in the zip package) In this first version, you will see two different icons in the Listview (as opposed to the screenshot where you see all images as icons) 1. Eye Icon: this is an image object, clicking once will open a preview box 2. Database with lightning arrow: any kind of other (non-image) object A BLOB field doesn’t need to be an image, it can store literally everything you can imagine, the database contains several objects that you can try-out. I didn’t include an exe file, but why not? It is possible. Up to you to find out if it is useful. These non-graph objects are not preview-able and will be executed. By the way, all these BLOB examples are inoffensive, for example the autoit code just displays an ‘Hello World’ MsgBox. If you don’t trust it, just comment out the ShellExecute in line 219 and verify the content in the Temp folder… or pass your favorite virus scanner over these files You will notice that the first execute take a bit more time to start, afterwards, everything is blinking fast. Not sure why this slow behavior, maybe it’s only on my PC. Although the example looks good, I am not completely satisfied with the result. The BLOB object is not natively recognized and I think it should, but I don’t know how to do this. Any suggestion is welcome here… I can use a isBinary() to identify the field as a BLOB, but I still don’t know how to recognize the BLOB as an image, or an Excel sheet, or anything else. So this is the reason why I have the TypeOfObject column, that links the object to the physical file. Again, any suggestion is welcome here… Another issue is that I have to export the file to a physical file to be able to display (or use) it. I tried UEZ’s _GDIPlus_BMPFromMemory() function for the bitmaps but it is too slow (not even sure it can handle anything else than BMP. And slowness will increase with very large binary objects. I am wondering what the performance would be with a database containing several hundreds of objects to export. I will come with version 0.0.0.2 within a few days. I just let you test the current version first. Version 2 has, as you can see in the screenshot above, a visible icon representation of the image object. This is how it should be but there are significant disadvantages in the way that I programmed it. But I will explain that when I publish the new example script. SQLite GreenCan_demo BLOB in Listview 0.0.0.1.au3 (Please jump immediately to version 0.0.0.2 if you do not intend to test both examples) Edit: 06 May 2013 New version! Version 0.0.0.2 This enhanced version displays a visible thumbnail of each Database image object in the ListView. The example works with the database file that you have to download or already downloaded with the first version (see Prerequisite 1 higher in this post) SQLite GreenCan_demo BLOB in Listview 0.0.0.2.au3 Edit: 10 May 2013 With this script, you can import your own objects into the GreenCan_demo.db Database (you have to download the database again from the link in Pre-requisite 1, because I modified the structure, the key column is now autoincremental) The script will work for both demo's Import Object in SQLite Database.zip Anyhow, enjoy and please give me feedback. GreenCan

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

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)

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

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.

I am trying to normalise a database to remove duplicate info. I am using SQLite Expert to design the database and test sql queries. The database is going to store information about setup installers, such as paths, installer specific info, users to install for, type and category of installer blah, blah. I have attached the database thus far and the tables function are as follows:- category - stores text describing the general usage the installer comes under, such as Browser, Compression etc. installer - this is the main table that has relationships with the other tables and stores info about the installer file, install order etc. installer_user - a link table. Stores the user or computer to install the program for or on. package - stores the type of installer, NSIS, Inno Nullsoft etc. platform - the OS architecture the installer file is compiled for. postinstall - a list of activities to perform when the main install has finished. postinstall_user - a link table. Stores the users\computers that are allowed to run the post install actions user - a list of computers or usernames. I might separate into two tables, undecided yet. Now there wil be one program that deals with the installation side and another that acts as a front end for editing the database suchs as adding new files, removing old files etc. The idea with the editing side is to be able to delete an installer from the installer table say with the id of 1 and all other pertinent information in the other tables will also be deleted. The same goes for deleting a user. All the fields relating to that user will be removed. I have managed to get that part working for the most part. If I delete either a user or installer, the related info in the installer_user and postinstall tables are removed but since I added the postinstall_user table to link usernames to the postinstall action, this is where I get the foreign key error. If someone can explain why, I am sure it is an obvious reason for someone who knows what they are doing 😄 Cheers Installer - Copy.db

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

#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

#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.

I was thinking but I don't have the experience, so you may have the experience. I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ? The DB is now in MySQL. I wanna do all this chopping and use SQLite. Thanks [solved]

Hi I am trying to set Accelerator keys from an array. I select the KEY and CONTROL from a SQLite table, the Array looks like that generated for the Helpfile, but I can't get the CONTROLS to resolve... I though about Assign & Eval, but not sure if that's a step in the right direction. IsDeclared shows that the $var exists in Local Scope -1. Local $Main = GUICreate("Custom MsgBox", 225, 80) GUICtrlCreateLabel("Please select a button.", 10, 10) Local $idButton_Yes = GUICtrlCreateButton("Yes", 10, 50, 65, 25) Local $idButton_No = GUICtrlCreateButton("No", 80, 50, 65, 25) Local $idButton_Exit = GUICtrlCreateButton("Exit", 150, 50, 65, 25) Local $query, $aResult, $iRows, $iColumns $query = "" ;reset $query = "Select hotkey_key, hotkey_ctrl from mytable where mykeys = 'hotkey' ; " ; ; Query $iRval = _SQLite_GetTable2d($sqliteDb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then Local $sizeofHotkeys = UBound($aResult) - 1 ConsoleWrite("$sizeofHotkeys " & $sizeofHotkeys & @CRLF) If $sizeofHotkeys > 0 Then Local $main__aAccelKeys[$sizeofHotkeys][2] For $i = 0 To $sizeofHotkeys - 1 $j = $i + 1 ; replace friendly text with code -- ! alt + Shift ^ Ctrl # Windows $aResult[$j][0] = StringReplace($aResult[$j][0], "Alt", "!") $aResult[$j][0] = StringReplace($aResult[$j][0], "Shift", "+") $aResult[$j][0] = StringReplace($aResult[$j][0], "Ctrl", "^") $main__aAccelKeys[$i][0] = $aResult[$j][0] ;--- $main__aAccelKeys[$i][1] = $aResult[$j][1] ;--- Next ;~ Row|Col 0|Col 1 ;~ Row 0|F2|$idButton_Yes ;~ Row 1|F3|$idButton_No _DebugArrayDisplay($main__aAccelKeys) Local $rv = GUISetAccelerators($main__aAccelKeys, $Main) GUISetState(@SW_SHOW) ; Display the GUI. Please note that this is a modified Helpfile example. The Helpfile specifies (a) WinHandle and (b) last Gui created. --> the example uses a control not a WinHandle and (b) what happens with ChildGuis? Also, the HelpFile specifies lower case, yet the examples show "{F1}" upper case? Also, is there a way to check the result of the GuiSetAccelerator function? Note, if I add these to lines after the FOR loop, then the F1 works, and the DebugArrayDisplays shows control 4... not it's name... So I am in the right place, but my $vars names do not convert to their control numbers in the GUI Next $main__aAccelKeys[$sizeofHotkeys - 1][0] = "{F1}" ; -- -- use the extra row for the F1 $main__aAccelKeys[$sizeofHotkeys - 1][1] = $ChmHLP ;--- Skysnake

Hi All, Here's a really simple question. I ran the code from the helpfile under: _SQLite_Open Issue is I end up with an error message: SQLite3.dll Can't be Loaded! I placed the *.dll in the include folder, but still nothing. Where must this file be placed.

Hello, Using SQLite, I was trying to select a specific column with having duplicate entries removed by using the DISTINCT function. Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) FROM data_db What the above snippet does is just list only one supervisor but with the total count of unique employee names in the whole database when it should be listing out all supervisor entries from the database and list the total count of employees per each supervisors. If I remove the COUNT function, it does list out all unique entries of supervisor names from the list. Attached is a screenshot of an example database as well. Any help will be much appreciated. Thank you!

Hey, I have a script that uses SQLite. It worked without a hiccup on my W7 system. However, last week I bought a new machine, installed W10 and autoit, and now I'm getting an error during _SQLite_Startup. Can anyone advise on how to find what's wrong? Tried checking $__g_hPrintCallback_SQLite but that returns nothing. Tried downloading the latest version of the dll from the link in the function page and I also replaced the default SQLite.dll.au3 that came with AutoIt with the one that came with the latest SQLite version zip. Relevant parts of my script: #include <SQLite.au3> #include <SQLite.dll.au3> _SQLite_Startup(@ScriptDir & "\Config\sqlite3_x64.dll", False, 1) If @error Then MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded! - " & $__g_hPrintCallback_SQLite & @CRLF & @CRLF & "Exiting application / Zavolej Honzovi") Exit -1 EndIf Thanks S.

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.

I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything. Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc

Hello, I've been searching on how to select the first row in my database which includes a time column based on the latest start_time date for today's date. This is my current code: Local $iCurrentDate = _Now() Local $sqlHandle = _dbOpen($sDbPath) Local $aResult, $iRows, $iCols _SQLite_GetTable2d($sqlHandle, "SELECT start_time FROM " & $srawDb & " ORDER BY datetime(start_time) DESC LIMIT 1", $aResult, $iRows, $iCols) _ArrayDisplay($aResult) _dbClose($sDbpath) What it does is it gets the first row entry in the database however it does not select the latest start_time entry in the database, it always select the first row. I've tried changing DESC to ASC to see if that will do it but the issue still remains. Any thoughts on this? Thanks in advance. *EDIT The date format in the database is by MM/DD/YYYY HH:MM:SS.

In my recent project I'm downloading a bunch of data, so I decided to store it in a SQLite database. NOTE: I'm using sqlite3_x64.dll Everything is working just fine but I'm struggling with getting the Median value. SQLite has an Average function but not a Median one. I googled but all of the provided solutions are way above my pay-grade. After some more searching I found 'extension-functions.c' on the SQLite site where Median is included. After almost an hour of struggling I was able to successfully compile it into a DLL. So I downloaded @jchd's SQLiteExtLoad.au3 as seen here: But I'm getting these errors: "Path\SQLiteExtLoad.au3"(21,40) : warning: $g_hDll_SQLite: possibly used before declaration. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "Path\SQLiteExtLoad.au3"(21,40) : error: $g_hDll_SQLite: undeclared global variable. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ So I tried, copying the function to my file and changing the DLL variable ($g_hDll_SQLite) directly to the DLL location (C:\...\.. .dll), but now this error fires If __SQLite_hChk($hConn, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE) To be honest, I don't know what to use as the $hConn - handle of connection. I would appreciate any help, be it getting the median using SQLite queries or getting the DLL extension loaded using AutoIt. Thanks, S. EDIT: well, I suspect the $hConn variable refers to the return value of the _SQLite_Open function. Well, at least now _SQLite_EnableExtensions doesn't give errors. Now I run into problems with _SQLite_LoadExtension, which gives error -1, and extended 1. Apparently the 1 constant is a generic error where other error do not apply. BTW, anybody knows whether I need to compile the extension DLL "into" x64 when I use a x64 SQLite? That might be the problem... EDIT2: I recompiled the dll and tried it using the SQLite3.exe and it works, so I'm confident the extension DLL has been created correctly

I have been testing AutoIt 3.3.14.3 with SQLite, and the Help File examples. The examples that I have tested are throwing errors or not doing anything. I have sqlite3.dll, sqlite3_x64.dll, and sqlite3.exe in the directories with the testing script. I am copying the examples directly from the help file into a test script for testing with no edits. The _SQLite_GetTable2d example is returning an error ("Library used incorrectly") with each _SQLite_Exec command. The _SQLite_FastEncode example returns an empty dialog box. The _SQLite_Exec example only prints out the SQLite version, and nothing else in the SciTE console. Currently, I'm still searching for what is causing this issue. I'm on Windows 7 Enterprise 64-bit. Is anyone else having this issue? Adam

As the title says, I'm trying to show a thumbnail in a gui, selected by the user, which must be resized to fit the Gui Control and store the image itself in SQLite as a blob. Thanks to some useful examples found on the forum, I managed to load, show and store an image but I'm experiencing some problems in resizing the picture. This is what i made until now: #include <SQLite.au3> #include <SQLite.dll.au3> #include <GUIConstantsEx.au3> #include <GDIPlus.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> Local $hGUI, $hBMP, $hBitmap, $hGraphic Local $hQuery, $item Local $filename Local $x = 100, $y = 100 _SQLite_Startup() _SQLite_Open() ; open Database in Memory If @error Then MsgBox(16, "SQLite Error", "Can't Load Database!") Exit -1 EndIf _SQLite_Exec(-1,"CREATE TABLE IF NOT EXISTS DBTest (IMAGE BLOB);") $hGUI = GUICreate("GUI", 400, 300) $GUIImage = GUICtrlCreatePic("", 10, 10, $x, $y, BitOR($GUI_SS_DEFAULT_PIC,$SS_CENTERIMAGE,$SS_SUNKEN,$WS_BORDER), BitOR($WS_EX_CLIENTEDGE,$WS_EX_STATICEDGE)) GUISetState() GUISetState(@SW_SHOW) Local $filename = FileOpenDialog("Select image",@ScriptDir,"Image (*.jpg;*.bmp)",3) ; I could show the image in the gui here: ; GUICtrlSetImage($GUIImage,$ImageFileName) ; But I want to resize it, store it in a DB, then show the resized image in the GUI ; This is what I am trying to do for resize (taken from an example made by UEZ): _GDIPlus_Startup() Local $hImageFromFile = _GDIPlus_ImageLoadFromFile($filename) Local $Thumbnail = DllCall($ghGDIPDll, "uint", "GdipGetImageThumbnail", "handle", $hImageFromFile, "uint", $x, "uint", $y, "int*", 0, "ptr", 0, "ptr", 0) $Thumbnail = $Thumbnail[4] $object_bitmap = _GDIPlus_BitmapCreateFromHBITMAP($Thumbnail); Create a Bitmap object from a bitmap handle (?) _GDIPlus_Shutdown() ;********** How could I show the resized pic in the GUI? Dunno :( ***** ;preparing resized image for storing in SQLite Local $binary_bitmap = Binary($object_bitmap) Local $encoded_bitmap = _SQLite_FastEncode($binary_bitmap) ;insert the blob If Not _SQLite_Exec(-1, "SELECT IMAGE FROM DBTest") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec(-1, "INSERT INTO DBTest (IMAGE) VALUES (" & $encoded_bitmap & ");") = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) ;Retrieve from database If Not _SQLite_Query(-1, "SELECT * FROM DBTest;", $hQuery) = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_FetchData($hQuery, $item, False, False) = $SQLITE_OK Then MsgBox(16, "SQLite Error", _SQLite_ErrMsg()) Local $retrieve_pic = $item[0] ;NOW *I THINK* I SHOULD HAVE THE RESIZED PICTURE IN $retrieve_pic BUT I CAN'T SHOW IT IN THE GUI..... ; Run the GUI until the dialog is closed While 1 $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then ExitLoop WEnd _SQLite_Close() _SQLite_Shutdown() Exit I'm not very good at GDI coding and quite a newbie with SQLite, too... Any help would be very appreciated!

Greetings, I have SQLite setup within my AutoIT program...I'm trying to accomplish what should be a relatively simple task. I want to be able to return an array of 'table' names for an established database...I believe this might be possible using the '_SQLite_SQLiteExe' command...since it seems to be able to access SQLite schemas...? The ".tables" command is one of them...if I am not mistaken that command returns a list of all table names in the active database. I am attempting the following: #include <SQLite.au3> #include <SQLite.dll.au3> Global $hDb, $sIn, $sOut ... $sIn = ".tables" & @CRLF _SQLite_SQLiteExe($hDb, $sIn, $sOut) if @error == 0 Then ;Show Table (using SQLite3.dll) Else if @error == 2 Then ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF) Else ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF) EndIf ;@error is "2"...OR NOT... EndIf ;@error is "0"...OR NOT... ... The error being thrown is "ERROR: Sqlite3.exe file not found" ... Am I required to have the Sqlite3.exe installed in my directory (i.e. @ScriptsDir)...??? I do not have it in there at present because I did not believe it was necessary with the 'include' calls to "SQLite.au3" and "SQLite.dll.au3"...any advice appreciated. Thanks in advance. Regards

Good evening guys I am working on a little project, in which I have to retrieve 8000+ rows of data from a table, from a SQLite database, to populate a combobox This is what I tried 'til now, but it still takes about 12 seconds to populate the combobox. _SQLite_Exec($objDatabase, "BEGIN TRANSACTION;") If _SQLite_GetTable($objDatabase, "SELECT DISTINCT Comune FROM LISTA_COMUNI;", $arrRisultatoQuery, $intRighe, $intColonne) = $SQLITE_OK Then For $i = 2 To UBound($arrRisultatoQuery) - 1 If $i < UBound($arrRisultatoQuery) - 1 Then GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i] & "|") Else GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i]) EndIf Next _SQLite_Exec($objDatabase, "COMMIT;") Are there any other solution to retrieve 8000+ records from a SQLite database? Thank you very much