Hi
An sqLite database is the most flexible way of doing what you want.
Pretty much everything done with a database is done with the Structured Query Language (sql). You can create a database file or an in-memory data base.
You can add a table with fields specified, names, data types etc.
You can extract and sort data using SELECT queries and can do calculations on the fields.
Have a look at Erel's video on sqlite. This is a good starting point.
I use databases for pretty much all the data I store and analyze, it's a breeze.
An alternative in B4x is maps which are useful for storing simple data structures which can be assigned unique keys. These are very handy for storing application data for user application customization. But maps are nowhere near as flexible and powerful as sql databases.
To learn sqLite download sqLite Studio, it's free. Every operation you perform is stored as sql code in a Tab called DDL. i.e.
View attachment 153720
You can build a database, copy out the sql statements from the DDL, they are just strings into b4x and away you go. You can import test data into you DB using the sqLite Studio import functions. It's much easier to build queries and test them in the sqLite environment than in b4x, particulalry for complex queries using multiple tables and data aggregation. sqLite queries can have parameters which can also be tested.
A typical subroutine to create a DB and create a table within it are shown below:
'============================================================================================
Public Sub tuningVarsSQL_Init
If tuningVarsSQL.IsInitialized = False Then
Dim qryStr As String
Dim rset As ResultSet
Dim rnum As Int
Try
tuningVarsSQL.InitializeSQLite(File.DirData(networkPars.TUNING_DATABASE_PATH), "shorttClockTuningVariables.sqlite3", True)
Catch
#IF MIN_LOGGING_ON Or LOGGING_ON
Log(LastException)
#END IF
End Try
qryStr = "PRAGMA auto_vacuum = 1"
Try
tuningVarsSQL.ExecNonQuery(qryStr)
Catch
#IF MIN_LOGGING_ON Or LOGGING_ON
Log("mcuClient Line 634" & LastException)
#END IF
End Try
' See if the data table exists
qryStr = _
$"SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type="table" AND name="tblTuningVars");"$
rset = tuningVarsSQL.ExecQuery(qryStr)
rnum = 0
Do While rset.NextRow
rnum = rset.GetInt2(0)
Loop
If rnum = 0 Then
qryStr = _
$"CREATE TABLE tblTuningVars (
unixEpoch BIGINT NOT NULL PRIMARY KEY,
dateTimeUTC DATETIME NOT NULL,
tEncSP DOUBLE NOT NULL,
tEncSP_Ramp DOUBLE NOT NULL,
EnclosureTempC DOUBLE NOT NULL,
AmbientTempC DOUBLE NOT NULL,
HeaterDemand DOUBLE NOT NULL,
PID_Op DOUBLE NOT NULL,
AutoManual DOUBLE NOT NULL
);"$
Try
tuningVarsSQL.ExecNonQuery(qryStr)
Catch
#IF MIN_LOGGING_ON Or LOGGING_ON
Log("mcuClient Line 652" & LastException)
#END IF
End Try
End If
If tuningVarsSQL.IsInitialized Then
setDbToWAL(tuningVarsSQL)
checkPointDataBase(tuningVarsSQL)
End If
End If
End Sub
You can use b4x smart strings to create the query string in the "standard" sql formats that make them more readable.
The code above tests for a data base file and if it is not present it creates it , then creates a table called "tblTuningVars"
There is also a query to turn on auto vacuuming, which is used to clear out the space used by deleted records.
Regards
Rob
