songnian qian

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!

brannon jones [ms]

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.

robert sedor

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

--
ROBERT SEDOR

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.