Get count of rows in CRecordset 
Author Message
 Get count of rows in CRecordset

Hello !

I use MySQL and VC++ 6.0 SP 4. Is there a possebility to get the rows in
a CRecordset without doing something like:
CRecordset *pRecset;
... do some SQL...
pRecset->MoveFirst();
long nCount = 0;
while (!pRecset->IsEOF())
{ pRecset->MoveNext();
  nCount++;
 }

I tried some methodes, but:
GetRecordCount() is always 1
GetRowsetSize() is always 1

Any ideas to make this faster ?

Regards

Martin



Fri, 11 Jul 2003 23:47:31 GMT  
 Get count of rows in CRecordset
You can put a count SQL statement when retrieving the records from the
database to count the results! I don't know any other way.

When you use the count of retrieved records to loop through these records,
then I would suggest that you use something like this:

while (!IsEOF())
{
    do actions here!!!
    MoveNext( )

Quote:
}

Ferry Janssen

Martin Thoma schreef:

Quote:
> Hello !

> I use MySQL and VC++ 6.0 SP 4. Is there a possebility to get the rows in
> a CRecordset without doing something like:
> CRecordset *pRecset;
> ... do some SQL...
> pRecset->MoveFirst();
> long nCount = 0;
> while (!pRecset->IsEOF())
> { pRecset->MoveNext();
>   nCount++;
>  }

> I tried some methodes, but:
> GetRecordCount() is always 1
> GetRowsetSize() is always 1

> Any ideas to make this faster ?

> Regards

> Martin



Sat, 12 Jul 2003 17:30:42 GMT  
 Get count of rows in CRecordset


Quote:
> Hello !

> I use MySQL and VC++ 6.0 SP 4. Is there a possebility to get the rows
in
> a CRecordset without doing something like:
> CRecordset *pRecset;
> ... do some SQL...
> pRecset->MoveFirst();
> long nCount = 0;
> while (!pRecset->IsEOF())
> { pRecset->MoveNext();
>   nCount++;
>  }

> I tried some methodes, but:
> GetRecordCount() is always 1
> GetRowsetSize() is always 1

> Any ideas to make this faster ?

> Regards

> Martin

Hi Martin, of course traversing the recordset is not optimal. However
the knowledge of actual number of records in table is problematic in
multiuser environment. Even recordset is "snapshot" in fact and it's
reaction to added or deleted records can be problematic. It cannot
automatically read every second the number of records and database does
not send this information via ODBC. The transactional SQL environment
mapped to ODBC should work in different way.

OK, try to do this:
1. Create recordset derived from CRecordset. Use only one long variable
(not "attach all records" or such what) for data exchange (MFC,
RFX_Long), for instance "nCount"
2. Do not use automatic table name, just database name is OK
3. Open the recordset with full SQL command
   myHelper.Open( AFX_DB_USE_DEFAULT_TYPE, "SELECT COUNT(*) FROM
MyTable", CRecordset::readOnly )
4. When opened successfuly, read the first and only result "nCount" and
you have got it...
5. Close record and open the full recordset with "approximate"
knowledge about number of records.
6. Your environment is transactional, you can freely call the record
counting recordset even when the full column recordset is still open.

Note: you can add filter condition to the SELECT clause, of course, if
you are reading just part of recordset. As far as I know, Requery is
not so much effective, better is open/count/close to spare database and
ODBC resources.

Jiri Kral

Sent via Deja.com
http://www.deja.com/



Sat, 12 Jul 2003 20:35:39 GMT  
 Get count of rows in CRecordset
It's possible to do something which is quite the same as you decsribe but
much more quicker on large rowset by a dichotomic function:

REMARKS : m_hstmt is handle of the statement (see SQLAllocStmt,
SQLSetStmtOption, SQLPrepare )

   long  step        = 32768;
   long  pos         = 0;
   UDWORD RowsFetched;
   UWORD  RowStatus;
   while ( step != 0 )
         {
         pos += step;
         if ( SQLExtendedFetch( m_hstmt, SQL_FETCH_ABSOLUTE, pos,
&RowsFetched, &RowStatus ) != SQL_SUCCESS )
            {
            pos-=step;
            step /= 2;
            }
         }
   // memorize record count
   m_nRecordCount = pos;
   TRACE("m_nRecordCount = %d\n", m_nRecordCount);
   // Goto first record
   SQLExtendedFetch( m_hstmt, SQL_FETCH_FIRST, pos, &RowsFetched,
&RowStatus );

   return m_nRecordCount;



Quote:
> Hello !

> I use MySQL and VC++ 6.0 SP 4. Is there a possebility to get the rows in
> a CRecordset without doing something like:
> CRecordset *pRecset;
> ... do some SQL...
> pRecset->MoveFirst();
> long nCount = 0;
> while (!pRecset->IsEOF())
> { pRecset->MoveNext();
>   nCount++;
>  }

> I tried some methodes, but:
> GetRecordCount() is always 1
> GetRowsetSize() is always 1

> Any ideas to make this faster ?

> Regards

> Martin



Tue, 15 Jul 2003 22:48:55 GMT  
 Get count of rows in CRecordset
I actually don't think it's a good coding practice to mix OLEDB with ODBC.


Quote:
> It's possible to do something which is quite the same as you decsribe but
> much more quicker on large rowset by a dichotomic function:

> REMARKS : m_hstmt is handle of the statement (see SQLAllocStmt,
> SQLSetStmtOption, SQLPrepare )

>    long  step        = 32768;
>    long  pos         = 0;
>    UDWORD RowsFetched;
>    UWORD  RowStatus;
>    while ( step != 0 )
>          {
>          pos += step;
>          if ( SQLExtendedFetch( m_hstmt, SQL_FETCH_ABSOLUTE, pos,
> &RowsFetched, &RowStatus ) != SQL_SUCCESS )
>             {
>             pos-=step;
>             step /= 2;
>             }
>          }
>    // memorize record count
>    m_nRecordCount = pos;
>    TRACE("m_nRecordCount = %d\n", m_nRecordCount);
>    // Goto first record
>    SQLExtendedFetch( m_hstmt, SQL_FETCH_FIRST, pos, &RowsFetched,
> &RowStatus );

>    return m_nRecordCount;



> > Hello !

> > I use MySQL and VC++ 6.0 SP 4. Is there a possebility to get the rows in
> > a CRecordset without doing something like:
> > CRecordset *pRecset;
> > ... do some SQL...
> > pRecset->MoveFirst();
> > long nCount = 0;
> > while (!pRecset->IsEOF())
> > { pRecset->MoveNext();
> >   nCount++;
> >  }

> > I tried some methodes, but:
> > GetRecordCount() is always 1
> > GetRowsetSize() is always 1

> > Any ideas to make this faster ?

> > Regards

> > Martin



Wed, 16 Jul 2003 00:25:26 GMT  
 Get count of rows in CRecordset
I actually don't think it's a good coding practice to mix OLEDB with ODBC.


Quote:
> It's possible to do something which is quite the same as you decsribe but
> much more quicker on large rowset by a dichotomic function:

> REMARKS : m_hstmt is handle of the statement (see SQLAllocStmt,
> SQLSetStmtOption, SQLPrepare )

>    long  step        = 32768;
>    long  pos         = 0;
>    UDWORD RowsFetched;
>    UWORD  RowStatus;
>    while ( step != 0 )
>          {
>          pos += step;
>          if ( SQLExtendedFetch( m_hstmt, SQL_FETCH_ABSOLUTE, pos,
> &RowsFetched, &RowStatus ) != SQL_SUCCESS )
>             {
>             pos-=step;
>             step /= 2;
>             }
>          }
>    // memorize record count
>    m_nRecordCount = pos;
>    TRACE("m_nRecordCount = %d\n", m_nRecordCount);
>    // Goto first record
>    SQLExtendedFetch( m_hstmt, SQL_FETCH_FIRST, pos, &RowsFetched,
> &RowStatus );

>    return m_nRecordCount;



> > Hello !

> > I use MySQL and VC++ 6.0 SP 4. Is there a possebility to get the rows in
> > a CRecordset without doing something like:
> > CRecordset *pRecset;
> > ... do some SQL...
> > pRecset->MoveFirst();
> > long nCount = 0;
> > while (!pRecset->IsEOF())
> > { pRecset->MoveNext();
> >   nCount++;
> >  }

> > I tried some methodes, but:
> > GetRecordCount() is always 1
> > GetRowsetSize() is always 1

> > Any ideas to make this faster ?

> > Regards

> > Martin



Wed, 16 Jul 2003 00:25:54 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. datareader row count

2. How to count returned rows

3. How to count the number of rows returned

4. Change my select statement in a CRecordSet class SELECT VERIABLE1, COUNT(*)

5. CRecordset and count(*)

6. CRecordset Count

7. Counting records in a CRecordset

8. Count(*) in CRecordset

9. CRecordset and count(*)

10. CRecordset: Any way to count number of records without scrolling through them all

11. CRecordset bulk row fetch broken?

12. CRecordset Bulk Row Fetching

 

 
Powered by phpBB® Forum Software