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

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.

<?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.

 

[Mashiane]

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

Private db As BANanoSQL

2. Open the database (you can do this on BANano_Ready or from inside a sub)

db.OpenWait("expenses", "expenses")

3. In case you want to create a table

'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

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

'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.

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

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.

'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

 

[Mashiane]

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:

'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()

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.

'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

Dim rsExpenses As BANanoMSSQLE

and not

Dim rsExpenses As BANanoAlaSQLE

4. Change all references to code like this

rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)

This should now be

rsExpenses.JSON = BANano.CallInlinePHPWait(rsExpenses.MethodName, rsExpenses.Build)

 

[Mashiane]

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..

Dim rsExpenses As BANanoMySQLE

Also here, all refences to this code should be changed, from

rsExpenses.Result = db.ExecuteWait(rsExpenses.query, rsExpenses.args)

To

rsExpenses.JSON = BANano.CallInlinePHPWait(rsExpenses.MethodName, rsExpenses.Build)

As you can see above, the code structure is almost the same with MSSQL. Few modifications.

 

[Mashiane]

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

Dim rsExpenses As BANanoSQLiteE

and

rsExpenses.JSON = BANano.CallInlinePHPWait(rsExpenses.MethodName, rsExpenses.Build)

Ta!

 

[alienhunter]

Hi Sir
it is just me
but each time I compile the config.php is overwritten and not what i have in the Folder.assets
with this

<?php echo("Access Forbidden");exit();
const DB_HOST = '127.0.0.1';
const DB_NAME = 'bvmdemo';
const DB_USER = 'root';
const DB_PASS = '';
?>

thanks AH

 

[Mashiane]

it is just me but each time I compile the config.php is overwritten and not what i have in the Folder.assets

First, please start a new thread when you want to ask questions.
Secondly, you are not really explaining what you have done in your question, as a result its vague. I don't know your project setup and I dont have any "how to reproduce steps", so I cant help as I dont understand what you are doing.

TheMash

 

[alienhunter]

sorry
it was me ..
! 1. Update the config.php file to point to your connection and database

 

[Mashiane]

Update the config.php file to point to your connection and database

Are you using the BANanoVueMaterial library in your project? If so, open the source project of BANanoVueMaterial in b4j, on the files tab, remove the config.php file, run the project (to create the library). Its likely that the config.php file included with BANanoVueMaterial is replacing your config.php file (I am not sure because I dont know the structure and libraries used by your project and thus cannot reproduce this).

TheMash

 

[alienhunter]

Are you using the BANanoVueMaterial library in your project? If so, open the source project of BANanoVueMaterial in b4j, on the files tab, remove the config.php file, run the project (to create the library). Its likely that the config.php file included with BANanoVueMaterial is replacing your config.php file (I am not sure because I dont know the structure and libraries used by your project and thus cannot reproduce this).

TheMash

Hi correct that was is happening ( BANanoVueMaterial is replacing the config.php file) , i am just using one of your Demos ,tried to link to my DB
... after 8 months of VB.net i was not sure about what i missed in B4J ? try to catch up ...

 

[Mashiane]

correct that was is happening

Great stuff!!!

 

[roberto64]

Hi, I'm connecting to ub Db mysql server, but it gives me this error "Failed to load resource: net :: ERR_CONNECTION_TIMED_OUT" and this "Uncaught (in promise) SyntaxError: Unexpected end of JSON input
at JSON.parse (<anonymous>)
at banano_bananoconnect_bananomysqle._B.fromjson (bananoconnect.js: 3430)
at banano_gioiabellzza.selectall (app1613647471342.js: 63)
_B.fromjson @ bananoconnect.js: 3430
selectall @ app1613647471342.js: 63 "
thank you

 

[Mashiane]

It's very difficult to help you without a project included as an example.

 

[roberto64]

hello, this is a small example

 

[Mashiane]

Failed to load resource: net :: ERR_CONNECTION_TIMED_OUT

You code looks fine, however...

Q. Can you connect to MySQL using phpMyAdmin or any other MySQL database administration platform. Can you connect using the same credentials in config.php file?
Q. Is your firewall / if running from a webserver opened for port 3306?
Q. Is the mysqli extension activated inside your web server php.ini file?

 

[roberto64]

I did a test by publishing an example directly in the Linux server always the same error, for the connection to the db in the server I connect with the navicat program

 

[Mashiane]

Linux server

Sadly I dont have any Linux experience and have never deployed to such. However, for a db connection to work, 1. the mysqli extension needs to be active in the php.ini file, 2. your port to 3306 should be opened and 3. you must be able to access the db via phpMyAdmin (testing purposes) using the details you specified in your config.php file inside the BANano project.

Your error is not related to code but a connection to the db, thus this

"Failed to load resource: net :: ERR_CONNECTION_TIMED_OUT"

That is what needs to be investigated and sorted.

 

[Mashiane]

Aha!

The DBName in config.php and the database name used to initialize the BANanoMySQLE are different.

In your code...

dbsql.Initialize("m234107d1", "Step0", "", "")

In your config file...

const DB_NAME = 'Dbclienti';

These should be the same name.

Eish!

 

[Mashiane]

Ola

We have updated the BANanoMYSQLE class due to changes to php in favour of not using mysqlnd drivers.

For a live demo of using this class, check this out, https://www.b4x.com/android/forum/t...ee-ebook-for-those-in-a-hurry.131172/#content

BANanoMySQLE

• GetCount As BANanoMySQLE
  get a count of all records
  
  dbConnect.GetCount
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  log(rec)
  next
• SetConnection (shost As String, susername As String, spassword As String) As BANanoMySQLE
  set database connection settings
• SetCallBack (v As String, a As String)
  set callback for BANanoServer
• SelectWhere1 (tblfields As List, tblWhere As Map, operators As List, AndOr As List, orderBy As List) As BANanoMySQLE
  select where
  
  dim sw As Map = CreateMap()
  sw.put("name", "Anele")
  dbConnect.SelectWhere1(array("id", "firstname", "lastname"), sw, array("="), array("and", "or"), array("name"))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in res
  Next
• NewList As List
  define a new list
• FromJSON As BANanoMySQLE
  convert the json
  
  'convert response to readable map
  dbConnect.FromJSON
• SchemaAddField (fldName As String, fldType As String)
  add a field to the schame
  
  'add schema to table
  dbConnect.SchemaAddField("id", dbConntect.DB_INT)
• GetMax As BANanoMySQLE
  get maximum of the primary key
  
  dbConnect.GetMax
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  log(rec)
  next
• GetMin As BANanoMySQLE
  get minimum of the primary key
  
  dbConnect.GetMin
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  log(rec)
  next
• GetDatabases As BANanoMySQLE
  get databases
  
  dbConnect.GetDatabases
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  log(rec)
  next
• GetTableNames As BANanoMySQLE
  get table names
  
  dbConnect.GetTableNames
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  log(rec)
  next
• ShowIndexes
  show indexes
  
  dbConnect.ShowIndexes
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  log(rec)
  next
• ShowForeignKeys
  show foreign keys
  
  dbConnect.ShowForeignKeys
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  log(rec)
  next
• ShowColumns As BANanoMySQLE
  get table structure
  
  dbConnect.ShowColumns
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  log(rec)
  next
• DescribeTable As BANanoMySQLE
  describe table
  
  dbConnect.DescribeTable
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  log(rec)
  next
• GetNextID As String
  get next available id using pk
  
  dim nid As Int = dbConnect.GetNextID
• CStr (o As Object) As String
  convert to string
• SchemaClear As BANanoMySQLE
  prepare for new table definition
• SetField (fldName As String, fldValue As Object) As BANanoMySQLE
  set record field
• SchemaAddBoolean (bools As List) As BANanoMySQLE
  schema add boolean
• SchemaAddInt (bools As List) As BANanoMySQLE
  add int to the schema
• SchemaAddDouble (bools As List) As BANanoMySQLE
  add double fields
• SchemaAddFloat (bools As List) As BANanoMySQLE
  add float to the schema
• SchemaAddBlob (bools As List) As BANanoMySQLE
  add blob to the schema
• SchemaAddText (bools As List) As BANanoMySQLE
  add text to the schema
• SchemaCreateTable As BANanoMySQLE
  schema create table
  
  'schema create table
  dbConnect.SchemaCreateTable
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• Json2Map (strJSON As String) As Map
  convert a json string to a map
• Map2Json (mp As Map) As String
  convert a map to a json string using BANanoJSONGenerator
• Connection As BANanoMySQLE
  return string for test connection operation
• CreateDatabase As BANanoMySQLE
  create a database
  
  'create a database
  dbConnect.CreateDatabase
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• DropDataBase As BANanoMySQLE
  drop the database
  
  'drop a database
  dbConnect.DropDatabase
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• Execute (strSQL As String) As BANanoMySQLE
  execute your own sql query
  
  'execute a query string
  dbConnect.Execute("...")
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  for each rec As Map in dbConnect.Result
  next
• Insert As BANanoMySQLE
  insert current record
  
  'insert current record
  dbConnect.Record.Initialize
  dbConnect.Record.put("id", "1")
  dbConnect.Record.put("name", "Mashy")
  dbConnect.Insert
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• Insert1 (Rec As Map) As BANanoMySQLE
  insert a record using own record
  
  'insert a record using own record
  Dim rec As Map = CreateMap()
  rec.put("id", "1")
  rec.put("name", "Mashy")
  dbConnect.Insert1(rec)
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• InsertReplace As BANanoMySQLE
  insert replace a record using current record
  
  'insert replace a record
  dbConnect.InsertReplace
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  End Select
• Delete (primaryValue As String) As BANanoMySQLE
  delete a record using primary key
  
  'delete a record using primary key
  dbConnect.Delete(10)
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• Read (primaryValue As String) As BANanoMySQLE
  read a record
  
  'read a record
  dbConnect.Read(10)
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in Res
  Next
• Exists (primaryValue As String) As BANanoMySQLE
  check existence of a record
  
  'check existence of a record
  dbConnect.Exists(10)
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in Res
  Next
• SelectWhere (tblfields As List, tblWhere As Map, operators As List, orderBy As List) As BANanoMySQLE
  select where
  
  'select where
  Dim sw As Map = CreateMap()
  sw.put("id", 10)
  sw.put("age", 20)
  dbConnect.SelectWhere(array("*"), sw, array(">=", "<"), array("name"))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in Res
  Next
• SelectMaxWhere (fld As String, tblWhere As Map, operators As List) As BANanoMySQLE
  select max where
  
  'select max where
  Dim sw As Map = CreateMap()
  sw.put("id", 10)
  sw.put("age", 20)
  dbConnect.SelectMaxWhere("field1", sw, array(">=", "<"))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in Res
  Next
• SelectWhere (tblfields As List, tblWhere As Map, operators As List, orderBy As List, AscDesc As List) As cDesc
  select where asc/desc
  
  'select where asc/desc
  Dim sw As Map = CreateMap()
  sw.put("id", 10)
  sw.put("age", 20)
  dbConnect.SelectWhereAscDesc(array("*"), sw, array(">=", "<"), array("name"), array("name"))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in Res
  Next
• SelectDistinctWhere (tblfields As List, tblWhere As Map, operators As List, orderBy As List) As BANanoMySQLE
  select distinct where
  
  'select distinct where
  Dim sw As Map = CreateMap()
  sw.put("id", 10)
  dbConnect.SelectDistinctWhere(array("name"), sw, array("="), array("name"))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in Res
  Next
• DeleteAll As BANanoMySQLE
  delete all records
  
  'delete all records
  dbConnect.DeleteAll
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• DeleteWhere (tblWhere As Map, operators As List) As BANanoMySQLE
  delete records where
  
  'delete records where
  Dim uw As Map = CreateMap()
  uw.put("id", 10)
  dbConnect.DeleteWhere(uw, array("="))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• SelectAll (tblfields As List, orderBy As List) As BANanoMySQLE
  select all records
  
  'select all records
  dbConnect.SelectAll(array("*"), array("name"))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in Res
  Next
• SelectDistinctAll (tblfields As List, orderBy As List) As BANanoMySQLE
  select distinct all order by
  
  dbConnect.SelectDistinctAll(array("name"), array("name"))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in Res
  Next
• Build As Map
  build qry
• Build1 As Map
  build qry
• BuildDynamic (isPHP As Boolean) As Map
  build qry for dynamic connection
• Truncate As BANanoMySQLE
  delete all records and reset auto increment
  
  dbConnect.Truncate
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• FirstRecord As Map
  get the first record
• Update (priValue As String) As BANanoMySQLE
  update a record
  
  'update current record
  dbConnect.Update(10)
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• Update1 (Rec As Map, priValue As String) As BANanoMySQLE
  update record using primary key
  
  'update record using primary key
  Dim rec as Map = CreateMap()
  rec.put("name", "Anele")
  rec.put("email", "[EMAIL]email@email.com[/EMAIL]")
  dbConnect.Update1(rec, 10)
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• UpdateWhere (tblfields As Map, tblWhere As Map, operators As List) As BANanoMySQLE
  update where using map...
  
  dim rec As Map = CreateMap()
  'define where clause
  rec.put("name", "Anele")
  Dim uw As Map = CreateMap()
  uw.put("id", 10)
  dbConnect.UpdateWhere(rec, uw, array("="))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• UpdateAll (tblFields As Map) As BANanoMySQLE
  update all records
  
  'update all records with new field details
  dbConnect.UpdateAll(CreateMap("name":"Anele", "age":30))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
• SelectAll (tblfields As List, orderBy As List, AscDesc As List) As cDesc
  select all records asc/desc
  
  'select all records
  dbConnect.SelectAllAscDesc(array("*"), array("name"), array("name"))
  dbConnect.JSON = BANano.CallInlinePHPWait(dbConnect.MethodName, dbConnect.Build)
  dbConnect.FromJSON
  Select Case dbConnect.OK
  Case False
  Dim strError As String = dbConnect.Error
  Log(strError)
  End Select
  Dim res As List = dbConnect.Result
  For Each rec As Map in res
  Next
• CallInlinePHPWait (req As String, params As Map) As String
  **** DONT EVER USE