Monday, May 21, 2012

Connect to SQL Server from Ubuntu

Connecting an Ubuntu box to a MS SQL Server can by achieved through the FreeTDS ODBC driver.

Here goes a small recipe to install it:

sudo apt-get update
sudo apt-get install tdsodbc
sudo apt-get install unixodbc unixodbc-dev
sudo apt-get install python-setuptools
sudo easy_install pyodbc
sudo echo -e "[FreeTDS]\nDriver=/usr/lib/odbc/libtdsodbc.so" >> /etc/odbcinst.ini

Now you can open a Python interpreter and test the connection using pyodbc.
This may speed you up if you're trying to import SQL Server data to OpenERP.

Thursday, May 17, 2012

Importing data from ODBC sources


With the import_odbc OpenERP  you can import data directly from other databases, such as Oracle or SQL Server, and schedule them to run regularly. Usage examples are to automatically update Customer data from a CRM app or Employee data from a HRIS/Payroll app.


The import is done using the standard import_data() ORM method, used when importing data through the user interface. So, it benefits from all its features, including relationship reconnecting (:id or /id columns).

Each import has a SQL statement used to build the equivalent for an import file. The column titles are given by the SQL's column names.


The first column in the SQL should provide a unique identifier for each record, and will be used to build it's xml_id. This id allows subsequent imports to update previously imported rows, instead of duplicating them. The xml_is is built using the form [MODEL_ID]_id_[UQ_ID]. For example: product_product_id_9999. This is important to remember when importing several tables with relationships between them. Columns titled "None" are ignored, so if you don't need to write the unique id to OpenERP, you can name the SQLs first column as "None".

For example, to keep the Products table updated with a product list in another company database, the SQL would look like this:
SELECT ID as "None", PRODUCT_CODE as "ref", PRODUCT_NAME as "name", 'product_category_id_'+CATEGORY_ID as "categ_id/id"
FROM T_PRODUCTS 
WHERE DATE_CHANGED >= ? 

The "?" is replaced by the last successful sync date. This date is updated after each successfull import run.
This way it's possible to import only the rows changed since last execution, so you can schedule frequent low-volume imports.


Recently a  feature was added to be able to tolerate to errors when trying to reconnect relationships. Using the previous example, if the product supplier's xml_id is not found, the product import would fail. But if you activate the "Ignore relationship errors" flag, the import would be retried without the "product_category_id/id" column, importing the product with an empty Category field.

You can find the import_odbc module in Launchpad's branch:
lp:~dreis-pt/addons-tko/reis.