Workaround for Kettle Steps to work with OpenERP 7

The Kettle OpenERP Output Step currently doesn't work with v7.

The problem seems to be that OpenERP v7 requires the ID to exist if specified, and the Output Step sends an ID 0 for new records. This does not work with the v7 ORM load method:

Failed to commit batch:
Line 1 : Unknown database identifier '0'
(...)
Caused by: com.debortoliwines.openerp.api.OpeneERPApiException: Line 1 : Unknown database identifier '0'


A workaround is to modify the BaseModel object, _convert_records function, so it that a zero ID is considered equivalent to an empty id. Here is the diff of the fix if you want to apply it yourself:

--- openerp/osv/orm.py.orig     2013-03-20 22:21:17.892445229 +1100

+++ openerp/osv/orm.py  2013-03-20 22:05:12.474594534 +1100

@@ -1499,7 +1499,9 @@ class BaseModel(object):

                 except ValueError:

                     # in case of overridden id column

                     dbid = record['.id']

-                if not self.search(cr, uid, [('id', '=', dbid)], context=context):

+                if dbid == 0:

+                    dbid = False

+                elif not self.search(cr, uid, [('id', '=', dbid)], context=context):

                     log(dict(extras,

                         type='error',

                         record=stream.index,

A branch with the change is also available in Launchpad.

OpenERP steps for Pentaho Kettle/PDI

Pentaho Kettle / PDI is a powerful tool to transfer data between systems.

If you're running OpenERP in together with other applications, and need them to share data, this should be the first place to look at. Data is extracted using "input steps", transformed and then loaded using "output steps".

Kettle has specialized steps for OpenERP, that interact with the OpenERP server in the proper way - through the XML-RPC API. Unfortunately these steps are not yet available in the stable version.

You try them out using the latest development version. The development versions, however, are not well suited for usage in a production environment. Wouldn't it be nice if you could install these OpenERP steps in a Kettle stable version?

Well, I gave a try at this, and got them working with the 4.3.0-stable version. Here's the recipe:

Download the trunk version and copy the following files to the stable version:

• from \data-integration\plugins\kettle-openerp-plugin\*, and 
• from \data-integration\libext\webservices, the files:
• ws-commons-util-1.0.2.jar 
• xmlrpc-client-3.1.3.jar, and  
• xmlrpc-common-3.1.3.jar. 

 Now (re)start Spoon and a "!BaseStep.Category.OpenERP!" will be available containing three new steps: Delete, Input and Output.

Note that for some reason the "Test" button in the "Database Connection" dialog doesn't work correctly: it reports an error when testing the OpenERP server connection, even though the connection is correct. I believe that this is a bug that has been fixed in later development branches.

A big advantage for using the OpenERP API instead of the direct access to the database tables is the requests go through the ORM, so you get improved data consistency and take advantage of the ORM's create() and write() logic, and model default values.

On the downside, these steps don't handle XML Ids, only database (integer) ids, so you need to set your own strategy to identify records, such as using "reference" or "code" fields. If your record identification relies on XML Ids,  then import_data() base tools, such as import_sqlpush, may still be a good option.

Pushing data into OpenERP

The PushSQL module allows external apps to export data to OpenERP properly (through the ORM) just by using INSERT SQL statements.

The motivation for this came from a project where an ETL service was responsible for handling synchronization of data (such as departments, customers and contracts) across several databases and applications.

The ETL tool didn't have a simple way to make XML-RPC calls, and writing directly to OpenERP's tables is not a good solution.

This was solved using a relatively simple "hybrid" solution. The PushSQL module provides a database table for external apps to write data to imported to OpenERP, using plain SQL INSERT statements. A scheduled job regularly looks for new rows in this table and processes them, making the intended changes to OpenERP data.

The table , import_pushsql, has two mandatory columns: model and data. The first is the target model, such as res.partner. The second contains the data to import, using the format for CSV files, but using TAB instead of comma as the column separator.

For example, using PSQL:
INSERT INTO import_pushsql (model, data) VALUES ('res.partner', E'id\tname\tref\nres_partner_XXX\tDemo Customer\tXXX') 

Or, if using SQL Server:
INSERT INTO import_pushsql (model, data) VALUES ('res.partner',
'id' +char(9)+ 'name' +char(9)+ 'ref' +char(10)+ 'res_partner_XXX' +char(9)+ 'Demo Customer' +char(9)+ 'XXX'
)

The scheduled action uses this to run a standard import_data ORM method,  writes the result into the log column, and changes the state to 'done' if successful, or 'cancel' if an error was found.

Compared to the "pull" approach used by the import_odbc module, this "push" approach can provide a few advantages:

•  No need to install other database clients and middleware in OpenERP's server.
• It uses less resources on OpenERP server, because there is no need to connect to other servers.
• Event driven data sync, where an action in another system immediately triggers a write on OpenERP that can be processed within a few minutes.

There is also an obvious disadvantage: the process exporting the data does not have an immediate visibility on the outcome of the OpenERP transaction . There are ways to provide this feedback, but it's not immediate.

Overall, the PushSQL module can provide a very simple and straighforward way for business apps to safely send data to OpenERP, in scenarios where using the API is not an option or would be too cumbersome.

The import_pushsql can be downloaded either from OpenERP Apps or from Launchpad using: bzr branch lp:reis-openerp-addons

Import ODBC module steps up

The import_odbc module for OpenERP was recently improved and moved  to the OpenObject Extension community project. the original module is now split in two: base_external_dbsource and import_odbc.

The import_odbc code was refactored, but keeps the same funcionality as before. Documentation was improved and some examples are provided out of the box.

The base_external_dbsource was created to isolate the logic for connecting to external databases and executing SQL queries. It's used by import_odbc, but it also enables other modules to fetch foreign data on the fly.

Additionally, the connection types supported were extended. Support for ODBC and Oracle native connections is kept, and support for other native connections was added:

• MS SQL Server
• MySQL
• PostgreSQL
• SQLite

With this evolution, it would be more fair to name the module as "import_sql" instead of the "import_odbc", but the name was kept unchanged to avoid confusing current users.

You can get the latest version of these modules from the OpenObject Extension trunk branch:

bzr branch lp:openobject-extension

Share you experiences and feature wishlists with us!

Using codes to search and enter data in OpenERP forms

Using codes to refer to entities, such as customers or employees, is a common practice, and widely used in ERPs. To users familiar with this method it's natural to have the code displayed alongside with the description, and to enter data in a form field by directly typing a code.

This feature can be easilly added to OpenERP using the refcodes family of modules, available in Launchpad. For example, in the HR module an employee selection list might look like this:

Have fun.