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.

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

  • BANanoConnect.zip
    20.4 KB · Views: 786
Last edited:
Click to expand...
Mashiane

Mashiane

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

Mashiane

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

Mashiane

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

Mashiane

Expert
Licensed User
Longtime 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:
alienhunter

alienhunter

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

B4X: 
<?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
 
Last edited:
Mashiane

Mashiane

Expert
Licensed User
Longtime User
alienhunter said:
it is just me but each time I compile the config.php is overwritten and not what i have in the Folder.assets
Click to expand...
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
 
Mashiane

Mashiane

Expert
Licensed User
Longtime User
alienhunter said:
Update the config.php file to point to your connection and database
Click to expand...
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

alienhunter

Active Member
Licensed User
Longtime User
Mashiane said:
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
Click to expand...

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

roberto64

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

roberto64

Active Member
Licensed User
Longtime User
hello, this is a small example
 

Attachments

  • Gioiabellzza.zip
    401.1 KB · Views: 328
Mashiane

Mashiane

Expert
Licensed User
Longtime User
roberto64 said:
Failed to load resource: net :: ERR_CONNECTION_TIMED_OUT
Click to expand...
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

roberto64

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

Mashiane

Expert
Licensed User
Longtime User
roberto64 said:
Linux server
Click to expand...
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"
Click to expand...

That is what needs to be investigated and sorted.
 
Mashiane

Mashiane

Expert
Licensed User
Longtime User
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
    B4X: 
    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
    B4X: 
    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
    B4X: 
    'convert response to readable map
dbConnect.FromJSON
  • SchemaAddField (fldName As String, fldType As String)
    add a field to the schame
    B4X: 
    'add schema to table
dbConnect.SchemaAddField("id", dbConntect.DB_INT)
  • GetMax As BANanoMySQLE
    get maximum of the primary key
    B4X: 
    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
    B4X: 
    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
    B4X: 
    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
    B4X: 
    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
    B4X: 
    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
    B4X: 
    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
    B4X: 
    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
    B4X: 
    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
    B4X: 
    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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    '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
    B4X: 
    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
    B4X: 
    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
    B4X: 
    '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
    B4X: 
    '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...
    B4X: 
    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
    B4X: 
    '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
    B4X: 
    '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
 

Attachments

  • BANanoMySQLE.bas
    53.7 KB · Views: 288
Last edited:
You must log in or register to reply here.
Top