Urgent Please Help... -- dotnet odbc.net

dotnet odbc.net : Urgent Please Help...

Irfan Akram
10/29/2004 6:14:02 AM

I keep getting this irritating exception on and on. Please Help.

There is already an open DataReader associated with this Connection which
must be closed first.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.InvalidOperationException: There is already an
open DataReader associated with this Connection which must be closed first.

Source Error:

Line 84: OdbcCommand myCommand = new
OdbcCommand(mySelectQuery,myConnection);
Line 85: myConnection.Open();
Line 86: OdbcDataReader myReader = myCommand.ExecuteReader();
Line 87: myReader.Close();
Line 88:

Here is that part of the source Code..

private void CheckUser()
{

string connectionString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=markingsystem;" +
"UID=akramm;" +
"PASSWORD=rootuser;";

string mySelectQuery = "SELECT * FROM account_details";
OdbcConnection myConnection = new OdbcConnection(connectionString);
OdbcCommand myCommand = new OdbcCommand(mySelectQuery,myConnection);
myConnection.Open();
OdbcDataReader myReader = myCommand.ExecuteReader();
myReader.Close();

}

Note please that I only have 1 datareader, so the exception doesnt make
sense to me..

Your kind comments will be very much appreciated...

Thanks,

Irfan

Bob Grommes
10/30/2004 7:23:58 PM

Irfan,

Do you have a DataReader anyplace else in your application that might not
have been properly closed before calling this routine? If I were coming
into this code cold I'd do a global serach for ExecuteReader() and look for
someplace where a reader isn't closed.

Also, you must be leaving out some code, because you are executing the
SELECT and then closing the reader without doing anything with it.

By the way to help avoid accidentally leaving something open, I tend to wrap
stuff like this in using blocks, for example:

OdbcConnection myConnection = new OdbcConnection(connectionString);
OdbcCommand myCommand = new OdbcCommand(mySelectQuery,myConnection);
myConnection.Open();

using (myConnection) {
OdbcDataReader myReader = myCommand.ExecuteReader();

using (myReader) {
// Do stuff with the reader
}

// When execution arrives here, myReader is closed.

}

// When execution arrives here, myConnection is closed.

--Bob

[quoted text, click to view]

8870
3/15/2005 3:29:02 AM

I hope the next release of the .net framework will allow multiple active
DataReaders for ALL connection types (sqlserver, oledb, odbc).
The version I have (1.1.4322) does not. I cannot understand the reason for
this limitation (see exception in the following code)
as the underlying technology, in this case ODBC, doesn't have this limitation.
The following example is of no use, but it shows the problem.

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;

public class test{
public static void Main(String[] args){
IDbConnection conn = new OdbcConnection("DSN=...");
conn.Open();

IDbCommand cmd1 = conn.CreateCommand();
cmd1.CommandText="SELECT tabname from systables";

IDataReader rdr1 = cmd1.ExecuteReader();
//rdr1.Close();

IDbCommand cmd2 = conn.CreateCommand();
cmd2.CommandText="SELECT idxname from sysindexes";
IDataReader rdr2 = cmd2.ExecuteReader();
/////////////
//// Unhandled Exception: System.InvalidOperationException: There is
already an open
//// DataReader associated with this Connection which must be closed first.
/////////////

while(rdr1.Read()) Console.WriteLine(rdr1.GetString(0));

for(int i=0; i<10; i++){
rdr1.Read(); Console.WriteLine(rdr1.GetString(0));
rdr2.Read(); Console.WriteLine(rdr2.GetString(0));
}

}

8870
3/15/2005 3:39:02 AM

the line "while(rdr1.Read()) Console.WriteLine(rdr1.GetString(0));" should
not be there, and of course I should check for eof. But the problem occurs

Sylvain Lafontaine
3/19/2005 2:08:14 AM

The ODBC and the OLEDB drivers doesn't permit to have multiple concurrent
streams opened from the same connection. What's happening is that these
drivers will open a second anonymous connection when required, giving you
the impression that a single connection is handling multiple stream.
However it's easy to see that this leads to a lot of problems/bugs when
transactions and other specific features linked to connections (like
temporary tables) are involved (for example because a transaction is always
associated with a single connection and that other connections, including
anonymous connections, cannot be part of it).

These "under the hood" features has been removed from the .NET framework and
if I were you, I wouldn't hope to much on seeing them in the next release.
If you need two open connections, then all you have to do is to open two
connections; no mess, no fuss.

S. L.

[quoted text, click to view]

8870
3/21/2005 4:11:03 AM

Thank you for the detailed answer. I don't know OLEDB, but have tried with
ODBC to an informix database (using the current version of the informix ODBC
driver) and it definitely doesn't open more than 1 connection in order to
fetch the data from the 2 SQL statements. The number of records in both
tables was large enough so that it had to read records alternately from the 2
statements several times. At no time there were more than one IP connection
to the database server. I have verified that on the client (netstat) and on
the server (db monitoring tool). A network sniffer has shown that records
from both statements have alternately gone over the network.
The SQLFetch function of the ODBC API has a parameter hStmt so the API
should allow multiple streams; maybe as you wrote some ODBC drivers do really
open more connections for providing the desired functionality. Whether more
connections are created seems to depend on the driver.
I agree that more connections is a bad choice; however as OdbcConnection has
the limitation mentioned, you are forced to use them, if you don't want to
store the entire result set (which can sometimes be large) in the DataTable
(that is in the RAM of the client) if you need other SQL statements while the
result from the first one has not yet been entirely fetched by the client (by
IDataReader.Read()).

[quoted text, click to view]

Sylvain Lafontaine
3/21/2005 6:43:57 PM

Well, I don't know about the informix ODBC driver and what's the exact
definition of an OdbcConnection. (Does a second connection necessarily
involve a second TCP/IP connection?). Maybe you will find more information
on dedicated references for ODBC and OLEDB.

All I can tell you is that it has been explicitely removed by Microsoft on
its native NET drivers for JET and SQL-Server because of the numerous bugs
that this feature has introduced in the past on complex situations. Maybe
other .NET providers - from Informix or Orable for example - will provide it
or maybe even MS will change its decision in the futur but for now, this is
*out* and it's not by inadvertence.

S. L.

[quoted text, click to view]

8870
3/30/2005 4:25:10 AM

excuse me if I continue to insist on this problem. Perhaps someone from
Microsoft can tell us the reasons why they allows only one active DataReader
per database connection.
The classes System.Data.Odbc.OdbcConnection etc are a wrapper around the
ODBC api.
1) the java JDBC-ODBC bridge is a similar wrapper, and it allows the desired
feature (I have tried it)
2) the Mono implementation of System.Data.Odbc does also allow it (I have
just tried it)
3) for the MS .NET framework, it is possible only with one DB: SQL Server
2005. Why this?
It is clear that limitations in the ODBC api cannot be the reason for this
constraint, as it works with 1) and 2).

I would like to repeat how important this feature is. A typical
Client-Server application is made of dozens of database tables. A particular
task normally uses data from several tables. In general it is not possible to
access them all with one big query. With the limitation in the
System.Data.Odbc and System.Data.OleDB classes, all the data from the
preceding queries must be stored in the RAM of the Client before the next
query can be executed.
There are many reasons why that is a bad solution, here are a few examples:
-a query may return large amounts of data that fills the RAM
-often only the first ... records of the result are needed (based on an
abort condition determined by the code that consumes the records)
-often it would be nice to get at the first records of a query before the
last record has gone from the DB server to the client (for example to display
the first page(s) of a long report in the print preview while the rest of the
report is still being built in the background, or to display the first page
of data in a grid and read more records when the user scrolls down)
....

Sylvain Lafontaine
3/30/2005 10:59:01 AM

All you have to do is to open a second connection (this is how ODBC is doing
it) or to redesign your queries to retrieve only the needed results. You
can even make some of this work directly on the SQL-Server.

However, this will work with SQL 2005 because MS has added this capability
to ADO.NET 2.0 but I don't know how they have implemented it so I cannot
tell you the real consequences of using this feature with SQL-Server
2005/ADO.NET 2.0.

Finally, the exemples you gave me have the default of imposing a big burden
on the resources of any SQL-Server. This is why such things as connected
and cached recordsets have been dropped from ADO.NET because it is nearly
impossible to scale them up on a high availability SQL-Server (which doesn't
mean that they don't work well when you are the only single person using
it). Good design practice require that you design your application in such
a way to have the less possibly burden on a partaged resource like a
SQL-Server. This is incompatible with a brut force technique.

S. L.

[quoted text, click to view]

8870
3/31/2005 2:05:02 AM

[quoted text, click to view]
yes that is the only choice (the most costly in terms of resources)

[quoted text, click to view]
ODBC doesn't in general open more connections in order to have more active
result sets. In my case (informix) there was only one connection and
therefore one transaction. (Maybe an ODBC driver for some particular DB does
really open more connections)

[quoted text, click to view]
I didn't intend to read data that is not needed.

[quoted text, click to view]
probably you mean stored procedures.

[quoted text, click to view]
It is clear that open cursors need RAM on the DB server. Connected
recordsets may not be the optimal thing for every task, but they are very
useful in many situations and have worked well for years.
What is a brute force technique? Assume you have a table with 50000 records
and want to display/edit data from this table in a grid, and before you see
any record, all the 50000 must be downloaded into the DataSet of the client.
This is a waste of resources (network traffic, disk access, time). One could
say in the case of such large tables there should be a filter which limits
the records retrieved to 200, for example. But I think many users won't
always specify an optimal filter. They open the window and want to see the
data. The software should be smart enough to do it in an efficient way.
Immagine you enter some criteria in an internet search engine, and it finds a
huge number of results, and before you see the first page all the results
must be downloaded to your client.
I think connected recordsets should be available when they are needed. If
they are useless, why did Microsoft introduce MARS in .NET 2.0 (but only for
their own SQL server)? I know no other DB client product that doesn't support
this technique. In my opinion the change to be made in the System.Data.Odbc

Raj
6/14/2005 1:14:02 PM

Hi irfan,

I am also trying to use the transactional serviced(com+) component to update
the informix database tables using the odbc.

Whenver i tried to connect to informinx database by ordinary .net
code(without any transactional components) it's working fine.but the problem
arises when i used to connect to informinx by using transactional com+.
any idea on this.
thanks in advance.
raj

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet odbc.net

home · blog · groups

Questions? Comments? Contact the dn