ERP 2.50:Developers Guide/Other Development Tools
- Oracle SqlDeveloper for debugging stored procedures (PL)
Let's take a quick look at each one of them and the benefits they provide us with.
Oracle SQL Developer
Oracle SQL Developer is a free tool released by Oracle to administer, browse, edit and debug Oracle databases which is the native database Openbravo is developed in currently. If you are using Postgres, pgAdmin III is a similar tool but does not support real-time debugging. At the time of this writing there were no free/open source tools that provide that functionality for Postgres.
iReport is a powerful, intuitive and easy to use visual report builder/designer for JasperReports written in 100% pure java. This tool allows users to visually edit complex reports with charts, images, and subreports. iReport is integrated with leading open source chart libraries for java.
Firebug Add-on for Firefox
Oracle SQL Developer
In order to administer, browse, edit and debug Oracle databases (also MySQL and MSSQL) which is the native database Openbravo is developed in currently, we can use a free graphical tool by Oracle called SQL Developer.
You can download the tool for free from http://www.oracle.com/technology/products/database/sql_developer.
Adding a New Connection
To add a new connection, navigate to File > New > Database Connection to get the following window.
Here enter the database connection information that you have already entered upon Openbravo installation and click 'Connect'. After expanding the connection in the Connections sidebar, you can browse through various elements of Openbravo database:
...most important being:
- Tables (Application Dictionary as well as Data tables)
- Procedures (Stored Procedures or PL where lots of business logic is entered)
- Triggers (again, lots of business logic is stored here)
On the right side of the window you also have an SQL tab where you can directly enter SQL commands (or sets of commands, ie. scripts) and execute them. Moreover, you can analyze these commands using Explain or Autotrace to make sure they are optimized as they should be.
Debugging Stored Procedures
Most of the stored procedures that are being called on user demand do their work through the use of two important tables: - AD_PINSTANCE - includes details of every call - AD_PINSTANCE_PARAM - includes all parameters that are needed for a particular call
That is why every user-triggered call to a stored procedure from Openbravo first inserts records in the above two tables and then calls the procedure with the ID of the record within AD_PINSTANCE.
Debugging Openbravo PL can be done in two ways: - executing it from the SQL Developer and looking at the DBMS Output tab - fully debug it
Regardless of the method, you will have to know the ID of the record inside AD_PINSTANCE table that needs to be passed to the PL in order to supply it with required parameters. My suggestion is that you trigger the PL first from within Openbravo and then reuse the created lines within AD_PINSTANCE and AD_PINSTANCE_PARAM. Just make sure that the ISPROCESSING column of the corresponding line within AD_PINSTANCE is set to 'N'.
Let's take a look at both cases.
As you may have noticed, most of our PLs have several lines such as DBMS_OUTPUT.PUT_LINE(message) inside. These lines are ignored upon execution unless you enable the 'DBMS Output' tab before you run them. See execution of a C_ORDER_POST PL below:
Hence, if you need to generate more information, variable values, note breakpoints to see where the PL breaks, simply add more DBMS_OUTPUT.PUT_LINE(message) inside the PL, recompile the PL and run it again with the 'DBMS Output' on (serveroutput on).
Second, and probably better option is to fully debug a PL. Before you can do that, you will need to recompile the corresponding PL. Right click on it and select 'Compile for Debug' as shown below.
Now, edit the PL and add required breakpoints.
When you are ready, click on the little ladybug icon to start debugging. But before the start, you will need to enter the parameters required by the PL, in this case the AD_PINSTANCE record ID.
Now run the debug and wait for the debugger to stop at your breakpoint and hand you over the control to Step Into or Step Over.
More information on debugging stored procedures can be found at Developing and Debugging PL/SQL using SQL Developer by Oracle.
Add-ons for Firefox
Firebug: supporting client-side development
It is an easy install into Firefox and can then be enabled whenever is needed on a particular page you would like to explore:
The following options (tabs are available):
- Console - Firebug adds a global variable named "console" to all web pages loaded in Firefox. This object contains many methods that allow you to write to the Firebug console to expose information that is flowing through your scripts.
- HTML - explore the source HTML in a structured way
- CSS - explore a Cascading Style Sheet
Poster, to test webservices
To support web service testing, Firefox has a nice add-on: Poster. This add-on allows you to POST/PUT XML to a URL and do GET and DELETE requests.