| Notify me of new responses |
Can anybody fill me in on the status of the various ODBC drivers for AOLserver?I've got a proprietary relational database that I want to try talking to with AOLserver, and this database comes with the appropriate ODBC stuff on the DBMS side. (I am vague on the details there as of yet, but I am told and believe that it is there.) So, what AOLserver ODBC driver should I use for this? I've found lots of tantalizing info, but nothing definitive, so before I rush off and start compiling random stuff, I hope some of you here can fill me in.
(Note that I do NOT want to write my own database specific AOLserver driver - at least not now. Right now, I just need something that works, quickly.)
Here's what I know so far:
The docs for the AOLserver nsodbc driver claim that:
This is an internal database driver that connects to ODBC databases on Win32 only. Because it requires external ODBC client libraries, Unix users will need to obtain those drivers from the database vendor. Unix users can use UnixODBC from http://genix.net/unixODBC/ or FreeODBC from http://www.freeodbc.org/.I need to run this on unix, so has anyone used any of these "external ODBC client libraries" with the nsodbc driver? How well does it work?
In this thread, Here, Jerry Asher says he "took Rob Mayoff's DB2 driver, and made it ODBC 3.0 compliant." But he does emphasize that it's experimental.
(Also, if Rob's/Jerry's driver was written to work without any special "client libraries" on unix, how come the AOLserver nsodbc driver wasn't?)
And finally, way back in 1999, in his Introduction to AOLserver, Part 2 article, Philip stated that "The Solid C library is basically the ODBC standard and therefore the Solid driver might well work with any RDBMS vendor's ODBC interface." Anyone ever actually try that?
So, how well do they work, and which of these ODBC drivers works should I try? Any others that I've missed?
-- Andrew Piskorski, July 24, 2001
I don't have any advice on which driver to use.OBDC is a C API. On Windows, it also includes the driver manager, which controls which implementation of that API will be used at runtime by programs. On Unix, there is no driver manager (unless you buy something like UnixODBC). You control which implementation will be used by #including the appropriate header files and linking against the appropriate client library.
Solid's API is basically ODBC. So is DB2's. I didn't know that when I started writing the ODBC driver. That's why I wrote a separate driver. I didn't limit myself to the ODBC API in the DB2 driver; I used some IBM-specific functions for transferring data directly between LOBs and files. Jerry probably removed that stuff out when he adapted the DB2 driver for generic ODBC.
The DB2 driver was written to work with a special client library on Unix: the DB2 library. The DB2 library provides an implementation of the ODBC API. The library talks to local or remote DB2 instances; it doesn't provide access to just any old database. Ditto for the Solid driver and the Solid client library.
-- Rob Mayoff, July 24, 2001
Well when you're done with all this, if you can write a coherent description of what works, I would appreciate it!This is my understanding, based on about two weeks of work about three months ago....
Historically, odbc came out of the *nix world. But nothing happened until Microsoft came along.
To make an odbc connection from client to database, your client needs to have an odbc driver (nsodbc, solid(?), my/Rob's odbc/db2 driver, etc.) You also need a piece of glue called a driver manager.
Windows comes with a driver manager. *nix doesn't.
Intriguingly, the driver manager doesn't need to be on your local machine. It can be there, or it can be on a remote machine. In fact, it doesn't even need to be on the machine that the database is on.
There are a variety of driver managers available for *nix, some free, some proprietary, and some proprietary ones that are based on free ones. Near as I can tell, the problem with the free ones is that their web sites are abysmal, so you can't tell if it's good or not, if it's an active project or not, or even how you set them up.
Google for unixodbc and freeodbc.
I got my aolserver working with nsodbc AND with Rob's (my) odbc2 driver on Red Hat Linux 6.2 speaking to, if I recall, SQL Server 7 and Oracle. I compiled/linked against OpenRDA's driver manager as well as EasySoft's driver manager product. I found OpenRDA to have a good reputation but a very primitive product (no lobs). Expensive too. EasySoft's was much better, and had better licensing arrangements. I believe it's based on a gpl'd package that was originally developed by an EasySoft developer/founder, but I couldn't make hide nor hair of the website, so I couldn't figure out how to put the free package to use, and my client (person client that is), didn't need free. That was three months ago.
I went through, line by line, comparing nsodbc with the driver I put together from Rob's code. The two drivers are virtually identical, except that Rob's code is much better written and commented. The nsodbc driver is very primitive. I think that the max field you can have is 8K. The newer driver makes that *much* bigger, where *much* is definable in your config.tcl. (Basically my work with Rob's driver was to take out the IBM DB2ism's and replace them with more standard ODBC calls or to just dyke out the code.)
The newer driver should support lobs using a mechanism similar to, but not quite like what you see in the oracle driver.
What's missing at the moment are some nice features offered by IBM in the DB2 driver, that have to be coded up for ODBC/Linux. Things to glom blobs directly from disk, and their ilk. (It's been three months since I looked at the code, I'd rather be purposely vague than promise you something that it doesn't do yet.)
The ODBC2 driver is experimental. I don't know anyone using it in production. Three months ago, I would've bet that it had fewer bugs than nsodbc though (as I said I examined each line of each driver), though since then, AOL has released a revamped nsodbc that I haven't looked at.
If you can write a description of how to get a free ODBC driver manager for *nix up and running with any ODBC driver, I would greatly appreciate that. I bet many in the community would.
-- Jerry Asher, July 24, 2001
Ah, yes, and now that I was actually able to download Jerry's version of the DB2 driver, the readme makes it clear that yes, it also needs ODBC client libraries on the unix box - sounds like Jerry may have been using something called "easysoft". So any comments on what ODBC client libraries are good (and preferably Open Source) would also be appreciated.
-- Andrew Piskorski, July 24, 2001
Jerry, can you tell us which version of AOL's nsodbc driver you evaluated? The tarball currently available for download on aolserver.com isnsodbc_v1.tar.gzdated 21-Jun-2001 11:14, and the nsodbc.c file inside it containts the CVS string:$Header: /cvsroot/aolserver/nsodbc/nsodbc.c,v 1.2 2001/06/20 21:03:17 kriston Exp $
-- Andrew Piskorski, July 24, 2001
Ah, good old cvs, sometimes much ado about nothing.The version I was using came from AOLserver 3.2ad10 or ad12, and it designates itself as v1.4 of 2000/08/15. The CVS mistags most likely arose when they moved it from one directory to another.
Diffing modulo whitepsace reveals only one behavior change: the max field size of the new, v1.2 nsodbc.c is now 200K raised from 8K.
I encourage you to work the ODBC2 driver...
-- Jerry Asher, July 24, 2001
I did get the odbc2 driver to compile and load into AOLserver successfully, using Merant's Connect ODBC driver manger. I just had to change the Makefile a bit:#EASYSOFT = /usr/local/easysoft MANAGER = /home/odbc #MODLIBS = -L$(EASYSOFT)/lib -L$(EASYSOFT)/oob/client -lesoobclient_r -lesrpc_r -lessupp_r -lesextra MODLIBS = -L$(MANAGER)/lib -lodbc #CFLAGS = -I$(EASYSOFT)/oob/client/include -pthread -DODBC CFLAGS = -I$(MANAGER)/include -DODBCAnd also found I needed to add this to odbc2.c:
#include <sqlucode.h>However, whenever I try to simply open a database handle to my ODBC data source, it doesn't work. At first the error message in AOLserver looked like:
And that[-conn0-] Notice: dbdrv: opening database 'odbc2:etsqp'
[-conn0-] Notice: odbc2: opening pool ts, datasource 'etsqp'
[-conn0-] Debug: odbc2: connecting to datasource "etsqp" with user "tsqdm" and password "[HIDDEN]". MaxFieldSize: 8388608
[-conn0-] Debug: odbc/db2_openDb: autoCommit: 0
[-conn0-] Debug: ODBC_checkCode(1)
[-conn0-] Debug: ODBC_checkCode: calling ns_dbsetexception: 109: [INTERSOLV][ODBC 20101 driver]258
[-conn0-] Warning: ODBC_checkCode: ODBC/DB2 return code SQL_SUCCESS_WITH_INFO; sqlState = [109]; nativeError = 0; ODBC/DB2 error message = "[INTERSOLV][ODBC 20101 driver]258"
[-conn0-] Debug: odbc/db2_openDb: ns/db/pool/ts: txnIsolation: Segmentation Fault"[INTERSOLV][ODBC 20101 driver]258"error message appears to mean:Then after setting ns_param ExtendedTableInfo Off, the error improved to:$ grep 258 sqlnk/4_51_00/locale/en_US/LC_MESSAGES/*
sqlnk/4_51_00/locale/en_US/LC_MESSAGES/ivslk13.po:258 "Driver's SQLSetConnectAttr failed."[-conn0-] Notice: odbc2: opening pool ts, datasource 'etsqp'
[-conn0-] Debug: odbc2: connecting to datasource "etsqp" with user "tsqdm" and password "[HIDDEN]". MaxFieldSize:
[-conn0-] Debug: odbc/db2_openDb: autoCommit: 0
[-conn0-] Debug: ODBC_checkCode(-1)
[-conn0-] Debug: ODBC_checkCode: calling ns_dbsetexception: HY000: [INTERSOLV][ODBC 20101 driver][20101]Call to SQLGetDiagRec failed
[-conn0-] Error: ODBC_checkCode: ODBC/DB2 return code SQL_ERROR; sqlState = [HY000]; nativeError = 100; ODBC/DB2 error message = "[INTERSOLV][ODBC 20101 driver][20101]Call to SQLGetDiagRec failed"
[-conn0-] Error: dbdrv: failed to open database 'odbc2:etsqp'I also have some trace files from the ODBC driver manager which seem to make it clear that AOLserver is talking to the ODBC database in some fashion, but at something goes wrong in the connection negotiation process.
With
ExtendedTableInfo Off, things appear better because at least AOLserver isn't dieing with a Segmentation Fault. WithExtendedTableInfo On, the driver seems to start doing lot of extra stuff, and then dies and segfaults before it ever gets to the point where it dies withExtendedTableInfo Off.With
Off, I know it's getting at least some sort of response back from the actual database (as opposed to just the ODBC driver manager software), because I see strings like "TimeSquare.01.00.0000" (the name of the database) in the driver manager trace files, but withOff, there's a lot less in the trace files, and I see no such strings.Now,
SQLGetDiagRecis only called in one place inodbc2.c, but looking at it didn't tell me anything. Does this look familiar to anybody? Is this a bug in the ODBC2 driver, or something else? Any suggestions?When it comes to suggestions, note that I've never tried running AOLserver under a debugger (yet), don't know anything at all about ODBC (yet), and haven't done any real C coding at all for the last couple years - but may be willing to learn/change. :)
Oh, and Jerry's statement that
"I went through, line by line, comparing nsodbc with the driver I put together from Rob's code. The two drivers are virtually identical, except that Rob's code is much better written and commented."seems to be born out, as I also compiled and tried the nsodbc v1 driver (the newest), and it failed in exactly the same manner as the odbc2 driver - the trace files from the Merant/Intersolv driver manager prove it - except that nsodbc didn't write any useful info to the error log at all, just a "[-conn0-] Error: dbdrv: failed to open database 'nsodbc:etsqp'" message.
-- Andrew Piskorski, August 17, 2001
Just a note to remind people to take a look at OpenLink software, if they are still in business, they had the best ODBC drivers for Unix, when I looked at this stuff back in, oh, 1997 or so.They were the best because they were the closest to open-source at the time, i.e., their client was free and source code available, they charged money for the "server" that ran on the db server and gatewayed between the native database calls and OpenLink's network protocol. www.openlinksw.com
-- Henry Minsky, August 20, 2001
Jerry, you may be disapointed to hear this, but after upgrading from AOLserver 3.2+ad12 to 3.3.1+ad13, and fixing a minor config problem with Merant's ODBC software, I tried again, and now the nsodbc driver works for me! But odbc2 still dies. (Before, the nsodbc driver would die as well, with no useful error messages.) Well, one working ODBC driver is good enough, but I did spend a little time trying (failing) to figure out why the odbc2 driver doesn't work.
Just like before, with "
ns_param debug true" in my nsd.tcl I get:[-conn0-] Notice: dbdrv: opening database 'odbc2:etsqp'
[-conn0-] Notice: odbc2: opening pool ts-odbc2, datasource 'etsqp'
[-conn0-] Debug: odbc2: connecting to datasource "etsqp" with user "tsqdm" and password "PASSWORD". MaxFieldSize: 8388608
[-conn0-] Debug: odbc/db2_openDb: autoCommit: 0
[-conn0-] Debug: ODBC_checkCode(1)
[-conn0-] Debug: ODBC_checkCode: calling ns_dbsetexception: 109: [INTERSOLV][ODBC 20101 driver]258
[-conn0-] Warning: ODBC_checkCode: ODBC/DB2 return code SQL_SUCCESS_WITH_INFO; sqlState = [109]; nativeError = 0; ODBC/DB2 error message = "[INTERSOLV][ODBC 20101 driver]258"
[-conn0-] Debug: odbc/db2_openDb: ns/db/pool/ts-odbc2: txnIsolation: Segmentation Fault (core dumped)While with "
ns_param debug false" in nsd.tcl I get:[-nssock-] Notice: nssock: accepting connections
[-conn0-] Notice: dbdrv: opening database 'odbc2:etsqp'
[-conn0-] Notice: odbc2: opening pool ts-odbc2, datasource 'etsqp'
[-conn0-] Warning: ODBC_checkCode: ODBC/DB2 return code SQL_SUCCESS_WITH_INFO; sqlState = [109]; nativeError = 0; ODBC/DB2 error message = "[INTERSOLV][ODBC 20101 driver]258"
[-conn0-] Error: ODBC_checkCode: ODBC/DB2 return code SQL_ERROR; sqlState = [102]; nativeError = 0; ODBC/DB2 error message = "[INTERSOLV][ODBC 20101 driver]251"
[-conn0-] Error: dbdrv: failed to open database 'odbc2:etsqp'
[-conn0-] Error: could not allocate 1 handle from pool "ts-odbc2"Ok, time to try running AOLserver under gdb. The only C debugger I'd used before was MS Visual C++, so while I've still largely no idea how to properly drive gdb, I gave it a shot. In Emacs, I did "
M-x gdb", then told it "run -i -t /home/andy/n/atp-misc/nsd-odbc/odbc-nsd.tcl" to start up AOLserver, and the hit my very simply test.tcl page, which has:set db [ns_db gethandle ts-odbc2] #set db [ns_db gethandle ts-nsodbc] set connected_p [ns_db connected $db] set users [database_to_tcl_list $db {SELECT user_name FROM _SYSTEM.SYSUSERS}] ns_db releasehandle $dbWith "
ns_param debug false" everything just kind of hung, and I couldn't get a stack grace out of gdb.With "
ns_param debug true", gdb gave me:
Current directory is /web/aol3/bin/ GNU gdb 5.0 Copyright 2000 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "sparc-sun-solaris2.8"... (gdb) run -i -t /home/andy/n/atp-misc/nsd-odbc/odbc-nsd.tcl Starting program: /web/aol3/bin/nsd8x -i -t /home/andy/n/atp-misc/nsd-odbc/odbc-nsd.tcl [New LWP 1] [New LWP 2] [New LWP 3] [New LWP 4] [New LWP 5] [New LWP 6] warning: Lowest section in /lib/libw.so.1 is .hash at 00000074 [New LWP 7] [New LWP 8] Program received signal SIGSEGV, Segmentation fault. [Switching to LWP 8] 0xff132f04 in strlen () from /lib/libc.so.1 (gdb) bt #0 0xff132f04 in strlen () from /lib/libc.so.1 #1 0xff182738 in _doprnt () from /lib/libc.so.1 #2 0xff18462c in vfprintf () from /lib/libc.so.1 #3 0x490bc in Log (severity=Debug, fmt=0xfe475798 "odbc/db2_openDb: %s: txnIsolation: %s", argsPtr=0xfdb7c034) at log.c:442 #4 0x48718 in ns_serverLog (severity=Debug, fmt=0xfe475798 "odbc/db2_openDb: %s: txnIsolation: %s", vaPtr=0xfdb7c034) at log.c:144 #5 0x4875c in Ns_Log (severity=Debug, fmt=0xfe475798 "odbc/db2_openDb: %s: txnIsolation: %s") at log.c:153 #6 0xfe472918 in ODBC_openDb (handle=0x1a11e0) at odbc2.c:404 #7 0x37d88 in NsDbOpen (handle=0x1a11e0) at dbdrv.c:707 #8 0x3a0f4 in Connect (handlePtr=0x1a11e0) at dbinit.c:1166 #9 0x38c10 in Ns_DbPoolTimedGetMultipleHandles (handles=0xfdb7c43c, pool=0x3b06c8 "ts-odbc2", nwant=1, wait=0) at dbinit.c:499 #10 0x3adb4 in NsTclDbCmd (dummy=0x0, interp=0x36a5e8, argc=1, argv=0xfdb7c4c0) at dbtcl.c:244 (gdb) step Single stepping until exit from function strlen, which has no line number information. Program received signal SIGSEGV, Segmentation fault. 0xff132f04 in strlen () from /lib/libc.so.1 (gdb) step Single stepping until exit from function strlen, which has no line number information. Retry #1: Program terminated with signal SIGSEGV, Segmentation fault. The program no longer exists. (gdb) bt No stack. (gdb) quit Inferior GDB finishedAny idea why odbc2 is dying, when nsodbc works fine?
-- Andrew Piskorski, August 30, 2001
Hi Andrew,There appear to be several things going on.
With DEBUG on, there seems to a problem in the Ns_Log(Debug statement, that's causing a segmentation fault. Looking at the code, it certainly looks like it is tripping over the value of isolationparam. To fix that, you might a) change the Ns_Log statement to check if isolationparam is NULL, and/or b) set a value for isolationparam in your config.tcl.
With DEBUG off, there is some unknown mismatch between the odbc2 driver and Intersolv. Not having Merant's Intersolv installed, I cannot tell you what the mismatch is: does Intersolv tell you what "INTERSOLV [ODBC 20101 driver]258" and especially what ""[INTERSOLV][ODBC 20101 driver]251" might mean?
A quick comparison of nsodbc.c's ODBCOpenDB function with odbc2.c's ODBC_openDb function suggests the problem is that Intersolv does not like having it's SQL_ATTR_TXN_ISOLATION parameter set, or it doesn't like the way we are setting it.
From the segfault you are getting when DEBUG is on, I am guessing that you do not have a default txnIsolation value in your config.tcl, and that would seem to imply that we are asking Intersolv to set SQL_ATTR_TXN_ISOLATION to SQL_TXN_SERIALIZABLE and to which Intersolv replies: "bletch"
For SQL_ATTR_TXN_ISOLATION, IBM says that "This connection attribute determines the isolation level at which the connection or statement will operate. The isolation level determines the level of concurrency possible, and the level of locking required to execute the statement. Applications need to choose an isolation level that maximizes concurrency, yet ensures data consistency"
Is any of this true? (I kind of feel like John Edward here, crossing over to speak to a core dump that's been dead for a month!)
-- Jerry Asher, September 19, 2001