Android Question Simple database example?

[cougar]

First off, thanks for all of the useful information I have picked up through this forum and the various tutorials. B4A is an incredible development environment and I look forward to learning even more in regards to its capabilities. I am a former VB programmer so the similarities between VB and B4A are greatly appreciated. I do have a question, however, regarding databases.
I have read through numerous tutorials and downloaded several database examples. They are great and I do appreciate them. However, I have seen numerous posts in the forum from "newbies" like myself that seem to be seeking the same basic database info I am but who are presented with very robust examples that can be a bit confusing to someone who is looking for a simple example. So, if at all possible (for both myself and future B4A newbies) I would love to see a simple example that presents the following. For those familiar with the old VB/Access sample programs (CD collection, contacts, etc.) this should be easy to pick up on.

A layout (form to VB programmers) that consists of a textbox and a button at top. The button will read Create Database. The user enters the name they want for the db in the textbox and, upon clicking the button, the database is created. For simplicity, let's say the database has 3 fields (First Name, Last Name, Middle Initial).

The layout also includes three textboxes (labeled as First Name, Last Name, Middle Initial). These textboxes will obviously display the fields from the database.

Beneath these fields are the familiar buttons from VB...Add New, Update, Move Next, Move Previous, Delete.

The database would simply allow the user to add a record, and navigate through those records, as well as delete a record if need be.

What I see in all of the examples are tables that allow the user to click on the table and then open an edit or filter screen/layout. The above example would be different. It would start with the Edit layout and avoid the table view, scrollviews, filter screen, etc. These components are great but sometimes a simple viewing of the database (via fields) is what's called for rather than a table view. However, it would be nice to have an additional button that would allow the user to see the data in a table as long as the table is not the focal point of the database and the user can easily return to the main layout (the edit layout).

Again, I appreciate the great examples and I have learned a good deal already but the simple example above, minus all of the scrollviews, tables, etc. would be a great way to show just how functional database work is in B4A and provide those like myself with a starting point to build from.

Thanks again for your assistance. I do apologize if in all of my searching I have overlooked the basic example above. I look forward to your response.

 

[klaus]

Your approach of the problem is exactly the right one.
You define your needs on what the program should do and you have a good idea of the screen layout(s).
Now instead of asking that someone does it for you why don't you begin the project on your own ?
Doing it yourself will let you gain much more experience than using a 'premade' version.
And if you have problems ask concrete questions on the forum with your project and we help you.
It takes of course some more time, but at the end with the experiene you got the next projects will become easier and less time consuming.
I don't agree with the statement that the user wants the edit screen as the main screen.
For me a database contains information that you want to show in different ways with filtering and the editing is secondary, it may be a bit more important at the beginning.

 

[Beja]

Programming is about individual and personal styles... So when I asked the very same questions before I found out the
style of our great mentors like Erel and Klaus are a bit different. I couldn't argue because the benefits I am receiving in
this place and the substantial amounts of time and efforts these wonderful people are putting to help us are so great
that we should accept whatever their time allows them to do.
This being said, I still hold the same viewpoint that you should only give the new user of your tools the head of the
thread.. the blind's cane.. instead of a fancy example with filters and lengthy tables, you show give her/him the basic example
of a database.. If I have a simple example with just one table, two fields, and with add, delete, commit or save, find,
next...etc. then I will not get up form my desk for 24 hours and will pass all meal times, and will have a huge energy to sit
and learn all the other aspects like filtering and querying, add complex SQL statements that I already know... etc.
But being given a complex example that lacks basic database functions.. complexity that I should learn by myself and
accumulate through time... then this may hold me back and may never learn..
The same argument that Klaus put, now I am using it the other way round.. I must learn by myself therefore I don't need
complex examples.. all I need is the Hello World of SQlite.. then I build on it.

 

[cougar]

Beja,

Thanks. You completely grasped my point. I am a self taught coder who learned vb by beginning with simple routines and became hooked enough to move on to far more complex coding. Like you i went without food and sleep many times when building on simple routines.

Klauss,

I have read many of your posts and i have found them very helpful and i do appreciate your efforts. However, your most recent one, in response to my first post on this forum, was of no real help to me. I am not seeking to have someone do all of the work for me but since my bookshelf is full of books that explain database programming by starting with the basics described in my above posts, i assumed there was a similar example out there. As for disagreement on design... I have developed numerous applications for both commercial and government entities that put table view of data secondary to data entry. I have also found this to be the standard approach in the environmental/engineering industry, which i tend to develop applications for. It's a personal preference of mine and those who i develop for. That doesn't make it the only way but it does make it one of many ways to be considered.
I spent yesterday evening stripping all of the code i didn't need from the sqlexample program to get to the basics and build up from there. I will continue that "on my own" and post the finished tutorial so future "newbies" can get a jumpstart on b4a database programming and, as Beja wrote, be motivated to learn more and expand on that Hello World foundation. Thanks!

 

[Beja]

Hi Cougar,
This is what's going on here.. they make it a little hard, but at the end of the day they make a hero out of you.
I hope you contribute with the following so I can go back to learn b4a way of doing database.

1- see the attached form
2- add this code

1- Create the database
2- find:
a) build "criteria" string
b) SQL SELECT statement and pass "criteria" to it to find a record.
c) display the found record (if any)
3- Delete code
4- Add code
5- Add next and prev (buttons not shown on the picture, sorry)

*1 Use abstract designer
*2 Comment each line of code
*3 Table is empty, no initial records unless you see necessary, then only one sample record

I think this you can contribute in this great efforts since everything is being done by contributors
who are just out there to help others.
good luck.

 

[klaus]

@cougar
Please don't consider my post negatively or that it would mean I don't want to help.
The explanation and the vision of your wish is so clear that it looked to me beeing a good project to begin with to learn the language, learning by doing. It expresses my own experience.
My background is mechanical engineering and not computer science.
About two years ago I knew almost nothing about databases.
I had a look at Erels' DBUtils module, found it a bit complicated, and then, because I like to know what I'm doing, I studied the basics of SQLite. The SQLiteExample program was the result trying to make it relatively 'universal'.
If you really need a simpler example and others are interested too I can write one which could be added to the User's Guide.

 

[cougar]

@cougar
Please don't consider my post negatively or that it would mean I don't want to help.
The explanation and the vision of your wish is so clear that it looked to me beeing a good project to begin with to learn the language, learning by doing. It expresses my own experience.
My background is mechanical engineering and not computer science.
About two years ago I knew almost nothing about databases.
I had a look at Erels' DBUtils module, found it a bit complicated, and then, because I like to know what I'm doing, I studied the basics of SQLite. The SQLiteExample program was the result trying to make it relatively 'universal'.
If you really need a simpler example and others are interested too I can write one which could be added to the User's Guide.

Klaus,

I will continue on and get it worked out and i will add the tutorial when done. Should you or others choose to do the same i think it would be of great assistance. Would definitely enhance user guide if added. I hope someone will post an example because mine will surely lack something that yours may not. Thanks.

 

[Dman]

Search here for Barebones. That example helped me tremendously.

 

[mangojack]

cougar , I agree wholeheartedly with your comments .. also coming from a VB background , any application needing data collection utilized Random access files. Over many years I tried my hand at learning SQL to no avail due to the abundance of 'over the top' complicated tutorials / examples.

Coming to B4A I started with Margarets DB class .. then Erels' DBUtils , where I understand Klaus's comments regarding understanding what your doing , I started out with a very small basic app utilizing a SQLite db and learnt my way from there. As I have said in previous posts, I'm still a bit bemused as to how simple the learning curve was this time round, but I'm sure that if it was not for B4A and Forum threads /examples I would not have gotten so far.
I still frustrates me when new members request information / help starting with a SQLite db the immediate suggestion is to have a look at DBUtils and the like, and in a sense bypass learning what they wish to learn

I did start a example app that was pretty much identical to what you suggested in your first post albeit "First Name, LastName,Telephone No"
with the sole intent of demonstrating the very basics of starting a DB , Create a new DB , Add New Record, Edit Existing Record ,Delete Record
and display the data in a simple Listview , but I don't have the time at present so its on the back burner at the moment.

Just in case you have not come across this site, it was and still is invaluable to me as a SQLite reference although the syntax needs a little "tweaking".

w3schools SQL Tutorial ...

Cheers mj

 

[Beja]

In this forum, I never asked for help and didn't receive it in record time.. sometimes I work on
something and find a problem, I throw a question to the forum and wait at my desk for the answer
that usually arrive in a short time.
Erel once said if I don't see what I need then I can post it and they can do it.. same as Klaus said
now in this post.
But I thought I can share my ideas as to improve the examples to be standard so anyone who has prev
experience of being inexperienced absolute beginner and went through any other language tutorials,
then she/he will feel at home if the examples here are so simple and complete. When using functions
like delete or add or find, you are not doing the home work of the learner, but simply you are giving
examples of these statements that your product supports, and how a new user of this product can
use them in their own application. This is the time of learning by doing, not learning by reading only.

 

[cougar]

Thanks to Dman and MangoJack for your input. I have checked out Barebones and it will definitely be helpful. @mangojack - I hope you get around to posting a version of that app one day. I am actually familiar with SQL. I have used it often. It was fairly easy to learn in vb but it's a bit different in b4a...the data control would really be nice. Ha! Thanks!

 

[devlei]

Everything you need to get started with a simple database is on the B4A website. Using the 'Search' sometimes provides too many results for you to find the starting point. The following resources helped me get a flying start to SQLite/databases:

• SQL tutorial (under Documentation) : http://www.b4x.com/android/forum/threads/sql-tutorial.6736/
• DBUtils tutorial (under Documentation): http://www.b4x.com/android/forum/threads/dbutils-android-databases-are-now-simple.8475/
• Chapter 12.4 in 'Getting Started Manual': Detailed & very simple - with Data Entry examples
• The W3 Schools mangojack referred to: http://www.w3schools.com/sql/sql_intro.asp

 

[klaus]

Is this what you were looking for ?
The code is commented and I hope self explanatory.
It's a very simple database with the elementary functions and a user interface kept very simple.

EDIT:
This example and another one are published here SQLiteLight Two simple SQLite projects.

 

[cougar]

Devlei,

Thanks. I have read through those tutorials and i have learned from them but i was still looking for a basic example to build from and then utilize those more complex things in the links you posted. A quick side-note... I went from no programming experience to writing my first simple db app in one night with evb and a beginning tutorial on database development in evb. Within the first 30 minutes i was displaying records in fields on the screen and i thought i was a genius...ha... That spark led me to slowly learn to turn that simple data entry screen into a complex data driven application that was and still is used by many people.
It seems to me that there is a bias against making things too easy for the new developer who happens upon a new programming language, as if over-simplifying a process demeans the development environment. However, it was that over-simplifying and redundant presentations of the most basic processes that made vb the success that it is. I have never had a problem with the fact that i was not a "real" programmer to some in the C++ crowd because my "easy" vb was earning me a living...lol! b4a is amazing and the documentation and tutorials are very professional and fairly in depth. However, there are those who will be far more likely to continue with b4a and move on to the advanced features if they feel that same excitement i did when i wrote my first sql statement and saw a textbox filled with recordset data. There is plenty of time for "paying dues" as s programmer and learning "on your own" but without that initial nudge and process of over simplification many will just give up or move on to an "easier" development environment. Thanks again for your links. It is appreciated.

@klaus...exactly what i was looking for. The B4A version of that evb code that got me hooked on visual basic. Thanks. If this was already out there then i hate that i overlooked it. if it wasn't, then thanks for writing it and i hope it will be the new "hello world" starting point for new b4a developers looking to build database applications. Thanks again!

 

[klaus]

If this was already out there then i hate that i overlooked it.

No, you didn't miss it, I wrote it.
A second one will follow with some more functions.

 

[Beja]

Klaus,

You made my day.. this is exactly what I was looking for and now I can come back and learn B4A database.
This example will have a big impact on the popularity of B4A.
No words are qualified to express my appreciation of the great effort and time you put into this. God bless you.

p.s.
On the emulator it is running perfect, but on my device there is something.. I am following it and will report it.

 

[Beja]

Hi Klaus,
Sorry but couldn't understand this piece of code:

    If Cursor1.RowCount > 0 Then
        'if it exists show a message and do nothing else
        ToastMessageShow("This entry already exists", False)

Each time I use the "Add" key to add a new record, I receive a message saying:
"This entry already exists"

Also some time when I open the app, I receive this message:
An error occurred in sub:
main_readdatabase (java line:
508)
java.lang.IllegalStateException:
attempt to re-open and already-
closed object: SQLiteDatabase:
/data/data/b4a.sqlitelight

 

[Beja]

Got it!

I removed the code above, may be it was there for debugging.. I also cleared the EditText boxes
whenever pressed the Add button.
Thanks again Klaus

 

[klaus]

The code you are refering to is the check if a same entry already exists.
I look in the database if there exist an entry with the texts in the EditText views.
If there exist one, Cursor.Rowcount is > 0 !
If you display an entry and click on the Add button without having changed anything you get the message that the entry already does exist !
For the second error you encounter sometimes could you run the program in debug mode and report the error message you get.

 

[Beja]

Thanks Klaus,
the code now is comparing the current displayed record against itself, and it will definitely find a match!!
Now I see the problem.. because the "Add" button is not clearing the edit boxes before checking for existing entry.
Clicking the add button, with this code means the current displayed record will soon be duplicated and the code
prohibiting this because of the ID's unique value requirement. When I removed the code a new record number is
generated automatically (incremented by 1), but the old field values of the edit boxes were carried forward and I
had to overwrite them. That's why I added the line to clear the boxes in the beginning of the Add button code.
This is what I think.. may be wrong.