Jump to content

SQLite problem

d0n

By d0n
in AutoIt General Help and Support

 Share

Recommended Posts

d0n Universalist

d0n

Posted
Posted

I am not sure what causes this but i have a script that lists out a sqlite database into listview, every once in a while the the database would be messed up and only showing some of the data. I can fix this by going into SQLite Expert Professional 2 and do a vacuum database and everything would be fine. Is this a problem with SQLite or my script?

d0n Universalist

d0n

Posted
  • Author
Posted 

#include <GUIConstantsEx.au3>
#include <GuiListView.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#Include <GuiImageList.au3>
#include <GUIConstantsEx.au3>
#include <GuiStatusBar.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <FF.au3>
#include <Inet.au3>
#Region ### START Koda GUI section ### Form=
Global $Complete
$Form1 = GUICreate("TVBXNOW Torrent Downloader", 475, 265)
$Status = _GUICtrlStatusBar_Create ($Form1)
$ListView1 = GUICtrlCreateListView("Name|Episode|Last Downloaded|Link", 28, 8, 438, 198)
_GUICtrlListView_SetExtendedListViewStyle($ListView1, BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_CHECKBOXES))
_GUICtrlListView_SetUnicodeFormat($ListView1, True)
;~ MsgBox("","",_GUICtrlListView_GetUnicodeFormat($ListView1))
_GUICtrlListView_SetColumnWidth($ListView1, 0, 160)
_GUICtrlListView_SetColumnWidth($ListView1, 1, 60)
_GUICtrlListView_SetColumnWidth($ListView1, 2, 130)
_GUICtrlListView_SetColumnWidth($ListView1, 3, 60)
$Button1 = GUICtrlCreateButton("Download", 8, 216, 75, 25, $WS_GROUP)
$Button2 = GUICtrlCreateButton("Add Link", 104, 216, 75, 25, $WS_GROUP)
$Button3 = GUICtrlCreateButton("Send Torrent", 390, 216, 75, 25, $WS_GROUP)
$Button4 = GUICtrlCreateButton("Open Link", 294, 216, 75, 25, $WS_GROUP)
$Button5 = GUICtrlCreateButton("Delete Link", 199, 216, 75, 25, $WS_GROUP)
$Button6 = GUICtrlCreateButton("(+)", 5, 8, 20, 25, $WS_GROUP)
$Button7 = GUICtrlCreateButton("(-)", 5, 40, 20, 25, $WS_GROUP)
$ButtonA = GUICtrlCreateButton("Up", 5, 80, 20, 25, $WS_GROUP)
$ButtonB = GUICtrlCreateButton("Dn", 5, 110, 20, 25, $WS_GROUP)

$Checkbox = GUICtrlCreateCheckbox("", 5, 190)
GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")

GUISetState(@SW_SHOW)

$hImage = _GUIImageList_Create(16, 16, 5, 3)
    _GUIImageList_AddIcon($hImage, @SystemDir & "\shell32.dll", 131);<== 0 = Red
    _GUIImageList_AddIcon($hImage, @SystemDir & "\shell32.dll", 137);<== 1 = Green
    _GUIImageList_AddIcon($hImage, @SystemDir & "\shell32.dll", 165);<== 2 = In Progress
    _GUICtrlListView_SetImageList($ListView1, $hImage, 1)
#EndRegion ### END Koda GUI section ###

If FileExists(@ScriptDir&"\Link.db") = 0 Then MsgBox("","","")

_SQLite_Startup()
_SQLite_Open(@ScriptDir & "\Link.db")
$Row = 1
Local $Name
While 1
    _SQLite_QuerySingleRow(-1, "SELECT Name,Episode,Last_Modify,Link FROM Link WHERE Rowid = '" & $Row & "'", $Name)
;~  ConsoleWrite($Row&" "&$Name[0]&@LF)
    If $Name[0] <> "" Then
        $Name[0] = BinaryToString(StringToBinary($Name[0], 1), 4)
        $index = _GUICtrlListView_AddItem($ListView1, $Name[0], 0)
        _GUICtrlListView_AddSubItem($ListView1, $index, $Name[1], 1)
        _GUICtrlListView_AddSubItem($ListView1, $index, $Name[2], 2)
        _GUICtrlListView_AddSubItem($ListView1, $index, $Name[3], 3)
        $Row = $Row + 1
    Else
        ExitLoop(1)
    EndIf
WEnd
_SQLite_Close()
_SQLite_Shutdown()

While 1
    $nMsg = GUIGetMsg()
    If $nMsg = $GUI_EVENT_CLOSE Then Exit
    If $nMsg = $Button1 Then Start_All()
    If $nMsg = $Button2 Then Add_Link()
    If $nMsg = $Button3 Then Send_Torrent()
    If $nMsg = $Button4 Then Open_Link()
    If $nMsg = $Button5 Then Delete()
    If $nMsg = $Button6 Then _GUICtrlListView_SetItemChecked($ListView1, -1, True)
    If $nMsg = $Button7 Then _GUICtrlListView_SetItemChecked($ListView1, -1, False)
    If $nMsg = $ButtonA Then Move_Up()
    If $nMsg = $ButtonB Then Move_Down()
WEnd

Func Delete()
    $Name = _GUICtrlListView_GetItemText(GUICtrlGetHandle($ListView1), _GUICtrlListView_GetSelectedIndices($ListView1))
    $User = MsgBox(4, "Delete "&$Name&"?", "Press Yes to DELETE")
    If $User = 6 Then
        _SQLite_Startup()
        _SQLite_Open(@ScriptDir & "\Link.db")
            _SQLite_Exec(-1, "DELETE FROM Link WHERE Name='"&$Name&"'")
        _SQLite_Close()
        _SQLite_Shutdown()
        _GUICtrlListView_DeleteItem(GUICtrlGetHandle($ListView1), _GUICtrlListView_GetSelectedIndices(GUICtrlGetHandle($ListView1)))
    EndIf
EndFunc

Func Start_All()
    $Need = 0
    $Complete = 0
    $begin = 0
    Do
        If TimerDiff($begin) > (5*60*1000) Then
            For $a = 0 To _GUICtrlListView_GetItemCount($ListView1)-1
                If _GUICtrlListView_GetItemChecked($ListView1, $a) = True Then
                    $Need = $Need + 1
                    _GUICtrlListView_SetItemSelected($ListView1, $a, True)
                    Start()
                EndIf
            Next
            $begin = TimerInit()
            Sleep(100)
        EndIf
    Until $Complete = $Need OR GUICtrlRead($Checkbox) = $GUI_UNCHECKED
EndFunc

Func Open_Link()
    $Select = _GUICtrlListView_GetSelectedIndices($ListView1)
    $Link = _GUICtrlListView_GetItemText(GUICtrlGetHandle($ListView1), $Select, 3)
    _FFConnect()
    _FFTabAdd($Link)
EndFunc

Func Move_Up()
    Local $Current, $Above
    $Select = _GUICtrlListView_GetSelectedIndices(GUICtrlGetHandle($ListView1))
    If $Select <> 0 Then
        _SQLite_Startup()
        _SQLite_Open(@ScriptDir & "\Link.db")
            _SQLite_QuerySingleRow(-1, "SELECT Name,Episode,Last_Modify,Link FROM Link WHERE Rowid = '" & $Select+1 & "'", $Current)
            _SQLite_QuerySingleRow(-1, "SELECT Name,Episode,Last_Modify,Link FROM Link WHERE Rowid = '" & $Select & "'", $Above)
            _SQLite_Exec(-1, "UPDATE Link SET Name='"&$Above[0]&"', Episode='"&$Above[1]&"', Last_Modify='"&$Above[2]&"', Link='"&$Above[3]&"' WHERE Rowid='"&$Select+1&"'")
            _SQLite_Exec(-1, "UPDATE Link SET Name='"&$Current[0]&"', Episode='"&$Current[1]&"', Last_Modify='"&$Current[2]&"', Link='"&$Current[3]&"' WHERE Rowid='"&$Select&"'")
        _SQLite_Close()
        _SQLite_Shutdown()
        $Current = _GUICtrlListView_GetItemTextArray($ListView1, -1)
        $Above = _GUICtrlListView_GetItemTextArray($ListView1, $Select-1)
        For $a = 1 To $Current[0]
            _GUICtrlListView_SetItemText($ListView1, $Select-1, $Current[$a], $a-1)
            _GUICtrlListView_SetItemText($ListView1, $Select, $Above[$a], $a-1)
        Next
        _GUICtrlListView_SetItemSelected(GUICtrlGetHandle($ListView1), $Select-1)
    EndIf
EndFunc

Func Move_Down()
    Local $Current, $Under
    $Select = _GUICtrlListView_GetSelectedIndices($ListView1)
    If $Select <> _GUICtrlListView_GetItemCount($ListView1)-1 Then
        _SQLite_Startup()
        _SQLite_Open(@ScriptDir & "\Link.db")
            _SQLite_QuerySingleRow(-1, "SELECT Name,Episode,Last_Modify,Link FROM Link WHERE Rowid = '" & $Select+1 & "'", $Current)
            _SQLite_QuerySingleRow(-1, "SELECT Name,Episode,Last_Modify,Link FROM Link WHERE Rowid = '" & $Select+2 & "'", $Under)
            _SQLite_Exec(-1, "UPDATE Link SET Name='"&$Under[0]&"', Episode='"&$Under[1]&"', Last_Modify='"&$Under[2]&"', Link='"&$Under[3]&"' WHERE Rowid='"&$Select+1&"'")
            _SQLite_Exec(-1, "UPDATE Link SET Name='"&$Current[0]&"', Episode='"&$Current[1]&"', Last_Modify='"&$Current[2]&"', Link='"&$Current[3]&"' WHERE Rowid='"&$Select+2&"'")
        _SQLite_Close()
        _SQLite_Shutdown()
        $Current = _GUICtrlListView_GetItemTextArray($ListView1, -1)
        $Under = _GUICtrlListView_GetItemTextArray($ListView1, $Select+1)
        For $a = 1 To $Current[0]
            _GUICtrlListView_SetItemText($ListView1, $Select+1, $Current[$a], $a-1)
            _GUICtrlListView_SetItemText($ListView1, $Select, $Under[$a], $a-1)
        Next
        _GUICtrlListView_SetItemSelected(GUICtrlGetHandle($ListView1), $Select+1)
    EndIf
EndFunc

Func Send_Torrent()
    FileMove(@DesktopDir&"\*.torrent","\_torrent.incomplete")
EndFunc

Func Add_Link()
    Local $Name, $Episode, $Link
    Dim $Add[1][4]
    $Add[0][0] = InputBox("Add Link", "Name of download", "", "", 250, 50)
    $Add[0][1] = InputBox("Add Link", "Episode number", "", "", 250, 50)
    $Add[0][3] = InputBox("Add Link", "HTML Link", "", "", 250, 50)
    If $Add[0][0] AND $Add[0][1] AND $Add[0][3] <> "" Then
        _SQLite_Startup()
        _SQLite_Open(@ScriptDir & "\Link.db")
            _GUICtrlListView_AddArray($ListView1, $Add)
            _SQLite_Exec(-1, "Insert into Link values ('"&$Add[0][0]&"',"&$Add[0][1]&","&"Null,'"&$Add[0][3]&"')")
            _GUICtrlStatusBar_SetText($Status, "Link added")
        _SQLite_Close()
        _SQLite_Shutdown()
    EndIf
EndFunc

Func Start()
    Local $Day, $Month
    $Error1 = False
    $Select = _GUICtrlListView_GetSelectedIndices($ListView1)
    _GUICtrlListView_SetItemImage($ListView1, $Select, 1)
    $Text = _GUICtrlListView_GetItemTextArray(GUICtrlGetHandle($ListView1), $Select)
    If Ping('www1.tvboxnow.com') = 0 OR Ping('www1.tvboxnow.com') > 4000 Then
        _GUICtrlListView_SetItemImage($ListView1, $Select, 0)
        _GUICtrlStatusBar_SetText($Status, "No Internet Connection")
    Else
        If StringLen($Text[2]) < 2 Then $Text[2] = "0"&$Text[2]
        $Mod_Text1 = $Text[1]
        $Mod_Text1 = StringReplace($Mod_Text1, "(", "\(")
        $Mod_Text1 = StringReplace($Mod_Text1, ")", "\)")
        If StringInstr($Text[4], "tvboxnow") = 0 Then
            If StringInStr($Text[2], "-") <> 0 Then
                $Start = StringLeft($Text[2], StringInStr($Text[2], "-")-1)
                $End = StringRight($Text[2], StringLen($Text[2])-StringInStr($Text[2], "-"))
                _FFStart("about:blank",Default,1,True)
                If $End > $Start Then
                    For $b = $Start To $End
                        If StringLen($b) < 2 Then $b = "0"&$b
                        _FFStart($Text[4],Default,2)
                        $change = StringRegExp(_FFReadHtml(), "\('(.*?)','(.*?)','(.*?)','(.*?)'\); return false.*?"&$Mod_Text1&"\s*?Ch?H?\s*?"&$b&".torrent", 1)
                        If @error = 0 Then
                            _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$b&".torrent] Found, begin download")
                            $Link = "http://www.myhdzone.net/dhdzone.php?a="&$change[0]&"&b="&$change[2]&"&d="&$change[1]&"&z=http%3A//"&$change[3]
                            _FFOpenURL($Link)
                            $source = _INetGetSource($Link)
                            $Link = StringRegExp($source, 'window.open\("(.*?)"\);', 1)
                            _FFOpenURL($Link[0])
                            _GUICtrlListView_SetItemChecked($ListView1, $Select, False)
                            If $Error1 <> True Then $Error1 = False
                        Else
                            _GUICtrlListView_SetItemImage($ListView1, $Select, 0)
                            $Error1 = True
                        EndIf
                    Next
                    If $Error1 = False Then _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] Downloaded")
                    If $Error1 = True Then _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] NOT ALL TORRENTS DOWNLOADED")
                EndIf
                _FFQuit()
                _FFDisConnect()
            Else
                _FFStart($Text[4],Default,1,True)
                $change = StringRegExp(_FFReadHtml(), "\('(.*?)','(.*?)','(.*?)','(.*?)'\); return false.*?"&$Mod_Text1&"\s*?Ch?H?\s*?"&$Text[2]&".torrent", 1)
                If @error = 0 Then
                    _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] Found, begin download")
                    $Link = "http://www.myhdzone.net/dhdzone.php?a="&$change[0]&"&b="&$change[2]&"&d="&$change[1]&"&z=http%3A//"&$change[3]
                    _FFSetValue("Thank you for sharing !", "message", "name")
                    _FFClick("postsubmit", "id")
                    _FFOpenURL($Link)
                    $source = _INetGetSource($Link)
                    $Link = StringRegExp($source, 'window.open\("(.*?)"\);', 1)
                    _FFOpenURL($Link[0])
                    _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] Downloaded")
                    _FFQuit()
                    _FFDisConnect()
                    _GUICtrlListView_SetItemImage($ListView1, $Select, 2)
                    _GUICtrlListView_SetItemText($ListView1, $Select, $Text[2]+1, 1)
                    _SQLite_Startup()
                    _SQLite_Open(@ScriptDir & "\Link.db")
                        _SQLite_QuerySingleRow(-1, "SELECT Day FROM Day WHERE Num = '" & @WDAY & "'", $Day)
                        _SQLite_QuerySingleRow(-1, "SELECT Month FROM Month WHERE Num = '" & @MON & "'", $Month)
                        $Date = $Day[0]&", "&$Month[0]&" "&@MDAY
                        _GUICtrlListView_SetItemText($ListView1, $Select, $Date, 2)
                        _SQLite_Exec(-1, "UPDATE Link SET Episode='"&$Text[2]+1&"', Last_Modify='"&$Day[0]&", "&$Month[0]&" "&@MDAY&"' WHERE Name='"&$Text[1]&"'")
                    _SQLite_Close()
                    _SQLite_Shutdown()
                    $Complete = $Complete + 1
                    If GUICtrlRead($Checkbox) = $GUI_CHECKED Then Send_Torrent()
                    _GUICtrlListView_SetItemChecked($ListView1, $Select, False)
                Else
                    _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] Not found")
                    _GUICtrlListView_SetItemImage($ListView1, $Select, 0)
                    _FFQuit()
                    _FFDisConnect()
                EndIf
            EndIf
        Else
            _GUICtrlStatusBar_SetText($Status, "Searching for ["&$Text[1]&" Ch"&$Text[2]&".torrent]...")
            $Source1 = _INetGetSource( $Text[4] )
;~          InetGet($Text[4], @DesktopDir&"\source.txt")
            $Mod_Text1 = BinaryToString(StringToBinary($Mod_Text1, 4), 1)
            If StringInStr($Text[2], "-") <> 0 Then
                $Start = StringLeft($Text[2], StringInStr($Text[2], "-")-1)
                $End = StringRight($Text[2], StringLen($Text[2])-StringInStr($Text[2], "-"))
                If $End > $Start Then
                    For $b = $Start To $End
                        If StringLen($b) < 2 Then $b = "0"&$b
                        $Link = StringRegExp($Source1, "(?i)href[\s=]+[\x22\x29]*(.+?)[\x22\x29\s].*"&$Mod_Text1&"\s*?Ch?H?\s*?"&$b&".*?.torrent" , 1)
                        If @error = 0 Then
                            _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$b&".torrent] Found, begin download")
                            $Source2 = _INetGetSource( 'http://www1.tvboxnow.com/'&$Link[0])
                            $Link = StringRegExp($Source2, "<p><a href[\s=]+[\x22\x29]*(.+?)[\x22\x29\s]", 1)
                            InetGet ( 'http://www1.tvboxnow.com/'&$Link[0], @DesktopDir&"\"&$Text[1]&' Ch'&$b&'.torrent' )
                            _GUICtrlListView_SetItemChecked($ListView1, $Select, False)
                            If $Error1 <> True Then $Error1 = False
                        Else
                            _GUICtrlListView_SetItemImage($ListView1, $Select, 0)
                            $Error1 = True
                        EndIf
                    Next
                    If $Error1 = False Then _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] Downloaded")
                    If $Error1 = True Then _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] NOT ALL TORRENTS DOWNLOADED")
                EndIf
            ElseIf StringInStr($Text[2], ",") <> 0 Then
                $Split = StringSplit($Text[2], ",")
                For $a = 1 to $Split[0]
                    If StringLen($Split[$a]) < 2 Then $Split[$a] = "0"&$Split[$a]
                    $Link = StringRegExp($Source1, "(?i)href[\s=]+[\x22\x29]*(.+?)[\x22\x29\s].*"&$Mod_Text1&"\s*?Ch?H?\s*?"&$Split[$a]&".*?.torrent" , 1)
                    If @error = 0 Then
                        _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Split[$a]&".torrent] Found, begin download")
                        $Source2 = _INetGetSource( 'http://www1.tvboxnow.com/'&$Link[0])
                        $Link = StringRegExp($Source2, "<p><a href[\s=]+[\x22\x29]*(.+?)[\x22\x29\s]", 1)
                        InetGet ( 'http://www1.tvboxnow.com/'&$Link[0], @DesktopDir&"\"&$Text[1]&' Ch'&$Split[$a]&'.torrent' )
                        _GUICtrlListView_SetItemChecked($ListView1, $Select, False)
                        If $Error1 <> True Then $Error1 = False
                    Else
                        _GUICtrlListView_SetItemImage($ListView1, $Select, 0)
                        $Error1 = True
                    EndIf
                    If $Error1 = False Then _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Split[$a]&".torrent] Downloaded")
                    If $Error1 = True Then _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] NOT ALL TORRENTS DOWNLOADED")
                Next
            Else
                $Link = StringRegExp($Source1, "(?i)href[\s=]+[\x22\x29]*(.+?)[\x22\x29\s].*"&$Mod_Text1&"\s*?Ch?H?\s*?"&$Text[2]&".*?.torrent" , 1)
                If @error = 0 Then
                    _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] Found, begin download")
                    _GUICtrlListView_SetItemImage($ListView1, $Select, 1)
                    $Source2 = _INetGetSource( 'http://www1.tvboxnow.com/'&$Link[0])
                    $Link = StringRegExp($Source2, "<p><a href[\s=]+[\x22\x29]*(.+?)[\x22\x29\s]", 1)
                    InetGet ( 'http://www1.tvboxnow.com/'&$Link[0], @DesktopDir&"\"&$Text[1]&' Ch'&$Text[2]&'.torrent' )
                    _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] Downloaded")
                    _GUICtrlListView_SetItemImage($ListView1, $Select, 2)
                    _GUICtrlListView_SetItemText($ListView1, $Select, $Text[2]+1, 1)
                    _SQLite_Startup()
                    _SQLite_Open(@ScriptDir & "\Link.db")
                        _SQLite_QuerySingleRow(-1, "SELECT Day FROM Day WHERE Num = '" & @WDAY & "'", $Day)
                        _SQLite_QuerySingleRow(-1, "SELECT Month FROM Month WHERE Num = '" & @MON & "'", $Month)
                        $Date = $Day[0]&", "&$Month[0]&" "&@MDAY
                        _GUICtrlListView_SetItemText($ListView1, $Select, $Date, 2)
                        $Mod_Text1 = StringReplace($Mod_Text1, "\(", "(")
                        $Mod_Text1 = StringReplace($Mod_Text1, "\)", ")")
                        _SQLite_Exec(-1, "UPDATE Link SET Episode='"&$Text[2]+1&"', Last_Modify='"&$Day[0]&", "&$Month[0]&" "&@MDAY&"' WHERE Name='"&$Mod_Text1&"'")
                    _SQLite_Close()
                    _SQLite_Shutdown()
                    $Complete = $Complete + 1
                    If GUICtrlRead($Checkbox) = $GUI_CHECKED Then Send_Torrent()
                    _GUICtrlListView_SetItemChecked($ListView1, $Select, False)
                Else
                    _GUICtrlStatusBar_SetText($Status, "["&$Text[1]&" Ch"&$Text[2]&".torrent] Not found")
                    _GUICtrlListView_SetItemImage($ListView1, $Select, 0)
                EndIf
            EndIf
        EndIf
    EndIf
EndFunc

Func WM_NOTIFY($hWnd, $iMsg, $iwParam, $ilParam)
    #forceref $hWnd, $iMsg, $iwParam
    Local $hWndFrom, $iIDFrom, $iCode, $tNMHDR, $hWndListView, $tInfo, $Text, $Column, $Section, $Key
    $hWndListView = $ListView1
    If Not IsHWnd($ListView1) Then $hWndListView = GUICtrlGetHandle($ListView1)

    $tNMHDR = DllStructCreate($tagNMHDR, $ilParam)
    $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))
    $iIDFrom = DllStructGetData($tNMHDR, "IDFrom")
    $iCode = DllStructGetData($tNMHDR, "Code")
    Switch $hWndFrom
        Case $hWndListView
            Switch $iCode
                Case $NM_DBLCLK ; Sent by a list-view control when the user clicks an item with the left mouse button
                    $tInfo = DllStructCreate($tagNMITEMACTIVATE, $ilParam)
                    If DllStructGetData($tInfo, "Index") <> -1 Then
                        $Column = _GUICtrlListView_GetColumn($ListView1, DllStructGetData($tInfo, "Subitem"))
                        $Text = _GUICtrlListView_GetItemText($ListView1, DllStructGetData($tInfo, "Index"), DllStructGetData($tInfo, "Subitem"))
                        $Input = InputBox("Modify "&$Column[5], "Enter new "&$Column[5], $Text, "", 250, 50)
                        If $Input <> "" Then
                            $Input = BinaryToString(StringToBinary($Input , 4), 1)
                            _SQLite_Startup()
                            _SQLite_Open(@ScriptDir & "\Link.db")
                            Select
                                Case DllStructGetData($tInfo, "Subitem") = 0
                                    _SQLite_Exec(-1, "UPDATE Link SET Name='"&$Input&"' WHERE Rowid='"&DllStructGetData($tInfo, "Index")+1&"'")
                                Case DllStructGetData($tInfo, "Subitem") = 1
                                    _SQLite_Exec(-1, "UPDATE Link SET Episode='"&$Input&"' WHERE Rowid='"&DllStructGetData($tInfo, "Index")+1&"'")
                                Case DllStructGetData($tInfo, "Subitem") = 3
                                    _SQLite_Exec(-1, "UPDATE Link SET Link='"&$Input&"' WHERE Rowid='"&DllStructGetData($tInfo, "Index")+1&"'")
                            EndSelect
                            _SQLite_Close()
                            _SQLite_Shutdown()
                            _GUICtrlListView_SetItemText($ListView1, DllStructGetData($tInfo, "Index"), $Input, DllStructGetData($tInfo, "Subitem"))
                        EndIf
                    EndIf
            EndSwitch
    EndSwitch
EndFunc   ;==>WM_NOTIFY

jchd Universalist

jchd

Posted
Posted

I am not sure what causes this but i have a script that lists out a sqlite database into listview, every once in a while the the database would be messed up and only showing some of the data. I can fix this by going into SQLite Expert Professional 2 and do a vacuum database and everything would be fine. Is this a problem with SQLite or my script?

Hi,

I've "some" experience with both SQL support in AutoIt and SQLite Expert Pro. I can assure you this can be made a really powerful combination with no problem. I use both daily for our production databases which are business critical. You can safely assume SQLite to be exempt of this kind of problems: SQLite is being used for _very_ large bases around the world on server with heavy load.

Now your script is a bit large for me to analyse and go down to pinpointing your problem. But from a quick look, there are potential problems.

Invoking _SQLite_Startup multiple times is likely to waste much cycles. Do that at your program startup and use OnAutoItExitRegister("_SQLite_ShutDown").

The same remark applies to multiple _SQLite_Open / Close. You should open your base at program startup and use OnAutoItExitRegister() to register a function to close the connection. This can only make your application more responsive.

Your code has a problem, where you omit escaping string litterals included in SQL. You must do so using _SQLite_Escape. If you don't and when your string contains a single quote, then you're passing a truncated string to SQLite. Reminder: SQL string litterals are enclosed in single quotes and single quotes inside the litteral must be escaped by doubling each of them.

Correct SQL statement: "select foo, bar from tbl where foo like 'Ocean''s Eleven';"

Correct AutoIt statement: $err = _SQLite_GetTable(-1, "select foo, bar from tbl where foo like " & _SQLite_Escape("Ocean's Eleven") & ";", $aFooBar, $iRows, $iCols)

Of course in practice, your litteral will be a variable most of the times, but you need to go thru _SQLite_Escape as well.

When you perform read/modify/write operations on a database, wrap your SQL statements inside a transaction, preferably imune to deadlocks.

SQL:: begin immediate;

SQL:: select some_data ......

modify data ......

SQL:: insert, update and/or delete .......

commit;

Note that this type of transactions don't nest in SQLite.

This way, you're sure that either the whole block of database operations wnet well and is now secured on disk, or the database has been rolled back to previous state.

Then, _every_ call to SQLite really should check for error condition. This is the only way to know something got wrong, or didn't produce the expected result.

Now I don't see what could be the relationship between truncated rowsets and Vacuum. I bet this is something with your code. Can you give answers to the following questions:

Are other processes using the database?

What is you schema?

Are you getting any AutoIt or SQL error before this happens?

Is your base stored on a local disk or on a remote machine (i.e. where is @scriptdir)?

What does SQLite Expert tell you when you check database integrity before vacuum?

When you say that after a vacuum "everything would be fine" do you mean that your data shows up again?

Be sure to use the latest release of SQLite Expert.

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 here
RegExp tutorial: enough to get started
PCRE 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)

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...