View SQLite database contents

[james3mg]

I searched a few ways and couldn't find a similar tool that's already in the forum. Hopefully I didn't just miss it. If I did, then just ignore this

This script just allows you to open a SQLite database and view the contents of all the available tables. It finds the table names by running a SELECT on the special table "SQLITE_MASTER", as documented here.

Hope someone finds it helpful!

Limitations:

-It takes a few seconds to show a table that has a few hundred entries - most of the time is in the GUICtrlCreateListViewItem() command. A list would be faster, but harder to read and get the data you're looking for. It also takes a few seconds when you switch AWAY from that table, because it has to GUICtrlDelete() each item.

-If a table exists but doesn't contain any data, you only see the name of the first column in the ListView header. I'm not sure why this is - I thought that SQLite would still return all the column names when you SELECT * from an empty table...

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <GUIConstants.au3>

Global $aResult,$iRows,$iColumns
_SQLite_Startup()

$DbFilename=FileOpenDialog("Select a SQLite database:","","Database files (*.db)|All files(*.*)",3,"")
If @error Then Exit
    
If _SQLite_Open($DbFilename)=0 Then;cancel pressed or file doesn't exist.
    MsgBox(16,"Error","Error opening the database file")
    Exit
EndIf

If _SQLite_GetTable2d(-1,"Select * From SQLITE_MASTER",$aResult,$iRows,$iColumns) <> $SQLite_OK Then;can't query the table - may be encrypted or not a SQLite database.
    MsgBox(16,"Error","This file does not appear to be a vaild SQLite database.")
    Exit
EndIf

Global $TableNames[1]=["0"];an array that just contains the names of the available tables - quickly transformed into $TableNameStr to populate the combo box.
For $i=1 To $iRows
    If $aResult[$i][0] = "Table" Then
        $TableNames[0]+=1
        ReDim $TableNames[$TableNames[0]+1]
        $TableNames[$TableNames[0]]=$aResult[$i][1]
    EndIf
Next

$MainGUI=GUICreate("View a SQLite database",640,480)
GUICtrlCreateLabel("Table names:",5,7,100,20)
Local $TableNameStr=""
For $i=1 To $TableNames[0]
    $TableNameStr&="|"&$TableNames[$i]
Next
$TableSelectCombo=GUICtrlCreateCombo("",110,5,525,20,$CBS_DROPDOWNLIST)
GUICtrlSetData(-1,$TableNameStr)
$TableDisplayList=GUICtrlCreateListView("|",5,30,630,445)
Global $ListViewItems[1]=["0"]

GUISetState()

While 1
    $msg=GUIGetMsg()
    Switch $msg
        Case $TableSelectCombo;new table selected for display
            For $i=1 To $ListViewItems[0]
                GUICtrlDelete($ListViewItems[$i])
            Next
            Global $ListViewItems[1]=["0"]
            If _SQLite_GetTable2d(-1,"Select * From "&GUICtrlRead($TableSelectCombo)&";",$aResult,$iRows,$iColumns) = $SQLite_OK Then
                GUICtrlDelete($TableDisplayList)
                Local $HeaderStr=$aResult[0][0]
                For $i=1 To $iColumns-1
                    $HeaderStr&="|"&$aResult[0][$i]
                Next
                $TableDisplayList=GUICtrlCreateListView($HeaderStr,5,30,630,445)
                For $i=1 To $iRows
                Local $ItemStr=$aResult[$i][0]
                    For $n=1 To $iColumns-1
                        $ItemStr&="|"&$aResult[$i][$n]
                    Next
                    $ListViewItems[0]+=1
                    Redim $ListViewItems[$ListViewItems[0]+1]
                    $ListViewItems[$ListViewItems[0]]=GUICtrlCreateListViewItem($ItemStr,$TableDisplayList)
                Next
            EndIf
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd

Edited September 7, 2007 by james3mg

[ptrex]

@james3mg

Nice example !!

regards,

ptrex

[randallc]

@james3mg

Nice example !!ptrex

Hi,

Yes, i like it..

Here it is shorter showing how simple the logic can look.. [ BETA; Running:(3.2.9.4)]

We can add sorting esaily too.. later?

#include <SQLite.au3>; sqliteBrowser7.au3;~ #include <SQLite.dll.au3>
#include <GUIConstants.au3>
Local $aResult, $iRows, $iColumns, $TableNames[1], $TableNameStr = "", $c = _SQLite_Startup()
$DbFilename = FileOpenDialog("Select a SQLite database:", "", "Database files (*.db*)|All files(*.*)", 3, "");
If _SQLite_Open($DbFilename) = 0 Then Exit;cancel pressed or file doesn't exist.;   
If _SQLite_GetTableNames($DbFilename, $TableNames) <> $SQLite_OK Then Exit; 
Local $MainGUI = GUICreate("View an SQLite database", 640, 480,-1,-1, $WS_MAXIMIZEBOX + $WS_MINIMIZEBOX + $WS_SIZEBOX)
Local  $c = GUICtrlCreateLabel("Table names:", 5, 7, 100, 20),$TableSelectCombo = GUICtrlCreateCombo("", 110, 5, 525, 20, $CBS_DROPDOWNLIST)
Local $c = GUICtrlSetData(-1, _ArrayToString($TableNames, "|", 2), $TableNames[2]),$c = GUISetState(), $Hdr = "",$LV ="",$s = WinGetPos("")
Do
    $msg = GUIGetMsg()
    If $msg = $TableSelectCombo Or $LV = "" Then ;new table selected for display
        If _SQLite_GetTable2d(-1, "Select * From " & GUICtrlRead($TableSelectCombo) & ";", $aResult, $iRows, $iColumns) = $SQLite_OK Then
            For $i = 0 To $iColumns - 1
                $Hdr &= $aResult[0][$i] & "|" 
            Next
            Local $c = GUICtrlDelete($LV),$s = WinGetPos(""),$LV = GUICtrlCreateListView(StringTrimRight($Hdr, 1), 5, 50, $s[2]-9, $s[3]-9)
            Local $c=_GUICtrlListView_AddArray ($LV, $aResult), $Hdr = ""
        EndIf
    EndIf
Until $msg = $GUI_EVENT_CLOSE
Func _SQLite_GetTableNames(ByRef $sDB, ByRef $a2Result)
    Local $iRows, $iColumns, $Select = "SELECT name FROM sqlite_master WHERE type = 'table' OR type = 'view';"
    return _SQLite_GetTable(-1, $Select, $a2Result, $iRows, $iColumns)
EndFunc   ;==>_SQLite_GetTableNames

Best, Randall

PS Example .db3 attached

Edited October 27, 2007 by randallc