Openbravo has a lunch with Groovy

In the last post (Openbravo meets Groovy) I showed how to install the Groovy Adapter module and its main UI: Groovy Console; in order to use and access to this UI you have to use the System Administror role. That’s because its usage may be very dangerous, so it’s better to allow to use it to a responsable user. But if you are reading even this post, I think you are asking yourself: why should I use or add this module to my Openbravo instance?

On the fly updates

I’m working from 5 years on several Openbravo projects, very often, even in production enviroments, we face the issue regarding simple updates to be done on a set of data. In that case we have to access to the operating system where the instance has been deployed and, through plsql/sqlplus commands, we have to execute update or insert sql statements. Not very confortable solution I think. Another solution may be to open a tunnel connection with that server (if it is possible) and use our preferred SQL client (TOAD, PgAdmin or something else); more confortable but not still enough. For instance (consider it just a sample/poc), imagine your customer calls you asking to modify the price of all products that are using the Price List Version “Sales PL” whose Valid From Date is 10 Nov 2013. These prices have to be increased of 10% due to a new decision token by Marketing team. You can follow one of the two solutions we’ve described before, but it could be a very useful way to satisfy that request to access as System Administrator and run that update directly on a web page belonging to Openbravo suite. Ok, let’s try to do that, imagine you have to use Java code with no class definition:

import java.sql.PreparedStatement;
import java.sql.Date;
import org.openbravo.dal.service.OBDal;
String sql="UPDATE m_productprice set  pricelist = pricelist + pricelist * 0.1 where m_pricelist_version_id in ";
sql+=" (select plv.m_pricelist_version_id from m_pricelist_version plv where and plv.validfrom=?)"
PreparedStatement ps = OBDal.getInstance().getConnection().prepareStatement(sql);
ps.setString(1,"Sales PL");
ps.setDate(2,new Date(113,10,10));
int q=ps.executeUpdate();
return q;

Nothing that needs to be explained, the awesome thing is that it is a working groovy script. You can put this code into the Source field of the Groovy Console UI, push the Execute Groovy button and it will solve the customer request. Very nice! Isn’t it?
I want to show an approach more “groovy oriented”:

import org.openbravo.dal.service.OBDal
import groovy.sql.Sql
def sql="""
UPDATE m_productprice set  pricelist = pricelist + pricelist * 0.1 where m_pricelist_version_id in
(select plv.m_pricelist_version_id from m_pricelist_version plv where and plv.validfrom=?)
def sqlc=new Sql(OBDal.getInstance().getConnection())
def res=sqlc.executeUpdate(sql, ['Sales PL',new java.sql.Date(113,10,10)])

I think it is at step ahead, it shows its “not typed” nature; it shows the utility of the “”” String delimiter and it shows the useful groovy.sql.Sql Class.

Make background Processes more agile

Imagine you have to write a background java process that has to decrease of 5%, each day, the price of all the products belonging to the price list version named “Price List V 2013_1”. You need a simple Java class with few Java code, something like this should be enough:

//import statements
public class PriceCorrectionProcess extends DalBaseProcess {

 protected void doExecute(ProcessBundle bundle) throws Exception {
 //implement the biz logic using java and DAL

You have to create a new Java Process using the Report and Process UI, after that you have to add a Process Request linked to that Process, and schedule “daily” it. That’s simply Openbravo, but what does it happen if your customer begins to use this process and after some days he calls to you and says: “Sorry, we noticed that the price is going down too much fast please use a 3% rate”? The answer?
You have to assign this task to a developer (or assign it to your self) and he has to replace a 5 with a 3 in the PriceCorrectionProcess java code. He has to compile (“ant smartbuild”) the code and build a new obx file. After that you have to call your customer, you have to get an acknowledge about the date-time you can deploy the updated module. All this stuff can reach 4-8 hours, not so bad but you can do it better and faster and your customer will appreciate it. From the 0.4 version of Groovy Adapter module it is possible to define a DalProcess that runs groovy script. Take a look to this class:

public class SimpleGroovyScriptPriceProcess extends BaseGroovyScriptProcess {
  protected String getGroovyScriptName() {
    return "Price_Groovy_Process";

It is very easy, you have just to define the name of the script that it has to use in order implement the customer requirement.

Thus all the business logic of your process can be defined in a groovy script like this:

import org.openbravo.dal.service.OBDal
import groovy.sql.Sql
def sql="""
UPDATE m_productprice set  pricelist = pricelist - pricelist * 0.05 where m_pricelist_version_id in
(select plv.m_pricelist_version_id from m_pricelist_version plv where
def sqlc=new Sql(OBDal.getInstance().getConnection())
def res=sqlc.executeUpdate(sql, ['Price List V 2013_1'])

If you use this approach, when the customer will call to you asking to decrease the rate from 5% to 3% you can do it in 2 minutes!!! You need only to access to the production enviroment, switch to System Administration role, go to the Groovy Console UI, select the row whose name is “Price_Groovy_Process” and change 0.05 with 0.03 in the sql String variable. That’s it!!!
However, how I’m repeating more and more times, be careful doing these kind of operations, they may be very dangerous, that data is going to be changed on the DB. May be that Openbravo developers are thinking: ok this is a case where I don’t need to read a ProcessBundle object and I don’t need to write on a OBError object; how can I use it if I need to “use” that two objects?
The answer is that each groovy script that is invoked thorugh a BaseGroovyScriptProcess class has two “binded” objects that are bundle and msg; the first one is an object instance of ProcessBundle the second one is an OBError where you can write your results.

Waiting for next step

Ok, it’s enough for this post, I don’t like to noise you with verbose posts; we’ve seen two simple samples about how the Groovy Adapter module can improve Openbravo agility. At this time I’m publishing the 0.0.4 version on the forge. It allows to develop Java Process putting the business logic in a groovy script; it is also able to manage callout using a similar approach. The Callout usage will be described in the next post, stay tuned!


About amicidiroberto

Curioso, onesto, polemico ed innamorato della programmazione, del mondo IT, ma soprattutto di mia moglie e delle mie figlie
This entry was posted in Groovy, Openbravo and tagged , , , . Bookmark the permalink.

4 Responses to Openbravo has a lunch with Groovy

  1. Martin says:

    Very useful feature. I’ll use it

  2. Thank you Martin, i’m going to release a new version that supports also EventHandler management through Groovy. How I wrote in this post, 0.0.4 version already supports java processes and callouts (also if I did not show their usage). So I hope to put into next post something about callouts and eventhandlers.

  3. jfandl says:

    Very nice to be able to execute insert and update statements to OB from the browser, thanks!

    Can you also show how to execute a Select statement? (and process the result)

    For example, I try below, and expect the result to be 14, but instead the output displays


    Thanks in advance, great stuff! Openbravo really does need a lighterweight way to interact, and Groovy is ideal for this. In addition to inject business logic with callouts, I can also see Groovy as very useful for writing and executing automated tests.
    import org.openbravo.dal.service.OBDal
    import groovy.sql.Sql
    def sql=”””
    select count(*) from m_pricelist_version plv where’General Sales’
    def sqlc=new Sql(OBDal.getInstance().getConnection())
    def res=sqlc.execute (sql)

    • tx @jfandl,
      all suggestions are welcomed 🙂
      Your code returns true due to the method execute that returns a boolean.
      you can find docs about its usage.
      However let’s see two simple ways to process select statements:
      import org.openbravo.dal.service.OBDal
      import groovy.sql.Sql
      def sql=”””
      select count(*) as rows from m_product
      def out=””
      def sqlc=new Sql(OBDal.getInstance().getConnection())
      sqlc.eachRow(sql){ aRow ->
      out+=” “+aRow.rows+” “//you can write also out+=” $aRow.rows ”
      def res=sqlc.firstRow(sql)
      In my enviroment the output is “911 911”
      How you can see in the first solution I used the eachRow closure and this pattern is useful in order to process statements with “not unique” responses; in the second case I used the firstRow method that returns a GroovyRowResult that is something like ResultSet. I hope to have answered to your comment. If you look inside the obx code you can find several tests within the src-test folder. I’m testing 0.0.5 that introduces EventHandler management … don’t esitate to add other comments about doubts or suggestions.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s