B4J Library [Web] Sithaso SDUIPocketBase - Use PocketBase BackEnd in your BANano Projects

Hi Fam

Please find attached a pocketbase class based on the PocketBase JavaScript SDK

In your BANano project, you need to add this line in App Start

B4X: 
BANano.Header.AddJavascriptFile("pocketbase.umd.js")

You declare a variable...

B4X: 
Private pb As SDUIPocketBase

Then you initialize it

B4X: 
pb.Initialize(Me, "pb", "http://127.0.0.1:8090", "projects")

For any CRUD related actions, you need to define the schema. This tells the class the field names and what types are they. For example

B4X: 
pb.SchemaAddText(Array("id", "name"))

There are various methods to add the schema fields.

Related Content

[Web] Mastering Pocketbase: Your Ultimate Guide to a Flawless SQLite WebServer Install on Windows!

Hi Fam Download PocketBase is a SQLite Webserver with some powerful functionality. Whilst it provides functionality to be a database, it also has a nice UI to manage that database. You can host it locally and also run it on a VPS. It does not support shared hosting. 1. Download and unzip this...
www.b4x.com

[BANano] Using PocketBase (Firebase Alternative) for your Apps.

Hi there. Update: This class is based on the JavaScript SDK available here, https://github.com/pocketbase/js-sdk REST API calls are dont internal to the library, if you want to use pure REST API, use BANanoFetch, the documentation should be easy to follow. I got to test the "Open Source...
www.b4x.com

You can also perform a search to get all PocketBase related content in the forum
 

Attachments

  • SDUIPocketBase.bas
    101.9 KB · Views: 22
Last edited:
Click to expand...

Mashiane

Expert
Licensed User
Longtime User
SELECT ALL RECORDS

You can select all records in your table

B4X: 
BANano.Await(pb.SELECT_ALL)
Do While pb.NextRow
Dim rec As Map = pb.Record
Dim sid As String = pb.GetString("id")
Loop

We have various methods like GetBool, GetDouble, GetInt etc.

This will return all records with all fields in your table. You can limit the column names retrieved by specifiying which ones to return before the SELECT_ALL clause.

For example, I only want these named fields to be returned per record from my table. So I use ADD_FIELDS before SELECT_ALL

B4X: 
pb.ADD_FIELDS(Array("idnumber", "signature", "signature1","signature2","signature3","signature4","signature5"))
BANano.Await(pb.SELECT_ALL)
 

Mashiane

Expert
Licensed User
Longtime User
SELECT WHERE

At times you want to select records that meet a particular criteria.

The first variant returns a List of records meeting your criteria. For example.

SELECT_WHERE

B4X: 
pb.CLEAR_WHERE
pb.ADD_WHERE_STRING("attractive", "=", "true")
pb.ADD_ORDER_BY("attrname")
Dim result As List = BANano.Await(pb.SELECT_WHERE)
For Each record As Map In result
pb.EstablishFileUrl1(record, "fileobj", "fileurl")
Dim sid As String = record.Get("id")
Next

NB: If your field type is a string, you need to use .ADD_WHERE_STRING, else use .ADD_WHERE if the type is Int, or Boolean. In this case our column name "attractive" is a string type in the database.

The second variant uses an internal list that you can loop on.

SELECT_WHERE1

B4X: 
pb.CLEAR_WHERE
    pb.ADD_WHERE_STRING("provinceid", "=", sprovince)
    pb.ADD_FIELDS(Array("idnumber", "signature", "signature1","signature2","signature3","signature4","signature5"))
    If bWaitingList Then
        pb.ADD_WHERE_STRING("waitinglist", "=", "true")
    End If
    banano.Await(pb.SELECT_WHERE1)
    
    Do While pb.NextRow
        Dim ssignature As String = pb.GetString("signature")
        Dim ssignature1 As String = pb.GetString("signature1")
        Dim ssignature2 As String = pb.GetString("signature2")
        Dim ssignature3 As String = pb.GetString("signature3")
        Dim ssignature4 As String = pb.GetString("signature4")
        Dim ssignature5 As String = pb.GetString("signature5")
        Dim sidnumber As String = pb.GetString("idnumber")

...

'do whatever

'...
Loop
 

Mashiane

Expert
Licensed User
Longtime User
SELECTING RECORDS BETWEEN A RANGE ON THE SAME COLUMN

At times you want to select records between a range from the same column. For example, records between the first and last day of the month.

Internally the WHERE clauses use a map, so to ensure that this works, one can suffix the column name. The suffix can be anything and is only relevant to make the column unique. Only the first part before the . (dot) is used internally.

B4X: 
pb.CLEAR_WHERE
    pb.ADD_WHERE_STRING("checkindate.start", ">=", sstartdate)
    pb.ADD_WHERE_STRING("checkindate.end", "<=", senddate)
    Select Case provinceid
    Case "all"
    Case Else
        pb.ADD_WHERE_STRING("provinceid", "=", provinceid)
    End Select
    banano.Await(pb.SELECT_WHERE1)

In the first spec, we uses checkindate.start, a unique internal map variable key for the start date and for the end date we used checkindate.end

Our internal query will pick the column name to be checkindate on both instances.
 

Mashiane

Expert
Licensed User
Longtime User
CREATING RECORDS

To create records you can pass an already existing map or define the fields individually with setfield. If you do not specify the "id" field name value, PocketBase will allocate a unique value on the id by itself.

The id of each record should be 15 characters long or less but now more. It can be numeric.

NB: The table schema should be specified first with SchemaAdd??? to ensure that the values are cast properly when saving to the table.

B4X: 
'generate the next id
xtitle = pb.NextID
'prepare internally
pb.PrepareRecord
pb.SetField("id", xtitle)
pb.SetField("name", svalue)
'only return the id column and not all columns
pb.GetAffectedID
banano.Await(pb.CREATE)

The .CREATE call will return the new record added with all the columns in the table. To limit it to only return some fields, you can use .ADD_FIELDS and specify which columns to return.

In the case above, using .GetAffectedID, we are calling an internal function to only return the "id" column of the created record and not the complete record.

To use an existing Map, just call .SetRecord(MapName) and there will be no need to call .SetField for each field.

At times I want to know if a record was indeed created...

B4X: 
Dim newid As String = banano.Await(pb.CREATE)
    If newid <> "" Then
        app.ShowSwalSuccessNotification("The Programme has been created!",3000)
    Else
        app.ShowSwalError("The Programme could not be created, please try again!")
    End If
 
Last edited:

Mashiane

Expert
Licensed User
Longtime User
UPDATING RECORDS

To update an existing record, you need to know its unique "id" value.

In this example I am getting the "id" column value based on a unique idnumber field and then use that to update the record

B4X: 
'find if record exists by id
        Dim bid As String = banano.Await(pb.GET_ID_BY_FIELD("idnumber", sidnumber))
        bCnt = banano.parseInt(bCnt) + 1
        ben.Put("id", bid)
        ben.Put("status", "Dropped Out")
        ben.Put("active", False)
        ben.Put("signature", "")
        ben.Put("signature1", "")
        ben.Put("signature2", "")
        ben.Put("signature3", "")
        ben.Put("signature4", "")
        ben.Put("signature5", "")
        ben.Put("signatureinitials", "")
        pb.SetRecord(ben)
        pb.GetAffectedID
        banano.Await(pb.UPDATE)

If you already know the "id" value of the record, there is no need to call GET_ID_BY_FIELD.

By executing .GetAffectedID, I want the update call to only return the "id" column of the updated record. If I removed this, it will read all the columns for the affected updated record, which is un-necessary in this case.

At times I want to know if a record was indeed updated.

B4X: 
Dim newid As String = banano.Await(pb.UPDATE)
        If newid <> "" Then
            app.ShowSwalSuccessNotification("The Attendance: Check Out has been saved!",3000)
            mdlbeneficiaryattendance.Hide
        Else
            app.ShowSwalError("The Attendance: Check Out could not be saved, please try again!")
            Return
        End If
 
Last edited:

Mashiane

Expert
Licensed User
Longtime User
READING RECORDS

To read a record from the DB, you use its "id" column value and also are able to read a record by a unique column value.

B4X: 
Dim up As Map = banano.Await(pb.READ_BY_STRING("idnumber", sidnumber))

This returns a map of the record

Here I am reading a record from the DB using the idnumber column.

If you know the "id" value for example, after calling GET_ID_BY_FIELD, you can also just execute a call like this.

B4X: 
banano.Await(pb.READ(sid))
    Dim sid As String = pb.GetString("id")
    Dim sprovinceid As String = pb.GetString("provinceid")
    Dim sidnumber As String = pb.GetString("idnumber")
    Dim saccountholder As String = pb.GetString("accountholder")
    Dim sbankname As String = pb.GetString("bankname")
    Dim sbranchcode As String = pb.GetString("branchcode")
    Dim saccountnumber As String = pb.GetString("accountnumber")
    Dim semailaddress As String = pb.GetString("mobilenumber")
    Dim smobileserviceprovider As String = pb.GetString("mobileserviceprovider")
    Dim bagree As Boolean = pb.GetBoolean("agree")
    Dim blocked As Boolean = pb.GetBoolean("locked")

NB: Reading records like this returns all the columns in the table for the record. You can limit the columns returns to only the ones you want by executing

B4X: 
.CLEAR_WHERE
.ADD_FIELDS(array())

Before calling .READ_BY_STRING / .READ
 

Mashiane

Expert
Licensed User
Longtime User
DELETING RECORDS

To delete a record, one uses the "id" value of the record.

You can also delete multiple records by using a loop.

B4X: 
For Each k As String In allBanks.Keys
        Dim bid As String = allBanks.Get(k)
        Dim bDelete As Boolean = banano.Await(pb.DELETE(bid))
        If bDelete Then
            dCnt = banano.parseint(dCnt) + 1
        End If
    Next

When I want to know whether a record was deleted...

B4X: 
Dim b As Boolean = banano.Await(pb.DELETE(uid))
        If b Then
            banano.Await(app.ShowSwalSuccessWait(sfullname, "The beneficiary profile was deleted successfully.", "Ok"))
        Else
            banano.Await(app.ShowSwalErrorWait(sfullname, "The beneficiary profile could NOT be deleted, please try again later.", "Ok"))
            Return
        End If
 
You must log in or register to reply here.

Similar Threads

  • Article
Share My Creation SithasoDaisy: PocketBase - Mini CRM WebApp Source Code @ $5.00
2 3
Replies
48
Views
7K
Mashiane
  • Article
Share My Creation SithasoDaisy: PocketBase - Paypal Insights WebApp @ $5.00
Replies
1
Views
1K
Mashiane
  • Article
Share My Creation SithasoDaisy: PocketBase - Social Media Manager for $5.00
Replies
0
Views
1K
Mashiane
  • Article
Share My Creation SithasoDaisy: PocketBase - Chatroom using Server Side Events (SSE)
Replies
2
Views
2K
amorosik
SithasoDaisy: Mashy Teaches TailwindCSS using b4x (with eBook)
4 5 6
Replies
110
Views
21K
Mashiane
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…