Android Tutorial SQLiteDB

enrico · Mar 2, 2013

Great work.
But, can you please post a version without the autocreated table, the related subs and the "load db" functions?
Only that automatically loads a Test.db table from File.DirAssets folder ?

Thanks

klaus · Mar 3, 2013

Feel free to modify the program to your needs.

Did you have a look at Erel's SQLite Viewer ?

Best regards.

enrico · Mar 3, 2013

I've modified it but I'm seeing that I can't handle more than about 1000 records in the table, otherwise I get "out of memory" error.

klaus · Mar 3, 2013

I tried with the original program and I get no error with 2000 entries but get an error with 2500.
With a big numbers of entries you should consider Erel's Table Class.

Best regards.

Caravelle · Apr 17, 2013

Hi Klaus

I tried your SqliteDB_2D with a database of my own but it stopped loading at line 830 when it encountered a <null> in one of the fields [LastException Java.Lang.NullPointerException]. I have used this database with a couple of android apps from Google Play, some of my own written in BASIC4PPC and Delphi 7, and other commercial programs like SQLite Expert Personal 3, so I know there is nothing wrong or abnormal about it. A null is a perfectly valid value in Sqlite terms, and is usually just shown textually as "<null>"

Why do I not use one of the available apps ? Well they're so inflexible and all have problems for me of one sort or another. One unbelievably won't "see" beyond the SDCard folder so never gets to "Removable" where my database is actually removable, for use on other platforms and devices. One insists on using an eccentric combination of font and background colours which I just can't read. And I need quite a lot of bespoke error-checking when adding new data, so really the only way is to create a specific app for this database, and I was hoping to base it on your code.

One problem that I came across when testing the database that comes with the demo is that when I pressed a column header to sort a column, the blue header row moved over to the right side of the screen and refused to go back. This is on an Asus TF101 Transformer tablet in both landscape and upright orientation.

Regards

Caravelle

klaus · Apr 18, 2013

Could you post your database so I could test it.
What is line 830 ?
If you changed the code the line numbering could be different.

I had never tested the program on a big screen. To fix the header position problem you should replace, in the Header_Click routine, the calculation for the Left property by this code:

B4X:

If SortDirection(col) = "ASC" Then
    SortDirection(col) = "DESC"
Else
    SortDirection(col) = "ASC"
End If

Left = Min(-TotalColWidth(col), 0)
Left = Min(-(TotalColWidth(NumberOfColumns)-ScreenWidth), 0)

ClearAll

Best regards.

Caravelle · Apr 18, 2013

Thank you Klaus. I cannot post my database, it is 40 megabytes with over half a million "cells" in one table alone. I tried extracting a small portion of one table containing <null> entries into a new db with a single table named Table1, but your app wouldn't open it at all ("Program paused on line 1244 DBTableName = Cursor1.GetString("name")"). This is line 1244 of SQLiteDB_2D as unzipped from your zip, no changes at all. SQLite Expert was quite happy to open it in Windows as was aSqliteManager in Android (which indicates the nulls by showing the relevant cells in a different colour), so it is attached for your tests.

My line 830 was in sub AddRow(Values() As String and reads:

B4X:

l.Text = Values(i)

.
It is line 829 in your original code, which I had only modified by adding a new DBFilePath option to the removable MicroSD card on my device.

See here for null handling in Sqlite. Nulls will appear if no default value for columns has been specified in the CREATE TABLE statement and then no value is supplied for the particular cell when populating the table. Obviously this is something we can avoid if we create our own table, but sometimes our databases come from external sources and we have no control over them. Of course, I may be wrong and it may not be the nulls that were causing the problem, but the program did stop at the first row that contained a null so it seems likely.

I am only telling you about these problems so you can investigate and fix for others if necessary. Personally I don't need the answers any more as after tinkering with various options, I decided in the end to build a system around Erel's Table class as amended by yourself - which works nicely with my original database so long as I limit the number of rows returned, but that is normal - I see no point in spending time scrolling and scrolling through tens of thousands of records on a small screen when an appropriate SQL query will return exactly what I want in an instant. There may be times when you want to browse a gigantic dataset, but you can do that with a paging routine using the SQL LIMIT construction.

regards

Caravelle

klaus · Apr 19, 2013

Attached you find a new version of the program working with your database.
But for the table it's better to use Erel's Table class.

Best regards.

colibri999 · May 22, 2013

SQLiteDiskIOException

Thanks for this very interesting example.
Unfortunatly I run into a problem I don't understand yet (as a newbie):
when I start debug mode things compile and the tool opens on my tablet (connected with USB cable), but when I turn the screen I get a SQLiteDiskIOException (code 1802) in SQLTableRead line 639 (For i = 0 To Cursor1.RowCount - 1)
What could be the problem?

Caravelle · May 22, 2013

I'm afraid this isn't an answer to Colibri999, merely an apology for not thanking Klaus for his last response. I thought I had done so, but I don't see any thank you message there now !

Caravelle

beaubassin · Jun 13, 2013

Hi,
I am starting to learn B4A with the SQLiteDB example.I click on a row in the table then on the Edit btn then the Edit view with EditTexts shows up.Here I would like to know how to pop up the date dialogue when I click on an EditText.I don't know how to retrieve the name of that specific EditText so as I can pop up the date dialogue.Thanks for any help.

klaus · Jun 13, 2013

You can change the Sub edtEdit_FocusChanged routine like this:

B4X:

Sub edtEdit_FocusChanged(HasFocus As Boolean)
    Dim Send As EditText
    
    If HasFocus = True Then
        Send = Sender
        scvEdit.ScrollPosition = Send.Top - 40dip
        If Send.Tag = 2 Then        ' Send.Tag is the LastName EditText in this example
            Dim DateDial As DateDialog
            Dim Answ As Int
            DateDial.DateTicks = DateTime.Now
            Answ = DateDial.Show("Enter date", "Test","Yes", "", "No", Null)
            If Answ = DialogResponse.POSITIVE Then
                ' your code
            End If
        End If
    End If
End Sub

You must change the value of Send.Tag = 2 to the EditText index you need.

Best regards.

beaubassin · Jun 14, 2013

Hi Klaus,
Thanks for your reply with the sample code.It is working exactly as I was looking for.

beaubassin · Sep 11, 2013

Hi Klaus,
I am continuing to modify your sample SQLiteDB to fit my need.I am adding all numeric values of a column and I put the sum in a Label.This is working fine but when I filter the Table I don't know where are the data so as I can update my Label.Please can you help me to retrieve all values of a column after a filter.Thanks for any help.

klaus · Sep 11, 2013

The simplest way to do it would be a SQL request for the sum with the filter.
Something like this:
Sum = SQL1.ExecQuerySingleResult("SELECT sum(Col1) FROM TableName WHERE ....")

I'd suggest you look at the SQLite example in the User's Guide which is more recent.
The example in this thread is quite 'old' with only one activity and panels.

Best regards.

stratos · Nov 25, 2013

Thanks Klaus
Such tutorials are very useful for newbies like me!!

Knoppi · Feb 9, 2014

Hello Klaus,
thank you for this tutorial.

i'm new but i think there is a line missing in the 'Sub btnLoad_Click'
(version 1.4)
in your Code is the Path of the choosen file not set

B4X:

If Answ1 = DialogResponse.POSITIVE Then
    DBFileName = FileDialog1.ChosenName
    LoadDatabase
End If

new Code

B4X:

If Answ1 = DialogResponse.POSITIVE Then
    DBFilePath = FileDialog1.FilePath
    DBFileName = FileDialog1.ChosenName
    LoadDatabase
End If

klaus · Feb 9, 2014

You are right, thank's for the bug report.
Project in the first post amended.

Beja · Mar 16, 2014

Hi Klaus,
Thanks for this complete and very intuitive example.
Only today I saw it and downloaded it in b4a3.5. I had a compile and runtime errors..
Note: didn't modify and run it AS IS.
This is the log:
** Activity (main) Create, isFirst = true **

android.database.sqlite.SQLiteException: unable to open database file

at android.database.sqlite.SQLiteDatabase.dbopen(Native Method)
at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1812)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:817)
at anywheresoftware.b4a.sql.SQL.Initialize(SQL.java:37)
at anywheresoftware.b4a.table.main._activity_create(main.java:516)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:521)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:174)
at anywheresoftware.b4a.table.main.afterFirstLayout(main.java:98)
at anywheresoftware.b4a.table.main.access$100(main.java:16)
at anywheresoftware.b4a.table.main$WaitForLayout.run(main.java:76)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:123)
at android.app.ActivityThread.main(ActivityThread.java:4627)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:521)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
at dalvik.system.NativeStart.main(Native Method)
android.database.sqlite.SQLiteException: unable to open database file

and here are the runtime errors:

klaus · Mar 16, 2014

Hi Beja,
I downloaded both examples from post #1 and ran them in B4A V3.5 without any trouble.
Could explain step by step how you get the error so I could try to reproduce it.

You might have a look at the SQLExample in the User's Guide which is more recent.
The program in this thread works with one Activity and panels, the SQLExample in the User's Guide works with several Activites.

Try to change
Dim DBFilePath As String : DBFilePath = File.DirRootExternal
to
Dim DBFilePath As String : DBFilePath = File.DirInternal

Android Tutorial SQLiteDB

Attachments

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Attachments

Expert

Attachments

Member

Active Member

New Member

Expert

New Member

New Member

Expert

New Member

Active Member

Expert

Expert

Attachments

Expert