odbcfunc.cpp

电信的97接口程序,用于把话单入库。这里是采用FTP方式采集话单

/*+============================================================================
File:        odbcFunc.cpp
Summary:     The class for connect database with ODBC
History:
2002/01/08	V2.0 
	Start it. ([e-mail])
2002/02/09	V2.0.1
	Add the AutoCommit Control for ODBC class. ([e-mail])
============================================================================+*/

#include "odbcFunc.h"

//////////////////////////////////////////////////////////////////////////
// class COdbcFunc
//////////////////////////////////////////////////////////////////////////

//construct
COdbcFunc::COdbcFunc()
{
	m_connect = 0;
	m_logTimeout = 60; // logon timeout is 5 seconds

	m_disconnect = 0;
	m_henv = 0;
	m_hdbc = 0;
	m_hstmt = 0;
	
	for(int i=0;i<MAXQUERY;i++)
		m_hstmtext[i] = 0;
}

COdbcFunc::~COdbcFunc()
{
	while(m_connect > 0)
		Disconnect();
}

// connect the database
int COdbcFunc::Connect(char* dsn, char* username, char* password)
{
	RTRACE(5, "Connect to database " << dsn << " ...");
	if(m_connect > 0)
	{
		RTRACE(2, "Has connected database!");
		return -1;
	}
	m_connect ++;
	/*Allocate environment handle */

	retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);  

	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
	{
		/* Set the ODBC version environment attribute */
		retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 

		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
		{
			m_henv ++;
			/* Allocate connection handle */
			retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 

			if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
			{
				m_hdbc ++;
				/* Set login timeout */
				SQLSetConnectAttr(hdbc, m_logTimeout, (void*)SQL_LOGIN_TIMEOUT, 0);
				/* Connect to data source */
				try // avoid connect sybase database exception
				{
					RTRACE(7, "Try to connect database with ODBC API");
					retcode = SQLConnect(hdbc, (SQLCHAR*)dsn, SQL_NTS,
						(SQLCHAR*)username, SQL_NTS,
						(SQLCHAR*)password, SQL_NTS);
				}
//	SQLCHAR  szConnStrIn[255], szConnStrOut[255];
//	SQLSMALLINT  cbConnStrOut;
	//lstrcpy(szConnStrIn, "DSN=MYDSN_SYB;userid=sa;password=system");
//	retcode = SQLBrowseConnect(hdbc, (SQLCHAR*)"DSN=MYDSN_SYB;UID=sa;PWD=system;DATABASE=ultralink", SQL_NTS,
//				szConnStrOut, 255, &cbConnStrOut);

//				}
				catch(...)
				{
					RTRACE(2, "Error! Unexpected error occured when connecting database");
					retcode = SQL_ERROR;
				}
				if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
				{
					m_disconnect ++;
					/* Allocate statement handle */
					retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); 

					if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
					{
						m_hstmt ++;
						RTRACE(2, "\n------------------------------Connect database " << dsn << " successfully------------------------------\n");
						return 0; // connect database successfully
					}
					RTRACE(3, "SQLAccocHandle hstmt " << dsn << " failed!");
					return -5; // failed
				}
				RTRACE(2, "\n------------------------------Connect database " << dsn << " failed------------------------------\n");
				return -4; // failed
			}
			RTRACE(3, "SQLAccocHandle hdbc " << dsn << " failed!");
			return -3; // failed
		}
		RTRACE(3, "SQLSetEnvAttr henv " << dsn << " failed!");
		return -2; // failed
	}
	RTRACE(3, "SQLAccocHandle henv " << dsn << " failed!");
	return -1; // failed
}

// disconnect the database
void COdbcFunc::Disconnect()
{
	RTRACE(5, "Disconnect...");
	for(int i=0;i<MAXQUERY;i++)
	{
		while(m_hstmtext[i] > 0)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmtext[i]);
			m_hstmtext[i] --;
		}
	}

	if(m_hstmt > 0)
	{
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		m_hstmt --;
	}

	if(m_disconnect > 0)
	{
		SQLDisconnect(hdbc);
		m_disconnect --;
	}

	if(m_hdbc > 0)
	{
		SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
		m_hdbc --;
	}

	if(m_henv > 0)
	{
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
		m_henv --;
	}

	m_connect --;
}

int COdbcFunc::ExecSQL(char * sql)
{
	// Execute sql
	retcode = SQLExecDirect(hstmt, (unsigned char *)sql, SQL_NTS);
	if (retcode == SQL_SUCCESS) 
	{
		RTRACE(5, "SQL execute successfully!");
		return 0;
	}
	else
	{
		RTRACE(5, "SQL execute failed!");
#ifdef DEBUGODBC
		char state[VALUE_LEN], message[SQL_SIZE];
		if(GetExecInfo(hstmt, state, message, VALUE_LEN, SQL_SIZE) == 0)
		{
			RTRACE(3, "Sql state : " << state);
			RTRACE(3, "Sql description : " << message);
		}
#endif /* DEBUFODBC */
		return -1;
	}
}

int COdbcFunc::ExecSQL(char * sql, int i)
{
	// Illegal judgement
	if(i < 0 || i >= MAXQUERY)
	{
		RTRACE(3, "FreeResult failed!");
		RTRACE(3, "\tThe handle number must between " << 0 << " to " << MAXQUERY);
		return -2;
	}

	if(m_hstmtext[i] <= 0)
	{
		RTRACE(3, "FreeResult failed!");
		RTRACE(3, "\tNo Added");
		return -3;
	}
	// Execute sql
	retcode = SQLExecDirect(hstmtext[i], (unsigned char *)sql, SQL_NTS);
	if (retcode == SQL_SUCCESS) 
	{
		RTRACE(5, "SQL execute successfully!");
		return 0;
	}
	else
	{
		RTRACE(5, "SQL execute failed!");
#ifdef DEBUGODBC
		char state[VALUE_LEN], message[SQL_SIZE];
		if(GetExecInfo(hstmtext[i], state, message, VALUE_LEN, SQL_SIZE) == 0)
		{
			RTRACE(3, "Sql state : " << state);
			RTRACE(3, "Sql description : " << message);
		}
#endif /* DEBUFODBC */

		return -1;
	}
}

int COdbcFunc::Next()
{
	retcode = SQLFetch(hstmt);
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
		return 0;
	else 
	{
		RTRACE(5, "No more rows fit for query");
		return -1;
	}
}

void COdbcFunc::GetFieldValue(int col, char * value, int size)
{
	SQLCHAR		szValue[VALUE_LEN];
	SQLINTEGER	valueOrInd;
	szValue[0] = '\0';
	if(SQLGetData(hstmt, col, SQL_C_CHAR, szValue, VALUE_LEN, &valueOrInd) == SQL_SUCCESS)
	{
		memcpy(value, (const char *)szValue, size - 1);
		value[size - 1] = 0;
	}
	else
		value[0] = '\0';
}

void COdbcFunc::GetFieldValue(int col, int * value)
{
	SQLINTEGER	valueOrInd, iValue=0;
	if(SQLGetData(hstmt, col, SQL_C_ULONG, &iValue, 0, &valueOrInd) == SQL_SUCCESS)
		*value = (int)iValue;
	else
		*value = 0;
}

int COdbcFunc::GetFieldValue(int col, struct tm * datetime)
{
	SQLINTEGER	valueOrInd;
	SQL_TIMESTAMP_STRUCT timestamp;

	timestamp.year = 0;
	timestamp.month = 0;
	timestamp.day = 0;
	timestamp.hour = 0;
	timestamp.minute = 0;
	timestamp.second = 0;

	if(SQLGetData(hstmt, col, SQL_C_TYPE_TIMESTAMP, &timestamp, 0, &valueOrInd) == SQL_SUCCESS)
	{
		datetime->tm_year = timestamp.year;
		datetime->tm_mon  = timestamp.month;
		datetime->tm_mday = timestamp.day;
		datetime->tm_hour = timestamp.hour;
		datetime->tm_min  = timestamp.minute;
		datetime->tm_sec  = timestamp.second;
		return 0;
	}
	else
		return -1;
}

// cancels the processing on a statement
void COdbcFunc::AbortQuery()
{
	SQLFreeStmt(hstmt, SQL_CLOSE);
}

int COdbcFunc::AbortQuery(int i)
{
	if(i < 0 || i >= MAXQUERY)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tThe handle number must between " << 0 << " to " << MAXQUERY);
		return -1;
	}

	if(m_hstmtext[i] <= 0)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tNo Added Result");
		return -1;
	}

	SQLFreeStmt(hstmtext[i], SQL_CLOSE);
	return 0;
}

int COdbcFunc::AddResult(int i)
{
	if(i < 0 || i >= MAXQUERY)
	{
		RTRACE(3, "AddResult failed!");
		RTRACE(5, "\tThe handle number must between " << 0 << " to " << MAXQUERY);
		return -1;
	}

	if(m_hstmtext[i] > 0)
	{
		RTRACE(3, "The Result has been added! So you can not allocate again.");
		return -1;
	}

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtext[i]); 

	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{
		m_hstmtext[i] ++;
		RTRACE(3, "AllocHandle successfully!");
		return 0; // connect database successfully
	}
	else
		return -1;
}

int COdbcFunc::FreeResult(int i)
{
	if(i < 0 || i >= MAXQUERY)
	{
		RTRACE(3, "FreeResult failed!");
		RTRACE(3, "\tThe handle number must between " << 0 << " to " << MAXQUERY);
		return -1;
	}

	if(m_hstmtext[i] <= 0)
	{
		RTRACE(3, "FreeResult failed!");
		RTRACE(5, "\tNo Added result");
		return -1;
	}

	SQLFreeHandle(SQL_HANDLE_STMT, hstmtext[i]);
	m_hstmtext[i] --;
	return 0;
}

int COdbcFunc::CancelResult(int i)
{
	if(i < 0 || i >= MAXQUERY)
	{
		RTRACE(3, "FreeResult failed!");
		RTRACE(5, "\tThe handle number must between " << 0 << " to " << MAXQUERY);
		return -1;
	}

	if(m_hstmtext[i] <= 0)
	{
		RTRACE(3, "FreeResult failed!");
		RTRACE(5, "\tNo Added Result");
		return -1;
	}

	SQLFreeStmt(hstmtext[i], SQL_CLOSE);
	return 0;
}

int COdbcFunc::Next(int i)
{
	// score judgement
	if(i < 0 || i >= MAXQUERY)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tThe handle number must between " << 0 << " to " << MAXQUERY);
		return -1;
	}

	if(m_hstmtext[i] <= 0)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tNo Added Result");
		return -1;
	}

	// Fetch handle
	retcode = SQLFetch(hstmtext[i]);
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
		return 0;
	else 
		return -1;
}

int COdbcFunc::GetFieldValue(int col, char * value, int size, int handle)
{
	// score judgement
	if(handle < 0 || handle >= MAXQUERY)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tThe handle number must between " << 0 << " to " << MAXQUERY);
		return -1;
	}

	if(m_hstmtext[handle] <= 0)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tNo Added Result");
		return -1;
	}

	SQLCHAR		szValue[VALUE_LEN];
	SQLINTEGER	valueOrInd;
	szValue[0] = '\0';
	if(SQLGetData(hstmtext[handle], col, SQL_C_CHAR, szValue, VALUE_LEN, &valueOrInd) == SQL_SUCCESS)
	{
		memcpy(value, (const char *)szValue, size - 1);
		value[size - 1] = 0;
	}
	else
		value[0] = '\0';

	return 0;
}

int COdbcFunc::GetFieldValue(int col, int * value, int handle)
{
	// score judgement
	if(handle < 0 || handle >= MAXQUERY)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tThe handle number must between " << 0 << " to " << MAXQUERY);
		return -1;
	}

	if(m_hstmtext[handle] <= 0)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tNo Added Result");
		return -1;
	}
	SQLINTEGER	valueOrInd, iValue=0;
	if(SQLGetData(hstmtext[handle], col, SQL_C_ULONG, &iValue, 0, &valueOrInd) == SQL_SUCCESS)
		*value = (int)iValue;
	else
		*value = 0;

	return 0;
}

int COdbcFunc::GetFieldValue(int col, struct tm * datetime, int handle)
{
	// score judgement
	if(handle < 0 || handle >= MAXQUERY)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tThe handle number must between " << 0 << " to " << MAXQUERY);
		return -1;
	}

	if(m_hstmtext[handle] <= 0)
	{
		RTRACE(3, "Next failed!");
		RTRACE(5, "\tNo Added Result");
		return -1;
	}

	SQLINTEGER	valueOrInd;
	SQL_TIMESTAMP_STRUCT timestamp;

	timestamp.year = 0;
	timestamp.month = 0;
	timestamp.day = 0;
	timestamp.hour = 0;
	timestamp.minute = 0;
	timestamp.second = 0;

	if(SQLGetData(hstmtext[handle], col, SQL_C_TYPE_TIMESTAMP, &timestamp, 0, &valueOrInd) == SQL_SUCCESS)
	{
		datetime->tm_year = timestamp.year;
		datetime->tm_mon  = timestamp.month;
		datetime->tm_mday = timestamp.day;
		datetime->tm_hour = timestamp.hour;
		datetime->tm_min  = timestamp.minute;
		datetime->tm_sec  = timestamp.second;
		return 0;
	}
	else
		return -1;
}

int COdbcFunc::GetExecInfo(SQLHSTMT hstmtIn, char *state, char *message, int value_len, int sql_len)
{
	SQLCHAR sqlState[VALUE_LEN], sqlMessage[SQL_SIZE];
	SQLINTEGER sqlErrPtr;
	SQLSMALLINT sqlLenPtr, sqlSize;

	sqlSize = (SQLSMALLINT)sql_len;

	sqlMessage[0] = '\0';
	sqlState[0] = '\0';

	try
	{
		retcode = SQLGetDiagRec(SQL_HANDLE_STMT, hstmtIn, 1,
						sqlState, &sqlErrPtr, sqlMessage, sqlSize, &sqlLenPtr);
		if(retcode == SQL_SUCCESS)
		{
			strncpy(state, (const char *)sqlState, value_len);
			strncpy(message, (const char *)sqlMessage, sql_len);
			return 0;
		}
	}
	catch(...)
	{
		// Unexpected error
	}
	return -1;
}

/*
int COdbcFunc::GetExecInfo(SQLHDBC hdbc, char *state, char *message, int size)
{
	SQLCHAR sqlState[VALUE_LEN], sqlMessage[SQL_SIZE];
	SQLINTEGER sqlErrPtr;
	SQLSMALLINT sqlLenPtr, sqlSize;

	sqlSize = (SQLSMALLINT)size;

	sqlMessage[0] = '\0';
	sqlState[0] = '\0';

	try
	{
		retcode = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1,
						sqlState, &sqlErrPtr, sqlMessage, sqlSize, &sqlLenPtr);
		if(retcode == SQL_SUCCESS)
		{
			strncpy(state, (const char *)sqlState, size);
			strncpy(message, (const char *)sqlMessage, size);
			return 0;
		}
	}
	catch(...)
	{
		// Unexpected error
	}
	return -1;
}

*/
void COdbcFunc::InsertSQL(char *table, VariousType * variousType, int size, char *sWhere)
{
	char sql[SQL_SIZE];
	strcpy(sql, "update ");
	strcat(sql, table);
	strcat(sql, " set ");
	for(int i=0;i<size;i++)
	{
		if(i != 0)
			strcat(sql, " , ");
		strcat(sql, (variousType + i)->column);
		strcat(sql, " = ? ");
	}
	strcat(sql, sWhere);
}

int COdbcFunc::SetAutoCommit(bool b)
{
	if(m_connect > 0)
	{
		if(b)
		{
			retcode = SQLSetConnectAttr(hdbc, 
						SQL_ATTR_AUTOCOMMIT,
						(void *)SQL_AUTOCOMMIT_ON,
						0);
			if(retcode == SQL_SUCCESS ||
				retcode == SQL_SUCCESS_WITH_INFO)
				return 0;
		}
		else 
		{
			retcode = SQLSetConnectAttr(hdbc, 
						SQL_ATTR_AUTOCOMMIT,
						(void *)SQL_AUTOCOMMIT_OFF,
						0);
			if(retcode == SQL_SUCCESS ||
				retcode == SQL_SUCCESS_WITH_INFO)
				return 0;
		}
	}
	else
		return -2; //not connect to database
	return -1; // set failed
}
//////////////////////////////////////////////////////////////////////////
// class VariousType
//////////////////////////////////////////////////////////////////////////
// 1:short int
// 2:long int
// 3:char
// 4:float
// 5:double
// 6:date
// 7:time
// 8:timestamp
// 9:binary
int VariousType::ConvertType(int type)
{
	switch(type)
	{
	case 1:
		return -15;
	case 2:
		return -16;
	case 3:
		return 1;
	case 4:
		return 7;
	case 5:
		return 8;
	case 6:
		return 91;
	case 7:
		return 92;
	case 8:
		return 93;
	case 9:
		return -2;
	default:
		return 1;
	}
}

/*
// get the result count of SQL affected (insert, delete & update)
int	COdbcFunc::GetAffectRowCount()
{
	SQLINTEGER RowCountPtr;
	retcode = SQLRowCount(hstmt, &RowCountPtr);
	if(retcode == SQL_SUCCESS)
		return (int)RowCountPtr;
	else
		return -1;
}

// get the result column count of SQL
int	COdbcFunc::GetColCount()
{
	SQLINTEGER ColCountPtr = 0;
	retcode = SQLRowCount(hstmt, &ColCountPtr);
	if(retcode == SQL_SUCCESS)
		return (int)ColCountPtr;
	else
		return -1;
}
*/