B4J Library [BANanoMSSQL] PDO CRUD Class for MSSQL

Mashiane

Expert
Licensed User
Ola

UPDATE 2020-05-23: Please use this library instead

This class is for CRUD functionality via a BANano and SQL Server BackEnd.

Usage.

1. Update the connection settings in msconnect.php to your database. Add this to the Files tab of your BANano project.
2. Include the attached class to your project.
3. Download PHP Drivers for Microsoft SQL Server, install and copy these to your PHP\ext folders and activate the php_pdo_sqlsvr_73_ts

In Main.AppStart, configure your php settings.

B4X:
'set php settings
    BANano.PHP_NAME = "mssql.php"
    BANano.PHPHost = $"http://localhost/myapp/"$
    BANano.PHPAddHeader("Access-Control-Allow-Origin: *")
Let's look at the CRUD Below.
 

Attachments

Last edited:

Mashiane

Expert
Licensed User
1. INSERT/CREATE

Create a map of the record to be added using the field name as key and value to be what is actual data.

B4X:
Dim dbsql As BANanoMSSQL
    dbsql.Initialize("BANanoMSSQL")
    
    'insert rec
    Dim z As Map = CreateMap()
    z.Put("id",1)
    z.Put("name", "Zone 1")
    z.Put("desc", "This is zone 1")
Dim insert As MSSQLResultSet = dbsql.Insert("zones", zone)
    insert.result = BANano.CallInlinePHPWait(dbsql.MethodName, dbsql.Build(insert) )
    Log(insert.result)
This returns [1] with the number of affected records.

2. READ

B4X:
'READ
    Log("READ***")
    Dim read As MSSQLResultSet = dbsql.Read("zones", "id", "1")
    read.result = BANano.CallInlinePHPWait(dbsql.MethodName, dbsql.Build(read) )
    Log(read.result)
3. UPDATE

B4X:
Log("UPDATE***")
    Dim zoneu As Map = CreateMap()
    zoneu.Put("desc", "Updated Zone Name")
    Dim updateWhere As MSSQLResultSet = dbsql.UpdateWhere("zones", zoneu, CreateMap("id":1))
    updateWhere.result = BANano.CallInlinePHPWait(dbsql.MethodName, dbsql.Build(updateWhere))
    Log(updateWhere.result)
4. DELETE

B4X:
Log("DELETE WHERE***")
    Dim delWhere As MSSQLResultSet = dbsql.DeleteWhere("zones", CreateMap("id":1))
    delWhere.result = BANano.CallInlinePHPWait(dbsql.MethodName, dbsql.Build(delWhere))
    Log(delWhere.result)
5. SELECT ALL

B4X:
Log("SELECT ALL***")
    Dim sel As MSSQLResultSet = dbsql.SelectAll("zones", Array("*"), Array("name"))
    sel.result = BANano.CallInlinePHPWait(dbsql.MethodName, dbsql.Build(sel) )
    Log(sel.result)
6. DELETE ALL

B4X:
Log("DELETE ALL***")
    Dim delAll As MSSQLResultSet = dbsql.DeleteAll("zones")
    delAll.result = BANano.CallInlinePHPWait(dbsql.MethodName, dbsql.Build(delAll) )
    Log(delAll.result)
7. OWN QUERY

B4X:
'execute
    Dim exec As MSSQLResultSet = dbsql.Execute("SELECT COUNT(*) as tot from zones")
    exec.result = BANano.CallInlinePHPWait(dbsql.MethodName, dbsql.Build(exec))
    Log(exec.result)
NB: In all instances, the .result is always a list.

Ta!
 
Top