Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

2

How can we display data from another database?

By
Moin
on 2/11/13, 2:53 PM 8,794 views

We have integrated OpenERP with an external application (Java) which has its own database (Postgres). We want to create a frontend in OpenERP which gets its data from that database and commits any changes we make in OpenERP back to that database. We don't want to import that data in OpenERP database first since there are millions of records to handle and we don't want duplication or massive performance delays. How can this be implemented?

EDIT I essentially want to intercept the data source of the list view and form view to plug my own data in. From the console logs it seems like the search_read method is responsible for acquiring data that is finally displayed, is it possible to plug my own data in that method? Thank You.

2

Mohammad Alhashash

--Mohammad Alhashash--
2075
| 5 5 8
Giza, Egypt
--Mohammad Alhashash--

Developer

Mohammad Alhashash
On 2/12/13, 1:31 AM

If the external application uses integer primary keys, you can use PostgreSQL Foreign Data Wrapper (doc) to link the external data to the OpenERP database and create new models to access these tables.

You need to have PostgreSQL v9.1 or higher. There are FDWs for many databases and other data sources including PostgreSQL iteself, Oracle, MySQL.

Start by creating foreign tables in the OpenERP database mapped to the external tables/views. OpenERP meta data columns id, create_uid, create_date, write_uid, write_date are required, you can create these columns in a view based on other columns or as a static data and use that view as the model table.

Then, you should create OpenERP models for these tables just like normal models. Each model must have an attribute _table = '_Linked_Table_Name_' to have OpenERP using the linked table instead of creating new one.

Foreign tables are read-only. So, you have to override the create(), write() and unlink() methods to update the original table. Other OpenERP functionality like search should work directly.

If the external table does not have integer id, you may use integer hash function to map the primary key and all foreign keys in other tables to integers (should be 64bit to minimize hash collision probability).

Thanks for your answer, I looked into experimenting with FDW but I'm stuck as to how shall I go about 'installing' the pgsql_fdw library? There's no download file on the page you linked and google didn't yield any results either. What do I need to do to produce a simple example? Thank You.

Moin
on 2/14/13, 6:00 AM

@Moin What is your operating system and postgresql version?

Mohammad Alhashash
on 2/14/13, 4:18 PM

@Mohammad Ali Ubuntu 12.04, postgresql 9.1 and OpenERP 6.1

Moin
on 2/15/13, 5:41 AM
1

Daniel Reis

--Daniel Reis--
3436
| 6 7 9
Lisbon, Portugal
--Daniel Reis--

Author of the "Odoo Development Essentials" book.

Applications Manager at Securitas Portugal

Github: https://github.com/dreispt

Twitter; @reis_pt


Daniel Reis
On 2/12/13, 9:17 AM

There is a simple module to access data in foreign databases from OpenERP: base_external_dbsource in branch lp:openobject-extension. I have used it toaccess data from Oracle an SQL Server databases. It should be difficult to extend it to also add write features.

You can find more about in it's manifest.

Hello For testing I installed the module base_external_dbsource and created an externel data source. Connection to my external db is successful, but now I dont know how to use this connection in my custom module. Are there any informations how to use this connection and make the external data available in a module ? Can someone help? René

René
on 4/17/13, 12:53 PM
1

Maybe you can create fields of type function in an object which will act like a proxy for the other database/external application you have. Just make sure your external database/app is accessible using Python. But this approach would make the fields read only.

If you want to allow changes to be made from OpenERP: Create object with stored fields (not function fields) which mirrors the external database field.

  • Override 'read' method to populate the mirroring fields by reading values from the external database.
  • Override 'write' method to allow changes from OpenERP frontend to be made to external database.

e.g:

def read(self, cr, uid, ids, fields=None, context=None):
    #Populate local fields with values from external database
    res = super(your_proxy_object, self).read(cr, uid, select, fields=fields, context=context)
    return res

def write(self, cr, uid, ids, vals, context=None):
    #Write changes to external database
    return super(your_proxy_object, self).write(cr, uid, ids, vals, context=context)

This makes perfect sense, I knew about the write method but wasn't aware of the read method. This is exactly what I was looking for, Thank you. Could you please answer the question at http://help.openerp.com/question/1040/how-does-the-orm-read-method-work-exactly/ regarding how to use the read metho

Moin
on 2/12/13, 7:52 AM

Sure, I just posted an answer about how the read ORM method work. Just a simple example but I hope it helps.

Yohanes Iwan Sugiarto
on 2/12/13, 12:18 PM
0
René
On 4/17/13, 12:52 PM

Hello For testing I installed the module base_external_dbsource and created an externel data source. Connection to my external db is successful, but now I dont know how to use this connection in my custom module. Are there any informations how to use this connection and make the external data available in a module ? Can someone help? René

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

2 follower(s)

Stats

Asked: 2/11/13, 2:53 PM
Seen: 8794 times
Last updated: 3/16/15, 8:10 AM