Sign in to follow this  
Followers 0
james3mg

View SQLite database contents

3 posts in this topic

#1 ·  Posted (edited)

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

#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 by james3mg

"There are 10 types of people in this world - those who can read binary, and those who can't.""We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true." ~Robert Wilensky0101101 1001010 1100001 1101101 1100101 1110011 0110011 1001101 10001110000101 0000111 0001000 0001110 0001101 0010010 1010110 0100001 1101110

Share this post


Link to post
Share on other sites



#3 ·  Posted (edited)

@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 by randallc

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  
Followers 0