This question has been flagged
2 Replies
3663 Views

What I am trying to do: use the base_external_dbsource to gain access to an MS SQL Server database, and query some information to add new records into an odoo model.

What I have done: I have an odoo installation in which I have used the base_external_datasource (odbc) to successfully connect to my ODBC database, and I have set up an odoo model to put the data into.

What I am stuck on: It is not clear how to retrieve information from the connected ODBC datasource using Odoo.

Note: I am happy to develop a module to do this if required, but it is still not clear to me how I could retrieve data using the base_external_dbsource ODBC connection I have set up from within a custom module.

Online documentation for 

base_external_dbsource only describes how to obtain a connection to your database and not how to utilize this connection. Many similar forum posts about base_external_dbsource have been left unanswered.

Thanks :)

Avatar
Discard

I agree an example would be nice. I would love to have a SQL query that gets turned into a readonly model.

Best Answer

Not sure if this is best pratice to use it but at least it works for me.

Model:

from odoo import models, fields, api
from odoo.addons.base_external_dbsource_odbc.models.base_external_dbsource import BaseExternalDbsource


class wws_dai_extension(models.TransientModel):
    _name = 'my_test_model'
    _description = 'my_test_model'

    name = fields.Char()
    description = fields.Char()

    def get_odbc_data(self):
        vals = {}
        self.dbsource = self.env['base.external.dbsource'].search([('name', '=', 'test')], limit=1)

        conn = BaseExternalDbsource.connection_open_pyodbc(self.dbsource)

        query = 'select myfield1, myfield2 from schema.mytable'

        cursor = conn.execute(query)
        
        self.env['my_test_model'].search([]).unlink()
        for row in cursor.fetchall() or []:
            vals['name'] = row[0]
            vals['description'] = row[1]
            self.create(vals)

        BaseExternalDbsource.connection_close_pyodbc(self, conn)
        action_vals =  {'name': 'Transient Model from external datasource',
            'type': 'ir.actions.act_window',
            'view_mode': 'tree',
            'res_model': 'my_test_model',
        }
        return action_vals
       

View:
<record model="ir.actions.server" id="my_test_model.action_server">
      <field name="name">my_test_model server</field>
      <field name="model_id" ref="model_my_test_model"/>
      <field name="type">ir.actions.server</field>
      <field name="state">code</field>
      <field name="code">action = model.get_odbc_data()</field>
    </record>

Avatar
Discard
Best Answer

Been looking for the best example as well ...most of them seems like you're building a new model

Avatar
Discard