3B. Upgrading BBjGrids

1. Overview

The DWC does not offer a direct 1:1 API compatible implementation of the BBjStandardGrid and its siblings, BBjDataAwareGrid and BBjDataBoundGrid. The reason for that is that these grid have been written with a legacy 1-tier architecture in mind. When they were brought over to BBj with its Thin Client Architecture, their API could not be implemented in the same performance, as the event callbacks and routines these grids offered through their API result in a substantial amount of round trips between the server and the client.

The BBjGridExWidget Plug-in has been written with best-possible performance for the Thin Client and the Web Browser in mind. It's build on a leading 3rd party implementation of a powerful data grid, built in JavaScript and TypeScript. The BBjGridExWidget works well in the DWC, which is why this course proposes it as one potential upgrade path for your existing data grids.

The BASIS Documentation hosts an intro page about the BBjGridExWidget Plug-In. As most Plug-Ins for BBj, it is hosted with its source code on GitHub at https://github.com/BBj-Plugins/BBjGridExWidget.

Differences between the BBjStandardGrid and BBjGridExWidget

While the BBjStandardGrid is typically filled cell-by-cell or using a BBjVector structure that holds the data for the entire grid or a set of consecutive cells, the BBjGridExWidget is strictly built as a data grid based on a collection of records. These records correspond to the rows of the grid, while the field list inside the records define the set of columns of the grid. Next to the core data types of the BBx language - String, Numeric and Integer values - the BBjGridExWidget and the basiscomponents library that is providing the underlying data structures can handle the most common SQL data types. By doing so, the grid natively supports the display and data entry for Boolean types, Dates and Timestamps, in a declarative way. No separate code is needed to convert from and to Strings, and if the underlying database is a standard SQL database like BBj ESQL or products like MySQL or Postgres, the data flows as efficient as possible between the database and the user interface.

The BBjGridExWidget also offers a few functions for user convenience, like drag and drop of columns, switching of visibility and many more. In the enhanced version which can be rented at affordable cost it provides more features like pivot tables, tree grid display or charting. 

Refer to the plug-in's homepage and the overview document to learn more.

2. Installation of the BBjGridExWidget Plug-In

Inspect the Demos

Review the JavaDoc

3. Working with ResultSet and DataRow

The central classes for data handling (not only) for BBjGridExWidget are implemented in the basiscomponents library.

A DataRow is a structure that can hold an entire record of a data table. It consists of fields that have a name and a type. The data types supported are those known by most current SQL databases, and not just numbers and strings like in the legacy BBx language. Apart from that, when you think of a DataRow, you may think of a BBj Templated String. In essence, a DataRow even implements most of the methods known from the BBjTemplatedString class. As we will later see, it even can convert from and to BBj Strings using the string templates known from the BBx language.

A ResultSet is a collection of DataRows. Besides holding the data, it has a number of convenience classes that allow converting from and to JSON format, to Excel, to CSV or to a JRDataSource for use with JasperReports.

Creating a new DataRow from Code

The following code snippet creates a DataRow and sets two string fields and one field containing a java.sql.Date. Like BBjTemplatedStrings, the DataRow can also hold field attributes.

use com.basiscomponents.db.DataRow
record! = new DataRow()
record!.setFieldValue("LAST_NAME","Picard")
record!.setFieldValue("FIRST_NAME","Jean-Luc")
record!.setFieldValue("DOB",java.sql.Date.valueOf("2305-07-23"))
record!.setFieldAttribute("DOB","DATEMASK","%Dl, %Dz. of %Ml, %Yl")
PRINT 
: record!.getFieldAsString("LAST_NAME")+", "+
: record!.getFieldAsString("LAST_NAME")+", born "+
: DATE(record!.getFieldAsNumber("DOB"):
: record!.getFieldAttribute("DOB","DATEMASK"))

The DataRow gets instantiated with the new keyword, like most any other Java class. Then you use setFieldValue to set field contents. Unlike the classic BBj templated strings, the DataRow can dynamically add fields and does not need to be initialized using DIM.

Upgrading existing code using BBx Templated Strings

The DataRow class brings a factory method fromTemplate that creates a DataRow object using a BBx String Template and a plain string that is typically a record coming from a data file. Altering the sample above to start from a BBx String Template illustrates the concept:

use com.basiscomponents.db.DataRow
TPL$="LAST_NAME:C(25*),FIRST_NAME:C(25*),DOB:N(7)"
DIM R$:TPL$

R.LAST_NAME$="Picard"
R.FIRST_NAME$="Jean-Luc"
R.DOB=JUL(2305,7,23)

record! = DataRow.fromTemplate(TPL$,R$)
PRINT 
: record!.getFieldAsString("LAST_NAME")+", "+
: record!.getFieldAsString("LAST_NAME")+", born "+
: DATE(record!.getFieldAsNumber("DOB"):"%Dl, %Dz. of %Ml, %Yl")

Combining DataRows to a ResultSet

To load a grid with data, you need to combine the single records into a ResultSet structure. After creating the ResultSet with new, use the add method to add the records:

use com.basiscomponents.db.DataRow
use com.basiscomponents.db.ResultSet
rs! = new ResultSet()

record! = new DataRow()
record!.setFieldValue("LAST_NAME","Picard")
record!.setFieldValue("FIRST_NAME","Jean-Luc")
rs!.add(record!)

record! = new DataRow()
record!.setFieldValue("LAST_NAME","Spock")
record!.setFieldValue("FIRST_NAME","S'Chn T'Gai")
rs!.add(record!)

record! = new DataRow()
record!.setFieldValue("LAST_NAME","Uhura")
record!.setFieldValue("FIRST_NAME","Nyota")
rs!.add(record!)

Source code: https://github.com/BasisHub/DWCTraining/tree/main/3C_Grid2GridEx/DataRow_and_ResultSet

Loading a ResultSet from SQL

If you work with data coming from SQL, you can create a ResultSet directly by using SqlQueryBC.

use com.basiscomponents.db.ResultSet
use com.basiscomponents.bc.SqlQueryBC
sbc! = new SqlQueryBC(BBjAPI().getJDBCConnection("CDStore"))
rs! = sbc!.retrieve("SELECT  * FROM CDINVENTORY")

The CDStore Sample of the BBjGridExWidget uses this method to retrieve the data and post it to the BBjGridExWidget.

4. Setting up a Simple BBjGridExWidget for DWC

The ResultSet we have created in the section can now be loaded into a BBjGridExWidget, using the setData method of its API:

use ::BBjGridExWidget/BBjGridExWidget.bbj::BBjGridExWidget
use com.basiscomponents.db.ResultSet
use com.basiscomponents.db.DataRow

wnd! = BBjAPI().openSysGui("X0").addWindow("Hello BBj DWC", $01111083$)
wnd!.setPanelStyle("height","100%")

cw! = wnd!.addChildWindow("",$00108000$,BBjAPI().getSysGui().getAvailableContext())
cw!.setStyle("height","100%")
cw!.setStyle("width","100%")
grid! = new BBjGridExWidget(cw!)

rs! = new ResultSet()

record! = new DataRow()
record!.setFieldValue("LAST_NAME","Picard")
record!.setFieldValue("FIRST_NAME","Jean-Luc")
rs!.add(record!)

record! = new DataRow()
record!.setFieldValue("LAST_NAME","Spock")
record!.setFieldValue("FIRST_NAME","S'Chn T'Gai")
rs!.add(record!)

record! = new DataRow()
record!.setFieldValue("LAST_NAME","Uhura")
record!.setFieldValue("FIRST_NAME","Nyota")
rs!.add(record!)

grid!.setData(rs!)

process_events

Styling and Configuring the Grid