Android Tutorial SQLiteDB

Below you find an application with a SQLite database and a ScrollView based table.
What the program can do:
- Read a database, 'Load' button.
- Edit, modify or delete date sets (single- or multi-selection), 'Edit' button.
- Edit and modify a single cell, 'Long_Click' on a cell in a selected row.
- Filter the database, 'Filter' button, Click filter or unfilter, LongClick definition of filter criterias.
- Sort a column ascending or descending, clickink onto the column header.
- Create a new database, 'New' button.
- Scroll the table verticaly with the standard Scrollview, and horizontaly on the blue line between the ScrollView and the toolbox (or with a SeekBar, hidden by default.
- Change the table setup, alignment, column width, text size, colors, etc.

What the program connot do:
- Share multiple tables.

I post it as a tutorial more to show what could be done, rather than a specific program in Share Your Creations.
Questions, constructive critics, comments, wishes and bug reports are welcome.

You can use the source code, or parts of it, for your own applications.

EDIT: 2012.07.05
Added a version using the ScrollView2D library.

EDIT: 2013.02.08
Amended error reported in post #27
Updated zip file SQLiteDB_2D_v_1_2.zip

EDIT: 2013.02.09
Amended error reported in post #30
Updated zip file SQLiteDB_2D_v_1_3.zip

EDIT: 2013.04.19
Eliminated error with Null values in a database reported in post #45.
Updated zip file SQLiteDB_2D_v_1_4.zip

EDIT: 2013.07.01
Eliminated some errors.
Removed most of the compiler warnings.

EDIT: 2014.02.09
Amended bug reported in post #57
Missing DBFilePath on btnLoad_Click

Best regards.
 

Attachments

  • SQLiteDB1.jpg
    SQLiteDB1.jpg
    48 KB · Views: 13,921
  • SQLiteDB2.jpg
    SQLiteDB2.jpg
    50.7 KB · Views: 2,580
  • SQLiteDB3.jpg
    SQLiteDB3.jpg
    34.4 KB · Views: 20,023
  • SQLiteDB4.jpg
    SQLiteDB4.jpg
    41.4 KB · Views: 2,322
  • SQLiteDB5.jpg
    SQLiteDB5.jpg
    27.4 KB · Views: 2,547
  • SQLiteDB6.jpg
    SQLiteDB6.jpg
    49.7 KB · Views: 9,086
  • SQLiteDB7.jpg
    SQLiteDB7.jpg
    49.4 KB · Views: 2,531
  • SQLiteDB_V1_1.zip
    26.2 KB · Views: 3,925
  • SQLiteDB_2D_V1_5.zip
    28.5 KB · Views: 3,363
Last edited:

klaus

Expert
Licensed User
Longtime User
To check, I just downloaded the zip file from the forum and the progam does work.
What did you change the code ?
Without knowing what you have done it is impossible to know what happens.
...why do you create all your windows in code not in the designer...
I partialy agree with you. Me too, I design my layout mostly in the Designer. But, depending on what you want or need there are adjustments that are easier made in the code (at least for me).
I find it easier making the adjustmets to fit different width/height ratios in the code rather than having another layout variant.
The filling of the scrollview panel for editing is made in the code because the number of columns can be different and this cannot be done in the Designer.
By the way, there are 8 layout files edited in the Designer but adjusted and completed in the code.

Best regards.
 
Last edited:

bekadata

New Member
Licensed User
Longtime User
Error occourred on 1280x800 lenovo

Hi Klaus,
when I run SQLiteDB on my lenovo thinkpad tablet, I get:
Error occured
An error has a
occured in sub:main_skbscroll_valuechanged
(java line:3403)
java.lang.RuntimeExeption: Object should first be initialized
(Panel).
Continue?

If I push 'yes' everything are working fine, until I turn the tablet horizontal or
vertical . Then the same msg.
On my HTC Desire, I can turn as much as I like without any problem.

Anyway, thanks for SQLiteDB. It is a great program itself and very usefull as inspiration to other specialized programs.
Bent Karlsen
 

klaus

Expert
Licensed User
Longtime User
Unfortunately I can't test it, I don't have a tablet.
But you can remove the skbScroll Seekbar.
The program has two means to scroll horizontally, pnlScroll and skbScroll.
pnlScroll is more dynamic and shown by default.

The only reason I see is that when changing skbScroll.Value the skbScroll_ValueChanged event is raised (in the tablet but not on a smartphone), in that case pnlHeader is not yet initialized.

Try the new version of the program in the first post.

Best regards.
 

bekadata

New Member
Licensed User
Longtime User
Error occourred on 1280x800 lenovo

Thanks for the very quick responce, Klaus.
The problem is still in the 1.1 version, but I removed the skbScroll as you sugestede and everything is running fine.
And, if you need something to be tested on a tablet in the future, I will be more than happy to help.
regards Bent
 

StevetheCanuck

New Member
Licensed User
Longtime User
Hi Klaus,

Is there an easy way to copy a sqlite database into the emulator and open it with your program?

TIA
Steve
 

klaus

Expert
Licensed User
Longtime User
Yes, you can copy a database to the Emulator.
Look at the Beginner's Guide Edition 2.1 chapter 6.4 Exchange files with PC.
Make sure the the Emulator is setup with SD card support chapter 6.2 Create a new Emulator.
Then in the program you can load the database clicking on the Load button.

Best regards.
 

JTmartins

Active Member
Licensed User
Longtime User
Error here

Hi there,

I would like to learn a little from this, but when running in release mode I got an error.

So I've tryed in debug mode and I get :

An error has accurred in sb:main_activity_resume (B4A line 262)
pnlScroll.top=100%y-pnlScroll.Height
java.lang.RuntimeException : Object should first be initialized (panel).

Any help ?

Thanks

José

B4A 2.5
 

KiloOne

Member
Licensed User
Longtime User
Hello Klaus,

This is a great demo program!! :sign0098:

I am however trying to run it on an 800x440 scale .75 android and am having some problems.

First, the button text was too big for all buttons so in designer I created new layout variants for all layouts at 1066x592 scale=1, I then changed all textsize 14 to 10 etc.

Second, I had to change this:
pnlColumnSetupTools.Left = ScreenWidth
to this:
pnlColumnSetupTools.Left = ScreenWidth - pnlColumnSetupTools.Width
if screen is landscape. Don't think that was scale related.

Next I have noticed 2D scrolling not working and I think it is related to the same scale=.75 issue but I am not sure how to solve that, is there a global way to fix the scaling of scvList to solve this?

Also, the header Left setting does not place the header correctly when I click on a column sort, this is also related to scale=.75 (I think) and have not sorted this out yet either.

Any help would be appreciated.:sign0104:

Thanks,
Dale
 

KiloOne

Member
Licensed User
Longtime User
Wow, thanks a lot, 2D scroll fixed now and I remmed out 755 and 756.

What is the best way to fix the textsize on the buttons and labels in v1.3 as the v1.3 text on the labels/buttons is too big for their views (should i do it like I explained in my first post to you?).

This is a $70 Chinese 7" A13-MID Ebay tablet. Very impressive for the money!

Sorry, it is 800x444 scale=.75.

Thanks,
Dale
 

KiloOne

Member
Licensed User
Longtime User
Klaus,

I am new so forgive my ignorance.

I added your scale module to the v1.3 project and I added these lines after all the layout.addview's in Sub Activity_Create:

Scale.SetRate(.33)
Scale.ScaleAll(Activity, True)

It seems to work, just want to confirm that is what you suggested. If this is the case would it not be useful to have that there for all devices with the .33 replaced by some formula based on scale of the device (.75 in my case)), perhaps (1/scale)-1. I tried -.2 in setrate and negative number worked (buttons got smaller) so a scale 2 device would setrate(-.5).

And, to me the column sorting indicators (up and down arrows) are confusing.

I expected that I should only see only one up or down arrow in the whole header which indicates the current sorted column and its sort order.

What I see is every column always has an Up or down arrow beside it. Is this intentional?

Thanks,
Dale

BTW I think in Column Setup you may have Row Height setting Col width and Col width setting Row height
 
Last edited:

klaus

Expert
Licensed User
Longtime User
If this is the case would it not be useful to have that there for all devices with the .33 replaced by some formula based on scale of the device ...
When you set a scale ratio two scale factors are calculated one for X and one for Y depending on the screen size, resolution and density.
With a ScaleRate of 1 the result is the same as dimesioning with 100%x and 100%y.
For your device and in portrait
with Scale.SetRate(1) gives sacleX = 2 and scaleY = 2.36
with Scale.SetRate(.33) gives sacleX = 1.33 and scaleY = 1.45
To make the program look OK it would need some rearangements when using it with the Scale Module. The program was written before Erel added the DesignerScripts and AutoScale function.

In my mind the up and down arrows show the direction for the next sorting for each column and not the last sorted column. But feel free to change it to your convenience.

Did you have a look at the SQLExample program in the User's Guide ?

Best regards.
 

KiloOne

Member
Licensed User
Longtime User
Thanks for the extra insight into scaling. The fact that scalex and scaley can be different certainly enhances the power of GUI development but at some cost of head scratching. Fortunately my current project will only be written for this android screen and for now I will unfortunately have to delay my knowledge expansion in this area.

Sounds like I should begin with Designer scripts and autoscale in mind though.

At this point using Scale.SetRate(.33) with your Scale module allows me to use this SQLiteDB example to gain the knowledge I need to continue.

Looks like the SQLExample Project in the user guide is very similar to the sample poject of this thread. Do they have the same beginnings?

Again, great sample code for many things!:sign0142:

Dale

P.S. If you don't mind, how does one insert what looks like extended ascii characters into the code (eg the up/down arrows you use in the header) and where do I find a table showing the available characters?
 

klaus

Expert
Licensed User
Longtime User
Looks like the SQLExample Project in the user guide is very similar to the sample poject of this thread. Do they have the same beginnings?
The SQLExample is more recent and works with different Activities instead of different Panels in the same Activity. The two are quite similar.

... and where do I find a table showing the available characters?
I use the Windows Character table tool.
On my computer it's C:\Windows\System32\charmap.exe

Best regards.
 
Top