Setup a test case

There is a separate how-to on creating test cases using the Data Access Layer (see here) so we will only give a summary here.

The test case is created inside of the module directory of the new module. This directory is located inside the modules directory of the Openbravo development project. To create a new source folder in Eclipse right-click on the project and select New and then source folder. Note the actual folder name depends on the java package set for the module.

In the source folder create a new package called: org.openbravo.howto.query (right click on the new folder and select New > package). Inside this package create a java file StoredProcedureTest.java. This file has the following content:

This test case inherits from the OBBaseTest class provided by Openbravo. The OBBaseTest class provides a user context and transaction handling. There is one test method in the class: testStoredProcedure. This method first selects the user context. It is possible that this needs to be different for your installation. The selected user must be allowed to create ProcessInstance records in the db. The commitTransaction() at the end commits the transaction, if an exception occurs during the test then this statement won't be executed and the transaction is automatically rolled back.

You can right-click the java file in Eclipse and then run as > junit testcase, you should see the greenbar in the junit view:

This is the main testcase setup. The next section will add some code to the testcase to call the stored procedure in the correct way.

Calling the stored procedures

The main business logic of Openbravo is implemented in stored procedures. Some stored procedures can be called directly through jdbc. But for other stored procedures you must first create a special record (the PInstance) and its related parameters (see How to develop a stored procedure).

This how-to describes how you can create the ProcessInstance record and Parameter through the DAL and then execute a stored procedure.

Here is the complete method, each part is discussed separately below:

public void testStoredProcedure() {
setUserContext("100");
// get the process, we know that 199 is the generate shipments from invoice sp
final Process process = OBDal.getInstance().get(Process.class, "199");
// Create the pInstance
final ProcessInstance pInstance = OBProvider.getInstance().get(ProcessInstance.class);
// sets its process
pInstance.setProcess(process);
// must be set to true
pInstance.setActive(true);
pInstance.setRecordID("0");
// get the user from the context
pInstance.setUserContact(OBContext.getOBContext().getUser());
// now create a parameter and set its values
final Parameter parameter = OBProvider.getInstance().get(Parameter.class);
parameter.setSequenceNumber("1");
parameter.setParameterName("Selection");
parameter.setString("Y");
// set both sides of the bidirectional association
pInstance.getADParameterList().add(parameter);
parameter.setProcessInstance(pInstance);
// persist to the db
OBDal.getInstance().save(pInstance);
// flush, this gives pInstance an ID
OBDal.getInstance().flush();
System.err.println(pInstance.getId());
// call the SP
try {
// first get a connection
final Connection connection = OBDal.getInstance().getConnection();
// connection.createStatement().execute("CALL M_InOut_Create0(?)");
final PreparedStatement ps = connection.prepareStatement("SELECT * FROM M_InOut_Create0(?)");
ps.setString(1, pInstance.getId());
ps.execute();
} catch (Exception e) {
throw new IllegalStateException(e);
}
// refresh the pInstance as the SP has changed it
OBDal.getInstance().getSession().refresh(pInstance);
System.err.println(pInstance.getResult());
System.err.println(pInstance.getErrorMsg());
commitTransaction();
}

Explaining the logic

Let's go through the main parts of the method.

This part sets the user which runs the stored procedure. It is possible that this needs to be different for your installation. The selected user must be allowed to create ProcessInstance records in the db.

setUserContext("100");

The following code reads the Process which will be run from the database. In this case we use Process 199 which is the generate shipment process.

final ProcessInstance pInstance = OBProvider.getInstance().get(ProcessInstance.class);
// sets its process
pInstance.setProcess(process);
// must be set to true
pInstance.setActive(true);
pInstance.setRecordID((long) 0);
// get the user from the context
pInstance.setUserContact(OBContext.getOBContext().getUser());

Note the way the current user is retrieved from the global OBContext object. See also how the process is set in the pInstance.

The process requires a Parameter which is created in the following step and linked to the ProcessInstance (and back):

// now create a parameter and set its values
final Parameter parameter = OBProvider.getInstance().get(Parameter.class);
parameter.setSequenceNumber("1");
parameter.setParameterName("Selection");
parameter.setString("Y");
// set both sides of the bidirectional association
pInstance.getADParameterList().add(parameter);
parameter.setProcessInstance(pInstance);

The next step is to persist the ProcessInstance, the Parameter will also automatically be saved because it is a child of the ProcessInstance business object. Hibernate caches database statements and sends them to the database when it is most appropriate. However, because we need to know the id (which is generated by the database) we force a flush of the Hibernate sql statements to the database. The System.err should print a uuid.

The ProcessInstance has been created with its parameter. Now you need to call the stored procedure. To accomplish the jdbc connection is retrieved from the DAL. The connection is the same one used by Hibernate for persisting the ProcessInstance, this ensure transaction acid behavior.

As the call is done through 'raw' jdbc there is a different syntax for Oracle and Postgresql. The last step, before executing, is to set the pInstanceId as a parameter in the PreparedStatement.

// call the SP
try {
// first get a connection
final Connection connection = OBDal.getInstance().getConnection();
// This is the way to call the SP in Oracle
// connection.createStatement().execute("CALL M_InOut_Create0(?)");
// This is the correct Postgresql format
final PreparedStatement ps = connection.prepareStatement("SELECT * FROM M_InOut_Create0(?)");
// Set the ProcessInstance id
ps.setString(1, pInstance.getId());
ps.execute();
} catch (Exception e) {
throw new IllegalStateException(e);
}

The stored procedure will update the ProcessInstance record in the database with a result code and an error message. As Hibernate does not detect this automatically we need to refresh the instance we have in-memory. Then we can call the methods on the instance to obtain the result:

// refresh the pInstance as the SP has changed it
OBDal.getInstance().getSession().refresh(pInstance);
System.err.println(pInstance.getResult());
System.err.println(pInstance.getErrorMsg());
commitTransaction();

The result

The logic has been explained, so let's now run the testcase. Again do run as > junit testcase (note: you can also do debug as > junit testcase to step through the code).

You should see an output as follows:

FF80818120000E9A0120000EC00B0002
0

The result 0 normally means that an error occurred, however with this stored procedure it means that no data was processed. Depending on the dataset this can occur.

If an error occurred then it should be present in the pInstance.getErrorMsg.