Android Tutorial New Easy to Use DataBase Functions V1.0 with Samples

* * * UPDATE * * *

July 8th, 2012 - New DataBase Class http://www.b4x.com/forum/additional-libraries-classes-official-updates/22118-class-databases.html


JUST POSTED - The New Easy DataBase V1.6 Functions with Samples and Full Working App! - September 3rd, 2011 5:00 PM

Just added some new date functions per request to Version 1.6. The 1.6 sample is attached below. It now includes these new date functions:

db.Date() --- Returns the current date
db.DateNew("09/03/2011", 30) --- Returns a new date from any selected date, enter positive or negative numbers as second parameter
db.DateNOD("09/03/2011", "03/11/2009") --- Returns the number of days between any two dates passed to it

See Post # 24 for other details: http://www.b4x.com/forum/basic4andr...sy-use-database-functions-v1-0-samples-3.html


Hello All. I just added Version 1.2 to this post. I found a bug that the db.pointer did not update correctly depending on the sorted results in the db.AddRecord and db.UpdateRecord functions. This has been corrected. I also made all functions not return any exceptions if you called them and the database didn't exist. It returns a Toast message stating that the file could not be found.

*** Older Post ***
I Just added Version 1.1. It has a few new things added and the variable names corrected to be more in line with the other functions. In version 1.1:

db.Pointer
db.RecordCount
db.FilePath
db.FileName
db.Field

I have also moved all variable declarations out of the main project. This leaves a totally clean main module to work in. All support for the database is now in the code module. I have attached a new Zip of version 1.1 but I also left the original. I changed over to 1.1 just this afternoon and went through it pretty quick. I feel it is tested and working just as it should, but I went through it quick. Please let me know if you find any errors or issues and I will fix them.

*** Original Post Below **************************************

Hello All,

No extra libraries are needed, just the CORE B4A library. I have tested some of the functions on my NookColor with up to 2500 records. With 2500 records, it moves very fast and searches are instant. All data stored will be in string format, however, you will be able to store links to images, web pages, etc. I wrote and posted the Strings functions here: http://www.b4x.com/forum/basic4android-getting-started-tutorials/10365-string-functions.html, and these functions will work in a similar way.

The functions and sample code are now attached. Some benefits may be that it is easier for new users to learn and less overhead on the processor. You don't have to learn SQL, etc. and the code required for each function is much less. Each device will have its limit on the number of records you can have before the system slows down, based on memory and processor. This will do just fine for most apps and contacts style programs. If you need to save images, objects, etc. directly into the database this will not work for you. You can have as many fields of any size as B4A and Android will support. It will also include the s.GenerateRecords(1000) functions, so you can populate the database with hundreds or thousands of records to test how the app performs under max load. Also, the database handles the pointer for you, it also handles BOF, EOF and no records in the database. It makes your coding job much easier!

If you have problems or questions, please post them and I will try to help. Hope you find it useful!:sign0089:

___________________________________________________________________
db.AddRecord()

db.DeleteRecord()

db.Exist()

db.FindRecord()

db.GenerateRecords()

db.GetRecord()

db.GetStructure()

db.ListRecords()

db.UpdateRecord()

db.FirstRecord()

db.LastRecord()

db.PreviousRecord()

db.NextRecord()

*** Added in Version 1.1 ***

db.Pointer

db.RecordCount

db.FilePath

db.FileName

db.Field

___________________________________________________________________


Thanks,

Margret

If you are using a version less than 1.2, please download the newest version, db.pointer issues were found in previous version(s).

 

[Kamac]

:sign0142:

I gotta try out your library ASAP

 

[margret]

Easy To Use DataBase Version 1.5

Update – Easy To Use DataBase Version 1.5 Coming Soon – Friday August 12th, 2011

New in Version 1.5 is a callback function for subs to more completely automate your programming task and again requires less user code. This new version will include a full working program demo. The demo program will have full working code of each feature using multiple database file structures. New callback functions are listed below:

ClearFields
PutFields
GetFields

Thanks,

Margret

 

[Gary Miyakawa]

:sign0098:

Margret Rocks !

Gary M

 

[margret]

The New Easy DataBase V1.5

JUST POSTED - The New Easy DataBase V1.5 Functions with Samples and Full Working App are Attached to Post #1 - August 14th, 2011 3:29 PM

Just added is Version 1.5 of Easy DataBase Functions with full working App Code! The Easy DataBase functions and sample code are now attached to post #1. The ADR.DataBaseProgram_1.5.zip is a full working app to show how to implement the db. Functions in code. Also, the new version 1.5 handles not only the db.Pointer for you as well as BOF, EOF, No records as the previous version did, but also handles DataBase does not exist. Version 1.5 has three new callback subs that are listed below.

____________________________________________________________________

NEW: The following are main module subs that can be used to automate the assignment of Edittext and fields in your program. Version 1.5 now
calls back to these subs, if they exist.

ClearFields        ‘Sub to clear Edittext values

GetFields          ‘Sub to assign fields values to Edittext

PutFields          ‘Sub to assign Edittext values to fields

____________________________________________________________________


Download is posted here: http://www.b4x.com/forum/basic4android-getting-started-tutorials/10553-new-easy-use-database-functions-v1-0-samples.html

Thanks,

Margret

 

[myriaddev]

Thanks!

I have it in my code and working!
Thanks, Jerry

 

[wjones]

Hi all, sorry im kinda new to B4A but have just started using margrets DB files and am having a little Issue. Below is my code.

Sub ListView1_ItemLongClick (Position As Int, Value As Object)
Dim srch As String
srch = value
db.FindRecord(srch) 'Find the record
Msgbox(db.Field(0,0)&CRLF&db.Field(1,0),"Search Results") 'Search ALL Fields in the datafile for match, it is not case sensitive
Activity.Title = srch
End Sub

The problem is I cant get the messagebox to display the results of "srch" the activity title updates corrects and displays the corrects data, Can i not use db.FindRecord(srch) The examples show db.FindRecord("Texttosearchfor") but obviously if i use ""s then it would search for that text and not the srch text. I hope this makes sense to someone I've tried for a while now to get it going. Appreciate the help.

btw thank you SO much for this margret i was dreading learning the database part, but you've made it a breeze!

 

[margret]

db.findrecord

What version are you using? You have value as an object and should be a string. Do you see a toastmessge that says no match found?

Margret

 

[wjones]

:signOops: Nevermind it was my code that was wrong, sorry still getting used to this, it's been a while since i've used VB. :sign0104:

Thanks again for your code. Its fantastic. cant thank you enough. :sign0188: :sign0089:

 

[netchicken]

Margret can you please add a section on working with dates?

I am totally stuck with adding dates to a sqlite db and then subtracting the saved date from now to get a date difference. I was going to use julian dates, as they can be manipulated, and I don't need the user to see the date.

http://www.b4x.com/forum/questions-help-needed/11269-working-date-sqlite-shopping-list-exercise.html

There seem to be lots of discussion about it on the net but no examples I could find.

 

[margret]

Date functions

Margret can you please add a section on working with dates?

I am totally stuck with adding dates to a sqlite db and then subtracting the saved date from now to get a date difference. I was going to use julian dates, as they can be manipulated, and I don't need the user to see the date.

http://www.b4x.com/forum/questions-help-needed/11269-working-date-sqlite-shopping-list-exercise.html

There seem to be lots of discussion about it on the net but no examples I could find.

Sounds like a good idea. I'll look into it, I'm sure I can come up with something.

Margret

 

[margret]

New Ver 1.6 Posted

Hello,

As per your request I added these date functions to the DataBase. Please see post #1 for info and to download the 1.6 sample:

It now includes these new date functions:

db.Date()
db.DateNew()
db.DateNOD() - DateNOD Stands for Number Of Days

Thanks,

Margret

 

[splatt]

File format

Hi Margaret,

Your db module looks excellent.

Are fields delimited in anyway when they are stored in the db file?

Are fields enclosed in quotes and comma separated, with a new line for each record?

I have a couple of large datasets to test.

 

[margret]

Database Structure

Hi Margaret,

1..Are fields delimited in anyway when they are stored in the db file?

2..Are fields enclosed in quotes and comma separated, with a new line for each record?

Hello,

The answers are as follows:

1..The fields are not delimited at all

2..The fields are not in quotes or separated with any character, and yes, there is a new line for each record. In a text editor it would look like below for the following data:

Jim Smith-25 spaces long
2789 West Drive-25 spaces long
Atlanta, GA 37341-25 spaces long
(706) 892-0938-14 spaces long

Jim Smith                2789 West Drive          Atlanta, GA 37341        (706) 892-0938

Thanks,

Margret

 

[Kbogle24]

Anyway to add images to a database?

 

[margret]

Anyway to add images to a database?

This current version will not hold the actual bitmap, but will hold the reference to the image. This should be all most need unless you have some special need.

 

[morecrabs]

Would like to give u an idea for the module, perhaps its already there and I don't understand some of the functions but a method for sorting all unique entries within a field column..

Used to program vb6 as a hobbist.... been. A few years and found your module really useful for torwards remebering how, keep up the good work and thanx..

 

[margret]

Hello and thank you. It does this already! You define the fields in the order you want. It indexes on the Field1 + Field2 + Field3, etc. Just list the structure of your database in the order you want them sorted. I have six apps I am updating and then I plan on doing some more work on the database module.

Margret

 

[morecrabs]

Actually I was refering to during the query process, to search for all unique entrys ie a column containing a series of city names

(detroit, Montreal,windsor,chicago,detroit,windsor)

And have the query return

Detroit, Montreal, windsor, chicago

Orrder isn't as important as elimation of dupicates or did I further misunderstand your explainnation?

If it does this already could you point me in the correct direction?

Thanx again and sorry for my ignorance

 

[margret]

I've gone back and looked through the code. It will not do what you are looking for at the current time. I will update this for group search functions. However, it will have to be after I complete two updates I am working on for other projects but I will add this.

Thanks,

Margret

 

[mleffler]

Possbile feature for library.

I like your library and am using it a lot.

One thing missing that I use often is a timestamp on the record. Almost all records I store in a database are going to need a timestamp. Most of my work involves either storing data on a group of devices over time or seeing the change in readings of one device over time.

I created a sub to do that for my code but I imagine others would want that also.