This method solves a real problem: How can I know the real number of records in a table without walking through all of them?
The answer is easy: use the SQL command SELECT COUNT(*) FROM MYTABLE and get the result.
class CMyRecordset : public CRecordset
{
public:
long GetRecordCount();
CMyRecordset(CDatabase* pDatabase = NULL);
DECLARE_DYNAMIC(CMyRecordset)
// Field/Param Data
//{{AFX_FIELD(CMyRecordset, CRecordset)
//}}AFX_FIELD
// Overrides
// ClassWizard generated virtual function overrides
//{{AFX_VIRTUAL(CMyRecordset)
public:
virtual void DoFieldExchange(CFieldExchange* pFX); // RFX support
//}}AFX_VIRTUAL
// Implementation
#ifdef _DEBUG
virtual void AssertValid() const;
virtual void Dump(CDumpContext& dc) const;
#endif
};
The code -the most important part of it-
long CMyRecordset::GetRecordCount()
{
SDWORD nCount=0, cbCount;
UCHAR* szStmt = new UCHAR[2048];
UCHAR* szDSN = new UCHAR[255];
UCHAR* szUID = new UCHAR[255];
UCHAR* szPWD = new UCHAR[255];
int nPosDNS_Start, nPosDNS_End,
nPosUID_Start, nPosUID_End,
nPosPWD_Start, nPosPWD_End;
int nPosFrom, nPosWhere, nPosOrder;
CString sConnect, sBuffer;
CString sSQL, sSQLUpper, sDummy;
SQLHENV hEnv = 0;
SQLHDBC hDBC = 0;
SQLHSTMT hStmt = 0;
// get connection string & SQL string
sConnect = GetDefaultConnect();
sSQL = GetSQL();
wsprintf( (char*)szDSN, "");
wsprintf( (char*)szUID, "");
wsprintf( (char*)szPWD, "");
// let's read tokens
sSQLUpper = sSQL;
sSQLUpper.MakeUpper();
nPosFrom = sSQLUpper.Find( " FROM " );
nPosWhere = sSQLUpper.Find( " WHERE " );
nPosOrder = sSQLUpper.Find( " ORDER BY " );
// search for table(s)
if( nPosWhere != -1 ) {
sDummy = sSQL.Mid( nPosFrom+6, nPosWhere-(nPosFrom+6) );
} else {
if( nPosOrder != -1) {
sDummy = sSQL.Mid( nPosFrom+6, nPosOrder-(nPosFrom+6) );
} else{
sDummy = sSQL.Right( sSQL.GetLength() - (nPosFrom+6));
}
}
sBuffer.Format( "SELECT COUNT(*) FROM %s", sDummy );
// search for filter
if( nPosWhere > 0 ) {
if( nPosWhere < nPosOrder )
sBuffer += sSQL.Mid( nPosWhere, nPosOrder - nPosWhere );
else
sBuffer += sSQL.Right( sSQL.GetLength() - nPosWhere );
}
wsprintf((char*)szStmt, sBuffer);
// connections string is ODBC;DSN=____;UID=____;PWD=____
// get DSN
sBuffer = sConnect;
nPosDNS_Start = sBuffer.Find("DSN=");
if( nPosDNS_Start != -1 ) {
sBuffer = sBuffer.Right( sBuffer.GetLength() - nPosDNS_Start -4);
nPosDNS_End = sBuffer.Find(";");
// here is DSN
if( nPosDNS_End != -1 )
wsprintf( (char*)szDSN, sBuffer.Left(nPosDNS_End));
}
// get UID
sBuffer = sConnect;
nPosUID_Start = sConnect.Find("UID=");
if( nPosUID_Start != -1 ) {
sBuffer = sBuffer.Right( sBuffer.GetLength() - nPosUID_Start -4);
nPosUID_End = sBuffer.Find(";");
if( nPosUID_End != -1 )
wsprintf( (char*)szUID, sBuffer.Left(nPosUID_End));
}
// get PWD
sBuffer = sConnect;
nPosPWD_Start = sConnect.Find("PWD=");
if( nPosPWD_Start != -1 ) {
sBuffer = sBuffer.Right( sBuffer.GetLength() - nPosPWD_Start -4);
nPosPWD_End = sBuffer.Find(";");
if( nPosPWD_End != -1 )
wsprintf( (char*)szPWD, sBuffer.Left(nPosPWD_End));
}
if( SQL_ERROR != SQLAllocEnv( &hEnv) )
if( SQL_ERROR != SQLAllocConnect( hEnv, &hDBC) )
if( SQL_ERROR != SQLConnect(hDBC, szDSN, SQL_NTS, szUID, SQL_NTS, szPWD, SQL_NTS) )
if( SQL_ERROR != SQLAllocStmt( hDBC, &hStmt) )
if( SQL_ERROR != SQLPrepare(hStmt, (unsigned char*)szStmt, SQL_NTS) )
if( SQL_ERROR != SQLBindCol(hStmt, 1, SQL_C_LONG, &nCount, 0, &cbCount) )
if( SQL_ERROR != SQLExecute( hStmt) )
SQLFetch(hStmt);
SQLFreeStmt(hStmt, SQL_CLOSE);
SQLDisconnect(hDBC);
SQLFreeConnect(hDBC);
SQLFreeEnv(hEnv);
delete[] szDSN;
delete[] szStmt;
delete[] szUID;
delete[] szPWD;
return nCount;
}
SELECT COUNT(*) FROM TABLE1, TABLE2
SELECT COUNT(*) FROM TABLE1, TABLE2 WHERE TABLE1.FIELD1 = TABLE1.FIELD1 AND TABLE2.FIELD1 = TABLE2.FIELD1;