Обсуждение: confused about *nix ODBC drivers
I´ve been looking around to find the definitve, current, most-up-to-date, maintained, tried-and-tested ODBC driver for *nix, and I am rather confused: There is unixODBC, the iODBC manager and FreeODBC, the latter of which provides an ftp link to "the latest" PostgreSQL ODBC at ftp.informika.ru, which turns out to be dated from 1998. There is also an ODBC driver included with the sources. I have an application (Real Streaming Video Server version 7, the free one which is restriced to 25 concurrent streams) that provides the option to hook it up to a database via ODBC for authentication purposes. I don´t know whether it would work, or has been tested, under Linux (Real seems to have an MS bias) but in theory it should, I believe, if the authentication mechanism can interface with ODBC. For the initial testing, I would be running the PostgreSQL server on the same box as the Real server, but eventually I would like to separate it so as to have a 2-tier (I think this would be called 2-tier, if the application is not on the same physical machine as the database server) setup. Openlinksw provides multi-tier ODBC drivers but they´re not free (actually quite expensive) as far as I could see. If I want to use iODBC, which driver do I need? Where is it? Does the free iODBC version support my (very simple) multi-tier setup? Can I use unixODBC for this? Would I use the same driver as for iODBC? Is 7.1 properly supported (I need 7.1 because of the TOASTed column support)? Cheers, Frank -- frank joerdens joerdens new media urbanstr. 116 10967 berlin germany e: frank@joerdens.de t: +49 (0)30 69597650 f: +49 (0)30 7864046 h: http://www.joerdens.de pgp public key: http://www.joerdens.de/pgp/frank_joerdens.asc
On Fri, Oct 27, 2000 at 04:19:45PM +0200, Cedar Cox wrote: > > Are you running the Real server on *nix as well? Sorry for not making that clearer. Both the Real Server are running under Linux, on the one and same machine presently. Eventually I intend to run either of them on physically distinct Linux boxes. -- frank joerdens joerdens new media urbanstr. 116 10967 berlin germany e: frank@joerdens.de t: +49 (0)30 69597650 f: +49 (0)30 7864046 h: http://www.joerdens.de pgp public key: http://www.joerdens.de/pgp/frank_joerdens.asc
> I´ve been looking around to find the definitve, current,
> most-up-to-date, maintained, tried-and-tested ODBC driver for *nix, and
> I am rather confused: There is unixODBC, the iODBC manager and FreeODBC,
> the latter of which provides an ftp link to "the latest" PostgreSQL ODBC
> at ftp.informika.ru, which turns out to be dated from 1998. There is
> also an ODBC driver included with the sources.
iODBC is a driver manager; on Linux I use it to connect Applix to
PostgreSQL via the psqlODBC driver included with the PostgreSQL sources.
afaik unixODBC was a code fork done by whoever is pursuing the unixODBC
project; they have contributed patches back on occasion so perhaps there
is strong similarity with the psqlODBC driver (or perhaps not). Haven't
heard from those folks in a while.
"The latest driver", if frozen in 1998, is probably not worth pursuing.
> For the initial testing, I would be running the PostgreSQL server on the
> same box as the Real server, but eventually I would like to separate it
> so as to have a 2-tier (I think this would be called 2-tier, if the
> application is not on the same physical machine as the database server)
> setup. Openlinksw provides multi-tier ODBC drivers but they´re not free
> (actually quite expensive) as far as I could see. If I want to use
> iODBC, which driver do I need? Where is it? Does the free iODBC version
> support my (very simple) multi-tier setup? Can I use unixODBC for this?
> Would I use the same driver as for iODBC? Is 7.1 properly supported (I
> need 7.1 because of the TOASTed column support)?
I know that psqlODBC works through iODBC on Linux. Not sure about the
others. If you use unixODBC, let us know how it goes and I'll try
testing it with my setup.
Not sure what you mean by "properly supported". It is not released yet,
but most days the current code tree builds and passes the regression
tests, so should be suitable for code development. But ymmv, and flames
about occasional breakage go to /dev/null ;)
Good luck, and let us know how unixODBC looks.
Oh, and all the above was imho; others may have better info for you.
- Thomas
hi, there! On Fri, 27 Oct 2000, Thomas Lockhart wrote: > If you use unixODBC, let us know how it goes and I'll try > testing it with my setup. we use unixODBC 1.8.10 successfully for more than half a year. 1.8.10 does not contain fixes related to referential integrity errors handling but later release will have them (they committed patches to their CVS in June/July IIRC) /fjoe
>> I am rather confused: There is unixODBC, the iODBC manager and FreeODBC, >> the latter of which provides an ftp link to "the latest" PostgreSQL ODBC >> at ftp.informika.ru, which turns out to be dated from 1998. There is >> also an ODBC driver included with the sources. > >iODBC is a driver manager; on Linux I use it to connect Applix to >PostgreSQL via the psqlODBC driver included with the PostgreSQL sources. Just trying to clear some stuff up: Your application uses ODBC calls, therefore it needs to link to an ODBC driver manager. iODBC is an example of this. iODBC is a library that provides your application with the ODBC functions. When establishing a database connection you tell it a Data Source Name (DSN). The iODBC library will look in a file (~/.odbc for example) which contains a list of mappings between these DSN's and database specific libraries. These database specific libraries just provide a mapping between ODBC calls and native database calls. The ODBC driver that comes with the postgresql source is an example of such a database specific library. The Openlink drivers are actually not that expensive if you compare them with other 3th party ODBC drivers for UNIX, but I agree that for personal use it's a lot of money. Openlink *does* allow you to download an evaluation copy of the multi-tier Universal Data Access set (that's the only one that works on UNIX, the single-tier editon is only available for windows platforms). Even if you use Openlink, you would need a separate driver manager. They actually recommend iODBC. Hope this information helped (maybe FAQ material?). Maarten ----------------------------------------------------------------- Visit our Internet site at http://www.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd.
Maarten Boekhold wrote: > Openlink *does* allow you to download an evaluation copy of the multi-tier > Universal Data Access set (that's the only one that works on UNIX, the > single-tier editon is only available for windows platforms). > > Even if you use Openlink, you would need a separate driver manager. They > actually recommend iODBC. What does the multi-tier Universal Data Access set do exactly? Maybe I'm still unclear about the term multi-tier. For instance, when I use the Win32 PostgreSQL ODBC driver from my NT box to access the PostgreSQL server on my Linux computer, is this not already 2-tier (because it involves a client and a server on physically distinct computers)? Or is it single tier (because physically distinctness has nothing to do with it, and the setup just involves a straightforward client-server model with the ODBC driver/driver manager to mediate between client and server)? Currently I am trying to get unixODBC to work, where the /usr/local/etc/odbc.ini files looks like: [PostgreSQL] Description = Test to Postgres Driver = PostgreSQL Trace = Yes TraceFile = sql.log (what's the absolute path? the user's home dir? I don't find an sql.log file anyhwere.) Database = intranet Servername = localhost UserName = Password = Port = 5432 Protocol = 7.1 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = and /usr/local/etc/odbcinst.ini: [PostgreSQL] Driver = /usr/local/pgsql/lib/libpsqlodbc.so FileUsage = 1 Since I've got a line Servername = localhost I could presumably also put in the IP address of some server, which would satisfy my requirement - which is to have the client on a different box than the server. This doesn't work yet, though. When I do ------------------------ snip ------------------------ limedes:~ # isql PostgreSQL -v ------------------------ snap ------------------------ I get ------------------------ snip ------------------------ [unixODBC]Missing server name, port, or database name in call to CC_connect. [ISQL]ERROR: Could not SQLConnect ------------------------ snap ------------------------ also, what's funny is ------------------------ snip ------------------------ limedes:~ # odbcinst -i -d -f /usr/local/pgsql/etc/odbcinst.ini [odbcinst] Driver installed. Usage count increased to 3. Target directory is /usr/lib ------------------------ snap ------------------------ What's it mean by "Target directory is /usr/lib"? This may be the cause of the problem. There is _nothing_ having anything to do with this setup in /usr/lib, as far as I can see. I'll be searching for more clues . . . Regards, Frank -- frank joerdens joerdens new media urbanstr. 116 10967 berlin germany e: frank@joerdens.de t: +49 (0)30 69597650 f: +49 (0)30 7864046 h: http://www.joerdens.de pgp public key: http://www.joerdens.de/pgp/frank_joerdens.asc
On Mon, 30 Oct 2000, Frank [iso-8859-1] J�rdens wrote: > Maarten Boekhold wrote: > > Openlink *does* allow you to download an evaluation copy of the multi-tier > > Universal Data Access set (that's the only one that works on UNIX, the > > single-tier editon is only available for windows platforms). > > > > Even if you use Openlink, you would need a separate driver manager. They > > actually recommend iODBC. > > What does the multi-tier Universal Data Access set do exactly? Maybe I'm > still unclear about the term multi-tier. For instance, when I use the > Win32 PostgreSQL ODBC driver from my NT box to access the PostgreSQL > server on my Linux computer, is this not already 2-tier (because it > involves a client and a server on physically distinct computers)? Or is > it single tier (because physically distinctness has nothing to do with > it, and the setup just involves a straightforward client-server model > with the ODBC driver/driver manager to mediate between client and > server)? Single tier has nothing to do with the number of computers invovled, but the number of software components. A direct connection from the client to the server is a single tier setup. It is how most systems work. If the client connects to a middle tier system, which then connects to the server, that is a two-tier system. More tiers aren't necessarily better, unless the added complexity is actually providing you some functionality. Now, the Openlink multitier system is designed to solve connectivity issues. For instance, you don't have a native driver for your client. So you use a Openlink client driver to connect to the Openlink broker which passes database calls into a native client. This means that the broker needs to run on a platform that a native driver is available for. Tom
On Tue, Oct 31, 2000 at 11:01:51AM +0200, Cedar Cox wrote: > > > Currently I am trying to get unixODBC to work, where the > > /usr/local/etc/odbc.ini files looks like: > > > > [PostgreSQL] > > Description = Test to Postgres > > Driver = PostgreSQL > > Trace = Yes > > TraceFile = sql.log (what's the absolute path? the user's > > home dir? I don't find an sql.log file anyhwere.) > > Database = intranet > > Servername = localhost > > UserName = > > Password = > > Port = 5432 > > Protocol = 7.1 > > ReadOnly = No > > RowVersioning = No > > ShowSystemTables = No > > ShowOidColumn = No > > FakeOidIndex = No > > ConnSettings = > > Which version of PG are you using? Is there a new 7.1 protocol? I am using the 7.1 current snapshot version and I don´t know which protocol it uses. I´ll try 6.5. Incidentally, I tried to access my 7.1 installation via the Insight Win32 ODBC driver (latest version from ftp.de.postgresql.org/odbc) - which I verified to be working correctly with another (6.5) installation - without success. I was going to post this issue separately in order not to confuse the unixODBC issue with the Win32 issue. But then again, they may be related. With the Win32 driver I do get a connection from MS Access to the 7.1 database, but I don´t see any tables (with the 6.5 database I do). From what I can gather from the log file (can´t include it now cuz it´s on another box in another office), the query to show tables is sent to the backend, but no rows are found. I get the same effect when cutting and pasting the query from the log file to a console to a psql prompt: On 6.5, rows are returned, on 7.1, there aren´t. Which leads me to conclude that something was indeed changed in 7.1 vis-a-vis the odbc interface. The source tree for the 7.1 snapshot contains an src/interfaces/odbc/odbc.sql file containing "PostgreSQL catalog extensions for ODBC compliance" which I did run against my 7.1 installation. Maybe this has something to do with. I don´t know whether these catalog extension where part of the 6.5 distro, because I don´t have a 6.5 source tree at hand. What I´ll try next is to get 6.5 to work with unixODBC to see whether it´s some issue with a new 7.1 interface. Regards, Frank -- frank joerdens joerdens new media urbanstr. 116 10967 berlin germany e: frank@joerdens.de t: +49 (0)30 69597650 f: +49 (0)30 7864046 h: http://www.joerdens.de pgp public key: http://www.joerdens.de/pgp/frank_joerdens.asc
On Mon, Oct 30, 2000 at 04:30:49PM +0100, Frank Jördens wrote: > Currently I am trying to get unixODBC to work, where the Do you use the graphical frontend to configure it? Should check for some misconfigurations. > /usr/local/etc/odbc.ini files looks like: > > [PostgreSQL] > Description = Test to Postgres ... > TraceFile = sql.log (what's the absolute path? the user's > home dir? I don't find an sql.log file anyhwere.) I'm not sure about this either. How about trying /tmp/sql.log instead? > Protocol = 7.1 I'm running version 7.0 and it definitely needs protocol 6.4! > and /usr/local/etc/odbcinst.ini: > > [PostgreSQL] > Driver = /usr/local/pgsql/lib/libpsqlodbc.so > FileUsage = 1 I'd recommend trying /usr/lib/libodbcpsql.so as Driver instead. I also had problems getting unixodbc going with our own odbc driver. If you add a line Setup = /usr/lib/libodbcpsqlS.so the graphical setup tool will help a lot. > I could presumably also put in the IP address of some server, which > would satisfy my requirement - which is to have the client on a > different box than the server. Some connections do not work with IP numbers. Did you try a resolvable host name? Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
> The source tree for the 7.1 snapshot contains an
> src/interfaces/odbc/odbc.sql file containing "PostgreSQL catalog
> extensions for ODBC compliance" which I did run against my 7.1 installation.
> Maybe this has something to do with. I don´t know whether these catalog
> extension where part of the 6.5 distro, because I don´t have a 6.5
> source tree at hand.
The extensions are not part of the 6.5 tree, but they can be applied to
a 6.5 database. They are simply functions defined in ODBC which are
synonyms for existing SQL9x or PostgreSQL functions. That is why they
can be defined with just "create function" statements and no compiled
code.
I doubt that they would contribute to a connectivity or operability
problem.
- Thomas