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"))

 

[Maartensaps]

record pointer

At first I thought the record-pointer did not do well, but more testing showed the following "glitch"

I have a database with 4 records. record 1 contains "1", record 2 contains "2", etc (Just for testing)
when I change the contents of record 1 (into "1" again), it stops being record 1and becomes record 4. All the other records skip 1 place.
So now: record 1 contains "2", record 2 contains "3", record 3 contains "4" and record 4 contains "1"

Apparently db.UpdateRecord deletes the original record and appends a new record at the end.

Is there a way to actually update the physical record ? (since I use it's place as "key")

For the rest: AWESOME.

 

[margret]

Can you post a code sample where you were getting the odd results using the db.Set_Pointer. I would like to see what it is doing...

 

[margret]

I see, what is happening is that the database indexes on the value in the datafields. So you are changing the values of the first fields and thats why they are now in a different position. I would create an extra field in the database file that you store an index key in. This way you can always pull up the record by the key.

 

[Maartensaps]

record pointer

Yep, that is what I was thinking also, so I am adjusting the routines and use a db.FindRecord(). Should work just as well. Just about to test brrrrrrrr....

 

[mb1793]

hi its me again i have two questions

#1 when i run your program i get an error message saying " an error has occured in sub: main_activity_create(java line:250) java.io.filenotfoundexception: 1.bal"

#2 and is it possible for me to to search items and add items to another database like dropbox, memento , or excell ? and if so how would i go about doing that? any help would be greatly appreciated .

thanks in advance

 

[margret]

hi its me again i have two questions

#1 when i run your program i get an error message saying " an error has occured in sub: main_activity_create(java line:250) java.io.filenotfoundexception: 1.bal"

I can't explain why you get a file not found looking for 1.bal because this class or the demo does not use a file named 1.bal. I would suggest you re-download the demo into a new directory and try it again.

#2 and is it possible for me to to search items and add items to another database like dropbox, memento , or excell ? and if so how would i go about doing that? any help would be greatly appreciated .

thanks in advance

This class was not written to read and write other database types. You can however, pull data from this database and write to any other format you wish, you will just have to handle the other database with different code.

Please let us know after you re-download the demo code what happens. As long as you are using B4A 2.0+ and the newest Reflection library 2.2, you should not have any issues running this demo.

 

[hoochy]

Hi margret,

Firstly can I please say what a fantastic class you have created. I stumbled across b4a and then this class and it has done exactly what I am after

One question though - is it possible to get the database to go to a random record within the database?

For example, we have db.NextRecord & db.PreviousRecord, is there anyway to create db.RandomRecord?

Any help greatly appreciated.

 

[hoochy]

Of course, if anyone else is able to help too that would be great! I really need this 'Random' button to complete a section of my project :BangHead:

 

[rboeck]

It could be very easy: generate an random value between0 and recordcount-1
Use Set_Pointer (m_Pointer As Int) to go to this record.
Greetings
Reinhard

 

[Kbogle24]

How would I sort the records by date or a field?

 

[margret]

The way it works now is it sorts on the first field + second field, etc. I do plan on converting this Class to a Lib and I will try to add that option plus some other functions I found it needed.

 

[Kbogle24]

How would I open a CSV to work with this class or How can I create a Database in a text editor to work with this? I have tons of data I want the user to be able to view and maybe edit.

 

[margret]

Create your database with code and add a few records. Then save it to the root of your sdcard which you can then copy to your PC. You can then open it on your PC and see the data and structure. You can use Notepad. Make your data match the first record and you can add your data that way. I use dbase to compile large data files and then write it out as an ascii delimited file.

 

[Kbogle24]

This is what I came up with and seems to work good. Takes a CSV and puts it in your DB class. I am sure my code needs to be cleaned up but it works for me.

Sub Convert_CSV(InDir As String,File1 As String,OutDir As String,File2 As String)

   Dim filestring As String
   Dim filedata() As String 
   Dim datacount As Int
   datacount = 0
   Dim i2 As Int 
   Dim Num As Int
   Num = 0
   File.Delete (OutDir,File2)
   DB.CreateDataBase(0, File2, OutDir, Array As String("pla","pl","in","out","se","de","sp"))
   
   If File.Exists (InDir, File1) Then
        filestring = File.ReadString(InDir, File1) 
        
      Dim Matcher1 As Matcher
      Matcher1 = Regex.Matcher("\,", filestring)

      Do While Matcher1.Find
          datacount = datacount + 1
      Loop
      
      Log(datacount)
      filedata = Regex.Split ("\,", filestring)
      i2 = datacount/ 6
      Log( "I2 = " & i2)
      For i = 1 To i2
         DB.PutField ("pla",filedata(Num))
         Num = Num +1
         Log(filedata(0))
         DB.PutField ("pl",filedata(Num))
         Log(filedata(1))
         Num = Num +1
         DB.PutField ("in",filedata(Num))
         Log(filedata(2))
         Num = Num +1
         DB.PutField ("out",filedata(Num))
         Log(filedata(3))
         Num = Num +1
         DB.PutField ("se",filedata(Num))
         Log(filedata(4))
         Num = Num +1
         DB.PutField ("de",filedata(Num))
         Log(filedata(5))
         Num = Num +1
         DB.PutField ("sp",filedata(Num))
         Log(filedata(6))
         Num = Num +1
         DB.AddRecord
      
      Next

          
   End If

End Sub

 

[b4AMarkO]

Margret,
Thank you for this ... I am enjoying it.

A bit new here so here goes

Second DataBase

db.CreateDataBase(1, "family-contacts.dat", File.DirInternal, Array As String("name", "address", "city", "state", "zip", "phone"))

Does it have to have the same structure as the first? I'm thinking not ....
But if I change its structure to say

 Array As String("reportdate", "incident"))

If the structure changes then there should be GetFields, clearfields, putfields created for it specifically ... Right?
Such as

Sub ClearFields_1
  etReportDate.Text   = ""
  etIncident.Text  = ""
End Sub

Then I would not need the following correct?

Sub ClearFields_1
    ClearFields_0
End Sub

I hope I am making sense .... I have the first DB working great just trying to figure out the second one.
BTW - When I created the first DB I did not create second ...... How do I create it now without messing with the first one ......

Sorry for so many questions .... Thank you in advance

 

[margret]

That is correct. Each workarea should have it's own Put, Get, Clear Fields sub.

 

[netsistemas]

ATTACH DATABASE - Link database additional - two databases in one

You cant work with 2 databases in only one:
Attach db to you data base main:

Sql1.ExecNonQuery ("ATTACH DATABASE '" & nombreDeBase & "' AS WEB")

now, you can eject:
select * from WEB.TABLE

Copy the database of your ftp to local:
myZip.ABUnzip(TargetDir & "/" & zipFileUnloaded, TargetDir)

 

[ValDog]

I like what I see here - can this be adapted for use with SQLite databases?

 

[margret]

I like what I see here - can this be adapted for use with SQLite databases?

This works with an ASCII file(one it creates) in which the data can be read in Notepad, etc. So, as it is, you can store data retrieved from an SQL file but it will not read a SQLITE file. I still plan to add some new features and functions but have been under the weather.

 

[ValDog]

This works with an ASCII file(one it creates) in which the data can be read in Notepad, etc. So, as it is, you can store data retrieved from an SQL file but it will not read a SQLITE file. I still plan to add some new features and functions but have been under the weather.

OK, thanks!