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.

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.

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.

 

[tamadon]

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?

 

[Erel]

This is a bug in Android OS. It fails to decompress files larger than 1mb. If the extension is jpg then it assumes that the file is already compressed and it doesn't try to compress / decompress it.

 

[hensetlfc7]

I'm new, sorry how to download this file :sign0163:. I need it, thanks

 

[Erel]

The source code is only available to users who purchased Basic4android.

 

[aklisiewicz]

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

 

[Erel]

This application allows you to browse an existing database. Put your database file in the storage card.

 

[TomDuncan]

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.

 

[Erel]

There is no such thing as a VarChar field in SQLite. Data types: http://www.sqlite.org/datatype3.html

SQLiteViewer checks the meta information with PRAGMA table_info.
If it is a BLOB field then it will try to load it as a bitmap. You can instead use BytesToString to convert an array of bytes to a string.

 

[tdocs2]

Hello, Erel,

For B4J:
Edit: I found the pointer to: SQLite Expert Personal:
http://www.sqliteexpert.com/download.html
in one of your B4J threads:
http://www.b4x.com/android/forum/threads/rdc-simple-way-to-create-your-own-back-end-database.31616/
Thank you.

Best regards.

Sandy

 

[tdocs2]

Greetings.

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

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

 

[LucaMs]

Open each layout in the Designer and save them without changes.

 

[JohnC]

I am getting this error just trying to open the internal file manager:

This is the line that is giving the error:

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

** 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)

 

[Erel]

You need to use the latest version of the Dialogs library.

 

[Antonio D'Angeli]

Hi,
where is the menu button?
I do not even see it in the previous screenshots.
Thanks

p.s.:

 

[Erel]

This is a very old example.
I've made some updates to make it work better with new versions of Android. It is available in the first post.

If you are using a B4A version lower than v8 then remove the CreateResourceFromFile line in the manifest editor.

 

[Antonio D'Angeli]

Thanks Erel
what is "the first post"

 

[OliverA]

what is "the first post"

The first post in this thread (here: https://www.b4x.com/android/forum/threads/android-sqlite-viewer.9197/)

 

[Didier9]

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):

SQL.Initialize(Dir, FileName, False)

Here is the error log:

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.

 

[roberto64]

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)