B4J Question work with database from a separate class/form

[JaunLukePicard]

I want to be able to access SQLLite database from different Class Modules which each use their own form on screen. How exactly is this done? So far the examples I have found only utilize dbUtils and one Main Form.

For example: If I have multiple forms like...
Customers
Users
Inventory
Storage
...

Each are separate Forms. Is it possible for each of these class modules (forms) to access specific code modules for their data from SQLLite?

 

[BPak]

Attached is a partly built example program using SQLite.

I use a Code Module (DM) to create the Databases and Tables.

The Database which is SQL1 as Sql can be referenced from the DM Module in any other Module.

The Example has a Main Module that starts the app.
A DM module to work the Database.
A DButils Module to use SQLite Database functions.
An Account Class Module to work with Accounts
An Invoice Class Module to work with Invoices.

DM.SQL1 can be used in any Module to use the Database.

Hope the example helps.

 

[JaunLukePicard]

Yes BPak this definately helps. Thank you!

I noticed that you use GetSystemProperty to help check for the folder where the database exists or if it doesn't create it. Is there a way to return the path where the application is currently running. This would most likely not be the user's home directory, but a separate location.

For example: Lets say I want to get the Current Directory and then create the folder beneath it to store the database if it doesn't exist already?

 

[JaunLukePicard]

I am looking for this feature because I will use it to help populate an ini file used for storing settings.

 

[BPak]

The Current Directory would be the DirApp
(File.DirApp)
Use that as the str1 parameter instead of the User location.

str1 = File.DirApp
    If Not(File.IsDirectory(str1, "MyCar")) Then
        File.MakeDir(str1, "MyCar")
        FilePath = File.Combine(str1, "MyCar")
    Else
        FilePath = File.Combine(str1, "MyCar")       
    End If
    SQL1.InitializeSQLite(FilePath,"mycar.db",True)

 

[JaunLukePicard]

Thank you BPak.

I started to work with your code and another bit of code regarding a Splash screen that comes up and then disappears leaving the user at the Main Menu. I wanted to start the Main Menu that the user will see from clsfrmMain. Everything seams to be right except that when the Main Menu appears it completely disappears and the program ends. I tried to figure this out.

Could you assist me please. I have uploaded a file with the program I have so far. Not all of the code was converted just yet for Invoices. Accounts was already completed.

Thanks again for all of the assistance.

 

[BPak]

The error is where you are setting the Path to the Database

Public Sub subCreateDBase

    Dim str1 As String
    'Create database under the application Folder
    str1 = File.DirApp
    ' Create Database under User Folder in the Database Folder
    'str1 = GetSystemProperty("user.home","")
    If Not(File.IsDirectory(str1, "Database")) Then
        File.MakeDir(str1, "Database")
        FilePath = File.Combine(str1, "Database")
    Else
'        FilePath = File.Combine(str1, "dbsStore.db")    ' incorrect
        FilePath = File.Combine(str1, "Database")        ' path ONLY    ---- < correct
    End If
    SQL1.InitializeSQLite(FilePath, "dbsStore.db", True)
    ' Create Tables for Database - if NOT exist
    subCreateTables
   
End Sub

 

[JaunLukePicard]

Ah! I thought I had changed all of the references properly. Next time I will use Find and Replace. That's why the error. Thank you!!

 

[JaunLukePicard]

Hello again BPak.

After the last issue I started to create the table in dbStore and that went well. I started next to Add records. I am apparently missing something when it comes to adding records. I followed (I believe) your example and am getting an error. I believe it has to do with the ID field (Store_ID) of the fact that a field is empty.

Could you take a quick peak at the StoreEditor class to see if I am missing something.

 

[BPak]

There are a few errors in the code.

In the Designer you have the WRONG ID for the WebSite - it is the same ID as another field.

    Private TextBox_FAXNumber        As TextField
    Private TextBox_WebSite            As TextField            ' error = ID wrong in Designer

Spelling mistakes in the Map entry

Sub subSaveRecordProperties

'    M.Put("Store_ID", DBUtils.DB_INTEGER)            'Primary Key
'    M.Put("Store_DataAdded",DBUtils.DB_TEXT)        'Date Record was added to Table
'    M.Put("Store_DataModified",DBUtils.DB_TEXT)        'Date Record was last modified in Table
'    M.Put("Store_CodeSequence", DBUtils.DB_TEXT)    'Sequence of Record       
'    M.Put("Store_Name", DBUtils.DB_TEXT)            'Store Name
'    M.Put("Store_AddressLine1", DBUtils.DB_TEXT)    'Address Line #1   
'    M.Put("Store_AddressLine2", DBUtils.DB_TEXT)    'Address Line #2
'    M.Put("Store_City", DBUtils.DB_TEXT)            'City
'    M.Put("Store_State", DBUtils.DB_Text)            'State
'    M.Put("Store_ZipCode", DBUtils.DB_TEXT)            'Zip Code
'    M.Put("Store_PhoneNumber", DBUtils.DB_TEXT)        'Phone Number
'    M.Put("Store_FAXNumber", DBUtils.DB_TEXT)        'FAX Number
'    M.Put("Store_WebSite", DBUtils.DB_TEXT)            'Web Site

    Dim mRecordMap As Map
    mRecordMap.Initialize
    mRecordMap.Put("Store_ID",Null)
    mRecordMap.Put("Store_DataAdded",TextBox_DateAdded.Text)            ' error - DateAdded      (see dmStore) Spelling
    mRecordMap.Put("Store_DataModified",TextBox_DateModified.Text)        ' error - DateModified  (see dmStore) Spelling
    mRecordMap.Put("Store_CodeSequence",TextBox_RecordNumber.Text)
    mRecordMap.Put("Store_Name",TextBox_StoreName.Text)                    ' error - Store_Nam - Spelling Store_Name
    mRecordMap.Put("Store_AddressLine1",TextBox_AddressLine1.Text)
    mRecordMap.Put("Store_AddressLine2",TextBox_AddressLine2.Text)
    mRecordMap.Put("Store_City",TextBox_AddressLine1.Text)
    mRecordMap.Put("Store_State",TextBox_AddressLine2.Text)
    mRecordMap.Put("Store_ZipCode",TextBox_ZipCode.Text)
    mRecordMap.Put("Store_PhoneNumber",TextBox_PhoneNumber.Text)
    mRecordMap.Put("Store_FAXNumber",TextBox_FAXNumber.Text)
    mRecordMap.Put("Store_WebSite",TextBox_WebSite.Text)
    DBUtils.InsertMaps(dmStore.SQL1, "tblStore", Array As Object(mRecordMap))

End Sub

 

[JaunLukePicard]

BPak,

After reviewing your feedback I found the problem in the form frmStoreEditor.fxml. Apparently when I was adding the fields in SceneBuilder I neglected to change the fxid for the WebSite field. I didn't catch this. Somehow I was thinging of the message in the B4j log as a warning that the variable didn't have a value. The record added successfully to the database.

THANK YOU!

 

[JaunLukePicard]

And I corrected the DateAdded/DateModified fields as well. LOL