B4A Library [Class] DataBases

Here is a new Class that creates/handles data files. It is an evolution of the db code module but should be much easier to use and learn. It is still in Beta and I would love to have any feedback. I have attached the Class code and have also attached the Class with Demo code.

Requires B4A 2.0+ and Reflection 2.2 Library

db Class Module

Commands

• FileName_ As String
  Returns the filename in the current work area
• FilePath_ As String
  Returns the path of the current work area
• Record_Count As Int
  Returns the number of records for the database in the current work area
• Record_Pointer As Int
  Returns the record pointer (current record) for the database in the current work area
• Set_Pointer (m_Pointer As Int)
  Used to set the record pointer of the database in the current work area. The range must be between
  0 and the RecordCount -1
• AddRecord
  Adds a record to the current DataBase in the current work area, sorts the entry based on the
  data entered and sets the DataBase Pointer to the newly added record.
• FindRecord (SearchStr As String) As Boolean
  Used to find a match to SearchStr. Searches every field in each record
  of the database and returns the first record that any Fields data matches the
  SearchStr. The more data entered the more likely the desired record will be returned
  This search IS NOT case sensitive and returns True if a match was found
  Example:
  
  If FindRecord("Billy J. Jones") Then
• ResetPointers
  Set all pointers for all databases in all work areas back the the last known
  value. Use in Activity_Resume to return back to known location after Activity_Pause
  or after orientation change. NOTE: These pointers are saved automatically, you do not
  need to try and save the pointers in Activity_Pause
  
  db.ResetPointers
• GetRecord
  Loads the record at the Pointer position into memory then
  calls back to the calling activity to the Sub GetFields_X,
  where X is the workarea number 0-9. You should have a
  GetFields_X sub in each of your Activities that makes a
  call to this db class. No Exception is thrown if the
  GetFields_X sub is missing. The GetFields_X sub is used
  to populate your EditText.Text fields. This helps automate
  you code. Example:
  
  Sub GetFields_0 'Put contents of database fields into EditText fields
  name.Text = db.GetField("name")
  address.Text = db.GetField("address")
  city.Text = db.GetField("city")
  state.Text = db.GetField("state")
  zip.Text = db.GetField("zip")
  Phone.Text = db.GetField("phone")
  End Sub
• GetField (mfieldname As String) As String
  Returns the value of the database field named mfieldname
  
  EditTextAddress.Text = GetField("address")
• PutField (mfieldname As String, varvalue As String)
  Writes any passed value to the database field memory variable named mfieldname.
  Does not write to disk. See AddRecord or Update record for writing the record
  to the disk.
  
  PutField("address", "8929 West Way Circle")
  
  or
  
  PutField("address", EditTextAddress.Text)
• DateNew (mDate As String, HowManyDays As Int) As String
  Returns a new date from the date passed plus or minus HowManyDays. Pass mDate as String,
  HowManyDays As Int. HowManyDays can be positive or negitive numbers.
  
  DateDue = DateNew("02/04/2012", 90)
• DateNOD (CurrentDate As String, OtherDate As String) As Int
  Returns the number of days that have passed between two dates.
  Pass the dates as a String
• Date ( As ) As String
  Returns the systems date as a String
• ActiveFields As Int
  Returns the current number of data fields in the current work area
  Returns number as Int. This object is Readonly
• GetStructure ( As )
  Displays the structure of the database in the active work area
• GetStructure2 (Work_Area As Int)
  Same as GetStructure but accepts the work area as an Int. This will display the
  structure of any database in any workarea even if it is not the active database.
• GenerateRecords (Howmany As )
  Use this for testing to populate the database with random data
  Accepts a number 1 to 10000 as Int. Populates the database in
  the current workarea.
• UpdateRecord ( As )
  Updates the record at pointer position in the current workarea with data
  stored in the memory fields. See db.PutFields() to place data into their
  memory location.
• DeleteRecord
  Delete the current record at Pointer position in the current work area.
  A confirmation box will be displayed, if user selects Yes, record will
  be deleted. The next record following will become the current record.
• FindExact (SearchStr As String)
  Find an Exact match to the SearchStr passed. Not case sensitive.
• FirstRecord
  Moves Pointer to the first record in the DataBase and reads the record into memory
• LastRecord
  Moves Pointer to the last record in the DataBase and reads the record into memory
• PreviousRecord
  Moves Pointer to the previous record in the DataBase and reads the record into memory
  BOF is handled and will show a Toast Message if BOF is reached
• NextRecord
  Moves Pointer to the next record in the DataBase and reads the record into memory
  EOF is handled and will show a Toast Message if EOF is reached
• SearchDate (CurrentDate As String, CompareToDate As String, DateRange As Int) As Boolean
  Returns True if the CurrentDate and the CompareToDate are within X number of days(DateRange)
  of each other. Otherwise False will be returned.
• ListRecords (ListTitle As String, dbFields() As String)
  Returns a list with your selected fields in an InputList.
  If user selects an entry, it reads the record in to memory
  and returns the number of the record selected and sets the
  record pointer to this record.
  
  db.ListRecords("Contact Listing", Array As String("name", "address", "phone", "zip")
• PullList (dbFields() As String, Filter As String) As List
  Returns a list with the select fields from the database. Pass the field names as
  a string. Returns the listing as a List variable. If no Filter is needed pass
  "". If a filter is needed pass Text to match:
  
  db.PullList(Array As String("name", "zip", "phone", "37421"))

 

[Mahares]

Thank you for your continued and valuable contributions to the forum. I am sure it is going to help many of us. I installed the demo and tested briefly:
1. I changed the folder to DirRootExternal so I can open and view the data in either files, but the files would not open. I guess I was looking for a SQLite database, but the files must have a special format.
2. As with databases in general, you might want to think about devising a way to export the file type to a text file so it can be manipulated outside the scope of the device. Also, an import feature will be valuable in your next class update.
3. Of course you want the ability to query and report, etc.
It is easy to offer ideas, but hard to implement them.

 

[asmag]

Hi Margret!

Thank you for your contribution to the forum. I Installed the demo and it worked fine.

Att. asmag

 

[bluedude]

Maybe it would be nice to add GEO spatial search

 

[metrick]

Thank you.

 

[LisaM]

Saving Images

I am assuming i cant save images using this database?
Say i had an imageview and i used the gallery to set a picture to the imageview would i be able to save that image?

 

[margret]

I am assuming i cant save images using this database?
Say i had an imageview and i used the gallery to set a picture to the imageview would i be able to save that image?

You can save the reference to the image and reload it to an ImageView with no problem. This also saves memory space. Use the code below as an example to save and reload the image:

'To set the ImageView1 to the saved info
ImageView1.Bitmap = LoadBitmapSample("", db.gf("your_fieldname"), 400, 300)

'Save the ImageView1 info to the database
db.pf("your_fieldname", ImageView1.Bitmap)

 

[EduardoElias]

Hi,

Nice job!

What I could understand from the code is that it is limited to 10 work areas that is the equivalent to a database each. Any problem on raise that limit?

Just a hint, since what is good always need to be changed, It could be nice to have wrapper on your code that could really implement it as class based database:

Dim dbClient as dbHandler
Dim dbItems as dbHandler
...

dbClient.Initialize(params to initialize db.initialize)
dbItems.Initialize(params to initialize db.initialize)

after that all the references to dbClient will be mapped to work area "0" and dbItems to work area "1" that will make transparent in the code the switching process.

Can I have more than one of the db class created in the same app? Each one will have 10 areas?

Thanks!

 

[margret]

Yes, you can have more work areas than Ten in the project. It is setup so that only Ten data files can be open at the same time. You can switch datafiles any time you wish on the fly. So you could have 100 files if you wanted.

As to your last question

Can I have more than one of the db class created in the same app? Each one will have 10 areas?

I would think so, you will just need to try it and see but I think that will work. Please post back after you try it.

The work area can be changed very easy like:

db.SelectWorkArea(0)

db.SelectWorkArea(4)

db.SelectWorkArea(9)

When I get a chance, I will modify this code so you can use the Alias/database name to select a work area.

 

[LisaM]

Thanks Margaret,
I did actually figure out how to do it...Amazing huh!! lol.
However your code looks much neater

Sorry to keep bugging you but i have another question can i import a csv file to this database. I have an app i made in app inventor and i am replacing it with the app i made in Basic4android I would like to be able to give users the option of exporting their data from the old app and importing it to the new one.

Thnx

 

[margret]

You would use something like this:

Dim su As StringUtils
Dim importdata As List
importdata = su.LoadCSV(File.DirInternal, "mydata.csv", ",")

How many columns are in each record?

I'll be glad to help you but I would need to see a sample csv file. Can you attach one to your next post?

 

[mb1793]

hey margret , im trying to run your test program , but everytime i do i just get an error saying im probably missing a library . but ive already downloaded the reflection library and i beleive my b4a is up to date. i have the androidmanifest xml document , but i do not have the jar . could that be the problem ?

 

[klaus]

Did you check the Reflection library in the Libs Tab in the lower right corner in the IDE ?

Best regards.

 

[mb1793]

yes i did check the reflections library , still no luck i appreciate the help klaus.did you have any issues with running this program youreself?

 

[klaus]

I hadn't tested it yet, but just tried it.
It compiles OK and shows on the device.

What Reflection library version do you have ? It should be 2.20.

Best regards.

 

[mb1793]

ok cool are you running b4a 2.2 ? and yes i am running the 2.20 version of reflection . i think i might try downloading the version 2.2 of b4a .

 

[margret]

You should have the Reflection.xml and Reflection.jar file both in your extras library folder. If you don't this would cause that error.

Requires B4A 2.0+ and Reflection 2.2 Library

 

[Maartensaps]

What ever happened to clipper etc

I was very happy to come across this database-class.

But after fighting it for hours I just don't understand it.

It comes with an example, but it seems that half the subroutines in the example are necessary to run, and the other half not (as they are the example and very specific)

For now I have 3 questions:

How do I open a database.

and

How do I write it to disk.
So far I managed to create a database, populate it with info and work with it, but every time the app starts, it re-creates the database (after testing if it exists), so obviously it does not get saved.

and

And which subs are needed? (as every time I ad one, it calls another one that calls another one, etc etc.)

Desperately hoping for some help:sign0163:

 

[margret]

Below is some sample code. You can see what each block does. Hope this helps:

Sub Globals
   Dim db As db            'Dims a variable to use for the db class
   db.Initialize(Me)         'Initializes the db object for use, pass Me so the class will know the calling Activity

   'This creates a database or opens one if it exist
   db.CreateDataBase(0, "misc-contacts.dat", File.DirInternal, Array As String("name", "zip", "phone"))
End Sub
Sub adddata
   db.PutField("name", "Jimmy Joe Brown")
   db.PutField("zip", "37373")
   db.PutField("phone", "(473) 998-9900")
   db.AddRecord   'Add a record with the above info
   
   db.PutField("name", "Betty Sue Brown")
   db.PutField("zip", "37373")
   db.PutField("phone", "(473) 996-0099")
   db.AddRecord   'Add another record with the above info
End Sub
Sub showdata
   'Show the records in the database
   db.ListRecords("Select Record to View/Modify", Array As String ("name", "zip", "phone"))
End Sub

 

[Maartensaps]

I am soooo Happy

Great, Thanks.

Works like a charm.
I guess I was making things way way to complicated.:sign0142: