Data Access

Data currently supports data access via Record-Level Access, therefore supports file access via RPGLE instructions like SETLL, READ, READE, WRITE...

Database data do not have to reside on AS400 DB2, but numerous databases are supported without having to change the interface mode. For example, an RPGLE program can read data from:

• PostgreSQL
• MySQL
• Oracle DB
• AS400/DB2
• DB2
• ...

Configuration

To obtain access to the data, it is not necessary to edit the existing .rpgle code, but it is mandatory to declare the database connection parameters.

Data access configurations are based on two configuration files in the DSL library:

• data_source_definition.yaml
• table_definition.yaml.
• RELOAD_METADATA/[TABLE_NAME].json

The first define the connection parameters to a specified data source (database), the second map each file (database table) with the data source and the last define the record format of the relative table.

Data Source Definition

To understand how to define a data source, see this guide.

Table Definition

The table definition file map each database table (RPGLE database file) with the data source.

Table definition is based on:

• tables: contains all table definition configuration. Each table configuration is based on:
  • names: the list of database table name
  • dataSourceId: the ID where database tables are stored.

info

RPGLE programs can read data from different database engines, such as PostgreSQL and MySQL

i.e.

table_definition.yaml

tables:
  - names:
    - "TAB001"
    - "TAB002"
    - "TAB003"
    dataSourceId: "relational_database_data_source_example_1"
  
  - names:
    - "TAB004"
    - "TAB005"
    dataSourceId: "relational_database_data_source_example_2"

In this example, the database tables TAB001, TAB002, TAB003 are mapped to relational_database_data_source_example_1 and tables TAB004, TAB005 in relational_database_data_source_example_2.

warning

data_source_definition.yaml and table_definition.yaml require micro executor (rpgle) reloading on change if you work in a non develop environment.

Default Table Definition

To avoid defining the names of database tables to their data source, a wildcard (*) can be used to automatically map each table to a specified data source, without explicitly declaring it.

i.e.

table_definition.yaml

tables:
  - names:
    - "*"
    dataSourceId: "relational_database_data_source_example_1"
  
  - names:
    - TAB004
    - TAB005
    dataSourceId: "relational_database_data_source_example_2"

In this example, the data source relational_database_data_source_example_1 handles all tables without TAB004, TAB005 because they are explicitly declared in relational_database_data_source_example_2.

Metadata File

The metadata file RELOAD_METADATA/[TABLE_NAME].json is a specific JSON file that describes the record format for a specified table. In this file you can define the table index, the table fields and the relative RPGLE type.

The started configuration is as follows:

RELOAD_METADATA/[TABLE_NAME].json

{
  "name":"", // the name of the table index 
  "tableName":"", // the name of the table
  "recordFormat":"", // the name of record format
  "accessFields":[], // the key fields
  "fields":[] // all fields
}

accessFields is a string array that contains the list of the specified table index. fileds is an object array that contains the configuration of each field.

The fields' configuration is heterogeneous and can be different based on type

String field

{
  "fieldName":"", // the name of field (the database column name)
  "type":{
    "type":"com.smeup.rpgparser.interpreter.StringType",
    "length":10 // the lenght of RPGLE field (integer)
  }
},

Number field (packed)

{
  "fieldName":"", // the name of field (the database column name)
  "type":{
    "type":"com.smeup.rpgparser.interpreter.NumberType",
    "entireDigits":12, // integer length
    "decimalDigits":5, // decimal lenght
    "rpgType":"P"
  }
},

Number field (zoned)

{
  "fieldName":"", // the name of field (the database column name)
  "type":{
    "type":"com.smeup.rpgparser.interpreter.NumberType",
    "entireDigits":12, // integer length
    "decimalDigits":5, // decimal lenght
  }
},