Android Tutorial Android SQLite Viewer

SQLite Viewer is an open source application that allows you to view the contents of SQLite databases right from your Android device.

sqliteviewer_1.png


sqliteviewer_2.png


Choosing the database file is done with an external file manager or with agraham's FileDialog.

After choosing a database file, you will see a list with the database tables. Choosing a table will open the second activity which shows the table fields types and the table contents.

BLOB columns will display: "click to see image".
When you click on such a cell the program will try to convert the BLOB data to an image and show it.

sqliteviewer_3.png


The code uses DBUtils code module for most of the SQL statements.
Handling the BLOB columns adds some complexity. You can see it handled in TableActivity code.
The source code is included in the zip file.
 

Attachments

  • SQLiteViewer.zip
    15.1 KB · Views: 2,757
Last edited:

tamadon

Active Member
Licensed User
Longtime User
OK Found a solution here

Apparently I have to rename the database extension to .jpg to prevent Android packager from trying to compress this file.

So does that mean the database file is limited to certain size unless its extension is .jpg?

Is this a bug?
 

aklisiewicz

Active Member
Licensed User
Longtime User
Nice example,... just one simple question - where the SQLite data file goes ?
when running it asks for the database file. any idea where am I supposed to point it ? what folder ?

Art
 

TomDuncan

Active Member
Licensed User
Longtime User
Just tested the Sqlite Viewer.
when I open a database with VarChar & Text fields, it comes up with an error with the text field, it thinks it is a blob field.
How do I convert a blob to a text string.
 

tdocs2

Well-Known Member
Licensed User
Longtime User
Greetings.

I found this error when I compiled (v4.30) SQLite Viewer:

upload_2015-5-4_11-20-20.png


I could not find the parameter name: input in Designer Scripts. I tried downloading fresh copy and same error.

Any help is welcomed.

Thank you.

Sandy
 

JohnC

Expert
Licensed User
Longtime User
I am getting this error just trying to open the internal file manager:

This is the line that is giving the error:

B4X:
If FileDialog1.Show("Choose database file", "Ok","Cancel", "", Null) = DialogResponse.POSITIVE Then

B4X:
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
java.lang.NoSuchFieldError: anywheresoftware.b4a.objects.SimpleListAdapter$SingleLineData.Text
    at anywheresoftware.b4a.agraham.dialogs.InputDialog$FileDialog.loadFileList(InputDialog.java:1786)
    at anywheresoftware.b4a.agraham.dialogs.InputDialog$FileDialog.Show(InputDialog.java:1832)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:710)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:339)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:134)
    at anywheresoftware.b4a.BA$1.run(BA.java:303)
    at android.os.Handler.handleCallback(Handler.java:725)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:5293)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1102)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:869)
    at dalvik.system.NativeStart.main(Native Method)
 

Didier9

Well-Known Member
Licensed User
Longtime User
I installed the version 4.01 of the Dialog library and the SQLDemo still crashes on this statement (line 52, I added a Try/Catch statement) when opening an existing database (that otherwise works fine in its own application):
B4X:
SQL.Initialize(Dir, FileName, False)
Here is the error log:
B4X:
Error occurred on line: 52 (Main)
android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:806)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:791)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:669)
    at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:44)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:755)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:345)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:139)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:259)
    at anywheresoftware.b4a.sqliteviewer.main._loaddatabase(main.java:464)
    at anywheresoftware.b4a.sqliteviewer.main._contentchooser_result(main.java:455)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:710)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:342)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:139)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:166)
    at anywheresoftware.b4a.phone.Phone$ContentChooser$1.ResultArrived(Phone.java:843)
    at anywheresoftware.b4a.BA$4.run(BA.java:547)
    at anywheresoftware.b4a.BA.setActivityPaused(BA.java:432)
    at anywheresoftware.b4a.sqliteviewer.main$ResumeMessage.run(main.java:301)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:135)
    at android.app.ActivityThread.main(ActivityThread.java:5343)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:905)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:700)
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
*** Service (starter) Create ***
Name
SchedC
Found column SchedC in Businesses
Looking for columns in Log
Found: Date
Found: Amount
Found: Business
Found: Category
Found: Description
Found: Vendor
Found: Bank
Found: Picture
Log table complete, does not need update
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
sending message to waiting queue (OnActivityResult)
running waiting messages (1)
Error occurred on line: 52 (Main)
android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14): Could not open database
    at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:209)
    at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:193)
    at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
    at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
    at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:806)
    at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:791)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:694)
    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:669)
    at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:44)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:755)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:345)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:139)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:259)
    at anywheresoftware.b4a.sqliteviewer.main._loaddatabase(main.java:464)
    at anywheresoftware.b4a.sqliteviewer.main._contentchooser_result(main.java:455)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:710)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:342)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:249)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:139)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:166)
    at anywheresoftware.b4a.phone.Phone$ContentChooser$1.ResultArrived(Phone.java:843)
    at anywheresoftware.b4a.BA$4.run(BA.java:547)
    at anywheresoftware.b4a.BA.setActivityPaused(BA.java:432)
    at anywheresoftware.b4a.sqliteviewer.main$ResumeMessage.run(main.java:301)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:135)
    at android.app.ActivityThread.main(ActivityThread.java:5343)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:905)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:700)
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **

This is running B4A version 7.80 on a Moto-X with Android 5.1

[SOLVED] The issue is that the SQLite database I wanted to look at is in my app's folder and I have noticed before that the Content Chooser seems to only work when files are in globally accessible folders like Pictures and Downloads. Even though the Content Chooser will let you navigate to any folder, the link it returns seems to fail when the file is in another app's folder.
I replaced the Content Chooser with a hard coded file location and the SQLite Viewer works fine.
 
Last edited:

roberto64

Active Member
Licensed User
Longtime User
B4X:
Dim myfdb As String : myfdb = File.DirDefaultExternal& "/Database"

Public  DBFileName As String    : DBFileName = "miodb.db"

Public DBFileDir As String        : DBFileDir = File.DirRootExternal

File.Delete(DBFileDir, DBFileName)
    If File.Exists(myfdb , "") = False Then
        File.MakeDir(File.DirDefaultExternal, "/Database")
    End If
If File.Exists(myfdb,DBFileName) = False Then
        File.Copy(File.DirAssets, DBFileName, myfdb, DBFileName)
    End If
SQL.Initialize(myfdb , DBFileName, True)
 
Top