crobotdatabase.cpp

Visual C++自动查询和智能代理程序设计书籍源码-WebSpeed

					   0,
					   hstmt,
					   szSQLSTATE,
					   &nErr, msg,
					   sizeof(msg),
					   &cbmsg)
			  == SQL_SUCCESS)
		{
			m_sErrMsg += "\t\t" + CString(msg) + "\n";
		} // End while
	} // End else
	SQLFreeStmt(hstmt, SQL_DROP);
	return false;
}


// ******************
// *                *
// *  DeleteRecord  *
// *                *
// ******************
// Description:	Deletes a record in a database
//
// Inputs:		m_nFields, m_sFieldName[], m_sFieldValue[], and
//              m_bKeyField[] must have been set up in advance
//
// Outputs:		<func-result> - True if successful, false on error
//				m_nError      - 0/1 (successful); n (error)
//				m_sErrMsg     - SQL error message(s)

BOOL CRobotDatabase::DeleteRecord()
{
	CString sValue;
	CString sDelimiter;
	RETCODE rc;
	CString sField;
	unsigned char szSQLSTATE[6];
	SDWORD nErr;
	unsigned char msg[MAX_ERROR_MSG + 1];
	SWORD cbmsg;

	if (!m_bOpen)
		return false;

	m_sQuery = "";
	m_sErrMsg = "";
	// ---- Update query -----
	m_sQuery.Format("DELETE * FROM %s WHERE ", m_sTable);
	int ff = 0;
	for (int f = 0; f < m_nFields; f++)
	{
		if (ff > 0)
			m_sQuery += " AND ";
		ff++;
		sField = m_sFieldName[f];
		if (m_bBrackets) {
			if (sField.Left(1) != "[")
				sField = "[" + sField;
			if (sField.Right(1) != "]")
				sField += "]";
		} // End if m_bBrackets
		if (m_bNumeric[f])
			sTemp.Format("%s=%s", sField, m_sFieldValue[f]);
		else
			sTemp.Format("%s=%s",sField, Quote(m_sFieldValue[f]));
		m_sQuery += sTemp;
	} // End for
	m_sQuery += ";";

	// --- Execute the query ----
	SQLAllocStmt(hdbc, &hstmt);

	strcpy((char*)szQuery, m_sQuery);
	rc = SQLExecDirect(hstmt, szQuery, SQL_NTS);
	m_nError = rc;

	if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
	{
		SQLFreeStmt(hstmt, SQL_DROP);
		return true;;
	} // End if
	else
	{
		m_sErrMsg = "";
		while(SQLError(0,
					   0,
					   hstmt,
					   szSQLSTATE,
					   &nErr,
					   msg,
					   sizeof(msg),
			  &cbmsg) == SQL_SUCCESS)
		{
			m_sErrMsg += "\t\t" + CString(msg) + "\n";
		} // End while
	} // End else
	SQLFreeStmt(hstmt, SQL_DROP);
	return false;
}


// ***************************
// *                         *
// *  SelectMatchingRecords  *
// *                         *
// ***************************
// Description:	Selects database record matching critiria
//
// Inputs:		sWhereClause  - Conditions for selection
//							     (the WHERE clause of an SQL statement)
//
// Outputs:		<func-result> - True if successful, false on error
//				m_nError      - 0/1 (successful); n (error)
//				m_sErrMsg     - SQL error message(s)
//
// Notes:		After calling, if successful, the first matching record
//				is loaded into the m_sFieldValue array

BOOL CRobotDatabase::SelectMatchingRecords(const CString& sWhereClause)
{
	CString sValue;
	CString sDelimiter;
	RETCODE rc;
	CString sField;
	unsigned char szSQLSTATE[6];
	SDWORD nErr;
	unsigned char msg[MAX_ERROR_MSG + 1];
	SWORD cbmsg;
	char szData[MAX_DATA];
	SDWORD cbData;

	if (!m_bOpen)
		return false;

	m_sQuery = "";
	m_sErrMsg = "";
	// ---- Update query -----
	if (sWhereClause == "")
		m_sQuery.Format("SELECT * FROM %s;", m_sTable);
	else
		m_sQuery.Format("SELECT * FROM %s WHERE %s;",
						m_sTable,
						sWhereClause);

	// --- Execute the query ----
	SQLAllocStmt(hdbc, &hstmt_select);

	strcpy((char*)szQuery, m_sQuery);
	rc = SQLExecDirect(hstmt_select, szQuery, SQL_NTS);
	m_nError = rc;

	if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
	{
		rc = SQLFetch(hstmt_select);
		m_nError = rc;
		if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
		{
			for (int f = 0; f < m_nFields; f++)
			{
				rc = SQLGetData(hstmt_select,
								f+1,
								SQL_C_CHAR,
								szData,
								sizeof(szData),
								&cbData);
				if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
					m_sFieldValue[f] = CString(szData);
				else
					m_sFieldValue[f] = "";
			} // End for
		return true;
		} // End inner if
	} // End outer if

// ODBC error

	m_sErrMsg = "";
	while(SQLError(0,
				   0,
				   hstmt_select,
				   szSQLSTATE,
				   &nErr,
				   msg,
				   sizeof(msg), &cbmsg)
		  == SQL_SUCCESS)
	{		
		m_sErrMsg += "\t\t" + CString(msg) + "\n";
	} // End while
	SQLFreeStmt(hstmt_select, SQL_DROP);
	return false;
}


// ************************
// *                      *
// *  NextMatchingRecord  *
// *                      *
// ************************
// Description:	Advances to next matching record after a previous
//				call to SelectRecords
//
// Inputs:		None
//
// Outputs:		<func-result> - True if successful, false on error
//				m_nError      - 0/1 (successful); n (error)
//				m_sErrMsg     - SQL error message(s)
//
// Notes:		After calling, if successful, the next matching record
//				is loaded into the m_sFieldValue array

BOOL CRobotDatabase::NextMatchingRecord()
{
	CString sValue;
	CString sDelimiter;
	RETCODE rc;
	CString sField;
	unsigned char szSQLSTATE[6];
	SDWORD nErr;
	unsigned char msg[MAX_ERROR_MSG + 1];
	SWORD cbmsg;
	char szData[MAX_DATA];
	SDWORD cbData;

	if (!m_bOpen)
		return false;

	rc = SQLFetch(hstmt_select);
	m_nError = rc;
	if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
	{
		for (int f=0; f<m_nFields; f++)
		{
			rc = SQLGetData(hstmt_select,
							f + 1,
							SQL_C_CHAR,
							szData,
							sizeof(szData),
							&cbData);
			if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
				m_sFieldValue[f] = CString(szData);
			else
				m_sFieldValue[f] = "";
		} // End for
		return true;
	} // End inner if

// ODBC error

	m_sErrMsg = "";
	while(SQLError(0,
				   0,
				   hstmt_select,
				   szSQLSTATE,
				   &nErr,
				   msg,
				   sizeof(msg),
				   &cbmsg)
		  == SQL_SUCCESS)
	{
		m_sErrMsg += "\t\t" + CString(msg) + "\n";
	} // End while
	SQLFreeStmt(hstmt_select, SQL_DROP);
	return false;
}


// **************************
// *                        *
// *  CountMatchingRecords  *
// *                        *
// **************************
// Description:	Counts database records matching criteria
//
// Inputs:		sWhereClause  - Conditions for selection
//							     (the WHERE clause of an SQL statement)
//
// Outputs:		<func-result> - Number of matching records

int CRobotDatabase::CountMatchingRecords(const CString& sWhereClause)
{
	CString sValue;
	CString sDelimiter;
	RETCODE rc;
	CString sField;
	unsigned char szSQLSTATE[6];
	SDWORD nErr;
	unsigned char msg[MAX_ERROR_MSG + 1];
	SWORD cbmsg;
	char szData[MAX_DATA];
	SDWORD cbData;

	if (!m_bOpen)
		return false;

	m_sQuery = "";
	m_sErrMsg = "";
	// ---- Update query -----
	if (sWhereClause == "")
		m_sQuery.Format("SELECT COUNT(*) FROM %s;", m_sTable);
	else
		m_sQuery.Format("SELECT COUNT(*) FROM %s WHERE %s;",
						m_sTable,
						sWhereClause);

	// --- Execute the query ----
	SQLAllocStmt(hdbc, &hstmt_select);

	strcpy((char*)szQuery, m_sQuery);
	rc = SQLExecDirect(hstmt_select, szQuery, SQL_NTS);
	m_nError = rc;

	if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
	{
		rc = SQLFetch(hstmt_select);
		m_nError = rc;

		if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
		{
			rc = SQLGetData(hstmt_select,
							1,
							SQL_C_CHAR,
							szData,
							sizeof(szData), &cbData);
			return atoi(CString(szData));
		} // End if
	} // End if

// ODBC error

	m_sErrMsg = "";
	while(SQLError(0,
				   0,
				   hstmt_select,
				   szSQLSTATE,
				   &nErr,
				   msg,
				   sizeof(msg),
				   &cbmsg)
		  == SQL_SUCCESS)
	{
		m_sErrMsg += "\t\t" + CString(msg) + "\n";
	} // End while
	SQLFreeStmt(hstmt_select, SQL_DROP);
	return 0;
}



// ***********
// *         *
// *  Quote  *
// *         *
// ***********
// Description:	Returns a quoted edition of its argument
//				Uses single or double quotes based on string content

CString CRobotDatabase::Quote(const CString& sText)
{
	CString sResult = "";
	CString sChar;
	int nLen = sText.GetLength();
	for (int nPos = 0; nPos < nLen; nPos++)
	{
		sChar = sText.Mid(nPos, 1);
		if (sChar == "'")
			sChar = "''";
		sResult += sChar;
	} // End for
	return CString("'" + sResult + "'");
}