Yaerox Posted January 25, 2013 Share Posted January 25, 2013 (edited) Hey guys, maybe some of you noticed that i posted some weeks ago a Topic about 'AutoIt causes memory leak?'. The Bug report got closed and I'm still working on this Script to find the mistake... Now I've rebuilded the Script back without SQL, without Listviews, and without real .bat files. If the memory of this Script now still rise, it has to me my mistake maybe because I declare some $vars multiple times or I dont know, otherway it has to be a problem of AutoIt. Am I right? So what I noticed is, I start around 11.600K RAM and after 18 hours I'm around 11.776K RAM ... Some different Script without SQL, without Listviews, but with real .bat files startet around 11.724K RAM and after 18 hours 13.060K RAM.Thats my Script (w/o SQL, Listview, Batch): expandcollapse popup#include #include #include #include Opt("GUIOnEventMode", 1) Opt("MustDeclareVars", 1) OnAutoItExitRegister("_exit") Global $aQueue[1][3] Global $aTasks[5][2] Global $sScriptstatus Global $hGUI = GUICreate("Aufgabenliste", 250, 390) Global $iCounter_label = 0 Global $hCounter_label = GUICtrlCreateLabel("0", 50, 72, 200, 20) Global $hLoadBtn = GUICtrlCreateButton("Load", 140, 40, 100, 30) Global $hStartBtn = GUICtrlCreateButton("Start", 30, 5, 50, 30) Global $hStopBtn = GUICtrlCreateButton("Stop", 30, 40, 50, 30) GUISetState(@SW_SHOW, $hGUI) GUICtrlSetOnEvent($hLoadBtn, "_load") GUICtrlSetOnEvent($hStartBtn, "_check") GUICtrlSetOnEvent($hStopBtn, "_stop_working") GUISetOnEvent($GUI_EVENT_CLOSE, "_exit", $hGUI) While 1 Sleep(100) WEnd Func _load() $aQueue[0][0] = "0" $aQueue[0][1] = "18/01/2013 08:00:00" $aQueue[0][2] = "Test" EndFunc ;==>_load Func _check() $sScriptstatus = True AdlibRegister("_compare_datetimes", 1000) EndFunc ;==>_check Func _compare_datetimes() Local $aRow, $next_date, $aDate If UBound($aQueue) = 0 Then _stop_working() Return 0 EndIf $aRow = StringSplit($aQueue[0][1], " ") $next_date = $aRow[1] $aRow = StringSplit($aRow[2], ":") $aDate = StringSplit($next_date, "/") If $aDate[3] * 365 + $aDate[2] * 30 + $aDate[1] = @YEAR * 365 + @MON * 30 + @MDAY Then If $aRow[1] * 3600 + $aRow[2] * 60 = @HOUR * 3600 + @MIN * 60 + @SEC Or $aRow[1] * 3600 + $aRow[2] * 60 < @HOUR * 3600 + @MIN * 60 + @SEC Then $aRow = 0 $aDate = 0 _start() EndIf ElseIf $aDate[3] * 365 + $aDate[2] * 30 + $aDate[1] < @YEAR * 365 + @MON * 30 + @MDAY Then $aRow = 0 $aDate = 0 _start() EndIf $iCounter_label = $iCounter_label + 1 GUICtrlSetData($hCounter_label, $iCounter_label) $aRow = 0 $next_date = 0 $aDate = 0 EndFunc ;==>_compare_datetimes Func _start() Local $sError, $sStatus, $ShellExecute Local $aBatch[5][5] = [[1, 1, $aQueue[0][1], "Task 1", ""], _ [2, 2, $aQueue[0][1], "Task 2", ""], _ [3, 3, $aQueue[0][1], "Task 3", ""], _ [4, 4, $aQueue[0][1], "Task 4", ""], _ [5, 5, $aQueue[0][1], "_create_next_date", ""]] ReDim $aTasks[5][2] For $i = 0 To UBound($aBatch) - 1 $aTasks[$i][0] = $aBatch[$i][1] * 10 $aTasks[$i][1] = $aBatch[$i][3] Next For $i = 0 To UBound($aBatch) - 1 $sStatus = " ... started" If $aBatch[$i][4] = "" Then $sError = " " _write_log($sError, $aBatch[$i][4], $sStatus) $sError = "OK" $sStatus = " ... finished" _write_log($sError, $aBatch[$i][4], $sStatus) Else $sError = " " _write_log($sError, $aBatch[$i][4], $sStatus) $ShellExecute = Run($aBatch[$i][4], "c:", @SW_HIDE) If $ShellExecute <> 0 Then $sError = "FEHLER" $sStatus = " Something went wrong." Else $sError = "OK" $sStatus = " ... finished" EndIf _write_log($sError, $aBatch[$i][4], $sStatus) EndIf If UBound($aTasks) = 1 Then $aTasks[0][0] = "" $aTasks[0][1] = "" Else _ArrayDelete($aTasks, 0) EndIf Next Sleep(1000) If UBound($aQueue) = 1 Then $aQueue[0][0] = "" $aQueue[0][1] = "" $aQueue[0][2] = "" Else _ArrayDelete($aQueue, 0) EndIf $sError = 0 $sStatus = 0 $ShellExecute = 0 $aBatch = 0 _load() EndFunc ;==>_start Func _stop_working() AdlibUnRegister("_compare_datetimes") $sScriptstatus = False Return 0 EndFunc ;==>_stop_working Func _exit() Exit EndFunc ;==>_exit Func _write_log($sError, $sCommand, $sStatus) Local $hFile $hFile = FileOpen("log_woBATCHwoSQLwoLV.txt", 1) FileWriteLine($hFile, "[" & @MDAY & "/" & @MON & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC & "][" & $sError & "] " & $sCommand & " " & $sStatus) FileClose($hFile) EndFunc ;==>_write_log Could you guys please take a look if I'm causing the memory leak or AutoIt? Maybe some ohter Informations: I wrote Testscripts for Run, RunWait (and both with @ComSpec), ShellExecute,ShellExecuteWait (and both with CMD) and the only two commands where memory isnt rising is Run and RunWait without @ComSpec!Then I wrote some Testscript to check if my funktion write_log maybe causes a memory leak. Did the test 2 times, both with 100.000 times of writing, and closing file, memory stayed same. I hope some of you can help me, working for 2 1/2 months on my real script now and dunno if it makes sense to try to finish it in AutoIt :/ Regards Edited January 25, 2013 by MikeWenzel Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted January 25, 2013 Moderators Share Posted January 25, 2013 MikeWenzel,The thing that immediately stands out is the fact that you have a Sleep(1000) within an Adlib function that you call every second - this means that you will eventually queue calls to that function as you can never return from it before it is called again. When I tested the script the overrun was about 25ms. Initially, this waiting Adlib call will be honoured before yet another call is added to the list - but every 40 secs or so you will get another call being queued before the top call in the queue is honoured. This would lead to an ever increasing Adlib call queue - and therefore perhaps your increase in memory. Try increasing the Adlib frequency so that the function always returns before the next call and see if that solves your problem. Unfortunately you cannot just UnRegister and the re-Register the Adlibfunction on function entry/exit as you can see from this thread - it still uses the same timer when re-Registered. But the flag trick I used in that thread might help. M23  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
Yaerox Posted January 25, 2013 Author Share Posted January 25, 2013 Thanks for the fast reply. I'll post and reply after trying Thanks. Link to comment Share on other sites More sharing options...
Yaerox Posted January 25, 2013 Author Share Posted January 25, 2013 (edited) I just increased the AdlibFunction in my Script up to 3 seconds. Seems like it works fine I'll poast another reply in some hours if it rly solved the problem. But I got a question if it should be the case. If my Script will run in my real enviroment, i don't know if i can really say how long the Task need which is running ... If there are some Tasks which need lets say 10 or 20 sec, I had to increase my Adlib more but this wouldn't be an good option for me. Did I understood it right, that I could solve this problem with your way of Adlib by checking some boolean $var if the actual Task is still running? Regards. Edit: If I understood it right, I'd do it this way:Func _check() ; Should we check the timer? If $fCheck_Timer Then ; Prevent any checking while the function runs $fCheck_Timer = False AdlibRegister("_compare_datetimes", 1000) EndIf EndFunc ;==>_check Func _compare_datetimes() . . . $fCheck_Timer = True EndFunc ;==>_compare_datetimes Edited January 25, 2013 by MikeWenzel Link to comment Share on other sites More sharing options...
Yaerox Posted January 28, 2013 Author Share Posted January 28, 2013 I rebuilded my Script with your function and I have to say my memory isn't rising up any more Thanks alot. Really. I'll now start building a complete version of my script and hopeful it will work fine (: Regards. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted January 28, 2013 Moderators Share Posted January 28, 2013 MikeWenzel, Great news! Let us know how the full script does when you try it. M23  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
Yaerox Posted January 28, 2013 Author Share Posted January 28, 2013 (edited) But the great news wont be the latest news for a long time :/A few weeks ago, i had a rly insane increasement because i didnt finalized any SQL_Statements.Now i added your part in my script and now i have a big increasement again ... but I didnt changed any other things :/Tried to write the whole script in eng (except Comments): expandcollapse popup#include <Array.au3> #include <DateTimeConstants.au3> #include <EditConstants.au3> #include <GuiComboBox.au3> #include <GUIConstantsEx.au3> #include <GuiListView.au3> #include <ListViewConstants.au3> #include <SendMessage.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <String.au3> #include <WindowsConstants.au3> Opt("GUIOnEventMode", 1) Opt("MustDeclareVars", 1) OnAutoItExitRegister("_exit") Local $CheckingParameters = $CmdLine[0] If $CheckingParameters = 0 Then Global $usedb = IniRead("H:\AutoIt\Automatische Ablaufsteuerung\config.ini", "Datenbank", "pfad", "default") Global $path = IniRead("H:\AutoIt\Automatische Ablaufsteuerung\config.ini", "SQLite3.exe", "pfad", "default") Else Local $param = $CmdLine[1] Global $usedb = IniRead($param, "Datenbank", "pfad", "default") Global $path = IniRead($param, "SQLite3.exe", "pfad", "default") EndIf ; This is a flag to show whether we should check the timer or not Global $fCheck_Timer = True Global $iBegin, $aQueue[1][4], $fScriptstatus, $Row, $Query ;~ Global $fl_name = @scriptdir & '\memstats.txt' _SQLite_Startup() Global $DB = _SQLite_Open($usedb) ;#GUI 1 Global $hGUI = GUICreate("GUI", 250, 390) Global $hMenuFile = GUICtrlCreateMenu("File") Global $hMenuFileExit = GUICtrlCreateMenuItem("Exit", $hMenuFile) Global $hMenuHelp = GUICtrlCreateMenu("?") Global $hMenuHelpHelp = GUICtrlCreateMenuItem("Help", $hMenuHelp) Global $hMenuHelpInfo = GUICtrlCreateMenuItem("Info", $hMenuHelp) Global $iCounter = 0 Global $hCounter = GUICtrlCreateLabel("0", 50, 72, 200, 20) Global $gfx = GUICtrlCreateGraphic(5, 10, 18, 18) GUICtrlSetBkColor($gfx, 0xFF0000) GUICtrlSetColor($gfx, 0) Global $hBtnAdminTool = GUICtrlCreateButton("AdminTool", 160, 5, 80, 30) Global $hBtnLoad = GUICtrlCreateButton("Load", 140, 40, 100, 30) Global $astart_button = GUICtrlCreateButton("Start", 30, 5, 50, 30) Global $hBtnStart = GUICtrlCreateButton("Stop", 30, 40, 50, 30) GUISetState(@SW_SHOW, $hGUI) ;Aufgabenliste GUICtrlSetOnEvent($hMenuFileExit, "_exit") GUICtrlSetOnEvent($hMenuHelpHelp, "hilfe") GUICtrlSetOnEvent($hMenuHelpInfo, "info") GUICtrlSetOnEvent($hBtnLoad, "_load") GUICtrlSetOnEvent($astart_button, "_check") GUICtrlSetOnEvent($hBtnStart, "_stop") GUISetOnEvent($GUI_EVENT_CLOSE, "_exit", $hGUI) ; Set teh initial timestamp $iBegin = TimerInit() While 1 Sleep(100) WEnd Func _load() Local $aStr, $i, $a _SQLite_Startup() $DB = _SQLite_Open($usedb) $Query = _sqlitequeryceck($DB, "SELECT id, geplante_startzeit, arbeitsplan_id FROM aufgabenliste ORDER BY geplante_startzeit ASC") While _SQLite_FetchData($Query, $Row) = $SQLITE_OK ReDim $aQueue[$i + 1][4] ;x = Row # y = Column $aQueue[$i][0] = $Row[0] ; Column 0 - ID $a = StringSplit($Row[1], " ") $aQueue[$i][1] = $a[1] ; Column 1 - Datum $aQueue[$i][2] = $a[2] ; Column 2 - Uhrzeit $aQueue[$i][3] = $Row[2] ; Column 3 - Arbeitsplan ID $i = $i + 1 WEnd _SQLite_QueryFinalize($Query) For $i = 0 To UBound($aQueue) - 1 $aStr = StringSplit($aQueue[$i][1], "/") $aQueue[$i][1] = $aStr[3] & $aStr[2] & $aStr[1] Next For $i = 0 To UBound($aQueue) - 1 $aQueue[$i][2] = StringMid($aQueue[$i][2], 1, 2) * 60 + StringMid($aQueue[$i][2], 4, 2) Next ;~ _ArrayDisplay($aTasks_Bot) _Array2DSortFree($aQueue, '1|0') ;~ _ArrayDisplay($aTasks_Bot) For $i = 0 To UBound($aQueue) - 1 $aQueue[$i][1] = StringMid($aQueue[$i][1], 7, 2) & "/" & StringMid($aQueue[$i][1], 5, 2) & "/" & StringMid($aQueue[$i][1], 1, 4) Next For $i = 0 To UBound($aQueue) - 1 If Mod($aQueue[$i][2], 60) = 0 Then If StringLen($aQueue[$i][2] / 60) = 2 Then $aQueue[$i][2] = ($aQueue[$i][2] / 60) & ":00" ElseIf StringLen($aQueue[$i][2] / 60) < 2 Then $aQueue[$i][2] = "0" & ($aQueue[$i][2] / 60) & ":00" EndIf Else If StringLen(Int($aQueue[$i][2] / 60)) = 2 Then If StringLen($aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60)) = 2 Then $aQueue[$i][2] = Int($aQueue[$i][2] / 60) & ":" & $aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60) ElseIf StringLen($aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60)) < 2 Then $aQueue[$i][2] = Int($aQueue[$i][2] / 60) & ":0" & $aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60) EndIf ElseIf StringLen(Int($aQueue[$i][2] / 60)) < 2 Then If StringLen($aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60)) = 2 Then $aQueue[$i][2] = "0" & Int($aQueue[$i][2] / 60) & ":" & $aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60) ElseIf StringLen($aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60)) < 2 Then $aQueue[$i][2] = "0" & Int($aQueue[$i][2] / 60) & ":0" & $aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60) EndIf EndIf EndIf Next For $i = 0 To UBound($aQueue) - 1 $Query = _sqlitequeryceck($DB, "SELECT arbeitsplan_name FROM arbeitsplaene WHERE arbeitsplan_id=" & $aQueue[$i][3]) While _SQLite_FetchData($Query, $Row) = $SQLITE_OK $aQueue[$i][0] = $aQueue[$i][0] $aQueue[$i][1] = $aQueue[$i][1] & " " & $aQueue[$i][2] $aQueue[$i][2] = $Row[0] WEnd _SQLite_QueryFinalize($Query) Next _SQLite_Close() _SQLite_Shutdown() $aStr = 0 $i = 0 $a = 0 EndFunc ;==>_load Func _Array2DSortFree(ByRef $ARRAY, $sCOL_ASC, $NUM = False) If Not IsArray($ARRAY) Then Return SetError(1, 0, 1) Local $tableStr = "CREATE TABLE [tblTEST] (" Local $insertStr = '', $insertBase = "INSERT INTO tblTEST VALUES (" Local $sortOrder = '', $sortStr = "SELECT * FROM tblTEST ORDER BY " Local $aResult, $asc, $iRows, $iCol, $hSQL $sCOL_ASC = StringStripWS($sCOL_ASC, 8) Local $ub2nd = UBound($ARRAY, 2) If @error = 2 Then If (StringLen($sCOL_ASC) > 3) Or (StringLeft($sCOL_ASC, 1) <> '0') Then Return SetError(2, 0, 1) If StringRight($sCOL_ASC, 1) = 0 Then _ArraySort($ARRAY) Else _ArraySort($ARRAY, 1) EndIf Return 0 Else Local $aOut[UBound($ARRAY)][$ub2nd] EndIf If @error > 0 Then Return SetError(3, 0, 1) $hSQL = _SQLite_Open() If @error > 0 Then _SQLite_Shutdown() Return SetError(3, 0, 1) EndIf For $i = 0 To UBound($ARRAY, 2) - 1 $tableStr &= "'field" & $i & "'," Next $tableStr = StringTrimRight($tableStr, 1) & ");" For $i = 0 To UBound($ARRAY) - 1 $insertStr &= $insertBase For $k = 0 To UBound($ARRAY, 2) - 1 $insertStr &= "'" & $ARRAY[$i][$k] & "'," Next $insertStr = StringTrimRight($insertStr, 1) & ");" Next If _SQLite_Exec($hSQL, $tableStr & $insertStr) <> $SQLITE_OK Then _SQLite_Shutdown() Return SetError(3, 0, 1) EndIf If StringInStr($sCOL_ASC, ',') Then Local $aOrder = StringSplit($sCOL_ASC, ',') For $i = 1 To UBound($aOrder) - 1 If StringInStr($sCOL_ASC, '|') Then Local $var = StringSplit($aOrder[$i], '|') $asc = ' ASC' If $var[2] = 1 Then $asc = ' DESC' If $NUM Then $sortOrder &= 'ABS(field' & $var[1] & ')' & $asc & ',' Else $sortOrder &= 'field' & $var[1] & $asc & ',' EndIf Else _SQLite_Shutdown() Return SetError(4, 0, 1) EndIf Next $sortOrder = StringTrimRight($sortOrder, 1) & ';' Else If (StringLen($sCOL_ASC) > 2) And (StringInStr($sCOL_ASC, '|')) Then Local $var = StringSplit($sCOL_ASC, '|') $asc = ' ASC' If $var[2] = 1 Then $asc = ' DESC' If $NUM Then $sortOrder &= 'ABS(field' & $var[1] & ')' & $asc Else $sortOrder &= 'field' & $var[1] & $asc EndIf Else _SQLite_Shutdown() Return SetError(4, 0, 1) EndIf EndIf If _SQLite_GetTable2d($hSQL, $sortStr & $sortOrder, $aResult, $iRows, $iCol) <> $SQLITE_OK Then _SQLite_Shutdown() Return SetError(3, 0, 1) EndIf For $i = 1 To UBound($aResult) - 1 For $j = 0 To UBound($ARRAY, 2) - 1 $ARRAY[$i - 1][$j] = $aResult[$i][$j] Next Next _SQLite_Exec($hSQL, "DROP TABLE tblTEST;") _SQLite_Close() Return 0 EndFunc ;==>_Array2DSortFree Func _check() $fScriptstatus = True GUICtrlSetBkColor($gfx, 0x32CD32) AdlibRegister("_compate_datetimes", 1000) EndFunc ;==>_check Func _compate_datetimes() Local $aSplit, $sNextplanneddate, $aDateform ; Should we check the timer? If $fCheck_Timer Then ; Have we waited 1 sec If TimerDiff($iBegin) > 1000 Then ; Prevent any checking while the function runs $fCheck_Timer = False If UBound($aQueue) = 0 Then _stop() Return 0 EndIf $aSplit = StringSplit($aQueue[0][1], " ") $sNextplanneddate = $aSplit[1] $aSplit = StringSplit($aSplit[2], ":") $aDateform = StringSplit($sNextplanneddate, "/") If $aDateform[3] * 365 + $aDateform[2] * 30 + $aDateform[1] = @YEAR * 365 + @MON * 30 + @MDAY Then If $aSplit[1] * 3600 + $aSplit[2] * 60 = @HOUR * 3600 + @MIN * 60 + @SEC Or $aSplit[1] * 3600 + $aSplit[2] * 60 < @HOUR * 3600 + @MIN * 60 + @SEC Then $aSplit = 0 $aDateform = 0 start_working() EndIf ElseIf $aDateform[3] * 365 + $aDateform[2] * 30 + $aDateform[1] < @YEAR * 365 + @MON * 30 + @MDAY Then $aSplit = 0 $aDateform = 0 start_working() EndIf $iCounter = $iCounter + 1 GUICtrlSetData($hCounter, $iCounter) $fCheck_Timer = True ; Reset the timestamp $iBegin = Timerinit() EndIf EndIf $aSplit = 0 $sNextplanneddate = 0 $aDateform = 0 EndFunc ;==>_compate_datetimes Func start_working() Local $sError, $sStatus, $sNewStartDatetime, $ShellExecute, $aSQL[1][6], $aTasks[1][2], $i _SQLite_Startup() $DB = _SQLite_Open($usedb) $Query = _sqlitequeryceck($DB, "SELECT laufende_nr, arbeitspunkt_name FROM arbeitspunkte WHERE arbeitsplan_id=(SELECT arbeitsplan_id FROM aufgabenliste WHERE id=" & $aQueue[0][0] & ") AND status='Aktiv' ORDER BY laufende_nr ASC") While _SQLite_FetchData($Query, $Row) = $SQLITE_OK ReDim $aTasks[$i + 1][2] $aTasks[$i][0] = $Row[0] $aTasks[$i][1] = $Row[1] $i = $i + 1 WEnd _SQLite_QueryFinalize($Query) $i = 0 $Query = _sqlitequeryceck($DB, "SELECT a.arbeitsplan_id, ap.arbeitsplan_name, a.geplante_startzeit, app.arbeitspunkt_name, app.befehl, app.parameter FROM aufgabenliste a, arbeitsplaene ap, arbeitspunkte app WHERE a.arbeitsplan_id=(SELECT arbeitsplan_id FROM aufgabenliste WHERE id=" & $aQueue[0][0] & ") AND a.arbeitsplan_id=ap.arbeitsplan_id AND a.arbeitsplan_id=app.arbeitsplan_id AND app.status='Aktiv' ORDER BY app.laufende_nr") While _SQLite_FetchData($Query, $Row) = $SQLITE_OK ReDim $aSQL[$i+1][6] $aSQL[$i][0] = $Row[0] $aSQL[$i][1] = $Row[1] $aSQL[$i][2] = $Row[2] $aSQL[$i][3] = $Row[3] $aSQL[$i][4] = $Row[4] $aSQL[$i][5] = $Row[5] $i = $i + 1 WEnd _SQLite_QueryFinalize($Query) _SQLite_Exec($DB, "UPDATE aufgabenliste SET startzeit=" & @HOUR & @MIN & @SEC & " WHERE id=" & $aQueue[0][0]) ;~ , arbeitspunkt_id=(SELECT arbeitspunkt_id FROM arbeitspunkte WHERE arbeitsplan_id=(SELECT arbeitsplan_id FROM aufgabenliste WHERE id=" & _GUICtrlListView_GetItemText($a_listview_bot, 0, 0) & ") AND laufende_nr=" & _GUICtrlListView_GetItemText($a_listview_top, 0, 0) & ") ;~ _sqliteerrorcheck(@error) For $i = 0 To UBound($aSQL) - 1 $sStatus = " ... wird gestartet" If $aSQL[$i][4] = "" Then $sError = " " _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][1], $aSQL[$i][2], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) $sNewStartDatetime = _new_datetime($aSQL[$i][0]) _SQLite_Exec($DB, "UPDATE aufgabenliste set geplante_startzeit='" & $sNewStartDatetime & "' WHERE arbeitsplan_id=" & $aSQL[$i][0]) _sqliteerrorcheck(@error) $sError = "OK" $sStatus = " ... ist beendet" _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][1], $aSQL[$i][2], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) Else $sError = " " _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][1], $aSQL[$i][2], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) $aSQL[$i][4] = StringRegExpReplace($aSQL[$i][4], "\\", "/") $ShellExecute = RunWait($aSQL[$i][4], "c:", @SW_HIDE) If $ShellExecute <> 0 Then $sError = "FEHLER" $sStatus = " Es ist ein Fehler aufgetreten." Else $sError = "OK" $sStatus = " ... ist beendet" EndIf _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][1], $aSQL[$i][2], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) EndIf $aTasks = 0 Next Sleep(1000) _load() _SQLite_Close() _SQLite_Shutdown() $sError = 0 $sStatus = 0 $sNewStartDatetime = 0 $ShellExecute = 0 $aSQL = 0 $aTasks = 0 $i = 0 EndFunc ;==>start_working Func _stop() AdlibUnRegister("_compate_datetimes") $fScriptstatus = False GUICtrlSetBkColor($gfx, 0xFF0000) ;~ rot Return 0 EndFunc ;==>_stop Func _add_date($day_to_add) Local $iDays_of_month, $mday, $mon, $year If @MON = 01 Or @MON = 03 Or @MON = 03 Or @MON = 05 Or @MON = 07 Or @MON = 08 Or @MON = 10 Or @MON = 12 Then $iDays_of_month = 31 ; großer Monat ElseIf @MON = 04 Or @MON = 06 Or @MON = 09 Or @MON = 11 Then $iDays_of_month = 30 ; mittlerer Monat ElseIf @MON = 02 Then If Mod(@YEAR, 4) = 0 Then $iDays_of_month = 29 ; Schaltjahr Else $iDays_of_month = 28 ; kleiner Monat EndIf EndIf If @MDAY + $day_to_add > $iDays_of_month Then ; Wenn Tage größer als Monat, Monat erhöhen If @MON + 1 > 12 Then ; Wenn Monat größer als 12 wird, Jahr erhöhen $mday = "01" $mon = "01" $year = @YEAR + 1 Else $mday = "01" $mon = @MON + 1 $year = @YEAR EndIf Else $mday = @MDAY + $day_to_add $mon = @MON $year = @YEAR EndIf If StringLen($mday) < 2 Then $mday = "0" & $mday EndIf If StringLen($mon) < 2 Then $mon = "0" & $mon EndIf If StringLen($year) < 2 Then $year = "0" & $year EndIf Return $mday & "/" & $mon & "/" & $year EndFunc ;==>_add_date Func _add_time($sStarttime, $admze) ;~~>> admze = count of minutes !! Local $aStarttime = StringSplit($sStarttime, ":") Local $min, $hour, $admze_std, $admze_min If $admze > 60 Then $admze_std = Int($admze / 60, 0) $admze_min = Int(Mod($admze, 60)) If $aStarttime[2] + $admze_min > 60 Then If $aStarttime[1] + $admze_std + 1 > 24 Then $min = ($aStarttime[2] + $admze_min) - 60 $hour = 0 Else $min = ($aStarttime[2] + $admze_min) - 60 $hour = $aStarttime[1] + $admze_std + 1 EndIf Else $min = $aStarttime[2] + $admze_min $hour = $aStarttime[1] + $admze_std EndIf Else If $aStarttime[2] + $admze > 60 Then If $aStarttime[1] + 1 > 24 Then $min = ($aStarttime[2] + $admze) - 60 $hour = 0 Else $min = ($aStarttime[2] + $admze) - 60 $hour = $aStarttime[1] + 1 EndIf ElseIf $aStarttime[2] + $admze = 60 Then If $aStarttime[1] + 1 > 24 Then $min = ($aStarttime[2] + $admze) - 60 $hour = 0 ElseIf $aStarttime[1] + 1 = 24 Then $min = "00" $hour = "00" Else $min = "00" $hour = $aStarttime[1] + 1 EndIf Else $min = $aStarttime[2] + $admze $hour = $aStarttime[1] EndIf EndIf If StringLen($min) < 2 Then $min = "0" & $min EndIf If StringLen($hour) < 2 Then $hour = "0" & $hour EndIf $aStarttime = 0 Return $hour & ":" & $min EndFunc ;==>_add_time Func _sort_wday_array($ARRAY) If $ARRAY[1] = @WDAY Then Return $ARRAY Else For $i = 1 To $ARRAY[0] If $ARRAY[$i] > @WDAY Then $ARRAY = _sort_wday_array_increase($ARRAY) Return $ARRAY EndIf Next $ARRAY = _sort_wday_array_decrease($ARRAY) Return $ARRAY EndIf EndFunc ;==>_sort_wday_array Func _sort_wday_array_increase($ARRAY) Local $i _ArrayAdd($ARRAY, "") If $ARRAY[1] > @WDAY Then Else Do $ARRAY[$ARRAY[0] + 1] = $ARRAY[1] $i = 1 Do $ARRAY[$i] = $ARRAY[$i + 1] $i = $i + 1 Until $i = $ARRAY[0] + 1 Until $ARRAY[1] = @WDAY Or $ARRAY[1] > @WDAY EndIf _ArrayDelete($ARRAY, UBound($ARRAY)) Return $ARRAY EndFunc ;==>_sort_wday_array_increase Func _sort_wday_array_decrease($ARRAY) Local $i _ArrayAdd($ARRAY, "") Do $ARRAY[$ARRAY[0] + 1] = $ARRAY[1] $i = 1 Do $ARRAY[$i] = $ARRAY[$i + 1] $i = $i + 1 Until $i = $ARRAY[0] + 1 Until $ARRAY[1] = @WDAY Or $ARRAY[1] < @WDAY _ArrayDelete($ARRAY, UBound($ARRAY)) Return $ARRAY EndFunc ;==>_sort_wday_array_decrease Func _new_datetime($ap_id) Local $Row, $Query, $day_to_add, $newDate, $TimeIncreased, $z $Query = _sqlitequeryceck($DB, "SELECT minute, wochentag, von, bis FROM zeitplan WHERE arbeitsplan_id=" & $ap_id) _SQLite_FetchData($Query, $Row) _sqliteerrorcheck(@error) _SQLite_QueryFinalize($Query) ;$Row[1] = $wochentag Local $aWday = StringSplit($Row[1], ",") $aWday = _sort_wday_array($aWday) If $Row[0] = "" Then ;FALL 1 - zu einer bestimmten Uhrzeit For $i = 1 To $aWday[0] If $aWday[$i] = @WDAY Then ;---Wochentag dabei--- If $Row[3] > @HOUR & ":" & @MIN & ":" & @SEC Then ;Fall 1.1 ;~ ConsoleWrite("Fall 1 . 1: Wochentag = Heute -> Uhrzeit noch nicht erreicht: " & @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[3] & @CR) Return @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[3] ElseIf $Row[3] < @HOUR & ":" & @MIN & ":" & @SEC Then ;Fall 1.2 If $Row[1] = @WDAY Then ;Nur der Wochentag $day_to_add = 7 Else If $aWday[2] = 1 Then $day_to_add = 7 - (($aWday[2] - @WDAY) * -1) ElseIf ($aWday[2] - @WDAY) > 0 Then $day_to_add = ($aWday[2] - @WDAY) ElseIf ($aWday[2] - @WDAY) < 0 Then $day_to_add = ($aWday[2] - @WDAY) + 7 EndIf EndIf $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 1 . 2: Wochentag = Heute -> Uhrzeit vorbei: " & $newDate & " " & $Row[3] & @CR) Return $newDate & " " & $Row[3] EndIf EndIf Next ;---Wochentag NICHT dabei--- # Fall 1.3 If ($aWday[1] - @WDAY) > 0 Then ; #1 $day_to_add = ($aWday[1] - @WDAY) $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 1 . 3 #1: " & $newDate & " " & $Row[3] & @CR) Return $newDate & " " & $Row[3] ElseIf ($aWday[1] - @WDAY) < 0 Then ; #2 $day_to_add = ($aWday[1] - @WDAY) + 7 $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 1 . 3 #2: " & $newDate & " " & $Row[3] & @CR) Return $newDate & " " & $Row[3] EndIf Else For $i = 1 To $aWday[0] ;---Wochentag dabei--- If $aWday[$i] = @WDAY Then If $Row[2] > @HOUR & ":" & @MIN & ":" & @SEC Then ;FALL 2.1 ;~ ConsoleWrite("Fall 2 . 1: Wochentag = Heute -> Uhrzeit noch nicht erreicht: " & @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[2] & @CR) Return @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[2] ElseIf $Row[2] < @HOUR & ":" & @MIN & ":" & @SEC And @HOUR & ":" & @MIN & ":" & @SEC < $Row[3] Then ;FALL 2.2 $z = 1 Do $TimeIncreased = _add_time($Row[2], ($Row[0] * $z)) $z = $z + 1 Until $TimeIncreased > @HOUR & ":" & @MIN & ":" & @SEC If ($TimeIncreased < $Row[3]) Or ($TimeIncreased = $Row[3]) Then ;FALL 2.2 ;~ ConsoleWrite("Fall 2 . 2 #1: " & @MDAY & "/" & @MON & "/" & @YEAR & " " & $TimeIncreased & @CR) Return @MDAY & "/" & @MON & "/" & @YEAR & " " & $TimeIncreased Else ;FALL 2.2 #2 $aWday = _sort_wday_array($aWday) If $aWday[2] = 1 Then $day_to_add = 7 - (($aWday[2] - $z) * -1) ElseIf ($aWday[2] - @WDAY) > 0 Then $day_to_add = ($aWday[2] - $z) ElseIf ($aWday[2] - @WDAY) < 0 Then $day_to_add = ($aWday[2] - $z) + 7 EndIf $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 2 #2: " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] EndIf ElseIf (@HOUR & ":" & @MIN & ":" & @SEC > $Row[3]) Or (@HOUR & ":" & @MIN & ":" & @SEC = $Row[3]) Then ;FALL 2.3 $aWday = _sort_wday_array($aWday) For $i = 0 To UBound($aWday) - 1 ;~ ConsoleWrite(@CR & "$aWday[$i]: " & $aWday[$i]) Next If $Row[1] = @WDAY Then ;Nur der Wochentag $day_to_add = 7 Else If $aWday[2] = 1 Then $day_to_add = 7 - (($aWday[2] - @WDAY) * -1) ElseIf ($aWday[2] - @WDAY) > 0 Then $day_to_add = ($aWday[2] - @WDAY) ElseIf ($aWday[2] - @WDAY) < 0 Then $day_to_add = ($aWday[2] - @WDAY) + 7 EndIf EndIf $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 3: " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] EndIf EndIf Next If ($aWday[1] - @WDAY) > 0 Then ; #1 $day_to_add = ($aWday[1] - @WDAY) $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 4 #1 " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] ElseIf ($aWday[1] - @WDAY) < 0 Then ; #2 $day_to_add = ($aWday[1] - @WDAY) + 7 $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 4 #2 " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] EndIf EndIf EndFunc ; Hilfe MsgBox Func hilfe() MsgBox(64, "Hilfe", "Hilfe") ;~ COOMING SOON EndFunc ;==>hilfe ; Info MsgBox Func info() MsgBox(64, "Info", "Info") ;~ COOMING SOON EndFunc ;==>info Func _sqlitequeryceck($DB, $sQuery) Local $retQuery If _SQLite_Query($DB, $sQuery, $retQuery) <> 0 Then _sqliteerrorcheck(@error) _SQLite_QueryFinalize($retQuery) Return 0 EndIf Return $retQuery EndFunc ;==>_sqlitequeryceck Func _sqliteerrorcheck($sql_error) If $sql_error = -1 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". SQLite reported an error (Check return value)") ElseIf $sql_error = 1 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". Error calling SQLite API 'sqlite3_exec'") ElseIf $sql_error = 2 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". Call prevented by SafeMode") ElseIf $sql_error = 3 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". Error Processing Callback from within _SQLite_GetTable2d") ElseIf $sql_error = 4 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". Error while converting SQL statement to UTF-8") EndIf EndFunc ;==>_sqliteerrorcheck Func _schreibe_log($sError, $sAp_id, $sAp_name, $sGeplante_startzeit, $sApp_name, $sBefehl, $sParameter, $sStatus) Local $hfile = FileOpen("log11111111111.txt", 1) ;Beispiel: [12.11.2012 16:14:30][OK] Arbeitsplan_ID: 1 | Arbeitsplan_Name: AP 1 | Geplante_Startzeit: 16:00 | Arbeitspunkt_Name: Hello World | Status: wurde gestartet FileWriteLine($hfile, "[" & @MDAY & "/" & @MON & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC & "][" & $sError & "] " & $sAp_id & " | " & _ $sAp_name & " | " & $sGeplante_startzeit & " | " & $sApp_name & " | " & $sBefehl & " " & $sParameter & " | " & $sStatus) FileClose($hfile) EndFunc ;==>_schreibe_log Func _exit() _SQLite_Close($usedb) _SQLite_Shutdown() Exit EndFunc ;==>_exit if you will run the Script you have to add the following Attachements:(I'm actual trying to pack them that you can run them too, so cooming soon) . . . Edit: Dunno it will help, maybe here this Script without SQL and without any Problems! expandcollapse popup#include <GUIConstantsEx.au3> #include <GuiListView.au3> #include <ListViewConstants.au3> #include <WindowsConstants.au3> Opt("GUIOnEventMode", 1) Opt("MustDeclareVars", 1) OnAutoItExitRegister("_exit") ; This is a flag to show whether we should check the timer or not Global $fCheck_Timer = True, $iBegin Global $aQueue[1][3] Global $aTasks[5][2] Global $hGUI = GUICreate("Aufgabenliste", 250, 390) Global $iCounter_label = 0 Global $hCounter_label = GUICtrlCreateLabel("0", 50, 72, 200, 20) Global $hLoadBtn = GUICtrlCreateButton("Load", 140, 40, 100, 30) Global $hStartBtn = GUICtrlCreateButton("Start", 30, 5, 50, 30) Global $hStopBtn = GUICtrlCreateButton("Stop", 30, 40, 50, 30) GUISetState(@SW_SHOW, $hGUI) GUICtrlSetOnEvent($hLoadBtn, "_load") GUICtrlSetOnEvent($hStartBtn, "_check") GUICtrlSetOnEvent($hStopBtn, "_stop_working") GUISetOnEvent($GUI_EVENT_CLOSE, "_exit", $hGUI) ; Set teh initial timestamp $iBegin = TimerInit() While 1 Sleep(100) WEnd Func _load() $aQueue[0][0] = "0" $aQueue[0][1] = "18/01/2013 08:00:00" $aQueue[0][2] = "Test" EndFunc ;==>_load Func _check() AdlibRegister("_compare_datetimes", 1000) EndFunc ;==>_check Func _compare_datetimes() ConsoleWrite(@CR & @HOUR & ":" & @MIN & ":" & @SEC & ":" & @MSEC & " - " & $fCheck_Timer & " ==>> ") ; Should we check the timer? If $fCheck_Timer Then ; Have we waited 2 secs If TimerDiff($iBegin) > 1000 Then ConsoleWrite(@MIN&":"&@SEC&":"&@MSEC & " - ") ; Prevent any checking while the function runs $fCheck_Timer = False Local $aRow, $next_date, $aDate If UBound($aQueue) = 0 Then _stop_working() Return 0 EndIf $aRow = StringSplit($aQueue[0][1], " ") $next_date = $aRow[1] $aRow = StringSplit($aRow[2], ":") $aDate = StringSplit($next_date, "/") If $aDate[3] * 365 + $aDate[2] * 30 + $aDate[1] = @YEAR * 365 + @MON * 30 + @MDAY Then If $aRow[1] * 3600 + $aRow[2] * 60 = @HOUR * 3600 + @MIN * 60 + @SEC Or $aRow[1] * 3600 + $aRow[2] * 60 < @HOUR * 3600 + @MIN * 60 + @SEC Then $aRow = 0 $aDate = 0 _start() EndIf ElseIf $aDate[3] * 365 + $aDate[2] * 30 + $aDate[1] < @YEAR * 365 + @MON * 30 + @MDAY Then $aRow = 0 $aDate = 0 _start() EndIf $iCounter_label = $iCounter_label + 1 GUICtrlSetData($hCounter_label, $iCounter_label) $aRow = 0 $next_date = 0 $aDate = 0 ConsoleWrite(@MIN&":"&@SEC&":"&@MSEC) $fCheck_Timer = True ; Reset the timestamp $iBegin = Timerinit() EndIf EndIf EndFunc ;==>_compare_datetimes Func _start() Local $sError, $sStatus, $ShellExecute Local $aBatch[5][5] = [[1, 1, $aQueue[0][1], "Task 1", "C:/PATH_TO/create_txt.bat"], _ [2, 2, $aQueue[0][1], "Task 2", ""], _ [3, 3, $aQueue[0][1], "Task 3", ""], _ [4, 4, $aQueue[0][1], "Task 4", ""], _ [5, 5, $aQueue[0][1], "_create_next_date", ""]] ReDim $aTasks[5][2] For $i = 0 To UBound($aBatch) - 1 $aTasks[$i][0] = $aBatch[$i][1] * 10 $aTasks[$i][1] = $aBatch[$i][3] Next For $i = 0 To UBound($aBatch) - 1 $sStatus = " ... started" If $aBatch[$i][4] = "" Then $sError = " " _write_log($sError, $aBatch[$i][4], $sStatus) $sError = "OK" $sStatus = " ... finished" _write_log($sError, $aBatch[$i][4], $sStatus) Else $sError = " " _write_log($sError, $aBatch[$i][4], $sStatus) ;~ ConsoleWrite($aBatch[$i][4] & @CR) $ShellExecute = RunWait($aBatch[$i][4], "c:", @SW_HIDE) If $ShellExecute <> 0 Then $sError = "FEHLER" $sStatus = " Something went wrong." Else $sError = "OK" $sStatus = " ... finished" EndIf _write_log($sError, $aBatch[$i][4], $sStatus) EndIf If UBound($aTasks) = 1 Then $aTasks[0][0] = "" $aTasks[0][1] = "" Else _ArrayDelete($aTasks, 0) EndIf Next Sleep(1000) If UBound($aQueue) = 1 Then $aQueue[0][0] = "" $aQueue[0][1] = "" $aQueue[0][2] = "" Else _ArrayDelete($aQueue, 0) EndIf $sError = 0 $sStatus = 0 $ShellExecute = 0 $aBatch = 0 _load() EndFunc ;==>_start Func _stop_working() AdlibUnRegister("_compare_datetimes") Return 0 EndFunc ;==>_stop_working Func _exit() Exit EndFunc ;==>_exit Func _write_log($sError, $sCommand, $sStatus) Local $hFile $hFile = FileOpen("log_wBATCHwoSQLwoLV.txt", 1) FileWriteLine($hFile, "[" & @MDAY & "/" & @MON & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC & "][" & $sError & "] " & $sCommand & " " & $sStatus) FileClose($hFile) EndFunc ;==>_write_log Here my create_txt.bat: @ECHO OFF CLS REM ------------------------------------------------------------ REM Name: create_txt.BAT REM Autor: Mike Wenzel REM Datum: 15 November 2012 REM ------------------------------------------------------------ ECHO. FOR /L %%i IN (1 1 1) DO ( ECHO >> "C:\PATH_TO_ANY_FOLDER\%date%_at_%time:~0,2%%time:~3,2%%time:~6,2%-Datei_%%i.txt" ) ECHO. REM -------------------------- E O F --------------------------- Edited January 28, 2013 by MikeWenzel Link to comment Share on other sites More sharing options...
Yaerox Posted January 28, 2013 Author Share Posted January 28, 2013 (edited) if you will try the one without SQL, you have to change a PATH at the .bat-Code and the au3.Code ... i wrote there PATH_TO and PATH_TO_ANY_FOLDER. Regards. Edit: Looked up the memory with some winapiex.au3 functions...seems like memory is rising between the following pats: Function _load() executes another function called Array2DSortFree() ... there is memory rising up... Function _start_working: at the sql lines memory is rising ... Edited January 28, 2013 by MikeWenzel Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted January 28, 2013 Moderators Share Posted January 28, 2013 MikeWenzel,Based on your comment that you ran into problems when you added the SQL part of the script, I took a quick look through those areas of the script and noticed that in the _Array2DSortFree function you Open a memory database (line #169) but subsequently ShutDown and Return without a formal Close statement - the only one is at the end of the function (line #232). I would expect the variable to be lost as you exit the function, but I wonder if the actual database is also lost - I am not too conversant with SQL but I would imagine that that does not happen and the memory remains locked. I wonder whether this is causing the problem? M23  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
Yaerox Posted January 28, 2013 Author Share Posted January 28, 2013 (edited) Well, even if Add SQL_Close tags .... maybe my code again that u can see where I'am actual checking the memory: expandcollapse popup#include #include #include #include #include #include #include #include #include #include #include #include #include #include Opt("GUIOnEventMode", 1) Opt("MustDeclareVars", 1) OnAutoItExitRegister("_exit") Local $fl_name = @scriptdir & '\memstats_b7.txt' Local $CheckingParameters = $CmdLine[0] If $CheckingParameters = 0 Then Global $usedb = IniRead("H:\AutoIt\Automatische Ablaufsteuerung\config.ini", "Datenbank", "pfad", "default") Global $path = IniRead("H:\AutoIt\Automatische Ablaufsteuerung\config.ini", "SQLite3.exe", "pfad", "default") Else Local $param = $CmdLine[1] Global $usedb = IniRead($param, "Datenbank", "pfad", "default") Global $path = IniRead($param, "SQLite3.exe", "pfad", "default") EndIf ; This is a flag to show whether we should check the timer or not Global $fCheck_Timer = True Global $iBegin, $fScriptstatus, $Row, $Query Dim $aQueue[1][4] ;~ Global $fl_name = @scriptdir & '\memstats.txt' _SQLite_Startup() Dim $DB = _SQLite_Open($usedb) ;#GUI 1 Global $hGUI = GUICreate("GUI", 250, 390) Global $hMenuFile = GUICtrlCreateMenu("File") Global $hMenuFileExit = GUICtrlCreateMenuItem("Exit", $hMenuFile) Global $hMenuHelp = GUICtrlCreateMenu("?") Global $hMenuHelpHelp = GUICtrlCreateMenuItem("Help", $hMenuHelp) Global $hMenuHelpInfo = GUICtrlCreateMenuItem("Info", $hMenuHelp) Global $iCounter = 0 Global $hCounter = GUICtrlCreateLabel("0", 50, 72, 200, 20) Global $gfx = GUICtrlCreateGraphic(5, 10, 18, 18) GUICtrlSetBkColor($gfx, 0xFF0000) GUICtrlSetColor($gfx, 0) Global $hBtnAdminTool = GUICtrlCreateButton("AdminTool", 160, 5, 80, 30) Global $hBtnLoad = GUICtrlCreateButton("Load", 140, 40, 100, 30) Global $astart_button = GUICtrlCreateButton("Start", 30, 5, 50, 30) Global $hBtnStart = GUICtrlCreateButton("Stop", 30, 40, 50, 30) GUISetState(@SW_SHOW, $hGUI) ;Aufgabenliste GUICtrlSetOnEvent($hMenuFileExit, "_exit") GUICtrlSetOnEvent($hMenuHelpHelp, "hilfe") GUICtrlSetOnEvent($hMenuHelpInfo, "info") GUICtrlSetOnEvent($hBtnLoad, "_load") GUICtrlSetOnEvent($astart_button, "_check") GUICtrlSetOnEvent($hBtnStart, "_stop") GUISetOnEvent($GUI_EVENT_CLOSE, "_exit", $hGUI) _logmem("Scriptstart ---------") ; Set teh initial timestamp $iBegin = TimerInit() While 1 Sleep(100) WEnd Func _load() _logmem("_load BEGINNING") Dim $aStr Dim $a Local $i _logmem("_load SQL-1") _SQLite_Startup() $DB = _SQLite_Open($usedb) _logmem("_load SQL-1") _logmem("_load SQL-2") $Query = _sqlitequeryceck($DB, "SELECT id, geplante_startzeit, arbeitsplan_id FROM aufgabenliste ORDER BY geplante_startzeit ASC") While _SQLite_FetchData($Query, $Row) = $SQLITE_OK ReDim $aQueue[$i + 1][4] ;x = Row # y = Column $aQueue[$i][0] = $Row[0] ; Column 0 - ID $a = StringSplit($Row[1], " ") $aQueue[$i][1] = $a[1] ; Column 1 - Datum $aQueue[$i][2] = $a[2] ; Column 2 - Uhrzeit $aQueue[$i][3] = $Row[2] ; Column 3 - Arbeitsplan ID $i = $i + 1 WEnd _SQLite_QueryFinalize($Query) _logmem("_load SQL-2") For $i = 0 To UBound($aQueue) - 1 $aStr = StringSplit($aQueue[$i][1], "/") $aQueue[$i][1] = $aStr[3] & $aStr[2] & $aStr[1] Next For $i = 0 To UBound($aQueue) - 1 $aQueue[$i][2] = StringMid($aQueue[$i][2], 1, 2) * 60 + StringMid($aQueue[$i][2], 4, 2) Next _logmem("_load before _Array2DSortFree") _Array2DSortFree($aQueue, '1|0') _logmem("_load after _Array2DSortFree") For $i = 0 To UBound($aQueue) - 1 $aQueue[$i][1] = StringMid($aQueue[$i][1], 7, 2) & "/" & StringMid($aQueue[$i][1], 5, 2) & "/" & StringMid($aQueue[$i][1], 1, 4) Next For $i = 0 To UBound($aQueue) - 1 If Mod($aQueue[$i][2], 60) = 0 Then If StringLen($aQueue[$i][2] / 60) = 2 Then $aQueue[$i][2] = ($aQueue[$i][2] / 60) & ":00" ElseIf StringLen($aQueue[$i][2] / 60) < 2 Then $aQueue[$i][2] = "0" & ($aQueue[$i][2] / 60) & ":00" EndIf Else If StringLen(Int($aQueue[$i][2] / 60)) = 2 Then If StringLen($aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60)) = 2 Then $aQueue[$i][2] = Int($aQueue[$i][2] / 60) & ":" & $aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60) ElseIf StringLen($aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60)) < 2 Then $aQueue[$i][2] = Int($aQueue[$i][2] / 60) & ":0" & $aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60) EndIf ElseIf StringLen(Int($aQueue[$i][2] / 60)) < 2 Then If StringLen($aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60)) = 2 Then $aQueue[$i][2] = "0" & Int($aQueue[$i][2] / 60) & ":" & $aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60) ElseIf StringLen($aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60)) < 2 Then $aQueue[$i][2] = "0" & Int($aQueue[$i][2] / 60) & ":0" & $aQueue[$i][2] - (Int($aQueue[$i][2] / 60) * 60) EndIf EndIf EndIf Next For $i = 0 To UBound($aQueue) - 1 $Query = _sqlitequeryceck($DB, "SELECT arbeitsplan_name FROM arbeitsplaene WHERE arbeitsplan_id=" & $aQueue[$i][3]) While _SQLite_FetchData($Query, $Row) = $SQLITE_OK $aQueue[$i][0] = $aQueue[$i][0] $aQueue[$i][1] = $aQueue[$i][1] & " " & $aQueue[$i][2] $aQueue[$i][2] = $Row[0] WEnd _SQLite_QueryFinalize($Query) Next _SQLite_Close() _SQLite_Shutdown() _logmem("_load END") EndFunc ;==>_load Func _Array2DSortFree(ByRef $ARRAY, $sCOL_ASC, $NUM = False) _logmem("_Array2DSortFree BEGINNING") If Not IsArray($ARRAY) Then Return SetError(1, 0, 1) Local $tableStr = "CREATE TABLE [tblTEST] (" Local $insertStr = '', $insertBase = "INSERT INTO tblTEST VALUES (" Local $sortOrder = '', $sortStr = "SELECT * FROM tblTEST ORDER BY " Local $aResult, $asc, $iRows, $iCol, $hSQL $sCOL_ASC = StringStripWS($sCOL_ASC, 8) Local $ub2nd = UBound($ARRAY, 2) If @error = 2 Then If (StringLen($sCOL_ASC) > 3) Or (StringLeft($sCOL_ASC, 1) <> '0') Then Return SetError(2, 0, 1) If StringRight($sCOL_ASC, 1) = 0 Then _ArraySort($ARRAY) Else _ArraySort($ARRAY, 1) EndIf Return 0 Else Local $aOut[UBound($ARRAY)][$ub2nd] EndIf If @error > 0 Then Return SetError(3, 0, 1) $hSQL = _SQLite_Open() If @error > 0 Then _SQLite_Close() _SQLite_Shutdown() Return SetError(3, 0, 1) EndIf For $i = 0 To UBound($ARRAY, 2) - 1 $tableStr &= "'field" & $i & "'," Next $tableStr = StringTrimRight($tableStr, 1) & ");" For $i = 0 To UBound($ARRAY) - 1 $insertStr &= $insertBase For $k = 0 To UBound($ARRAY, 2) - 1 $insertStr &= "'" & $ARRAY[$i][$k] & "'," Next $insertStr = StringTrimRight($insertStr, 1) & ");" Next If _SQLite_Exec($hSQL, $tableStr & $insertStr) <> $SQLITE_OK Then _SQLite_Close() _SQLite_Shutdown() Return SetError(3, 0, 1) EndIf If StringInStr($sCOL_ASC, ',') Then Local $aOrder = StringSplit($sCOL_ASC, ',') For $i = 1 To UBound($aOrder) - 1 If StringInStr($sCOL_ASC, '|') Then Local $var = StringSplit($aOrder[$i], '|') $asc = ' ASC' If $var[2] = 1 Then $asc = ' DESC' If $NUM Then $sortOrder &= 'ABS(field' & $var[1] & ')' & $asc & ',' Else $sortOrder &= 'field' & $var[1] & $asc & ',' EndIf Else _SQLite_Close() _SQLite_Shutdown() Return SetError(4, 0, 1) EndIf Next $sortOrder = StringTrimRight($sortOrder, 1) & ';' Else If (StringLen($sCOL_ASC) > 2) And (StringInStr($sCOL_ASC, '|')) Then Local $var = StringSplit($sCOL_ASC, '|') $asc = ' ASC' If $var[2] = 1 Then $asc = ' DESC' If $NUM Then $sortOrder &= 'ABS(field' & $var[1] & ')' & $asc Else $sortOrder &= 'field' & $var[1] & $asc EndIf Else _SQLite_Close() _SQLite_Shutdown() Return SetError(4, 0, 1) EndIf EndIf If _SQLite_GetTable2d($hSQL, $sortStr & $sortOrder, $aResult, $iRows, $iCol) <> $SQLITE_OK Then _SQLite_Close() _SQLite_Shutdown() Return SetError(3, 0, 1) EndIf For $i = 1 To UBound($aResult) - 1 For $j = 0 To UBound($ARRAY, 2) - 1 $ARRAY[$i - 1][$j] = $aResult[$i][$j] Next Next _SQLite_Exec($hSQL, "DROP TABLE tblTEST;") _SQLite_Close() _logmem("_Array2DSortFree END") Return 0 EndFunc ;==>_Array2DSortFree Func _check() ;~ _logmem("_check BEGINNING") $fScriptstatus = True GUICtrlSetBkColor($gfx, 0x32CD32) AdlibRegister("_compate_datetimes", 1000) ;~ _logmem("_check END") EndFunc ;==>_check Func _compate_datetimes() ;~ _logmem("_compate_datetimes BEGINNING") Local $aSplit, $sNextplanneddate, $aDateform ; Should we check the timer? If $fCheck_Timer Then ; Have we waited 1 sec If TimerDiff($iBegin) > 1000 Then ; Prevent any checking while the function runs $fCheck_Timer = False If UBound($aQueue) = 0 Then _stop() Return 0 EndIf $aSplit = StringSplit($aQueue[0][1], " ") $sNextplanneddate = $aSplit[1] $aSplit = StringSplit($aSplit[2], ":") $aDateform = StringSplit($sNextplanneddate, "/") If $aDateform[3] * 365 + $aDateform[2] * 30 + $aDateform[1] = @YEAR * 365 + @MON * 30 + @MDAY Then If $aSplit[1] * 3600 + $aSplit[2] * 60 = @HOUR * 3600 + @MIN * 60 + @SEC Or $aSplit[1] * 3600 + $aSplit[2] * 60 < @HOUR * 3600 + @MIN * 60 + @SEC Then $aSplit = 0 $aDateform = 0 start_working() EndIf ElseIf $aDateform[3] * 365 + $aDateform[2] * 30 + $aDateform[1] < @YEAR * 365 + @MON * 30 + @MDAY Then $aSplit = 0 $aDateform = 0 start_working() EndIf $iCounter = $iCounter + 1 GUICtrlSetData($hCounter, $iCounter) $fCheck_Timer = True ; Reset the timestamp $iBegin = Timerinit() EndIf EndIf $aSplit = 0 $sNextplanneddate = 0 $aDateform = 0 ;~ _logmem("_compate_datetimes END") EndFunc ;==>_compate_datetimes Func start_working() _logmem("start_working BEGINNING") Local $sError, $sStatus, $sNewStartDatetime, $ShellExecute, $aSQL[1][6], $aTasks[1][2], $i _SQLite_Startup() $DB = _SQLite_Open($usedb) $Query = _sqlitequeryceck($DB, "SELECT laufende_nr, arbeitspunkt_name FROM arbeitspunkte WHERE arbeitsplan_id=(SELECT arbeitsplan_id FROM aufgabenliste WHERE id=" & $aQueue[0][0] & ") AND status='Aktiv' ORDER BY laufende_nr ASC") While _SQLite_FetchData($Query, $Row) = $SQLITE_OK ReDim $aTasks[$i + 1][2] $aTasks[$i][0] = $Row[0] $aTasks[$i][1] = $Row[1] $i = $i + 1 WEnd ;~ _SQLite_QueryFinalize($Query) $i = 0 $Query = _sqlitequeryceck($DB, "SELECT a.arbeitsplan_id, ap.arbeitsplan_name, a.geplante_startzeit, app.arbeitspunkt_name, app.befehl, app.parameter FROM aufgabenliste a, arbeitsplaene ap, arbeitspunkte app WHERE a.arbeitsplan_id=(SELECT arbeitsplan_id FROM aufgabenliste WHERE id=" & $aQueue[0][0] & ") AND a.arbeitsplan_id=ap.arbeitsplan_id AND a.arbeitsplan_id=app.arbeitsplan_id AND app.status='Aktiv' ORDER BY app.laufende_nr") While _SQLite_FetchData($Query, $Row) = $SQLITE_OK ReDim $aSQL[$i+1][6] $aSQL[$i][0] = $Row[0] $aSQL[$i][1] = $Row[1] $aSQL[$i][2] = $Row[2] $aSQL[$i][3] = $Row[3] $aSQL[$i][4] = $Row[4] $aSQL[$i][5] = $Row[5] $i = $i + 1 WEnd ;~ _SQLite_QueryFinalize($Query) _SQLite_Exec($DB, "UPDATE aufgabenliste SET startzeit=" & @HOUR & @MIN & @SEC & " WHERE id=" & $aQueue[0][0]) ;~ , arbeitspunkt_id=(SELECT arbeitspunkt_id FROM arbeitspunkte WHERE arbeitsplan_id=(SELECT arbeitsplan_id FROM aufgabenliste WHERE id=" & _GUICtrlListView_GetItemText($a_listview_bot, 0, 0) & ") AND laufende_nr=" & _GUICtrlListView_GetItemText($a_listview_top, 0, 0) & ") ;~ _sqliteerrorcheck(@error) For $i = 0 To UBound($aSQL) - 1 $sStatus = " ... wird gestartet" If $aSQL[$i][4] = "" Then $sError = " " _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][1], $aSQL[$i][2], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) $sNewStartDatetime = _new_datetime($aSQL[$i][0]) _SQLite_Exec($DB, "UPDATE aufgabenliste set geplante_startzeit='" & $sNewStartDatetime & "' WHERE arbeitsplan_id=" & $aSQL[$i][0]) _sqliteerrorcheck(@error) $sError = "OK" $sStatus = " ... ist beendet" _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][1], $aSQL[$i][2], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) Else $sError = " " _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][1], $aSQL[$i][2], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) $aSQL[$i][4] = StringRegExpReplace($aSQL[$i][4], "\\", "/") $ShellExecute = RunWait($aSQL[$i][4], "c:", @SW_HIDE) If $ShellExecute <> 0 Then $sError = "FEHLER" $sStatus = " Es ist ein Fehler aufgetreten." Else $sError = "OK" $sStatus = " ... ist beendet" EndIf _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][1], $aSQL[$i][2], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) EndIf $aTasks = 0 Next Sleep(1000) _load() _SQLite_Close() _SQLite_Shutdown() _logmem("start_working END") EndFunc ;==>start_working Func _stop() _logmem("_stop BEGINNING") AdlibUnRegister("_compate_datetimes") $fScriptstatus = False GUICtrlSetBkColor($gfx, 0xFF0000) ;~ rot Return 0 _logmem("_stop END") EndFunc ;==>_stop Func _add_date($day_to_add) ;~ _logmem("_add_date BEGINNING") Local $iDays_of_month, $mday, $mon, $year If @MON = 01 Or @MON = 03 Or @MON = 03 Or @MON = 05 Or @MON = 07 Or @MON = 08 Or @MON = 10 Or @MON = 12 Then $iDays_of_month = 31 ; großer Monat ElseIf @MON = 04 Or @MON = 06 Or @MON = 09 Or @MON = 11 Then $iDays_of_month = 30 ; mittlerer Monat ElseIf @MON = 02 Then If Mod(@YEAR, 4) = 0 Then $iDays_of_month = 29 ; Schaltjahr Else $iDays_of_month = 28 ; kleiner Monat EndIf EndIf If @MDAY + $day_to_add > $iDays_of_month Then ; Wenn Tage größer als Monat, Monat erhöhen If @MON + 1 > 12 Then ; Wenn Monat größer als 12 wird, Jahr erhöhen $mday = "01" $mon = "01" $year = @YEAR + 1 Else $mday = "01" $mon = @MON + 1 $year = @YEAR EndIf Else $mday = @MDAY + $day_to_add $mon = @MON $year = @YEAR EndIf If StringLen($mday) < 2 Then $mday = "0" & $mday EndIf If StringLen($mon) < 2 Then $mon = "0" & $mon EndIf If StringLen($year) < 2 Then $year = "0" & $year EndIf Return $mday & "/" & $mon & "/" & $year ;~ _logmem("_add_date END") EndFunc ;==>_add_date Func _add_time($sStarttime, $admze) ;~~>> admze = count of minutes !! ;~ _logmem("_add_time BEGINNING") Local $aStarttime = StringSplit($sStarttime, ":") Local $min, $hour, $admze_std, $admze_min If $admze > 60 Then $admze_std = Int($admze / 60, 0) $admze_min = Int(Mod($admze, 60)) If $aStarttime[2] + $admze_min > 60 Then If $aStarttime[1] + $admze_std + 1 > 24 Then $min = ($aStarttime[2] + $admze_min) - 60 $hour = 0 Else $min = ($aStarttime[2] + $admze_min) - 60 $hour = $aStarttime[1] + $admze_std + 1 EndIf Else $min = $aStarttime[2] + $admze_min $hour = $aStarttime[1] + $admze_std EndIf Else If $aStarttime[2] + $admze > 60 Then If $aStarttime[1] + 1 > 24 Then $min = ($aStarttime[2] + $admze) - 60 $hour = 0 Else $min = ($aStarttime[2] + $admze) - 60 $hour = $aStarttime[1] + 1 EndIf ElseIf $aStarttime[2] + $admze = 60 Then If $aStarttime[1] + 1 > 24 Then $min = ($aStarttime[2] + $admze) - 60 $hour = 0 ElseIf $aStarttime[1] + 1 = 24 Then $min = "00" $hour = "00" Else $min = "00" $hour = $aStarttime[1] + 1 EndIf Else $min = $aStarttime[2] + $admze $hour = $aStarttime[1] EndIf EndIf If StringLen($min) < 2 Then $min = "0" & $min EndIf If StringLen($hour) < 2 Then $hour = "0" & $hour EndIf $aStarttime = 0 Return $hour & ":" & $min ;~ _logmem("_add_time END") EndFunc ;==>_add_time Func _sort_wday_array($ARRAY) ;~ _logmem("_sort_wday_array BEGINNING") If $ARRAY[1] = @WDAY Then Return $ARRAY Else For $i = 1 To $ARRAY[0] If $ARRAY[$i] > @WDAY Then $ARRAY = _sort_wday_array_increase($ARRAY) Return $ARRAY EndIf Next $ARRAY = _sort_wday_array_decrease($ARRAY) Return $ARRAY EndIf ;~ _logmem("_sort_wday_array END") EndFunc ;==>_sort_wday_array Func _sort_wday_array_increase($ARRAY) ;~ _logmem("_sort_wday_array_increase BEGINNING") Local $i _ArrayAdd($ARRAY, "") If $ARRAY[1] > @WDAY Then Else Do $ARRAY[$ARRAY[0] + 1] = $ARRAY[1] $i = 1 Do $ARRAY[$i] = $ARRAY[$i + 1] $i = $i + 1 Until $i = $ARRAY[0] + 1 Until $ARRAY[1] = @WDAY Or $ARRAY[1] > @WDAY EndIf _ArrayDelete($ARRAY, UBound($ARRAY)) Return $ARRAY ;~ _logmem("_sort_wday_array_increase END") EndFunc ;==>_sort_wday_array_increase Func _sort_wday_array_decrease($ARRAY) ;~ _logmem("_sort_wday_array_decrease BEGINNING") Local $i _ArrayAdd($ARRAY, "") Do $ARRAY[$ARRAY[0] + 1] = $ARRAY[1] $i = 1 Do $ARRAY[$i] = $ARRAY[$i + 1] $i = $i + 1 Until $i = $ARRAY[0] + 1 Until $ARRAY[1] = @WDAY Or $ARRAY[1] < @WDAY _ArrayDelete($ARRAY, UBound($ARRAY)) Return $ARRAY ;~ _logmem("_sort_wday_array_decrease END") EndFunc ;==>_sort_wday_array_decrease Func _new_datetime($ap_id) _logmem("_new_datetime BEGINNING") Local $Row, $Query, $day_to_add, $newDate, $TimeIncreased, $z $Query = _sqlitequeryceck($DB, "SELECT minute, wochentag, von, bis FROM zeitplan WHERE arbeitsplan_id=" & $ap_id) _SQLite_FetchData($Query, $Row) _sqliteerrorcheck(@error) _SQLite_QueryFinalize($Query) ;$Row[1] = $wochentag Local $aWday = StringSplit($Row[1], ",") $aWday = _sort_wday_array($aWday) If $Row[0] = "" Then ;FALL 1 - zu einer bestimmten Uhrzeit For $i = 1 To $aWday[0] If $aWday[$i] = @WDAY Then ;---Wochentag dabei--- If $Row[3] > @HOUR & ":" & @MIN & ":" & @SEC Then ;Fall 1.1 ;~ ConsoleWrite("Fall 1 . 1: Wochentag = Heute -> Uhrzeit noch nicht erreicht: " & @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[3] & @CR) Return @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[3] ElseIf $Row[3] < @HOUR & ":" & @MIN & ":" & @SEC Then ;Fall 1.2 If $Row[1] = @WDAY Then ;Nur der Wochentag $day_to_add = 7 Else If $aWday[2] = 1 Then $day_to_add = 7 - (($aWday[2] - @WDAY) * -1) ElseIf ($aWday[2] - @WDAY) > 0 Then $day_to_add = ($aWday[2] - @WDAY) ElseIf ($aWday[2] - @WDAY) < 0 Then $day_to_add = ($aWday[2] - @WDAY) + 7 EndIf EndIf $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 1 . 2: Wochentag = Heute -> Uhrzeit vorbei: " & $newDate & " " & $Row[3] & @CR) Return $newDate & " " & $Row[3] EndIf EndIf Next ;---Wochentag NICHT dabei--- # Fall 1.3 If ($aWday[1] - @WDAY) > 0 Then ; #1 $day_to_add = ($aWday[1] - @WDAY) $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 1 . 3 #1: " & $newDate & " " & $Row[3] & @CR) Return $newDate & " " & $Row[3] ElseIf ($aWday[1] - @WDAY) < 0 Then ; #2 $day_to_add = ($aWday[1] - @WDAY) + 7 $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 1 . 3 #2: " & $newDate & " " & $Row[3] & @CR) Return $newDate & " " & $Row[3] EndIf Else For $i = 1 To $aWday[0] ;---Wochentag dabei--- If $aWday[$i] = @WDAY Then If $Row[2] > @HOUR & ":" & @MIN & ":" & @SEC Then ;FALL 2.1 ;~ ConsoleWrite("Fall 2 . 1: Wochentag = Heute -> Uhrzeit noch nicht erreicht: " & @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[2] & @CR) Return @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[2] ElseIf $Row[2] < @HOUR & ":" & @MIN & ":" & @SEC And @HOUR & ":" & @MIN & ":" & @SEC < $Row[3] Then ;FALL 2.2 $z = 1 Do $TimeIncreased = _add_time($Row[2], ($Row[0] * $z)) $z = $z + 1 Until $TimeIncreased > @HOUR & ":" & @MIN & ":" & @SEC If ($TimeIncreased < $Row[3]) Or ($TimeIncreased = $Row[3]) Then ;FALL 2.2 ;~ ConsoleWrite("Fall 2 . 2 #1: " & @MDAY & "/" & @MON & "/" & @YEAR & " " & $TimeIncreased & @CR) Return @MDAY & "/" & @MON & "/" & @YEAR & " " & $TimeIncreased Else ;FALL 2.2 #2 $aWday = _sort_wday_array($aWday) If $aWday[2] = 1 Then $day_to_add = 7 - (($aWday[2] - $z) * -1) ElseIf ($aWday[2] - @WDAY) > 0 Then $day_to_add = ($aWday[2] - $z) ElseIf ($aWday[2] - @WDAY) < 0 Then $day_to_add = ($aWday[2] - $z) + 7 EndIf $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 2 #2: " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] EndIf ElseIf (@HOUR & ":" & @MIN & ":" & @SEC > $Row[3]) Or (@HOUR & ":" & @MIN & ":" & @SEC = $Row[3]) Then ;FALL 2.3 $aWday = _sort_wday_array($aWday) For $i = 0 To UBound($aWday) - 1 ;~ ConsoleWrite(@CR & "$aWday[$i]: " & $aWday[$i]) Next If $Row[1] = @WDAY Then ;Nur der Wochentag $day_to_add = 7 Else If $aWday[2] = 1 Then $day_to_add = 7 - (($aWday[2] - @WDAY) * -1) ElseIf ($aWday[2] - @WDAY) > 0 Then $day_to_add = ($aWday[2] - @WDAY) ElseIf ($aWday[2] - @WDAY) < 0 Then $day_to_add = ($aWday[2] - @WDAY) + 7 EndIf EndIf $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 3: " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] EndIf EndIf Next If ($aWday[1] - @WDAY) > 0 Then ; #1 $day_to_add = ($aWday[1] - @WDAY) $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 4 #1 " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] ElseIf ($aWday[1] - @WDAY) < 0 Then ; #2 $day_to_add = ($aWday[1] - @WDAY) + 7 $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 4 #2 " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] EndIf EndIf _logmem("_new_datetime END") EndFunc ; Hilfe MsgBox Func hilfe() MsgBox(64, "Hilfe", "Hilfe") ;~ COOMING SOON EndFunc ;==>hilfe ; Info MsgBox Func info() MsgBox(64, "Info", "Info") ;~ COOMING SOON EndFunc ;==>info Func _sqlitequeryceck($DB, $sQuery) Local $retQuery If _SQLite_Query($DB, $sQuery, $retQuery) <> 0 Then _sqliteerrorcheck(@error) _SQLite_QueryFinalize($retQuery) Return 0 EndIf Return $retQuery EndFunc ;==>_sqlitequeryceck Func _sqliteerrorcheck($sql_error) If $sql_error = -1 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". SQLite reported an error (Check return value)") ElseIf $sql_error = 1 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". Error calling SQLite API 'sqlite3_exec'") ElseIf $sql_error = 2 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". Call prevented by SafeMode") ElseIf $sql_error = 3 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". Error Processing Callback from within _SQLite_GetTable2d") ElseIf $sql_error = 4 Then ConsoleWriteError("Fehler, _SQLite_Exec Error: " & $sql_error & ". Error while converting SQL statement to UTF-8") EndIf EndFunc ;==>_sqliteerrorcheck Func _schreibe_log($sError, $sAp_id, $sAp_name, $sGeplante_startzeit, $sApp_name, $sBefehl, $sParameter, $sStatus) Local $hfile = FileOpen("log11111111111.txt", 1) ;Beispiel: [12.11.2012 16:14:30][OK] Arbeitsplan_ID: 1 | Arbeitsplan_Name: AP 1 | Geplante_Startzeit: 16:00 | Arbeitspunkt_Name: Hello World | Status: wurde gestartet FileWriteLine($hfile, "[" & @MDAY & "/" & @MON & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC & "][" & $sError & "] " & $sAp_id & " | " & _ $sAp_name & " | " & $sGeplante_startzeit & " | " & $sApp_name & " | " & $sBefehl & " " & $sParameter & " | " & $sStatus) FileClose($hfile) EndFunc ;==>_schreibe_log Func _logmem($sPos) Local $hfl = FileOpen($fl_name,1) If $hfl = -1 Then MsgBox(0,'Memstats File Open Error','File Name = ' & $fl_name) Local $aMem = _winapi_getprocessmemoryinfo() FileWrite($hfl, StringFormat('Current Private Space = %010i ' & $aMem[0], $aMem[1], $aMem[2], $aMem[3], $aMem[4], $aMem[5], $aMem[6], $aMem[7], $aMem[8], $aMem[9]) & " " & $sPos & " [" & _now() & "]" & @lf) FileClose($hfl) $hfl = 0 EndFunc Func _exit() _SQLite_Close($usedb) _SQLite_Shutdown() Exit EndFunc ;==>_exit And this is what my memlog.txt looks like:Current Private Space = 0030060544 7815 Scriptstart --------- [28.01.2013 14:32:52] Current Private Space = 0030371840 7897 _load BEGINNING [28.01.2013 14:32:53] Current Private Space = 0030371840 7897 _load SQL-1 [28.01.2013 14:32:53] Current Private Space = 0030429184 7911 _load SQL-1 [28.01.2013 14:32:53] Current Private Space = 0030429184 7911 _load SQL-2 [28.01.2013 14:32:53] Current Private Space = 0030859264 8016 _load SQL-2 [28.01.2013 14:32:53] Current Private Space = 0030871552 8019 _load before _Array2DSortFree [28.01.2013 14:32:53] Current Private Space = 0030871552 8019 _Array2DSortFree BEGINNING [28.01.2013 14:32:53] Current Private Space = 0030973952 8044 _Array2DSortFree END [28.01.2013 14:32:53] Current Private Space = 0030973952 8044 _load after _Array2DSortFree [28.01.2013 14:32:53] Current Private Space = 0030982144 8046 _load END [28.01.2013 14:32:53] Current Private Space = 0030990336 8048 start_working BEGINNING [28.01.2013 14:32:54] Current Private Space = 0031326208 8130 _new_datetime BEGINNING [28.01.2013 14:32:55] Current Private Space = 0031326208 8130 _load BEGINNING [28.01.2013 14:32:56] Current Private Space = 0031326208 8130 _load SQL-1 [28.01.2013 14:32:56] Current Private Space = 0031379456 8143 _load SQL-1 [28.01.2013 14:32:56] Current Private Space = 0031379456 8143 _load SQL-2 [28.01.2013 14:32:56] Current Private Space = 0031379456 8143 _load SQL-2 [28.01.2013 14:32:56] Current Private Space = 0031379456 8143 _load before _Array2DSortFree [28.01.2013 14:32:56] Current Private Space = 0031379456 8143 _Array2DSortFree BEGINNING [28.01.2013 14:32:56] Current Private Space = 0031432704 8156 _Array2DSortFree END [28.01.2013 14:32:56] Current Private Space = 0031432704 8156 _load after _Array2DSortFree [28.01.2013 14:32:56] Current Private Space = 0031432704 8156 _load END [28.01.2013 14:32:56] Current Private Space = 0031432704 8156 start_working END [28.01.2013 14:32:56] Current Private Space = 0031432704 8156 _stop BEGINNING [28.01.2013 14:32:57] Edited January 28, 2013 by MikeWenzel Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted January 28, 2013 Moderators Share Posted January 28, 2013 MikeWenzel, As I said earlier I am not very conversant with SQL so I cannot really help any more. But I know someone who is - I will point him here. M23  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
Yaerox Posted January 28, 2013 Author Share Posted January 28, 2013 That would be awesome. Thanks alot. Regards Link to comment Share on other sites More sharing options...
Yaerox Posted January 28, 2013 Author Share Posted January 28, 2013 (edited) I got an Information about ReDim - obvious my memory is rising around 100K every time on my second SQL Query+Fetch in my function _load(). $Query = _sqlitequeryceck($DB, "SELECT id, geplante_startzeit, arbeitsplan_id FROM aufgabenliste ORDER BY geplante_startzeit ASC") While _SQLite_FetchData($Query, $Row) = $SQLITE_OK ReDim $aQueue[$i + 1][4] ;x = Row # y = Column $aQueue[$i][0] = $Row[0] ; Column 0 - ID $a = StringSplit($Row[1], " ") $aQueue[$i][1] = $a[1] ; Column 1 - Datum $aQueue[$i][2] = $a[2] ; Column 2 - Uhrzeit $aQueue[$i][3] = $Row[2] ; Column 3 - Arbeitsplan ID $i = $i + 1 WEnd _SQLite_QueryFinalize($Query)Now someone told me, that ReDim will free the memory of the Array, but this memory is still allocated by the programm. Does someone know if this is really the case, and if it is, if there is a way to fix it except trying to rewrite my Code by using _SQLite_GetTable? I wrote a small testscript and I'd say ReDim isnt the Problem :/ expandcollapse popup#include <Array.au3> #include <GUIConstantsEx.au3> Opt("GUIOnEventMode", 1) Global $hGUI = GUICreate("GUI", 200, 200) GUISetState(@SW_SHOW, $hGUI) Global $hButton = GUICtrlCreateButton("GO", 5, 5, 80, 80) Global $hButton2 = GUICtrlCreateButton("_ArrayDisplay", 115, 5, 80, 80) Global $hLabel = GUICtrlCreateLabel("0", 100, 120, 100, 100) Global $aArray[1][3] GUICtrlSetOnEvent($hButton, "_go") GUICtrlSetOnEvent($hButton2, "_Array") GUISetOnEvent($GUI_EVENT_CLOSE, "_exit", $hGUI) While 1 Sleep(100) WEnd Func _go() GUICtrlSetData($hLabel, "0") For $i = 0 To 9999 Step 1 ReDim $aArray[$i+1][3] $aArray[$i][0] = Int(Random(0,100)) $aArray[$i][1] = Int(Random(0,100)) $aArray[$i][2] = Int(Random(0,100)) Next GUICtrlSetData($hLabel, "1") ;~ ReDim $aArray[1] EndFunc Func _Array() _ArrayDisplay($aArray) EndFunc Func _exit() Exit EndFunc Edited January 28, 2013 by MikeWenzel Link to comment Share on other sites More sharing options...
jchd Posted January 28, 2013 Share Posted January 28, 2013 No, ReDim is no issue here. In your last psted code, you still have multiple _SQLite_Startup and _SQLite_Open invokations at undesirable places. I'm currently cleaning that. This is at least one reason for the memory consumption you see. For instance you open a new connection every time the user hits the Load button. Also as I remember telling you some time ago, maybe in PM or in your original thread, you're making your life overly difficult by selecting a date-time format unsuitable to your needs. Then your code seems very cumbersome for what I wild-guess are relatively simple operations. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Yaerox Posted January 31, 2013 Author Share Posted January 31, 2013 Yeah jchd I know my way of handling date-time format is pretty bad ... I heared there is an easy way by comparing two date-times by just add them like: 2012-01-31 08:00:00 -> 20120131080000 or 2012-02-01 08:30:20 _> 20120201083020. I'll see if i can change this. Would you use SQLite Startup just once at the Beginning to start/open and once at the End to close/shutdown SQL? I thought I'd would be better to open the connection just if I need them and try to hold it closed the other time... Link to comment Share on other sites More sharing options...
jchd Posted January 31, 2013 Share Posted January 31, 2013 Sorry for not giving you an update on this but reverse-engineering the date/time code glue (don't take it bad) needed more time than I had. I guess that using _DateAdd could be almost all you need. This implies that the timestamps are stored in the right format in the DB, e.g. 2013/01/31 10:54:12. The priviledged format for timestamps computations in SQLite is slightly different 2013-01-31 10:54:12 but passing from the preferred AutoIt format to this one only needs a single function call: replace(mydate, '/', '-') in SQL or StringReplace($mydate, '/', '-') in AutoIt. Alternatively it's possible to store timestamps directly in some epoch format (seconds elapsed since some reference date) as integers. Delta times are then trivial to compute. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
guinness Posted January 31, 2013 Share Posted January 31, 2013 Epoch conversion functions. #include <Constants.au3> #include <Date.au3> Local $iEpoch = _DateToEpoch(_NowCalc()) MsgBox($MB_SYSTEMMODAL, '', $iEpoch) MsgBox($MB_SYSTEMMODAL, '', _EpochToDate($iEpoch)) Func _DateToEpoch($sDate) Return Int(_DateDiff('s', '1970/01/01 00:00:00', $sDate)) EndFunc ;==>_DateToEpoch Func _EpochToDate($iEpoch) Return _DateAdd('s', $iEpoch, '1970/01/01 00:00:00') EndFunc ;==>_EpochToDate UDF List:  _AdapterConnections() • _AlwaysRun() • _AppMon() • _AppMonEx() • _ArrayFilter/_ArrayReduce • _BinaryBin() • _CheckMsgBox() • _CmdLineRaw() • _ContextMenu() • _ConvertLHWebColor()/_ConvertSHWebColor() • _DesktopDimensions() • _DisplayPassword() • _DotNet_Load()/_DotNet_Unload() • _Fibonacci() • _FileCompare() • _FileCompareContents() • _FileNameByHandle() • _FilePrefix/SRE() • _FindInFile() • _GetBackgroundColor()/_SetBackgroundColor() • _GetConrolID() • _GetCtrlClass() • _GetDirectoryFormat() • _GetDriveMediaType() • _GetFilename()/_GetFilenameExt() • _GetHardwareID() • _GetIP() • _GetIP_Country() • _GetOSLanguage() • _GetSavedSource() • _GetStringSize() • _GetSystemPaths() • _GetURLImage() • _GIFImage() • _GoogleWeather() • _GUICtrlCreateGroup() • _GUICtrlListBox_CreateArray() • _GUICtrlListView_CreateArray() • _GUICtrlListView_SaveCSV() • _GUICtrlListView_SaveHTML() • _GUICtrlListView_SaveTxt() • _GUICtrlListView_SaveXML() • _GUICtrlMenu_Recent() • _GUICtrlMenu_SetItemImage() • _GUICtrlTreeView_CreateArray() • _GUIDisable() • _GUIImageList_SetIconFromHandle() • _GUIRegisterMsg() • _GUISetIcon() • _Icon_Clear()/_Icon_Set() • _IdleTime() • _InetGet() • _InetGetGUI() • _InetGetProgress() • _IPDetails() • _IsFileOlder() • _IsGUID() • _IsHex() • _IsPalindrome() • _IsRegKey() • _IsStringRegExp() • _IsSystemDrive() • _IsUPX() • _IsValidType() • _IsWebColor() • _Language() • _Log() • _MicrosoftInternetConnectivity() • _MSDNDataType() • _PathFull/GetRelative/Split() • _PathSplitEx() • _PrintFromArray() • _ProgressSetMarquee() • _ReDim() • _RockPaperScissors()/_RockPaperScissorsLizardSpock() • _ScrollingCredits • _SelfDelete() • _SelfRename() • _SelfUpdate() • _SendTo() • _ShellAll() • _ShellFile() • _ShellFolder() • _SingletonHWID() • _SingletonPID() • _Startup() • _StringCompact() • _StringIsValid() • _StringRegExpMetaCharacters() • _StringReplaceWholeWord() • _StringStripChars() • _Temperature() • _TrialPeriod() • _UKToUSDate()/_USToUKDate() • _WinAPI_Create_CTL_CODE() • _WinAPI_CreateGUID() • _WMIDateStringToDate()/_DateToWMIDateString() • Au3 script parsing • AutoIt Search • AutoIt3 Portable • AutoIt3WrapperToPragma • AutoItWinGetTitle()/AutoItWinSetTitle() • Coding • DirToHTML5 • FileInstallr • FileReadLastChars() • GeoIP database • GUI - Only Close Button • GUI Examples • GUICtrlDeleteImage() • GUICtrlGetBkColor() • GUICtrlGetStyle() • GUIEvents • GUIGetBkColor() • Int_Parse() & Int_TryParse() • IsISBN() • LockFile() • Mapping CtrlIDs • OOP in AutoIt • ParseHeadersToSciTE() • PasswordValid • PasteBin • Posts Per Day • PreExpand • Protect Globals • Queue() • Resource Update • ResourcesEx • SciTE Jump • Settings INI • SHELLHOOK • Shunting-Yard • Signature Creator • Stack() • Stopwatch() • StringAddLF()/StringStripLF() • StringEOLToCRLF() • VSCROLL • WM_COPYDATA • More Examples... Updated: 22/04/2018 Link to comment Share on other sites More sharing options...
Yaerox Posted January 31, 2013 Author Share Posted January 31, 2013 (edited) I changed my date-time format in my script like i said in my last reply and I was able to delete around 100 lines ... ill try to get this done in a better way in future. Now tried to get a first look on the main parts, fixing or holding down the memory and actual got a version where the memory seem to rise just around 300K in one hour. Sometimes after a few hours the memory got a stable point and it seems like it wont rise anymore. Ill wait now and see what happens ... For the case that it should run now by just rising maybe around a few MB per week i'd start to get this runnign in some my real mirror enviroment then i just have to restart my script maybe once a month, but this shouldn't be a Problem then. Any way ill try to find all bugs, but actual looking for solutions and bugs for 8 weeks and I had to start with other scripts too Regards Edited January 31, 2013 by MikeWenzel Link to comment Share on other sites More sharing options...
jchd Posted January 31, 2013 Share Posted January 31, 2013 Can you please post you current script and a copy of your DB? From there I'll have a close look at how things can improve and try fix the memory leak. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Yaerox Posted January 31, 2013 Author Share Posted January 31, 2013 Yeah sure: expandcollapse popup#include #include #include #include #include #include #include #include #include #include #include #include #include #include Opt("GUIOnEventMode", 1) Opt("MustDeclareVars", 1) OnAutoItExitRegister("_exit") Local $CheckingParameters = $CmdLine[0] If $CheckingParameters = 0 Then Global $usedb = IniRead("path_to\config.ini", "Datenbank", "pfad", "default") Global $path = IniRead("path_to\config.ini", "SQLite3.exe", "pfad", "default") Else Local $param = $CmdLine[1] Global $usedb = IniRead($param, "Datenbank", "pfad", "default") Global $path = IniRead($param, "SQLite3.exe", "pfad", "default") EndIf ; This is a flag to show whether we should check the timer or not Global $fCheck_Timer = True Global $iBegin, $fScriptstatus, $Row, $Query Dim $aQueue[1][4] _SQLite_Startup() Dim $DB = _SQLite_Open($usedb) ;#GUI 1 Global $hGUI = GUICreate("GUI", 250, 390) Global $hMenuFile = GUICtrlCreateMenu("File") Global $hMenuFileExit = GUICtrlCreateMenuItem("Exit", $hMenuFile) Global $hMenuHelp = GUICtrlCreateMenu("?") Global $hMenuHelpHelp = GUICtrlCreateMenuItem("Help", $hMenuHelp) Global $hMenuHelpInfo = GUICtrlCreateMenuItem("Info", $hMenuHelp) Global $iCounter = 0 Global $hCounter = GUICtrlCreateLabel("0", 50, 72, 200, 20) Global $gfx = GUICtrlCreateGraphic(5, 10, 18, 18) GUICtrlSetBkColor($gfx, 0xFF0000) GUICtrlSetColor($gfx, 0) Global $hBtnAdminTool = GUICtrlCreateButton("AdminTool", 160, 5, 80, 30) Global $hBtnLoad = GUICtrlCreateButton("Load", 140, 40, 100, 30) Global $astart_button = GUICtrlCreateButton("Start", 30, 5, 50, 30) Global $hBtnStart = GUICtrlCreateButton("Stop", 30, 40, 50, 30) GUISetState(@SW_SHOW, $hGUI) ;Aufgabenliste GUICtrlSetOnEvent($hMenuFileExit, "_exit") GUICtrlSetOnEvent($hMenuHelpHelp, "hilfe") GUICtrlSetOnEvent($hMenuHelpInfo, "info") GUICtrlSetOnEvent($hBtnLoad, "_load") GUICtrlSetOnEvent($astart_button, "_check") GUICtrlSetOnEvent($hBtnStart, "_stop") GUISetOnEvent($GUI_EVENT_CLOSE, "_exit", $hGUI) ; Set teh initial timestamp $iBegin = TimerInit() While 1 Sleep(100) WEnd Func _load() Dim $aStr Dim $a Local $i = 0 $Query = _sqlitequeryceck($DB, "SELECT a.id, a.geplante_startzeit, a.arbeitsplan_id, ap.arbeitsplan_name FROM aufgabenliste a, arbeitsplaene ap WHERE a.arbeitsplan_id=ap.arbeitsplan_id ORDER BY geplante_startzeit ASC") While _SQLite_FetchData($Query, $Row) = $SQLITE_OK ReDim $aQueue[$i + 1][4] ;x = Row # y = Column $aQueue[$i][0] = $Row[0] ; Column 0 - ID $aQueue[$i][1] = $Row[1] ; Column 1 - DatumUhrzeit $aQueue[$i][2] = $Row[2] ; Column 2 - Arbeitsplan ID $aQueue[$i][3] = $Row[2] ; Column 3 Arbeitsplan Name $i = $i + 1 WEnd _SQLite_QueryFinalize($Query) EndFunc ;==>_load Func _check() $fScriptstatus = True GUICtrlSetBkColor($gfx, 0x32CD32) AdlibRegister("_compate_datetimes", 1000) EndFunc ;==>_check Func _compate_datetimes() ; Should we check the timer? If $fCheck_Timer Then ; Have we waited 1 sec If TimerDiff($iBegin) > 1000 Then ; Prevent any checking while the function runs $fCheck_Timer = False If UBound($aQueue) = 0 Then _stop() Return 0 EndIf If $aQueue[0][1] = @YEAR & @MON & @MDAY & @HOUR & @MIN & @SEC OR $aQueue[0][1] < @YEAR & @MON & @MDAY & @HOUR & @MIN & @SEC Then start_working() EndIf $iCounter = $iCounter + 1 GUICtrlSetData($hCounter, $iCounter) $fCheck_Timer = True ; Reset the timestamp $iBegin = Timerinit() EndIf EndIf EndFunc ;==>_compate_datetimes Func start_working() Local $sError, $sStatus, $sNewStartDatetime, $ShellExecute, $aSQL[1][7], $i $Query = _sqlitequeryceck($DB, "SELECT a.arbeitsplan_id, a.geplante_startzeit, ap.arbeitsplan_name, app.arbeitspunkt_name, app.befehl, app.parameter, app.laufende_nr FROM aufgabenliste a, arbeitsplaene ap, arbeitspunkte app WHERE app.arbeitsplan_id=(SELECT arbeitsplan_id FROM aufgabenliste WHERE id=" & $aQueue[0][0] & ") AND app.status='Aktiv' AND a.arbeitsplan_id=(SELECT arbeitsplan_id FROM aufgabenliste WHERE id=" & $aQueue[0][0] & ") AND a.arbeitsplan_id=ap.arbeitsplan_id AND a.arbeitsplan_id=app.arbeitsplan_id AND app.status='Aktiv' ORDER BY laufende_nr ASC") While _SQLite_FetchData($Query, $Row) = $SQLITE_OK ReDim $aSQL[$i + 1][7] $aSQL[$i][0] = $Row[0] ; arbeitsplan_id $aSQL[$i][1] = $Row[1] ; geplante_startzeit $aSQL[$i][2] = $Row[2] ; arbeitsplan_name $aSQL[$i][3] = $Row[3] ; arbeitspunkt_name $aSQL[$i][4] = $Row[4] ; befehl $aSQL[$i][5] = $Row[5] ; parameter $aSQL[$i][6] = $Row[6] ; laufende_nr $i = $i + 1 WEnd _SQLite_QueryFinalize($Query) _SQLite_Exec($DB, "UPDATE aufgabenliste SET startzeit=" & @HOUR & @MIN & @SEC & " WHERE id=" & $aQueue[0][0]) For $i = 0 To UBound($aSQL) - 1 $sStatus = " ... wird gestartet" If $aSQL[$i][4] = "" Then $sError = " " _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][2], $aSQL[$i][1], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) $sNewStartDatetime = _new_datetime($aSQL[$i][0]) $sNewStartDatetime = StringMid($sNewStartDatetime, 7, 4) & StringMid($sNewStartDatetime, 4, 2) & StringMid($sNewStartDatetime, 1, 2) & StringMid($sNewStartDatetime, 12, 2) & StringMid($sNewStartDatetime, 15, 2) & "00" _SQLite_Exec($DB, "UPDATE aufgabenliste set geplante_startzeit='" & $sNewStartDatetime & "' WHERE arbeitsplan_id=" & $aSQL[$i][0]) _sqliteerrorcheck(@error) $sError = "OK" $sStatus = " ... ist beendet" _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][2], $aSQL[$i][1], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) Else $sError = " " _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][2], $aSQL[$i][1], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) $aSQL[$i][4] = StringRegExpReplace($aSQL[$i][4], "\\", "/") $ShellExecute = RunWait($aSQL[$i][4], "c:", @SW_HIDE) If $ShellExecute <> 0 Then $sError = "FEHLER" $sStatus = " Es ist ein Fehler aufgetreten." Else $sError = "OK" $sStatus = " ... ist beendet" EndIf _schreibe_log($sError, $aSQL[$i][0], $aSQL[$i][2], $aSQL[$i][1], $aSQL[$i][3], $aSQL[$i][4], $aSQL[$i][5], $sStatus) EndIf Next $iCounter = $iCounter + 1 GUICtrlSetData($hCounter, $iCounter) Sleep(1000) _load() EndFunc ;==>start_working Func _stop() AdlibUnRegister("_compate_datetimes") $fScriptstatus = False GUICtrlSetBkColor($gfx, 0xFF0000) ;~ rot Return 0 EndFunc ;==>_stop Func _add_date($day_to_add) ;~ _logmem("_add_date BEGINNING") Local $iDays_of_month, $mday, $mon, $year If @MON = 01 Or @MON = 03 Or @MON = 03 Or @MON = 05 Or @MON = 07 Or @MON = 08 Or @MON = 10 Or @MON = 12 Then $iDays_of_month = 31 ; großer Monat ElseIf @MON = 04 Or @MON = 06 Or @MON = 09 Or @MON = 11 Then $iDays_of_month = 30 ; mittlerer Monat ElseIf @MON = 02 Then If Mod(@YEAR, 4) = 0 Then $iDays_of_month = 29 ; Schaltjahr Else $iDays_of_month = 28 ; kleiner Monat EndIf EndIf If @MDAY + $day_to_add > $iDays_of_month Then ; Wenn Tage größer als Monat, Monat erhöhen If @MON + 1 > 12 Then ; Wenn Monat größer als 12 wird, Jahr erhöhen $mday = "01" $mon = "01" $year = @YEAR + 1 Else $mday = "01" $mon = @MON + 1 $year = @YEAR EndIf Else $mday = @MDAY + $day_to_add $mon = @MON $year = @YEAR EndIf If StringLen($mday) < 2 Then $mday = "0" & $mday EndIf If StringLen($mon) < 2 Then $mon = "0" & $mon EndIf If StringLen($year) < 2 Then $year = "0" & $year EndIf Return $mday & "/" & $mon & "/" & $year ;~ _logmem("_add_date END") EndFunc ;==>_add_date Func _add_time($sStarttime, $admze) ;~~>> admze = count of minutes !! ;~ _logmem("_add_time BEGINNING") Local $aStarttime = StringSplit($sStarttime, ":") Local $min, $hour, $admze_std, $admze_min If $admze > 60 Then $admze_std = Int($admze / 60, 0) $admze_min = Int(Mod($admze, 60)) If $aStarttime[2] + $admze_min > 60 Then If $aStarttime[1] + $admze_std + 1 > 24 Then $min = ($aStarttime[2] + $admze_min) - 60 $hour = 0 Else $min = ($aStarttime[2] + $admze_min) - 60 $hour = $aStarttime[1] + $admze_std + 1 EndIf Else $min = $aStarttime[2] + $admze_min $hour = $aStarttime[1] + $admze_std EndIf Else If $aStarttime[2] + $admze > 60 Then If $aStarttime[1] + 1 > 24 Then $min = ($aStarttime[2] + $admze) - 60 $hour = 0 Else $min = ($aStarttime[2] + $admze) - 60 $hour = $aStarttime[1] + 1 EndIf ElseIf $aStarttime[2] + $admze = 60 Then If $aStarttime[1] + 1 > 24 Then $min = ($aStarttime[2] + $admze) - 60 $hour = 0 ElseIf $aStarttime[1] + 1 = 24 Then $min = "00" $hour = "00" Else $min = "00" $hour = $aStarttime[1] + 1 EndIf Else $min = $aStarttime[2] + $admze $hour = $aStarttime[1] EndIf EndIf If StringLen($min) < 2 Then $min = "0" & $min EndIf If StringLen($hour) < 2 Then $hour = "0" & $hour EndIf $aStarttime = 0 Return $hour & ":" & $min ;~ _logmem("_add_time END") EndFunc ;==>_add_time Func _sort_wday_array($ARRAY) ;~ _logmem("_sort_wday_array BEGINNING") If $ARRAY[1] = @WDAY Then Return $ARRAY Else For $i = 1 To $ARRAY[0] If $ARRAY[$i] > @WDAY Then $ARRAY = _sort_wday_array_increase($ARRAY) Return $ARRAY EndIf Next $ARRAY = _sort_wday_array_decrease($ARRAY) Return $ARRAY EndIf ;~ _logmem("_sort_wday_array END") EndFunc ;==>_sort_wday_array Func _sort_wday_array_increase($ARRAY) ;~ _logmem("_sort_wday_array_increase BEGINNING") Local $i _ArrayAdd($ARRAY, "") If $ARRAY[1] > @WDAY Then Else Do $ARRAY[$ARRAY[0] + 1] = $ARRAY[1] $i = 1 Do $ARRAY[$i] = $ARRAY[$i + 1] $i = $i + 1 Until $i = $ARRAY[0] + 1 Until $ARRAY[1] = @WDAY Or $ARRAY[1] > @WDAY EndIf _ArrayDelete($ARRAY, UBound($ARRAY)) Return $ARRAY ;~ _logmem("_sort_wday_array_increase END") EndFunc ;==>_sort_wday_array_increase Func _sort_wday_array_decrease($ARRAY) ;~ _logmem("_sort_wday_array_decrease BEGINNING") Local $i _ArrayAdd($ARRAY, "") Do $ARRAY[$ARRAY[0] + 1] = $ARRAY[1] $i = 1 Do $ARRAY[$i] = $ARRAY[$i + 1] $i = $i + 1 Until $i = $ARRAY[0] + 1 Until $ARRAY[1] = @WDAY Or $ARRAY[1] < @WDAY _ArrayDelete($ARRAY, UBound($ARRAY)) Return $ARRAY ;~ _logmem("_sort_wday_array_decrease END") EndFunc ;==>_sort_wday_array_decrease Func _new_datetime($ap_id) Local $Row, $Query, $day_to_add, $newDate, $TimeIncreased, $z $Query = _sqlitequeryceck($DB, "SELECT minute, wochentag, von, bis FROM zeitplan WHERE arbeitsplan_id=" & $ap_id) _SQLite_FetchData($Query, $Row) _sqliteerrorcheck(@error) _SQLite_QueryFinalize($Query) ;$Row[1] = $wochentag Local $aWday = StringSplit($Row[1], ",") $aWday = _sort_wday_array($aWday) If $Row[0] = "" Then ;FALL 1 - zu einer bestimmten Uhrzeit For $i = 1 To $aWday[0] If $aWday[$i] = @WDAY Then ;---Wochentag dabei--- If $Row[3] > @HOUR & ":" & @MIN & ":" & @SEC Then ;Fall 1.1 ;~ ConsoleWrite("Fall 1 . 1: Wochentag = Heute -> Uhrzeit noch nicht erreicht: " & @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[3] & @CR) Return @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[3] ElseIf $Row[3] < @HOUR & ":" & @MIN & ":" & @SEC Then ;Fall 1.2 If $Row[1] = @WDAY Then ;Nur der Wochentag $day_to_add = 7 Else If $aWday[2] = 1 Then $day_to_add = 7 - (($aWday[2] - @WDAY) * -1) ElseIf ($aWday[2] - @WDAY) > 0 Then $day_to_add = ($aWday[2] - @WDAY) ElseIf ($aWday[2] - @WDAY) < 0 Then $day_to_add = ($aWday[2] - @WDAY) + 7 EndIf EndIf $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 1 . 2: Wochentag = Heute -> Uhrzeit vorbei: " & $newDate & " " & $Row[3] & @CR) Return $newDate & " " & $Row[3] EndIf EndIf Next ;---Wochentag NICHT dabei--- # Fall 1.3 If ($aWday[1] - @WDAY) > 0 Then ; #1 $day_to_add = ($aWday[1] - @WDAY) $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 1 . 3 #1: " & $newDate & " " & $Row[3] & @CR) Return $newDate & " " & $Row[3] ElseIf ($aWday[1] - @WDAY) < 0 Then ; #2 $day_to_add = ($aWday[1] - @WDAY) + 7 $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 1 . 3 #2: " & $newDate & " " & $Row[3] & @CR) Return $newDate & " " & $Row[3] EndIf Else For $i = 1 To $aWday[0] ;---Wochentag dabei--- If $aWday[$i] = @WDAY Then If $Row[2] > @HOUR & ":" & @MIN & ":" & @SEC Then ;FALL 2.1 ;~ ConsoleWrite("Fall 2 . 1: Wochentag = Heute -> Uhrzeit noch nicht erreicht: " & @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[2] & @CR) Return @MDAY & "/" & @MON & "/" & @YEAR & " " & $Row[2] ElseIf $Row[2] < @HOUR & ":" & @MIN & ":" & @SEC And @HOUR & ":" & @MIN & ":" & @SEC < $Row[3] Then ;FALL 2.2 $z = 1 Do $TimeIncreased = _add_time($Row[2], ($Row[0] * $z)) $z = $z + 1 Until $TimeIncreased > @HOUR & ":" & @MIN & ":" & @SEC If ($TimeIncreased < $Row[3]) Or ($TimeIncreased = $Row[3]) Then ;FALL 2.2 ;~ ConsoleWrite("Fall 2 . 2 #1: " & @MDAY & "/" & @MON & "/" & @YEAR & " " & $TimeIncreased & @CR) Return @MDAY & "/" & @MON & "/" & @YEAR & " " & $TimeIncreased Else ;FALL 2.2 #2 $aWday = _sort_wday_array($aWday) If $aWday[2] = 1 Then $day_to_add = 7 - (($aWday[2] - $z) * -1) ElseIf ($aWday[2] - @WDAY) > 0 Then $day_to_add = ($aWday[2] - $z) ElseIf ($aWday[2] - @WDAY) < 0 Then $day_to_add = ($aWday[2] - $z) + 7 EndIf $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 2 #2: " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] EndIf ElseIf (@HOUR & ":" & @MIN & ":" & @SEC > $Row[3]) Or (@HOUR & ":" & @MIN & ":" & @SEC = $Row[3]) Then ;FALL 2.3 $aWday = _sort_wday_array($aWday) For $i = 0 To UBound($aWday) - 1 ;~ ConsoleWrite(@CR & "$aWday[$i]: " & $aWday[$i]) Next If $Row[1] = @WDAY Then ;Nur der Wochentag $day_to_add = 7 Else If $aWday[2] = 1 Then $day_to_add = 7 - (($aWday[2] - @WDAY) * -1) ElseIf ($aWday[2] - @WDAY) > 0 Then $day_to_add = ($aWday[2] - @WDAY) ElseIf ($aWday[2] - @WDAY) < 0 Then $day_to_add = ($aWday[2] - @WDAY) + 7 EndIf EndIf $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 3: " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] EndIf EndIf Next If ($aWday[1] - @WDAY) > 0 Then ; #1 $day_to_add = ($aWday[1] - @WDAY) $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 4 #1 " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] ElseIf ($aWday[1] - @WDAY) < 0 Then ; #2 $day_to_add = ($aWday[1] - @WDAY) + 7 $newDate = _add_date($day_to_add) ;~ ConsoleWrite("Fall 2 . 4 #2 " & $newDate & " " & $Row[2] & @CR) Return $newDate & " " & $Row[2] EndIf EndIf EndFunc ; Hilfe MsgBox Func hilfe() MsgBox(64, "Hilfe", "Hilfe") ;~ COOMING SOON EndFunc ;==>hilfe ; Info MsgBox Func info() MsgBox(64, "Info", "Info") ;~ COOMING SOON EndFunc ;==>info Func _sqlitequeryceck($DB, $sQuery) Local $retQuery If _SQLite_Query($DB, $sQuery, $retQuery) <> 0 Then _sqliteerrorcheck(@error) _SQLite_QueryFinalize($retQuery) Return 0 EndIf Return $retQuery EndFunc ;==>_sqlitequeryceck Func _sqliteerrorcheck($sql_error) If $sql_error = -1 Then ConsoleWriteError(@CR & "~~>> Fehler, _SQLite_Exec Error: " & $sql_error & ". SQLite reported an error (Check return value)") ElseIf $sql_error = 1 Then ConsoleWriteError(@CR & "~~>> Fehler, _SQLite_Exec Error: " & $sql_error & ". Error calling SQLite API 'sqlite3_exec'") ElseIf $sql_error = 2 Then ConsoleWriteError(@CR & "~~>> Fehler, _SQLite_Exec Error: " & $sql_error & ". Call prevented by SafeMode") ElseIf $sql_error = 3 Then ConsoleWriteError(@CR & "~~>> Fehler, _SQLite_Exec Error: " & $sql_error & ". Error Processing Callback from within _SQLite_GetTable2d") ElseIf $sql_error = 4 Then ConsoleWriteError(@CR & "~~>> Fehler, _SQLite_Exec Error: " & $sql_error & ". Error while converting SQL statement to UTF-8") EndIf EndFunc ;==>_sqliteerrorcheck Func _schreibe_log($sError, $sAp_id, $sAp_name, $sGeplante_startzeit, $sApp_name, $sBefehl, $sParameter, $sStatus) Local $hfile = FileOpen("log.txt", 1) ;Beispiel: [12.11.2012 16:14:30][OK] Arbeitsplan_ID: 1 | Arbeitsplan_Name: AP 1 | Geplante_Startzeit: 16:00 | Arbeitspunkt_Name: Hello World | Status: wurde gestartet FileWriteLine($hfile, "[" & @MDAY & "/" & @MON & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC & "][" & $sError & "] " & $sAp_id & " | " & _ $sAp_name & " | " & $sGeplante_startzeit & " | " & $sApp_name & " | " & $sBefehl & " " & $sParameter & " | " & $sStatus) FileClose($hfile) EndFunc ;==>_schreibe_log Func _exit() _SQLite_Close($usedb) _SQLite_Shutdown() Exit EndFunc ;==>_exit Unfortunatly is this database filled up with german words :/ If you'll try to run it, you had to edit the table 'arbeitspunkte' on the column 'befehl' ... there has to be your path to the Batch-File. And you have to change the config.ini and a line on the Script @ iniRead.autoablaufstrg.rarconfig.rar Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now