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

Mashiane · May 19, 2020

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.

Mashiane · May 19, 2020

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

Mashiane · May 19, 2020

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

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)

Mashiane · May 19, 2020

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.

Mashiane · May 19, 2020

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!

alienhunter · Nov 2, 2020

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

Mashiane · Nov 2, 2020

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

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 · Nov 3, 2020

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

Mashiane · Nov 3, 2020

alienhunter said:
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 · Nov 3, 2020

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

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 · Nov 3, 2020

alienhunter said:
correct that was is happening

Great stuff!!!

roberto64 · Feb 18, 2021

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 · Feb 18, 2021

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

roberto64 · Feb 18, 2021

hello, this is a small example

Mashiane · Feb 18, 2021

roberto64 said:
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 · Feb 18, 2021

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 · Feb 18, 2021

roberto64 said:
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 · Feb 18, 2021

Aha!

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

In your code...

B4X:

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

In your config file...

B4X:

const DB_NAME = 'Dbclienti';

These should be the same name.

Eish!

Mashiane · Jun 6, 2021

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

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

Attachments

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert

alienhunter

Active Member

Mashiane

Expert

alienhunter

Active Member

Mashiane

Expert

alienhunter

Active Member

Mashiane

Expert

roberto64

Active Member

Mashiane

Expert

roberto64

Active Member

Attachments

Mashiane

Expert

roberto64

Active Member

Mashiane

Expert

Mashiane

Expert

Mashiane

Expert

Attachments

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

Attachments

Expert

Expert

Expert

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Expert

Active Member

Attachments

Expert

Active Member

Expert

Expert

Expert

Attachments

Privacy & Transparency

Privacy & Transparency