crobotdatabase.cpp

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

////////////////////////////////////////////////////////////////////
//
// CRobotDatabase.h - CRobotDatabase class implementation
//
// Source: "Programming Bots, Spiders, and Intelligent Agents
//          in Microsoft Visual C++"
//
// Copyright (c) 1999, David Pallmann. All rights reserved.


#include <stdafx.h>
#include <sql.h>
#include <sqlext.h>
#include "CRobotDatabase.h"


#define MAX_ERROR_MSG	1024
#define	MAX_DATA		200


// -----------------------------------------------------------------
// ***********************
// *                     *
// *  Class Constructor  *
// *                     *
// ***********************
// Description:	Class constructor for CRobotDatabase class

CRobotDatabase::CRobotDatabase()
{
	m_nError = 0;
	m_bOpen = false;
	m_nFields = 0;
	m_bBrackets = false;
}


// -----------------------------------------------------------------
// **********************
// *                    *
// *  Class Destructor  *
// *                    *
// **********************
// Description:	Class destructor for CRobotDatabase class

CRobotDatabase::~CRobotDatabase()
{
	if (m_bOpen)
	{
		Close();
	} // End if
}


// -----------------------------------------------------------------
// **********
// *        *
// *  Open  *
// *        *
// **********
// Description:	Open a DSN/table
//
// Outputs:		<func-result>  - True if successful, false on error
//				m_nError       - SQL completion code
//                                0 or 1 (success), > 1 (error)

BOOL CRobotDatabase::Open(const CString& sDSN,
						  const CString& sTable,
						  const CString& sUserId,
						  const CString& sPassword)
{
	if (m_bOpen)
	{
		Close();
	} // End if
	
	// Open up ODBC connection

	RETCODE rc;
	CString sTemp;

	SQLAllocEnv(&henv);
	SQLAllocConnect(henv, &hdbc);
	strcpy((char*)szDSN, sDSN);
	
	sTemp = sUserId;
	strcpy((char*)szID, sTemp);

	sTemp = sPassword;
	strcpy((char*)szPassword, sTemp);

	m_sDSN = sTable;
	m_sTable = sTable;

	rc = SQLConnect(hdbc,
					szDSN,
					SQL_NTS,
					szID,
					SQL_NTS,
					szPassword,
					SQL_NTS);

	m_nError = (int) rc;

	if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
	{
		m_bOpen = true;
		return true;
	} // End if
	else
	{
		return false;
	} // End else
}


// -----------------------------------------------------------------
// ***********
// *         *
// *  Close  *
// *         *
// ***********
// Description:	Close a DSN/table

void CRobotDatabase::Close()
{
	if (m_bOpen)
	{
		SQLDisconnect(hdbc);
		SQLFreeConnect(hdbc);
		SQLFreeEnv(henv);

		m_bOpen = false;
	} // End if
}


// -----------------------------------------------------------------
// *************
// *           *
// *  Execute  *
// *           *
// *************
// Description:	Executes an SQL query
//
// Outputs:		<func-result> - True if successful;
//                               false if an error occurs
//				m_nError      - SQL completion code
//                               0 or 1 = success, n = error
//				m_sErrMsg     - Error message text

BOOL CRobotDatabase::Execute(const CString& sSQL)
{
	RETCODE rc;
	unsigned char szSQLSTATE[6];
	SDWORD nErr;
	unsigned char msg[MAX_ERROR_MSG + 1];
	SWORD cbmsg;
	m_sErrMsg = "";
	m_sQuery = sSQL;

	if (!m_bOpen)
		return false;

	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 {
		while(SQLError(0,
					   0,
					   hstmt,
					   szSQLSTATE,
					   &nErr,
					   msg,
					   sizeof(msg),
					   &cbmsg) == SQL_SUCCESS)
			m_sErrMsg += "\t\t" + CString(msg) + "\n";
		SQLFreeStmt(hstmt, SQL_DROP);
		return false;
	} // End else
}



// ***************
// *             *
// *  AddRecord  *
// *             *
// ***************
// Description:	Adds a record to 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::AddRecord()
{
	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 = "";
	// ---- Insert into query -----
	m_sQuery.Format("INSERT INTO %s (", m_sTable);
	for (int f = 0; f < m_nFields; f++)
	{
		if (f > 0) 
			m_sQuery += ",";
		sField = m_sFieldName[f];
		if (m_bBrackets)
		{
			if (sField.Left(1) != "[")
				sField = "[" + sField;
			if (sField.Right(1) != "]")
				sField += "]";
		} // End if m_bBrackets
		m_sQuery += sField;
	} // End for
	m_sQuery += ") VALUES (";
	for (f = 0; f < m_nFields; f++)
	{
		if (f > 0)
			m_sQuery += ",";
		if (m_bNumeric[f])
			sTemp = m_sFieldValue[f];
		else
			sTemp.Format("%s", 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;
}


// ******************
// *                *
// *  UpdateRecord  *
// *                *
// ******************
// Description:	Updates 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::UpdateRecord()
{
	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("UPDATE %s SET ", m_sTable);
	for (int f = 0; f < m_nFields; f++)
	{
		if (f > 0)
			m_sQuery += ",";
		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 += " WHERE ";
	int ff = 0;
	for (f = 0; f < m_nFields; f++)
	{
		if (m_bKeyField[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 if pRequired
	} // 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,