Utilizando Campos tipo BLOBs en RPG con Java, VFP o Net.

Tomado de: 

http://wiki.gxtechnical.com/commwiki/servlet/hwiki?Using+BLOBs+in+RPG+and+Java+modelsEng,

Resumen de la Solución

Para ello debemos crear una KB (base de conocimiento) con el Generador Java, Net o VFP, ya que estos generadores son los que permiten almacenar imagenes, dentro de una tabla.
Esta base de datos dentro del ISERIES se creará como una colección no soportada por el ambiente pantalla verde, solo podemos revisar los datos con sentencias SQL.
  
En la grafica siguiente nos indica como seria la soucion para el caso de Empleados.

 
La Base de Datos Main sera creada con el generador RPG, y la Base de Datos Secondary sera creada con el generador Java, Net o VFP.
Para el caso no se requiere que el modelo en RPG accesar al modelo en Java, Net o VFP.

Detalle

Para el caso se descargo una KB desde el gxopen estas dos KB estaban en la version GX8, se acomodo a nuestra realidad para que se puede ver como solucion viable.

Secondary KB

En esta KB se creara una transaccion en cuya estructra debe constar un atributo BLOB, su clave principal puede ser un autonumerico, lo que facilitaria en gran medida su codificacion de imagenes.
Con esta KB

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..