B4J Library [BANanoMSSQL] PDO CRUD Class for MSSQL

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.

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

 

[Mashiane]

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.

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

'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

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

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

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

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

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