Jump to content
Sign in to follow this  
Athfar

Getting SQLite Working

Recommended Posts

I seems like you need to do a lot of research on web-based databases. SQLite, as far as I know, is like MySql, which I have been using along with php on some of my webpages. What are you trying to accomplish or what are you interested in - making applications or storing data online and web-building? You could probably find out a lot about SQLite via google.com. You don't need to set up your computer as a server to use MySql (and probably SQLite) but you do to use php. :)

Share this post


Link to post
Share on other sites

Sorry if I am just missing something simple... But I can't find the ".dll" or have any clue how to get the examples working here: http://www.autoitscript.com/forum/index.php?showtopic=17099

I have never used SQLite but my impression is it is just a text based database storage with some SQL functionality.

Any help would be great... Thanks! :)

The DLL can be downloaded from www.sqlite.org, but you might get a newer version than the AutoIt UDF currently supports that way. The UDF actually has the DLL included. It has the binary file converted into string-Hex (SQLite.dll.au3) and if you don't provide a path to the actual binary, it will convert that back to binary as a temp file and use it. See the remarks under _SQLite_Startup():

Remarks

Requires SQLite3.dll in @ScriptDir or @SystemDir.

If SQLite3.dll.au3 is included the dll will be created in @SystemDir.

If not successful a temporary file will be created that will be destroyed at _SQLite_Shutdown().

^_^


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

No I understand how SQLite works and have created a database (though I did miss the dll being packed with SQLite)...

My problem lies with the examples provided with SQLite...

example_browser.au3 in particular...

The first error I get is GuiList.au3 cannot be found.

I changed that to GuiListBox.au3 and now I get the error:

_GUICtrlListClear ($gui_Tables). Unknown function name.

I am just looking for an example as a quick start in order to start using SQLite with ListViews (editable if possible) and eventually saving SQLite to an Excel File.

example_browser.au3

Edited by Athfar

Share this post


Link to post
Share on other sites

I seems like you need to do a lot of research on web-based databases. SQLite, as far as I know, is like MySql, which I have been using along with php on some of my webpages. What are you trying to accomplish or what are you interested in - making applications or storing data online and web-building? You could probably find out a lot about SQLite via google.com. You don't need to set up your computer as a server to use MySql (and probably SQLite) but you do to use php. :)

The big difference is that MySQL (or PostgreSQL, or MS SQL, Oracle, Sybase, etc.) are all installed as (very heavy) services. SQLite has no back end service. You can carry it around on a USB thumb drive and run with it witout installing anything on Windows. You don't even have to register SQLite.dll, just be able to find it (or let the UDF put a temp copy on the HDD, as above). The clients do all the work, including record locking when more than one accesses the same database.

This makes SQLite inappropriate for some heavier uses, but ideal for light-weight portable stuff like you might compile in AutoIt.

Their web site is very up-front on the topic: Appropriate Uses For SQLite

^_^


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Also, the SQLite GUI seems like a pretty sweet example to work with but it throws COM examples (After loading either of the Excelcom.au3)...

Has anyone had trouble with these?

Share this post


Link to post
Share on other sites

Also, the SQLite GUI seems like a pretty sweet example to work with but it throws COM examples (After loading either of the Excelcom.au3)...

Has anyone had trouble with these?

Share this post


Link to post
Share on other sites

Also, the SQLite GUI seems like a pretty sweet example to work with but it throws COM exceptions (After loading either of the Excelcom.au3)...

Has anyone had trouble with these?

SQLite_Gui.au3

Edited by Athfar

Share this post


Link to post
Share on other sites

No I understand how SQLite works and have created a database (though I did miss the dll being packed with SQLite)...

My problem lies with the examples provided with SQLite...

example_browser.au3 in particular...

The first error I get is GuiList.au3 cannot be found.

I changed that to GuiListBox.au3 and now I get the error:

_GUICtrlListClear ($gui_Tables). Unknown function name.

I am just looking for an example as a quick start in order to start using SQLite with ListViews (editable if possible) and eventually saving SQLite to an Excel File.

AutoIt recently went through some major changes. There was some breakage of backwards compatiblity in the GUICtrl* management UDFs, including GUIListBox.au3. The demo has not been updated yet. So you can run it with an older version, or try this tweak I just did:

; Updated for compatibility with AutoIt 3.2.10.0 version of GuiListBox.au3 by PsaltyDS 17Dec07.

#NoTrayIcon
#include <GUIConstants.au3>
#include <sqlite.au3>
#include <sqlite.dll.au3>
#Include <GuiListBox.au3>
#Include <GuiListView.au3>
#include <Misc.au3>
_Singleton("a89s76da9sd768")

#AutoIt3Wrapper_useupx=n

Global $gui_Form, $gui_Log, $gui_InputQuery, $gui_SendQuery, $gui_Result, $gui_Tables, $gui_filemenu, $gui_fileopen, $gui_TblContexDelDat, $gui_TblContexSelectCnt, $gui_TblContexHtml
Global $gui_optGetTable, $gui_optmenu, $gui_optQuery, $gui_funcChanges, $gui_funcLastInsertId, $gui_funcMenu, $gui_funcRepQuery, $gui_optPragmaInChk, $gui_optPragmaSyn
Global $gui_TblContexDrop, $gui_TblContexMenu, $gui_TblContexSelect, $gui_ResultContexCopy = -1, $gui_ResultContexMenu, $gui_TblContexRndIns
$gui_Form = GUICreate("SQLite Browser", 622, 460, 199, 135, BitOR($GUI_SS_DEFAULT_GUI, $WS_SIZEBOX, $WS_MAXIMIZEBOX))
$gui_Log = GUICtrlCreateEdit("", 8, 368, 609, 70, BitOR($WS_VSCROLL, $ES_AUTOVSCROLL, $ES_READONLY), $WS_EX_CLIENTEDGE)
GUICtrlSetFont(-1, 7)
GUICtrlSetResizing(-1, $GUI_DOCKSTATEBAR)
$gui_InputQuery = GUICtrlCreateCombo("SELECT * FROM sqlite_master;", 8, 336, 561, 21, BitOR($GUI_SS_DEFAULT_COMBO, $CBS_DROPDOWN), $WS_EX_CLIENTEDGE)
GUICtrlSetResizing(-1, $GUI_DOCKSTATEBAR)
$gui_SendQuery = GUICtrlCreateButton("&Query", 576, 336, 41, 21, $BS_DEFPUSHBUTTON)
GUICtrlSetState(-1, $GUI_DISABLE)
GUICtrlSetResizing(-1, $GUI_DOCKSTATEBAR)
$gui_Result = GUICtrlCreateListView("", 131, 8, 485, 305, BitOR($LVS_SHOWSELALWAYS, $LVS_NOSORTHEADER), BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT))
$hGui_Result = ControlGetHandle($gui_Form, "", $gui_Result)
GUICtrlSetResizing(-1, $GUI_DOCKBORDERS)
$gui_Tables = GUICtrlCreateList("", 8, 8, 121, 318, -1, $WS_EX_CLIENTEDGE)
$hGui_Tables = ControlGetHandle($gui_Form, "", $gui_Tables)
GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKBOTTOM + $GUI_DOCKWIDTH)
$gui_TblContexMenu = GUICtrlCreateContextMenu($gui_Tables)
$gui_TblContexSelect = GUICtrlCreateMenuItem("Select Table", $gui_TblContexMenu)
GUICtrlSetState(-1, $GUI_DEFBUTTON)
$gui_TblContexHtml = GUICtrlCreateMenuItem("Show Table in Browser", $gui_TblContexMenu)
$gui_TblContexSelectCnt = GUICtrlCreateMenuItem("Count Rows", $gui_TblContexMenu)
$gui_TblContexDrop = GUICtrlCreateMenuItem("Drop Table", $gui_TblContexMenu)
$gui_TblContexInsert = GUICtrlCreateMenuItem("Build Insert", $gui_TblContexMenu)
$gui_TblContexRndIns = GUICtrlCreateMenuItem("Insert 'random(*)' Data", $gui_TblContexMenu)
$gui_TblContexDelDat = GUICtrlCreateMenuItem("Delete Data", $gui_TblContexMenu)
$gui_filemenu = GUICtrlCreateMenu("&File")
$gui_fileopen = GUICtrlCreateMenuItem("Open", $gui_filemenu)
GUICtrlSetState(-1, $GUI_DEFBUTTON)
$gui_fileClose = GUICtrlCreateMenuItem("Close", $gui_filemenu)
GUICtrlSetState(-1, $GUI_DISABLE)
$gui_optmenu = GUICtrlCreateMenu("&Options")
$gui_optGetTable = GUICtrlCreateMenuItem("Use GetTable", $gui_optmenu)
GUICtrlSetState(-1, $GUI_CHECKED)
$gui_optQuery = GUICtrlCreateMenuItem("Use Prepare,Step", $gui_optmenu)
GUICtrlCreateMenuItem("", $gui_optmenu)
$gui_optPragmaSyn = GUICtrlCreateMenuItem("PRAGMA synchronous;", $gui_optmenu)
$gui_optPragmaInChk = GUICtrlCreateMenuItem("PRAGMA integrity_check;", $gui_optmenu)
$gui_funcMenu = GUICtrlCreateMenu("F&unctions")
$gui_funcLastInsertId = GUICtrlCreateMenuItem("Last Insert RowID", $gui_funcMenu)
$gui_funcChanges = GUICtrlCreateMenuItem("Changes", $gui_funcMenu)
$gui_funcRepQuery = GUICtrlCreateMenuItem("Repeat Query", $gui_funcMenu)
GUISetState(@SW_SHOW)

_SQLite_Startup()
If @error > 0 Then
    MsgBox(0, @error, "Error loading SQLite.dll")
    Exit
EndIf
_log("SQLite Version: " & _SQLite_LibVersion(), 0)

Local $sDBFileName, $tmp
While 1
    $msg = GUIGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop
        Case $msg = $gui_fileopen
            $sDBFileName = FileOpenDialog("Select Database, Nonexistend will be Created", @ScriptDir, "db Files (*.db)|All (*.*)", 0, "test.db")
            If @error > 0 Then $sDBFileName = ""
            _SQLite_Open($sDBFileName)
            If @error > 0 Then
                _log("Error opening " & $sDBFileName)
                ContinueLoop
            EndIf
            GUICtrlSetState($gui_fileopen, $GUI_DISABLE)
            GUICtrlSetState($gui_fileClose, $GUI_ENABLE)
            _updatetables()
            GUICtrlSetState($gui_SendQuery, $GUI_ENABLE)
        Case $msg = $gui_Tables Or $msg = $gui_TblContexSelect
            Local $sSelectedTable = GUICtrlRead($gui_Tables)
            GUICtrlSetData($gui_InputQuery, "SELECT ROWID AS RowID,* FROM " & $sSelectedTable & ";", "SELECT ROWID AS RowID,* FROM " & $sSelectedTable & ";")
        Case $msg = $gui_TblContexDelDat
            Local $sSelectedTable = GUICtrlRead($gui_Tables)
            GUICtrlSetData($gui_InputQuery, "DELETE FROM " & $sSelectedTable & ";", "DELETE FROM " & $sSelectedTable & ";")
        Case $msg = $gui_optPragmaInChk
            GUICtrlSetData($gui_InputQuery, "PRAGMA integrity_check;", "PRAGMA integrity_check;")
        Case $msg = $gui_optPragmaSyn
            GUICtrlSetData($gui_InputQuery, "PRAGMA synchronous;", "PRAGMA synchronous;")
        Case $msg = $gui_TblContexSelectCnt
            Local $sSelectedTable = GUICtrlRead($gui_Tables)
            GUICtrlSetData($gui_InputQuery, "SELECT count() as RowCount FROM " & $sSelectedTable & ";", "SELECT count() as RowCount FROM " & $sSelectedTable & ";")
        Case $msg = $gui_TblContexHtml
            _BrowseTable(GUICtrlRead($gui_Tables))
        Case $msg = $gui_SendQuery
            Local $nTimerQuery
            GUICtrlSetState($gui_SendQuery, $GUI_DISABLE)
            GUICtrlSetData($gui_InputQuery, GUICtrlRead($gui_InputQuery))
            $nTimerQuery = TimerInit()
            If BitAND(GUICtrlRead($gui_optGetTable), $GUI_CHECKED) Then
                _sqlGetTable(GUICtrlRead($gui_InputQuery))
            Else
                _sqlQuery(GUICtrlRead($gui_InputQuery))
            EndIf
            _log("Total Query Time: " & Round(TimerDiff($nTimerQuery), 2) & " mSec.")
            _updatetables()
            GUICtrlSetState($gui_SendQuery, $GUI_ENABLE)
        Case $msg = $gui_funcRepQuery
            Local $nTimerQuery, $nRepeats = InputBox('Repeat Query', "how often?", "10")
            If @error > 0 Then ContinueLoop
            GUICtrlSetState($gui_SendQuery, $GUI_DISABLE)
            GUICtrlSetData($gui_InputQuery, GUICtrlRead($gui_InputQuery))
            $nTimerQuery = TimerInit()
            If BitAND(GUICtrlRead($gui_optGetTable), $GUI_CHECKED) Then
                _sqlGetTable(GUICtrlRead($gui_InputQuery), $nRepeats)
            Else
                _sqlQuery(GUICtrlRead($gui_InputQuery), $nRepeats)
            EndIf
            _log("Total Query Time: " & Round(TimerDiff($nTimerQuery), 2) & " mSec.")
            _updatetables()
            GUICtrlSetState($gui_SendQuery, $GUI_ENABLE)
        Case $msg = $gui_fileClose
            Local $iTmp
            $iTmp = _SQLite_Close()
            If Not $iTmp = $SQLITE_OK Then
                _log("Cannot close Database.")
                _log("SQLite ErrCode: " & $iTmp)
                _log("SQLite ErrMsg: " & _SQLite_ErrMsg())
            EndIf
            _GUICtrlListBox_ResetContent($hGui_Tables)
            _ResetResults()
            GUICtrlSetState($gui_fileClose, $GUI_DISABLE)
            GUICtrlSetState($gui_fileopen, $GUI_ENABLE)
            GUICtrlSetState($gui_SendQuery, $GUI_DISABLE)
        Case $msg = $gui_optGetTable
            GUICtrlSetState($gui_optGetTable, $GUI_CHECKED)
            GUICtrlSetState($gui_optQuery, $GUI_UNCHECKED)
            GUICtrlSetState($gui_funcRepQuery, $GUI_ENABLE)
        Case $msg = $gui_optQuery
            GUICtrlSetState($gui_optQuery, $GUI_CHECKED)
            GUICtrlSetState($gui_optGetTable, $GUI_UNCHECKED)
            GUICtrlSetState($gui_funcRepQuery, $GUI_DISABLE)
        Case $msg = $gui_funcChanges
            _log("Changes: " & _SQLite_Changes())
            _log("Total Changes: " & _SQLite_TotalChanges())
        Case $msg = $gui_funcLastInsertId
            _log("Last INSERT RowID: " & _SQLite_LastInsertRowID())
        Case $msg = $gui_TblContexDrop
            Local $sSelectedTable = GUICtrlRead($gui_Tables)
            GUICtrlSetData($gui_InputQuery, "DROP TABLE " & $sSelectedTable & ";", "DROP TABLE " & $sSelectedTable & ";")
        Case $msg = $gui_TblContexInsert
            Local $aTbl, $aRow, $iTmp, $i, $sQuery, $hQuery
            $aTbl = _GUICtrlListBox_GetSelItemsText($hGui_Tables)
            If IsArray($aTbl) Then
                $iTmp = _SQLite_Query(-1, "SELECT * FROM " & $aTbl[1] & " LIMIT 1;", $hQuery)
                If $iTmp = $SQLITE_OK Then
                    _SQLite_FetchNames($hQuery, $aRow)
                    _SQLite_QueryFinalize($hQuery)
                    $sQuery = "INSERT INTO " & $aTbl[1] & " VALUES ("
                    For $i = 0 To UBound($aRow) - 1
                        $sQuery &= "'',"
                    Next
                    $sQuery = StringTrimRight($sQuery, 1)
                    $sQuery &= ");"
                    GUICtrlSetData($gui_InputQuery, $sQuery, $sQuery)
                EndIf
            EndIf
        Case $msg = $gui_TblContexRndIns
            Local $aTbl, $aRow, $iTmp, $i, $sQuery, $hQuery
            $aTbl = _GUICtrlListBox_GetSelItemsText($hGui_Tables)
            If IsArray($aTbl) Then
                $iTmp = _SQLite_Query(-1, "SELECT * FROM " & $aTbl[1] & " LIMIT 1;", $hQuery)
                If $iTmp = $SQLITE_OK Then
                    _SQLite_FetchNames($hQuery, $aRow)
                    _SQLite_QueryFinalize($hQuery)
                    $sQuery = "INSERT INTO " & $aTbl[1] & " VALUES ("
                    For $i = 0 To UBound($aRow) - 1
                        $sQuery &= "random(*),"
                    Next
                    $sQuery = StringTrimRight($sQuery, 1)
                    $sQuery &= ");"
                    GUICtrlSetData($gui_InputQuery, $sQuery, $sQuery)
                EndIf
            EndIf
        Case $msg = $gui_ResultContexCopy
            ClipPut(_GUICtrlListBox_GetText($hGui_Result, _GUICtrlListBox_GetCurSel($hGui_Result)))
        Case Else
            ;;;;;;;
    EndSelect
WEnd
_SQLite_Shutdown()
Exit

Func _log($str, $fprepandcr = True)
    If $fprepandcr Then $str = @CRLF & $str
    GUICtrlSetData($gui_Log, $str, 1)
EndFunc   ;==>_log

Func _updatetables()
    _GUICtrlListBox_ResetContent($hGui_Tables)
    Local $hQuery, $iTmp, $aRow
    $iTmp = _SQLite_Query(-1, "SELECT name FROM sqlite_master WHERE type = 'table' OR type = 'view'", $hQuery)
    If Not $iTmp = $SQLITE_OK Then
        _log("Cant Read out Tables")
        Return
    EndIf
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        _GUICtrlListBox_AddString($hGui_Tables, $aRow[0])
    WEnd
    _SQLite_QueryFinalize($hQuery)
    $iTmp = _SQLite_Query(-1, "SELECT name FROM sqlite_temp_master WHERE type = 'table' OR type = 'view'", $hQuery)
    If Not $iTmp = $SQLITE_OK Then
        _log("Cant Read out Temp Tables")
        Return
    EndIf
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        _GUICtrlListBox_AddString($hGui_Tables, $aRow[0])
    WEnd
    _SQLite_QueryFinalize($hQuery)
    _GUICtrlListBox_AddString($hGui_Tables, 'sqlite_master')
    _GUICtrlListBox_AddString($hGui_Tables, 'sqlite_temp_master')
EndFunc   ;==>_updatetables

Func _sqlQuery($sql, $nRepeats = -1)
    Local $iTmp, $hQuery, $aRow, $aNames
    _log("SQLite Query: " & $sql)
    $iTmp = _SQLite_Query(-1, $sql, $hQuery)
    If Not $iTmp = $SQLITE_OK Then
        _log("SQLite ErrCode: " & $iTmp)
        _log("SQLite ErrMsg: " & _SQLite_ErrMsg())
        Return
    EndIf
    _SQLite_FetchNames($hQuery, $aNames)
    If UBound($aNames) = 1 Then $aNames[0] &= "|"
    _ResetResults(_ArrayToString($aNames, "|"))
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        GUICtrlCreateListViewItem(_ArrayToString($aRow, "|"), $gui_Result)
    WEnd
EndFunc   ;==>_sqlQuery

Func _sqlGetTable($sql, $nRepeats = -1)
    Local $aResult, $iRows, $iColumns, $iRval, $iCol, $iRow, $ResHead, $ResData, $nSQLiteApiTimer, $sqlrep
    _log("SQLite GetTable: " & $sql)
    If $nRepeats > 0 Then
        For $i = 1 To $nRepeats
            $sqlrep = $sqlrep & $sql
        Next
        $sql = $sqlrep
    EndIf
    $nSQLiteApiTimer = TimerInit()
    $iRval = _SQLite_GetTable2d(-1, $sql, $aResult, $iRows, $iColumns)
    If Not $iRval = $SQLITE_OK Then
        _log("SQLite ErrCode: " & $iRval)
        _log("SQLite ErrMsg: " & _SQLite_ErrMsg())
        Return
    EndIf
    _log("SQLite Api Time: " & Round(TimerDiff($nSQLiteApiTimer), 2) & " mSec.")
    If UBound($aResult, 2) = 0 Then _ResetResults()
    For $iCol = 0 To UBound($aResult, 1) - 1
        $ResData = ''
        For $iRow = 0 To UBound($aResult, 2) - 1
            If $iCol = 0 Then
                $ResHead &= $aResult[$iCol][$iRow] & "|"
            Else
                $ResData &= $aResult[$iCol][$iRow] & "|"
            EndIf
        Next
        If $iCol = 0 Then
            StringTrimRight($ResHead, 1)
            _ResetResults($ResHead)
        Else
            StringTrimRight($ResData, 1)
            GUICtrlCreateListViewItem($ResData, $gui_Result)
        EndIf
    Next
EndFunc   ;==>_sqlGetTable

Func _ResetResults($str = "")
    Local $a
    $a = ControlGetPos($gui_Form, "", $gui_Result)
    GUICtrlDelete($gui_Result)
    ;$gui_Result = GUICtrlCreateListView($str, 131, 8, 485, 305, BitOR($LVS_SHOWSELALWAYS, $LVS_NOSORTHEADER), BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT))
    $gui_Result = GUICtrlCreateListView($str, $a[0], $a[1], $a[2], $a[3], BitOR($LVS_SHOWSELALWAYS, $LVS_NOSORTHEADER, $LVS_SINGLESEL), BitOR($LVS_EX_GRIDLINES, $LVS_EX_HEADERDRAGDROP, $LVS_EX_TRACKSELECT, $LVS_EX_FULLROWSELECT))
    ;GUICtrlSendMsg($gui_Result, 0x101E, 0, -1) ;$listview, LVM_SETCOLUMNWIDTH, 0,  resize to widest value
    GUICtrlSetResizing($gui_Result, $GUI_DOCKBORDERS)
    $gui_ResultContexMenu = GUICtrlCreateContextMenu($gui_Result)
    $gui_ResultContexCopy = GUICtrlCreateMenuItem("Copy", $gui_ResultContexMenu)
EndFunc   ;==>_ResetResults

Func _BrowseTable($sTable)
    If $sTable = "" Then Return ; No Table Selected
    ; Write Table to disk
    Local $sTempDbFile = FileGetShortName(_TempFile(@TempDir, "", ".db")), $aRow, $nTimer = TimerInit()
    $i = _SQLite_Exec(-1, "ATTACH DATABASE '" & $sTempDbFile & "' AS HtmlExport;")
    _SQLite_QuerySingleRow(-1, "SELECT sql FROM sqlite_master WHERE type = 'table' and name = '" & $sTable & "';", $aRow)
    If StringLen($aRow[0]) < 6 Then
        _SQLite_QuerySingleRow(-1, "SELECT sql FROM sqlite_temp_master WHERE type = 'table' and name = '" & $sTable & "';", $aRow)
        If StringLen($aRow[0]) < 6 Then Return ; Table not found
    EndIf
    Local $sTableCreate = StringReplace($aRow[0], "CREATE TABLE " & $sTable, "CREATE TABLE HtmlExport." & $sTable)
    _SQLite_Exec(-1, $sTableCreate)
    _SQLite_Exec(-1, "INSERT INTO HtmlExport." & $sTable & " SELECT * FROM " & $sTable & ";")
    _SQLite_Exec(-1, "DETACH DATABASE HtmlExport;")

    ; Create html
    Local $sTempPrefix = FileGetShortName(_TempFile(@TempDir, "", ".htm"))
    Local $sTempSuffix = FileGetShortName(_TempFile(@TempDir, "", ".htm"))
    Local $sTempMiddle = FileGetShortName(_TempFile(@TempDir, "", ".htm"))
    Local $sTempOutputHtml = FileGetShortName(_TempFile(@TempDir, "", ".htm"))
    FileWrite($sTempPrefix, "<html><body><table cellpadding='2' cellspacing ='1'rules='all' border='1' >")
    FileWrite($sTempSuffix, "</table></body></html><!-- ")
    Local $sInput, $sOutput
    $sInput = ".header ON" & @CRLF
    $sInput &= ".mode html" & @CRLF
    $sInput &= ".output '" & $sTempMiddle & "'" & @CRLF
    $sInput &= "SELECT ROWID as RowID,* FROM " & $sTable & ";" & @CRLF
    _SQLite_SQLiteExe($sTempDbFile, $sInput, $sOutput)
    RunWait(@ComSpec & " /c copy /y " & $sTempPrefix & "+" & $sTempMiddle & "+" & $sTempSuffix & " " & $sTempOutputHtml, @WorkingDir, @SW_HIDE)
    _log('Export Time:' & Round(TimerDiff($nTimer), 2) & " mSec.")
    RunWait(@ComSpec & " /c START " & $sTempOutputHtml, @WorkingDir, @SW_HIDE)
    
EndFunc   ;==>_BrowseTable

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Thanks for the help PsaltyDS. So SQLite might be good to use with Autoit to make an accounting program? I wonder what else it might be good for? I was thinking at one point, of writing an accounting program. I'm unsure of the accounting principles used by the government in my country or what software they use to do their accounting, so I was going to write my own and make them use it. :)

Edited by Squirrely1

Share this post


Link to post
Share on other sites

AutoIt recently went through some major changes. There was some breakage of backwards compatiblity in the GUICtrl* management UDFs, including GUIListBox.au3. The demo has not been updated yet. So you can run it with an older version, or try this tweak I just did:

Awesome thanks! Do you think that the COM errors that are in the SQLite_Gui.au3 are caused by the updates in the GUI controls? I would like something that has an editable grid as an example :)

Share this post


Link to post
Share on other sites

@Athfar

The SQLite GUI is based on the SQLite COM object not the native SQLite DLL !!

Therefor you need to download and install the SQLiteX from here :

LiteX COM

I will update the first post of the SQLite with a new compatibility change for AU3.

regards

ptrex


Share this post


Link to post
Share on other sites

@Athfar

The SQLite GUI is based on the SQLite COM object not the native SQLite DLL !!

Therefor you need to download and install the SQLiteX from here :

LiteX COM

I will update the first post of the SQLite with a new compatibility change for AU3.

regards

ptrex

DOH! ^_^

I didn't even look at the COM errors yet, because fixing the _GuiCtrl* calls made the whole thing work for me... only because I had downloaded SQLiteX before, evidently.

Thanks for the UDF, ptrex rocks on!

:)

Edit: Confusion reigns again. I don't have SQLiteX loaded, so I went back looking for the COM error and we're talking about two different example files. My tweak was to Example_Browser.au3, not SQLite_GUI.au3. One more cup of coffee and all will be clear... :)

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

@Athfar

The SQLite GUI is based on the SQLite COM object not the native SQLite DLL !!

Therefor you need to download and install the SQLiteX from here :

LiteX COM

Ok I'm confused now :). (Can I|How can I) use the UDF Litex (LitexUDF.au3) and if not how do I install LiteX and will I have to install it on any machine I want to run my au3 script on?

This is my first dabble with COM stuff so please bear with me ^_^

Share this post


Link to post
Share on other sites

@Athfar

I can understand the confusion.

Before you start you need to make a choise to either start developing using the LiteX COM approach.

OR

start using the SQLite.UDF's

Don't mix them together.

It is up to you which you prefer best.

regards

ptrex


Share this post


Link to post
Share on other sites

Ok I'm confused now :). (Can I|How can I) use the UDF Litex (LitexUDF.au3) and if not how do I install LiteX and will I have to install it on any machine I want to run my au3 script on?

This is my first dabble with COM stuff so please bear with me ^_^

As ptrex says, pick one or the other to simplify your life.

I had to check LiteX out a bit more for my own information. It is SQLite at it's heart, with a wrapper around it to present a certain kind of interface. There are thee varieties of these wrappers right now:

LiteX package consists of:

LiteX Automation automation wrapper around SQLite3 library.

This wrapper is useful if you want to use SQLite3 databases in Visual Basic or any scripting

languages such as JScript,VBScript and (my favorite) AutoIt3.

LiteX++ - C++ wrapper around SQLite3 library.

This wrapper is useful to build fast SQLite3 database access in your C++ projects.

LiteX ADO .NET provider - beta code.

ADO .NET provider for SQLite3 databases to use SQLite3 databases in managed code.

All these wrappers comes with full source and are public domain.

Author: Edmunt Pienkowsky.

E-Mail: roed@onet.eu.

Then you have the SQLite.au3 UDF, which is an AutoIt functions wrapper around the DLL.

So, you have four ways there to make use of SQLite, but only two will work in AutoIt (the C++ and .NET wrappers won't work inside AutoIt). One is a UDF for use as AutoIt functions, the other is the LiteX automation wrapper for use as COM object calls.

With either method, something will have to be put on the HDD for use, either SQLite.dll or the LiteX Automation wrapper. The difference there, I think, is that the SQLite.dll required for the UDF does not have to be INSTALLED (registry entries, registration in Windows, etc.) only file copied on the HDD somewhere and can be just deleted when done. The LiteX executable has to be really installed to provide the "LiteX.LiteConnection" object interface.

That's how it looks to me. I'm just learning it too, so corrections are welcome.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

@PsaltyDS

You are definitely correct !!

But the LiteX com object can be provided in a registration free version as well if you need it.

look here for an example Registration free COM object

regards

ptrex

Oh, man! That is sweet!

Extra candy in the stocking for ptrex this Christmas!

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

From what I've read about the Reg-Free com you will need the xml file ( manifest ) generated by either VS 2005 or mt.exe from the Windows SDK. I went with mt.exe and tried to create a manifest for the sqlite3u.dll that is in the litex package, but it would not create one. Could someone give it a shot with VS2005 to see if it works? Post the file if you are successful please.

Btw I tried these samples against sqlite3u.dll with mt.exe

> To extract manifest out of a dll:

mt.exe -inputresource:dll_with_manifest.dll;#1 -out:extracted.manifest

> To generate an XML manifest from a managed assembly:

mt.exe -managedassemblyname:managed.dll -out:out.manifest

Share this post


Link to post
Share on other sites

AutoIt recently went through some major changes.

Hi,

Can you tell me how to get your 3.2.10.0. SQL browser script to work in 3.2.11.1?

I have tried all the constant includes i can think of?...

Best, randall

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

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

Create an account

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

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...