Martin Taal

Openbravo Store Server is aimed at mid-sized and large retail organizations that need to implement a flexible cloud solution, one that allows them to deploy Openbravo centrally where practical and/or locally in the store if needed. The Openbravo Commerce Cloud solution offers retailers many benefits such as easier external integration, lower

More and more developers are using the Data Access Layer (DAL) for coding business logic in the Openbravo application. I sometimes get questions related to how to integrate DAL constructs with standard Openbravo approaches like Stored Procedures and XSQL generated classes.

As there are several options I felt it was time to spend a blog on this. In this blog I will discuss a number of ‘good-to-know’ classes which can be handy when you want to integrate DAL with the classic Openbravo approaches.

The first one to cover is the probably the most important one: the DalConnectionProvider. The DalConnectionProvider can be used when you call utility methods which need a ConnectionProvider object. The great thing of the DalConnectionProvider is that it shares the same connection and transaction as the DAL itself. Let’s take an example. This one is from the MassInvoicing module, computing the DocumentNo for a new invoice:

// create a new invoice object

final Invoice invoice = OBProvider.getInstance().get(Invoice.class);

// set some properties for the invoice

invoice.setOrganization(org);

invoice.setBusinessPartner(bp);

invoice.setInvoiceDate(invoiceDate);

invoice.setAccountingDate(invoiceDate);

// …. code truncated for clarity

// now compute a documentno, in the same database transaction

final String documentNo = Utility.getDocumentNo(conn,

new DalConnectionProvider(),vars, “”, Invoice.TABLE_NAME,

invoice.getDocumentType().getId(),

invoice.getDocumentType().getId(), false, true);

invoice.setDocumentNo(documentNo);

The above code creates an invoice and then calls a stored procedure using the DalConnectionProvider as the connect provider. As the same transaction is used any updates done by the stored procedure can be committed together with the rest of your work. As you can see the DalConnectionProvider is easy to work with as it does not need any extra parameters, you can create and use it directly.

Sometimes it makes sense to call OBDal.getInstance().flush() before calling a stored procedure. This ensures that Hibernate has flushed all your changes to the database so that the stored procedure can see them. Also read the remark at the end of this blog!

The next two are somewhat related: CallProcess and CallStoredProcedure, they both call/run database logic in the database. The first, the CallProcess, can be used to call a process (defined in AD_PROCESS) directly. You can get a CallProcess by calling CallProcess.getInstance() (there is one instance shared by all threads). Again an example from the MassInvoicing module, calling the C_Order_Post process for a list of invoices:

The last parameter in the CallProcess.call method is a map of parameters, these are placed in the ad_pinstance_para table.

The CallStoredProcedure class makes it possible to call any stored procedure in the database in a java programmer friendly way. The nice thing of this class is that you can pass in actual java objects as parameters, so you don’t need to worry about String conversions. The return is also a type-specific java object. This code snippet shows its usage (calling the C_Divide stored procedure):

The last one to discuss: the OBObjectFieldProvider class makes it possible to wrap an Openbravo business object in a FieldProvider interface. The FieldProvider is used throughout the Openbravo system to wrap data read from the database (for example through a XSQL generated class). The OBObjectFieldProvider is useful when you read business objects through the DAL and need to pass them on to code expecting a FieldProvider.

The discussed classes are all documented with javadoc describing the meaning of parameters and methods and you can ofcourse study the source code directly.

Before concluding this blog, as a last tip: The DAL (Hibernate) queues actions to the database. So when updating the database through DAL and then calling a stored procedure using a DalConnectionProvider make sure to call OBDal.getInstance().flush() before calling the stored procedure. This flushes the queue to the database and ensures that the stored procedures sees your changes. Also when you want to work with the results from the stored procedure and read them back through the DAL read this tip.

As always I hope this is an interesting read, feedback is always welcomed. If you have any questions or remarks visit the Openbravo forge forum and post them there.

More and more developers are using the Data Access Layer (DAL) for coding business logic in the Openbravo application. I sometimes get questions related to how to integrate DAL constructs with standard Openbravo approaches like Stored Procedures and XSQL generated classes.

As there are several options I felt it was time to spend a blog on this. In this blog I will discuss a number of ‘good-to-know’ classes which can be handy when you want to integrate DAL with the classic Openbravo approaches.

The first one to cover is the probably the most important one: the DalConnectionProvider. The DalConnectionProvider can be used when you call utility methods which need a ConnectionProvider object. The great thing of the DalConnectionProvider is that it shares the same connection and transaction as the DAL itself. Let’s take an example. This one is from the MassInvoicing module, computing the DocumentNo for a new invoice:

// create a new invoice object

final Invoice invoice = OBProvider.getInstance().get(Invoice.class);

// set some properties for the invoice

invoice.setOrganization(org);

invoice.setBusinessPartner(bp);

invoice.setInvoiceDate(invoiceDate);

invoice.setAccountingDate(invoiceDate);

// …. code truncated for clarity

// now compute a documentno, in the same database transaction

final String documentNo = Utility.getDocumentNo(conn,

new DalConnectionProvider(),vars, “”, Invoice.TABLE_NAME,

invoice.getDocumentType().getId(),

invoice.getDocumentType().getId(), false, true);

invoice.setDocumentNo(documentNo);

The above code creates an invoice and then calls a stored procedure using the DalConnectionProvider as the connect provider. As the same transaction is used any updates done by the stored procedure can be committed together with the rest of your work. As you can see the DalConnectionProvider is easy to work with as it does not need any extra parameters, you can create and use it directly.

Sometimes it makes sense to call OBDal.getInstance().flush() before calling a stored procedure. This ensures that Hibernate has flushed all your changes to the database so that the stored procedure can see them. Also read the remark at the end of this blog!

The next two are somewhat related: CallProcess and CallStoredProcedure, they both call/run database logic in the database. The first, the CallProcess, can be used to call a process (defined in AD_PROCESS) directly. You can get a CallProcess by calling CallProcess.getInstance() (there is one instance shared by all threads). Again an example from the MassInvoicing module, calling the C_Order_Post process for a list of invoices:

The last parameter in the CallProcess.call method is a map of parameters, these are placed in the ad_pinstance_para table.

The CallStoredProcedure class makes it possible to call any stored procedure in the database in a java programmer friendly way. The nice thing of this class is that you can pass in actual java objects as parameters, so you don’t need to worry about String conversions. The return is also a type-specific java object. This code snippet shows its usage (calling the C_Divide stored procedure):

The last one to discuss: the OBObjectFieldProvider class makes it possible to wrap an Openbravo business object in a FieldProvider interface. The FieldProvider is used throughout the Openbravo system to wrap data read from the database (for example through a XSQL generated class). The OBObjectFieldProvider is useful when you read business objects through the DAL and need to pass them on to code expecting a FieldProvider.

The discussed classes are all documented with javadoc describing the meaning of parameters and methods and you can ofcourse study the source code directly.

Before concluding this blog, as a last tip: The DAL (Hibernate) queues actions to the database. So when updating the database through DAL and then calling a stored procedure using a DalConnectionProvider make sure to call OBDal.getInstance().flush() before calling the stored procedure. This flushes the queue to the database and ensures that the stored procedures sees your changes. Also when you want to work with the results from the stored procedure and read them back through the DAL read this tip.

As always I hope this is an interesting read, feedback is always welcomed. If you have any questions or remarks visit the Openbravo forge forum and post them there.

The Openbravo Seam module integrates Seam with Openbravo, making it possible to develop Seam applications on top of Openbravo. The OB Seam module is a special module in that it provides custom implementations of specific Openbravo core (DAL) classes. T…

The Openbravo Seam module integrates Seam with Openbravo, making it possible to develop Seam applications on top of Openbravo. The OB Seam module is a special module in that it provides custom implementations of specific Openbravo core (DAL) classes. T…

As some of you may know we are currently prototyping a new user interface library. As part of the development for the new user interface we have developed a JSON REST webservice module.This module is a very good example on how you can extend the Openbr…

As some of you may know we are currently prototyping a new user interface library. As part of the development for the new user interface we have developed a JSON REST webservice module.This module is a very good example on how you can extend the Openbr…

Openbravo is planning to re-design and re-implement the current user interface using both new user interface design paradigms as well as using new technology in a different architecture. The user experience re-design is being done by Rob Goris with so…

Openbravo is planning to re-design and re-implement the current user interface using both new user interface design paradigms as well as using new technology in a different architecture. The user experience re-design is being done by Rob Goris with so…

Openbravo 2.50 has been released a few months ago, so some of you may have already noticed that as part of this release we created a completely new developers guide. This blog is to help spread the word even further that the developers guide is a great…