Is possible to use ODBC (to external db) from pythonpl?

From: Michele Bendazzoli <mickymouse(at)mickymouse(dot)it>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Is possible to use ODBC (to external db) from pythonpl?
Date: 2005-03-05 18:11:06
Message-ID: 1110046266.15329.56.camel@mickymouse.i.mickymouse.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

I have to develope an intranet/extranet with plone on linux platform,
integrated with a Navision Erp on windows that use its native db (i.e.
it soesn't use sql server). I want to use PostgreSql as a bridge from
the Navision Db and the plone system.

I try to connect with sqlrelay directly from linux to the windows odbc
of Navision without any fortune, so i come with a strategy that I think
should be better: install the PostgreSql server in a Windows machine an
develope a set of plpython function to syncronize tables of navision
(through a buffer table writable/readable from an odbc source) with the
PostgreSql server the mirrored tables in PostgreSql (the Customer Table
etc.). If I'm able to do so infact, I have the opportunity to connect
directly to postgresql from plone with a better performance and a more
secure infrastructure (the webserver is not able to write directly in
the Erp tables!).

The problem is, apart from the multiline bug, that it seems that I can't
connect to the odbc DSN from the pythonpl function in postgresql.

This is the code I tried directly from a python editor (without any
CRLF ...): it worked.

import odbc, dbi;s = odbc.odbc("NODBC/user/pw");cur =
s.cursor();cur.execute("select * from customer");print cur.description

Then I try to create the plpython function (in the same machine where
are the postgresql, python, odbc and navision db) to mimic the
behaviour:

CREATE OR REPLACE FUNCTION myfunc(text)
RETURN text AS
'import odbc, dbi;s = odbc.odbc("NODBC/user/pw");cur =
s.cursor();cur.execute("select * from customer");print cur.description'
LANGUAGE 'plpythonu' VOLATILE
ALTER FUNCTION myfunc(text) OWNER TO postgres;

But when I try to execute it, the system give me the following error:

ERROR: plpython: function "myfunc" failed
DETAIL: dbi.operation-error: [Microsoft][Driver Manager ODBC] Nome
origine dati non trovato e driver predefinito non specificato. in LOGIN

(my transation: data source name not found and default driver not
specified. in LOGIN)

I try to remove all the the instruction I can and leave only 'import
odbc, dbi;s = odbc.odbc("NODBC/user/pw")': the error message is the same
so the problem is that myfunc can't connect to the ODBC driver.

It is my fault or the plpythonu function can't accesso to external data
source?

ciao, Michele

Browse pgsql-interfaces by date

  From Date Subject
Next Message Tom Wallick 2005-03-06 04:50:06 CORBA Server Requiring Connectivity and Activity with PostgreSQL 8.0 on RH Linux 9
Previous Message Michael Fuhr 2005-03-05 16:41:44 Re: plpythonu strange syntax error