Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > Open two rowset with same database connection
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 2nd July 15:58
songnian qian
External User
 
Posts: 1
Default Open two rowset with same database connection


How to open two rowset with same connection?

In my application (I use OleDB consumer class), I need
loop though two tables. For example:

CDatebase db;
.... open database connection

CCommand table1;
CCommand table2;
table1.Open(&db);
while(!table1.IsEOF())
{
table2.m_strFilter.Format("Index = %d", table1.m_nIndex);
table2.Open(&db);
...
table2.Close();
table1.MoveNext();
}
table1.Close();

From SQL trace, table2.Open(&db) cause open new database
connection and therefore, it is very slow.

Why the second open cause opening new database connection?
How to solve the problem?


Thanks!
  Reply With Quote


  sponsored links


2 2nd July 15:59
brannon jones [ms]
External User
 
Posts: 1
Default Open two rowset with same database connection


The problem is you can only have one active rowset per connection. If you
try to use a second rowset, while another is still active, the provider will
automatically open a new connection for you (the alternative is that you
would receive an error that the connection was busy, like you do in ODBC).

In this scenario you either need to use two seperate connections, or use
server-side cursors. If using two connections is acceptable, I would leave
your code as-is and let us open the second connection implicitly. That way,
if we change the limitation of one active rowset per connection, we will do
the right thing for you and you wont have to change your code.

It looks like you are opening a new rowset for each row of your first
rowset. This will never perform very well. What sort of work are you doing
for the second rowset? Can it be done in a stored procedure on the server?
Can you modify your two queries to produce a single query?

--
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
  Reply With Quote
3 2nd July 15:59
robert sedor
External User
 
Posts: 1
Default Open two rowset with same database connection


Doesn't CCommand.Close free the rowset and accessor.

--
ROBERT SEDOR
  Reply With Quote
4 2nd July 15:59
External User
 
Posts: 1
Default Open two rowset with same database connection


Thank you for your help.

The table1 and table2 are different table ralated by a
foreign key:
Table1:
Index1 (auto index, primary key)
Other fields...
Table2:
Index2 (auto index, primary key)
Index1 (same as Table1.Index1, foreign key)
Other fields...

I solved the problem by add rowset property
propset.AddProperty(DBPROP_SERVERCURSOR, true);
to table1 and table2. When I use SQL trace, no new
connection any more.

My question are:
1. BY default, the cousor is server-side, why
should I set SERVERCURSOR property?
2. For better performance, what is the rule for open
multiple rowsets? Could you recommand any article?

Thanks!


connection. If you

active, the provider will

alternative is that you

you do in ODBC).


connections, or use

acceptable, I would leave

implicitly. That way,

connection, we will do

code.


of your first

work are you doing

procedure on the server?


confers no rights.
  Reply With Quote
Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666