B4J Library [BANanoConnect] BANanoSQL+SQLite+MySQL+MSSQL Library

Mashiane

Expert
Licensed User
Ola

For a while I have been exploring backend connectivity using BANano for BANanoSQL, SQLite, MSSQL and MySQL. The purpose of this lib is for a one place for all your connectivity with these backends.

This library as a couple of class utilities for connecting to BANAnoSQL, SQLite, MSSQL and MySQL. SQLite, MSSQL and MySQL use PHP.

BANAnoAlaSQLE - CRUD functionality for BANanoSQL
BANanoMSSQLE - CRUD functionality for MSSQL (uses PHP)
BANanoMySQLE - CRUD functionality for MySQL (uses PHP)
BANanoSQLite - CRUD functionality for SQLite (uses PHP)

BANanoMSSQLE and BANanoMySQLE need a config file to be available in your Files. The file name is strictly config.php. This means you need to install PHP on your web server. You can do that using the Web Platform Installer on windows.

B4X:
<?php
const DB_HOST = 'localhost';
const DB_NAME = 'expenses';
const DB_USER = 'root';
const DB_PASS = '';
?>
In all instances of these classes, when Adding and Updating records to a database, you just need to pass a function the Map variable. This map variable should have the field names as keys. As these classes uses paramater queries, one also needs to pass the data types for those fields. The data types are S(string), I(integer), B(Blob) and D(double).

With all these classes, you can run scripts to create a table, insert a record to a table, update the record and also delete records and also select all records from a table.

PS:

Other News: Read Only Access to embedded SQLite Databases

BANanoSQLiteR - this is usable in case one needs to embeds SQLite databases inside BANano through File > Import. This library is for cases where you want to use the database for READ ONLY purposes. It does not depend on PHP and you cannot persist your changes to it i.e. your changes are not permanent. This can be used to backup other databases as the database created can be downloaded using filesave.js. This library uses sql.js and filesave.js.
 

Attachments

Last edited:

Mashiane

Expert
Licensed User
Using BANanoSQLE (WebBrowser storage in IndexedDB)

You can see this example source code of how this has been applied

1. Define a variable for your database

B4X:
Private db As BANanoSQL
2. Open the database (you can do this on BANano_Ready or from inside a sub)

B4X:
db.OpenWait("expenses", "expenses")
3. In case you want to create a table

B4X:
'resultset variable
    Dim rsExpenses As BANanoAlaSQLE
    'initialize table for table creation
    rsExpenses.Initialize("expenses", "expid")
    'add each field to the schema
    rsExpenses.SchemaAddField("expid", "INT")
    rsExpenses.SchemaAddField("expdate", "TEXT")
    rsExpenses.SchemaAddField("expcatid", "INT")
    rsExpenses.SchemaAddField("exptypeid", "INT")
    rsExpenses.SchemaAddField("expamount", "FLOAT")
    rsExpenses.SchemaAddField("expdesc", "TEXT")
    'generate & run command to create the table
    rsExpenses.SchemaCreateTable
    rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)
    rsExpenses.FromJSON
4. Lets Add a record to the table. BANAnoSQL does not have auto-increment, we need to do that manually for the expid field

B4X:
Dim Record As Map = CreateMap()
Record.put("expdate", DateTime.Now)
Record.put("expcatid", 1)
Record.put("exptypeid", 2)
Record.put("expamount", 300.45)
Record.put("expdesc","Food")
'
'database variable
Dim db As BANanoSQL
'open the database and wait
db.OpenWait("expenses", "expenses")
'resultset variable
Dim rsExpenses As BANanoAlaSQLE
'generate max value
Dim nextID As Int = 0
'generate & run command to get max value
'initialize table
rsExpenses.Initialize("expenses", "expid")
rsExpenses.GetMax
rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)
rsExpenses.FromJSON
nextID = rsExpenses.GetNextID
'update the record with the next id
Record.Put("expid", nextID)
'initialize table for insert
rsExpenses.Initialize("expenses", "expid")
'define schema for record
Dim Integers As List
Integers.Initialize
Integers.AddAll(Array("expid","expcatid","exptypeid"))
Dim Strings As List
Strings.Initialize
Strings.AddAll(Array("expdesc"))
Dim Doubles As List
Doubles.Initialize
Doubles.AddAll(Array("expamount"))
rsExpenses.SchemaFromDesign(Null, Null, Doubles, Integers, Strings)
'insert a record
rsExpenses.Insert1(Record)
'generate & run command to insert record
rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)
rsExpenses.FromJSON
5. Lets read a saved record

B4X:
'get the key
Dim RecID As String = "1"
If RecID = "" Then Return
'set mode to E-dit
Mode = "E"
'read record from database
'database variable
Dim db As BANanoSQL
'open the database and wait
db.OpenWait("expenses", "expenses")
'resultset variable
Dim rsExpenses As BANanoAlaSQLE
'initialize table for reading
rsExpenses.Initialize("expenses", "expid")
'define schema for record
Dim Integers As List
Integers.Initialize
Integers.AddAll(Array("expid","expcatid","exptypeid"))
Dim Strings As List
Strings.Initialize
Strings.AddAll(Array("expdesc"))
Dim Doubles As List
Doubles.Initialize
Doubles.AddAll(Array("expamount"))
rsExpenses.SchemaFromDesign(Null, Null, Doubles, Integers, Strings)
'generate & run command to read record
rsExpenses.Read(RecID)
rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)
rsExpenses.FromJSON
'was the read successful?
If rsExpenses.Result.Size = 0 Then Return
'the record as found!
Dim Record As Map = rsExpenses.result.get(0)
6. Lets update an existing record. Assuming the key (expid) for the record is 1.

B4X:
Dim Record As Map = CreateMap()
Record.put("expid", 1)
Record.put("expdate", DateTime.Now)
Record.put("expcatid", 2)
Record.put("exptypeid", 3)
Record.put("expamount", 200.00)
Record.put("expdesc","Food")
'
'database variable
Dim db As BANanoSQL
'open the database and wait
db.OpenWait("expenses", "expenses")
'resultset variable
Dim rsExpenses As BANanoAlaSQLE
'read record id
Dim RecID As String = Record.Get("expid")
'initialize table for edit
rsExpenses.Initialize("expenses", "expid")
'define schema for record
Dim Integers As List
Integers.Initialize
Integers.AddAll(Array("expid","expcatid","exptypeid"))
Dim Strings As List
Strings.Initialize
Strings.AddAll(Array("expdesc"))
Dim Doubles As List
Doubles.Initialize
Doubles.AddAll(Array("expamount"))
rsExpenses.SchemaFromDesign(Null, Null, Doubles, Integers, Strings)
'update a record
rsExpenses.Update1(Record, RecID)
'generate & run command to update record
rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)
rsExpenses.FromJSON[code]
7. Let's delete an existing record

B4X:
Dim RecordID As String = "1"
'database variable
    Dim db As BANanoSQL
    'open the database and wait
    db.OpenWait("expenses", "expenses")
    'resultset variable
    Dim rsExpenses As BANanoAlaSQLE
    'initialize table for deletion
    rsExpenses.Initialize("expenses", "expid")
    'define schema for record
Dim Integers As List
Integers.Initialize
Integers.AddAll(Array("expid","expcatid","exptypeid"))
Dim Strings As List
Strings.Initialize
Strings.AddAll(Array("expdesc"))
Dim Doubles As List
Doubles.Initialize
Doubles.AddAll(Array("expamount"))
rsExpenses.SchemaFromDesign(Null, Null, Doubles, Integers, Strings)
    'generate & run command to delete single record
    rsExpenses.Delete(RecordID)
    rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)
    rsExpenses.FromJSON
8. Lets select all records in a table, this returns a JSON array.

In this example we use a query with relationships. You can however use the .SelectAll() method instead of .Execute.

B4X:
'database variable
    Dim db As BANanoSQL
    'open the database and wait
    db.OpenWait("expenses", "expenses")
    'resultset variable
    Dim rsExpenses As BANanoAlaSQLE
    'initialize table for reading
    rsExpenses.Initialize("expenses", "expid")
    'generate & run command to select all records
    Dim strSQL As String = "SELECT expenses.expid,expenses.expdate,expenses.expcatid,expensecategories.catname,expenses.exptypeid,expensetypes.typename,expenses.expamount FROM expenses, expensecategories,expensetypes WHERE expenses.expcatid = expensecategories.catid AND expenses.exptypeid = expensetypes.typeid ORDER BY expenses.expdate,expensecategories.catname,expensetypes.typename,expenses.expamount"
    rsExpenses.Execute(strSQL)
    rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)
    rsExpenses.FromJSON
    'save records to state
    Dim Records As List = rsExpenses.Result
 
Last edited:

Mashiane

Expert
Licensed User
Using BANanoMSSQLE

You can see this example source code of how this has been applied.

This class is to connect to MSSQL and uses PHP PDO to connect to MSSQL.

First and foremost, ensure that in AppStart the BANanoPHP linkages are properly set:

B4X:
'set php settings
BANano.PHP_NAME = $"${AppName}.php"$
BANano.PHPHost = $"http://${ServerIP}:${Port}/${AppName}/"$
BANano.PHPAddHeader("Access-Control-Allow-Origin: *")
1. Update the config.php file to point to your connection and database. Ensure that the database and tables are created. For the above example, you will need to create the same definition for the field types. Ensure that your TEXT fields are VARCHAR(?) with specific sizes also.

2. Download PDO PHP driver. You will do this installation once. Extract and save the files under the PHP installation ext folder. Update your php config to use the nts and ts expension for the PHP version. If all goes well, you should have this when you run phpinfo()

pdo.png


Now come the interesting part. For all CRUD functionality for SQLite, MySQL and MySQL, the structure of the source code is the same as discussed above, with some tweaks.

To use the same code as above for these 3 connections without BANAnoSQL.

For MSSQL, update the code like this.

3. Remove all instances to BANanoSQL. This is the following code.

B4X:
'database variable
Dim db As BANanoSQL
'open the database and wait
db.OpenWait("expenses", "expenses")
All of this should be deleted.

3. Change the references of BANanoAlaSQLE to BANanoMSSQLE i.e. replace BANAnoAlaSQLE with BANanoMSSQLE.

This should now be

B4X:
Dim rsExpenses As BANanoMSSQLE
and not

B4X:
Dim rsExpenses As BANanoAlaSQLE
4. Change all references to code like this

B4X:
rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)
This should now be

B4X:
rsExpenses.JSON = BANano.CallInlinePHPWait(rsExpenses.MethodName, rsExpenses.Build)
 
Last edited:

Mashiane

Expert
Licensed User
Using MySQL

You can see this example source code of how this has been applied

The methodology discussed above for MSSQL applies as is with some exceptions.

1. Set up the BANAnoPHP settings in AppStart (refer to prev thread post)
2. Ensure the mysqlcli extension is activated in your PHP.ini file
3. From the code above, all refences to BANanoSQL should be removed just like we explained for MSSQL.
4. The class name to refer is now BANanoMySQLE instead of BANanoAlaSQLE

This should now be..

B4X:
Dim rsExpenses As BANanoMySQLE
Also here, all refences to this code should be changed, from

B4X:
rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)
To

B4X:
rsExpenses.JSON = BANano.CallInlinePHPWait(rsExpenses.MethodName, rsExpenses.Build)
As you can see above, the code structure is almost the same with MSSQL. Few modifications.
 
Last edited:

Mashiane

Expert
Licensed User
Using SQLite

You can see this example source code of how this has been applied.

To use SQLite, you need to activate the sqlite3 extension for PHP and update your php.ini file.

The class name to use this case is BANanoSQLiteE, so all references to the other classes should be changed to BANanoSQLiteE.

The same methodology we discussed for MSSQL and MySQL as above applies.

So we should have

B4X:
Dim rsExpenses As BANanoSQLiteE
and

B4X:
rsExpenses.JSON = BANano.CallInlinePHPWait(rsExpenses.MethodName, rsExpenses.Build)
Ta!
 
Last edited:
Top