Unofficial Content

Spanish version
 

 

Introduction

The conversion of applications from native AS (RPG or Cobol) to other platforms like .NET or Java is well-known.

In this way, we can have in the same model: some objects generated for a platform (RPG/Cobol) and others for another one (Java/.NET). We can have some objects generated for both platforms as well.

NOTE: In this article we will mention the case of Java and RPG, since it was the case of study that motivated this solution, but it also applies to Cobol and .NET.

 

The database: the common point

The particularitity that occurs in this case is that RPG defines the data base like compiled DDS and Java like SQL Collection. Although Java can access the database created with RPG, the inverse is not possible.
As a result, in the case of having a model with more than one generator and being RPG one of them, it must be the most used generator the one that creates/reorganizes the database. 

The limitation of the scheme

That leads us to a situation where we can have functionalities in Java that require some particularitity of the database that RPG does not support. These functionalities will have to be solved in a different way.

The particular case to analyze in this document is the use of blob attributes. This type of attributes is not supported in RPG and when trying to create/reorganize a database including this type of attributes, an error message will appear: "rgz0022 <attribute> 's datatype (Binary) is not supported on the selected environment (DBMS/Generator)." preventing the creation/reorganization of the database.

Since there are no plans to support BLOBs data type in RPG, a possible solution is explained to solve the necessity

 

The metaphor

Although the case can be generic (blob can be used for different purposes), we will describe a minimum example for the best understanding of the solution. From that example or metaphor, it is possible to generalize the solution to other similar cases.

In this case the statement is:

"A Human Resources that handles information of the employees of a company is in production with RPG and we want to develop some modules in Java. Those modules need to handle the photo of the employee".
 

The analysis of the problem to solve

If all the application could be migrated to Java then the problem will not exist. Since coexistence "of both generators is not required".
The problem is that some objects are required (liquidation of salaries, etc.) in RPG and others (handling of employee's information, etc.) in Java. 

The situation implies:

  • in order to handle the "photo of the employee" the blob data type is necessary.
  • the RPG Generator does not support the Blob data type, not just the generator itselft but also it cannot either create/reorganize tables that contain them.
  • it is not possible to have a model with RPG and Java being Java the generator that handles (create/reorganize) the database, it must be RPG

It seems to be a problem without solution: we need to reorganize the database with RPG because RPG programs are used, we need a BLOB but RPG cannot create them... then...confused
 

Possible solutions

There are several possible solutions, a primary one can be to handle blobs outside the database, this was the solution before the blob data type was supported.
That is to say, the files are stored in a file server and in the database there is a  reference to the file (basically path) in each record. So, when you need to see the file you use the LoadBitmap() function.

This solution can help. However, due to administrative, security, transaccional integrity, etc it would be better to handle everything in a database and not to require an additional file server.

Now the problem can be reduced to: "I need to add a blob attribute to the table and that RPG and Java programs can access the same table" 

It is not possible frown or at least some indirect effects will appear because RPG programs will not compile or if they do, they will not know how to handle that attribute.

However, we can create a "parallel" transaction of Employees table that includes the blob attribute. As a result, RPG programs will only use Employees table and Java programs may use both transactions if required (Employees if they do not need the BLOB or the parallel if they do need to use it).

In other words, we are extending the Employees table into another one instead of adding attributes to the original one.
 

Summary of the solution

Therefore, a solution is to define another Knowledge Base ("Secondary KB") that has only Java which is the one that creates/reorganizes the table that includes the blob attribute.
Then, in the original KB ("Main KB") you can create a Data View pointing to the new table and you also add a foreign key to the original table (EmpImgId).
 

img/wiki_up//kbs and dbs.jpg


The application will be generated in the Main KB.
The Java programs will access the tables of the "main" database (handled by RPG) and also tables of the "secondary" database (handled by Java), only in the case where the blob atribute is needed.
The RPG programs will only access tables of the "main" database..
 

Detailed solution

There is a project at gxopen that has two KBs. One uses RPG and Java and the other one is a Java KB that creates the tables with blobs.
The most important parts of them are detailed next.
 

Secondary KB

A KB is defined that the only thing that it has is structures (TRNS) to define and to handle (create/reorganize) the tables with blob attributes.

This KB only uses the Java generator.

In the previous picture there is a "EmployeesImg" transaction with the following structure:

EmployeeImgId*
EmployeeImgPhoto - atributo blob

 

Main KB

This KB has RPG as the default/reorg generator and Java as a secondary generator.
It's the KB that has all the structures and generates all the objects of the Human Resources application.

To be more specific, the obejects to solve the example are:

DataView to the table defined in the secondary KB

EmpImgId*
EmppImgPhoto - atributo blob

Employees transation

EmpId*
EmpName
EmpImgId 
EmpImgPhoto

The attributes EmpImgId and EmpImgPhoto were added to the employees transaction.

A sample of use could be a report of the employees including the relevant photo.
In this case, the code could be as follows:

for each                            // for each employee (table managed in main KB)
    &Photo=loadbitmap(EmpImgPhoto)   // loading the employee's photo (blob). It's accessing using the DV 
    print employee                  // printing (name, id, photo, etc)
endfor


In other words, the report will navigate the "Employees" and will perform a join with the "EmployeesImg" table to get the relevant image.

As the solution "add an attribute to the table" it is not possible, then the solution becomes to "add a table with that attribute".

NOTE: It is important to emphasize that at database level a relation N - 1 between Employees and EmployeesImg is established, when in fact the relation is 1 - 1.
This must be controlled by code, that is to say, it should not exist more than one employee pointing to the same image.

How to insert a new employe?
It depends on the interface required.
There are many possibilities:

  • set Nulls property to Yes for EmpImgId in the Employees transaction and call another transaction after the insert.
  • use parallel transactions that do not have foreign key so that, even a RPG program can insert a new employee.
  • in GeneXus 9.0 we have an update rule that could solve the problem more information about Update rule

It does not seem to be so complex, the thing is to find the best option that suits your needs. In addition, we will mention some variations of the solution later on this document.

How to delete an employee?
Very similar to the insert, when deleting an employee a procedure should be called in order to delete the relevant record on the EmployeesImg table.
 

Variations

The solution at structural level allows many variations.

For instance, maintaining the table of employees like it is but adding a new table that establishes the relation.

Result:
 

EMPLOYEES
EmployeeId*
EmployeeName

EMPLOYEEIMG
EmpImgId*
EmpImgPhoto


and the following transacton is added
 

EMPLOYEE2
EmpId*
EmpImgId*


In this case the functionality Filters As Hint included in GeneXus 9.0 can be useful.

Another possible variation is that there is not any relation between the tables. In this case the structures will be as follows:
 

EMPLOYEES
EmployeeId*
EmployeeName

EMPLOYEEIMG
EmpImgId*
EmpImgPhoto



there is no relation between them. If you need to establish a relation, it must be by coding it.
In the case of the report, the code would be as follows:
 

for each                            // for each employee (table managed in main KB)
    &EmployeeId=EmployeeId          // saving the employeeId
    &Photo=nullvalue(&Photo)        // initializing with null the employeePhoto
    for each                        // for each image (accessing the table defined in the secondary KB)
        where EmpImgId=&EmployeeId  // filtering using the employeeId saved previously
        &Photo=loadbitmap(EmpImgPhoto)// loading the employee's photo (blob)
    endfor
    print employee                  // printing (name, id, photo, etc)
endfor


There are more variations. However, all of them arrive to the same idea "extend one table defining a 1-1 table and not adding attributes".

Maybe the first solutions when establishing a 1->N or N->1 relation define a more approximate structure to the reality and the last option, when not definf relations at all, can bring more complexity when coding the objects that require the relation..
 

Last update: February 2024 | © GeneXus. All rights reserved. GeneXus Powered by Globant