3B. Upgrading BBjGrids

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

The resulting rs! variable can be given to the BBjGridExWidget to load it with data.

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.