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

Attachments

  • db_Class.zip
    7.2 KB · Views: 1,169
  • db_Class_With_Demo.zip
    19.4 KB · Views: 1,473
Last edited:

Maartensaps

Member
Licensed User
Longtime User
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.
 
Last edited:

margret

Well-Known Member
Licensed User
Longtime User
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

Member
Licensed User
Longtime User
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

Member
Licensed User
Longtime User
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

Well-Known Member
Licensed User
Longtime User
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

New Member
Licensed User
Longtime User
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 :D

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? :D

Any help greatly appreciated.
 

hoochy

New Member
Licensed User
Longtime User
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

Well-Known Member
Licensed User
Longtime User
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

Member
Licensed User
Longtime User
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.
 
Last edited:

margret

Well-Known Member
Licensed User
Longtime User
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

Member
Licensed User
Longtime User
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.

B4X:
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

Member
Licensed User
Longtime User
Margret,
Thank you for this ... I am enjoying it.

A bit new here so here goes

Second DataBase

B4X:
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
B4X:
 Array As String("reportdate", "incident"))
If the structure changes then there should be GetFields, clearfields, putfields created for it specifically ... Right?
Such as
B4X:
Sub ClearFields_1
  etReportDate.Text   = ""
  etIncident.Text  = ""
End Sub
Then I would not need the following correct?
B4X:
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
 

netsistemas

Active Member
Licensed User
Longtime User
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

Active Member
Licensed User
Longtime User
I like what I see here - can this be adapted for use with SQLite databases?
 

margret

Well-Known Member
Licensed User
Longtime User
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

Active Member
Licensed User
Longtime User
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!
 
Top