Problem with ODBC, Oracle8, LOBs and LONGs

Sorry if this is in a FAQ somewhere but I couldn't find a FAQ for any Oracle groups at rtfm.mit.edu. Are there any available?

I'm having trouble accessing a table that contains both a lob and a long with the Oracle 8.0.4.4.0 odbc driver. I'm running Personal Oracle 8.0.4 on NT 4.0 service pack 4.

The short version of the problem is that if I try to call SQLGetData on a long column that comes *after* a lob in my result set, I get an ora-3127 error. This is "no new operations allowed until the active operation ends."

The long version of the problem is that the table has 3 columns:

	c1 varchar(10)
	c2 clob
	c3 long

If I do a

        select * from tabname;

I get a result set with 3 columns. I bind the first column, then notice the clob and so do not bind columns 2 or 3. This is because you must use SQLGetData to return the clob and you cannot use SQLGetData on a column that precedes the last bound column. (The Microsoft odbc book says that this latter restriction can be relaxed by driver implementors but Oracle apparently has not done this.)

I then call SQLFetch to position the cursor and get the contents of c1. I then loop through the rest of the columns calling SQLGetData. When I try to call SQLGetData for column c2 I get ora-3127, "no new operations allowed until the active operation ends". Ie it fails when getting the clob even though it precedes the long in the result set.

This looks to me like a bug in the odbc driver's use of OCI non-blocking mode.

I've tried several variations but it is the long following the lob in the result set that seems to be the key. I tried using a table that had another varchar instead of the long and then this approach worked correctly. I tried it on a table that only contains a lob followed by a long (so no columns were bound) and still got the error. So it appears to be a lob/long interaction. I've tried it on a table where the long precedes the lob and it works ok. It does not matter whether you name the columns or use * in the query. It does not matter in which order the columns are named in the query, just the order in the result set. I also tried getting a long by calling SQLGetData, rather than SQLFetch on a bound column, and that worked ok too.

So the question is can result sets with longs preceded by lobs be accessed via the Oracle8 Odbc driver?

A slightly related question is that if you call SQLGetDataTypeInfo with the value SQL_ALL_TYPES you do not get told about the LOB types, just the old Oracle 7 types. Is this a bug or feature?

Please respond via email to the address below. I'll summarize if people are interested.

TIA Keith Crews keith at belmont dot com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 05 1999 - 11:05:45 CST