basic (mx) ODBC question

[M.-A. Lemburg]

Paul Boddie wrote:
> 
> "Hanna Joo" <hanna at chagford.com> wrote in message news:<994236193.821045 at nntp01.uskonet.com>...
> > Hi all
> >
> > I don't understand ODBC well. From the little that I understand - postgresql
> > has its ODBC driver (on linux). I am connecting from windows. mxODBC is a
> > module I can use. mxODBC suggests that if I am connecting from windows, I
> > should (from here mad rambling because I don't know what the doc is saying)
> > compile using VC compiler on windows.
> 
> That may depend on binary availability of the package for Windows. I
> don't remember anyone releasing binaries for mxODBC recently.

eGenix.com provides binaries for Windows and all major Python
releases starting from Python 1.5.2. You don't have to have a VC
compiler to use mxODBC on Windows.
 
> > I thought ==>
> >
> > ODBC python talks to on windows -- ODBC manager on Win machine -- ODBC
> > module of pgsql.
> > (on win machine)                                 (between win machine and
> > linux server)    (linux server)
> 
> The problem with ODBC by itself is that, as far as I am aware, it
> doesn't address the issue of communication between client and server,
> program and database system, or whatever. Therefore, you need to
> choose drivers which will let you have the distributed communication
> that you desire. In the past, I have used drivers for an obscure,
> proprietary database system which can be installed in the Windows ODBC
> driver manager and which can be configured to communicate with remote
> servers running the database instances.
> 
> I don't know whether there are any such drivers for postgresql, but
> then I have never used it on Windows or UNIX (or both!).

You normally install an ODBC driver on Windows, configure the
data source using this driver in the Windows ODBC manager and
then talk to the Windows ODBC manager using the mx.ODBC.Windows
subpackage.

Whether the driver support multi-tier setups (client and server
on different machines), depends on the driver you use, but I
think that the postgresql driver does support this.
 
> > mxODBC has many subpackages for various DBs.. how does it work then? Does it
> > work like the following?
> >
> > python talks (or uses) to mxODBC that is postgresql specific  -- ODBC module
> > of pgsql
> > (same machine)
> 
> Going from my experiences with mxODBC on UNIX [1], one can compile
> mxODBC with a specific subpackage which knows about a particular ODBC
> implementation. In my case, I compiled it with knowledge of the Sybase
> Adaptive Server Anywhere ODBC library. This meant that mxODBC linked
> directly to that library at run-time.

You can do basically the same on Windows, but I would not recommend
it since it requires a lot of knowledge about how ODBC and the
specific driver work.
 
> > Or...
> >
> > python talks to mxODBC that creates an entry for win ODBC that is postgres
> > specific -- ODBC module of pgsql
> 
> Well, this would mean compiling the Windows subpackage, which I
> presume is aware of the details of the Windows ODBC driver manager. If
> you have a postgresql driver for the Windows ODBC driver manager, then
> mxODBC doesn't need to know explicitly that it's using postgresql, but
> the postgresql driver obviously needs to integrate with the driver
> manager.
> 
> > or...
> >
> > python talks to ODBC on linux  -- iODBC on same machine -- ODBC module of
> > pgsql
> > (same machine)
> 
> This can also be done, although you're removing Windows from the
> architecture, unless you are suggesting that Python is talking across
> a network to the Linux host where iODBC resides; this would involve
> you inventing or discovering a protocol which lets this happen, since
> ODBC doesn't mandate such protocols itself. I have seen an ODBC
> network gateway package, but this exported Windows-resident database
> systems to other hosts, not the other way around.
> 
> > mxODBC has different subpackages as I said, one being Windows.. I don't
> > understand how that works.. different packages for different databases and
> > one for windows?
> 
> Well, there's an iODBC subpackage, if I remember correctly, so you
> could say that there are "different packages for different databases,
> one for Windows and one for UNIX". ;-) But the most pertinent detail
> is the "point of integration" between mxODBC and the ODBC drivers.
> Either one integrates mxODBC with a driver manager (Windows or iODBC),
> or one integrates it with a particular database system's own driver
> (Sybase ASA).
> 
> The advantage of integrating with a driver manager is the lack of need
> to recompile mxODBC should you change or add database systems. The
> advantage of integrating with a specific implementation is, in
> practice, increased functionality, performance (I would think) and
> reliability.
> 
> > (realizing post lacks focus as a result of pathetic lack of understanding)
> >
> > Can sombody direct me to a site where I may be able to find out more about
> > this ODBC issue?
> 
> The mxODBC site may provide some detail, but you might want to visit
> my "mxODBC Configuration Guide" [1] which provides links to software
> which may let you achieve your distributed environment ambitions.

Great summary, Paul !
 
> Regards,
> 
> Paul
> 
> [1] http://www.paul.boddie.net/Python/mxODBC.html

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
______________________________________________________________________
Consulting & Company:                           http://www.egenix.com/
Python Software:                        http://www.lemburg.com/python/