ftdb.cpp

方便的数据库连接调试程序

#include "stdafx.h"
#include "sql.h"
#include "sqlext.h"
#include "FTDB.h"

#include "outputCon.h"

CFTDB::CFTDB()
{
	m_henv=NULL;
	m_hdbc=NULL;
	m_szUser[0]='\0';
}

CFTDB::~CFTDB()
{
	Close();
}

BOOL CFTDB::Open(void)
{
	Close();
	if(SQL_SUCCESS ==SQLAllocEnv(&m_henv))
	{
		if( SQL_SUCCESS==SQLAllocConnect(m_henv,&m_hdbc))
		{
			SWORD cbLen;
			char szBuf[255];
			RETCODE rt=SQLDriverConnect(m_hdbc,
										::GetFocus(),
										NULL,
										0,
										(UCHAR*)szBuf,
										255,
										&cbLen,
										SQL_DRIVER_COMPLETE);
			if(SQL_SUCC(rt))
				return TRUE;
		}
	}
	Close();
	return FALSE;
}

BOOL CFTDB::Open(LPSTR pszDSN,LPSTR pszUser,LPSTR pszPwd)
{
	Close();
	strcpy(m_szUser,pszUser);
	if(SQL_SUCCESS ==SQLAllocEnv(&m_henv))
	{
		if( SQL_SUCCESS==SQLAllocConnect(m_henv,&m_hdbc))
		{
			RETCODE rt=SQLConnect(m_hdbc,
									(UCHAR*)pszDSN,
									strlen(pszDSN),
									(UCHAR*)pszUser,
									strlen(pszUser),
									(UCHAR*)pszPwd,
									strlen(pszPwd));
			if(SQL_SUCC(rt))
				return TRUE;
		}
	}
	Close();
	return FALSE;
}
void CFTDB::Close(void)
{
	if(m_hdbc)
		SQLFreeConnect(m_hdbc);
	if(m_henv)
		SQLFreeEnv(m_henv);
	m_henv=NULL;
	m_hdbc=NULL;
	m_arrTableName.RemoveAll();
	m_arrColName.RemoveAll();
	m_arrColTypeName.RemoveAll();
	m_arrColDataPrecision.RemoveAll();
	m_arrColDataLength.RemoveAll();
	m_arrColDataScale.RemoveAll();
	m_arrColDataType.RemoveAll();
	m_arrColNullable.RemoveAll();
}

BOOL CFTDB::ListAllTable(LPSTR pszType)
{
	if(m_hdbc==NULL)
		return FALSE;
	HSTMT hstmt;
	RETCODE rt=SQLAllocStmt(m_hdbc,&hstmt);
	SQLSetStmtOption(hstmt,SQL_CONCURRENCY,SQL_CONCUR_READ_ONLY);
	SQLSetStmtOption(hstmt,SQL_CURSOR_TYPE,SQL_CURSOR_FORWARD_ONLY);
	if(!SQL_SUCC(rt))
		return FALSE;
//#define _USE_ORA_TABLE 
#ifdef _USE_ORA_TABLE

	char szSQL[]="select tname from tab";
	rt=SQLExecDirect(hstmt,(UCHAR*)szSQL,strlen(szSQL));
	if(SQL_SUCC(rt))
	{
		m_arrTableName.RemoveAll();
		rt = SQLFetch(hstmt);
		while (TRUE) 
		{
			char szName[300];
			if (SQL_SUCC(rt))
			{
				SDWORD cbName;
				SQLGetData(hstmt, 1, SQL_C_CHAR, szName, 300, &cbName);
			} 
			else
				break;
			m_arrTableName.Add(szName);
			rt = SQLFetch(hstmt);
		}
	}
#else
	rt=SQLTables(hstmt,
				NULL,
				SQL_NTS,
				NULL,
				SQL_NTS,
				NULL,
				SQL_NTS,
				(UCHAR*)pszType,
				SQL_NTS);
	int iTail=0;
	if(SQL_SUCC(rt))
	{
		m_arrTableName.RemoveAll();
		rt = SQLFetch(hstmt);
		while (TRUE) 
		{
			char szName[300];
			if (SQL_SUCC(rt))
			{
				SDWORD cbName;
				SQLGetData(hstmt, 3, SQL_C_CHAR, szName, 300, &cbName);
			} 
			else
				break;
			m_arrTableName.Add(szName);
			rt = SQLFetch(hstmt);
		}
	}
	else
		//AfxMessageBox("table enum error ");
		DisplayError(hstmt);

#endif
	SQLFreeStmt(hstmt,SQL_CLOSE);

#ifdef _DEBUG
	for(int _i=0;_i<m_arrTableName.GetUpperBound()+1;_i++)
		TRACE("%d %s\n",_i,m_arrTableName.GetAt(_i));
#endif
	return TRUE;
}

BOOL CFTDB::DescTable(int iSel)
{
	if(iSel>=m_arrTableName.GetUpperBound()+1)
		return FALSE;
	return DescTable(m_arrTableName.GetAt(iSel));
}

BOOL CFTDB::DescTable(LPCSTR pszName)
{
	if(m_hdbc==NULL)
		return FALSE;
	HSTMT hstmt;
	RETCODE rt=SQLAllocStmt(m_hdbc,&hstmt);
	SQLSetStmtOption(hstmt,SQL_CONCURRENCY,SQL_CONCUR_READ_ONLY);
	SQLSetStmtOption(hstmt,SQL_CURSOR_TYPE,SQL_CURSOR_FORWARD_ONLY);
	if(!SQL_SUCC(rt))
		return FALSE;

	//char szSQL[100];
	//sprintf(szSQL,"desc %s",pszName);
	//rt=SQLExecDirect(hstmt,(UCHAR*)szSQL,strlen(szSQL));
	char szTName[300];
	sprintf(szTName,pszName);
	TRACE("desc table %s\n",szTName);
	rt=SQLColumns(hstmt,
					NULL,//qualifier
					0,
					NULL,//owner
					0,
					(UCHAR*)szTName,//table name
					SQL_NTS,
					NULL,
					0);


	int iTail=0;
	if(SQL_SUCC(rt))
	{
		m_arrColName.RemoveAll();
		m_arrColTypeName.RemoveAll();
		m_arrColDataLength.RemoveAll();
		m_arrColDataPrecision.RemoveAll();
		m_arrColDataScale.RemoveAll();
		m_arrColDataType.RemoveAll();
		m_arrColNullable.RemoveAll();

		char szColName[128],szTypeName[128];
		SDWORD cbDataType,cbDataPrecision,cbDataScale,cbDataLength,cbNullable;
		SDWORD cbT;

		SQLBindCol(hstmt, 4, SQL_C_CHAR, szColName, 128, &cbT);
		SQLBindCol(hstmt, 5, SQL_C_SLONG, &cbDataType, 0, &cbT);
		SQLBindCol(hstmt, 6, SQL_C_CHAR, szTypeName, 128, &cbT);
		SQLBindCol(hstmt, 7, SQL_C_SLONG, &cbDataPrecision, 0, &cbT);
		SQLBindCol(hstmt, 8, SQL_C_SLONG, &cbDataLength, 0, &cbT);
		SQLBindCol(hstmt, 9, SQL_C_SSHORT, &cbDataScale, 0, &cbT); 
		SQLBindCol(hstmt, 11,  SQL_C_SSHORT, &cbNullable, 0, &cbT);

		rt = SQLFetch(hstmt);
		while (TRUE) 
		{
			if (SQL_SUCC(rt))
			{
				m_arrColName.Add(szColName);
				m_arrColTypeName.Add(szTypeName);
				m_arrColDataType.Add((DWORD)cbDataType);
				m_arrColDataPrecision.Add((DWORD)cbDataPrecision);
				m_arrColDataLength.Add((DWORD)cbDataLength);
				m_arrColDataScale.Add((DWORD)cbDataScale);
				m_arrColNullable.Add((DWORD)cbNullable);
				rt=SQLFetch(hstmt);
			} 
			else
				break;
		}
	}
	SQLFreeStmt(hstmt,SQL_CLOSE);

	return TRUE;

}

void CFTDB::DisplayError(HSTMT hstmt)
{
	char szBuf[10],szBuf1[500];
	SDWORD cbNError;
	SWORD cbRet;
	SQLError(m_henv,
				m_hdbc,
				hstmt,
				(UCHAR*)szBuf,
				&cbNError,
				(UCHAR*)szBuf1,
				500,
				&cbRet);
	//CString szOut;
	//szOut.Format("%d
	AfxMessageBox(szBuf1);
}
void CFTDB::DisplayError(HENV henv,HDBC hdbc,HSTMT hstmt)
{
	char szBuf[10],szBuf1[500];
	SDWORD cbNError;
	SWORD cbRet;
	SQLError(henv,
				hdbc,
				hstmt,
				(UCHAR*)szBuf,
				&cbNError,
				(UCHAR*)szBuf1,
				500,
				&cbRet);
	//CString szOut;
	//szOut.Format("%d
	AfxMessageBox(szBuf1);
}

BOOL CFTDB::GenerateDescString(CStringArray &arrRes)
{
	arrRes.RemoveAll();
	int iTotal=m_arrColName.GetUpperBound()+1;
	CString szT;
	for(int i=0;i<iTotal;i++)
	{
		DWORD dwType=m_arrColDataType.GetAt(i);
		switch(dwType)
		{
		case(SQL_DECIMAL):
		case(SQL_NUMERIC):
			if(m_arrColDataScale.GetAt(i)+m_arrColDataPrecision.GetAt(i) 
				> m_arrColDataLength.GetAt(i)+1)
				szT.Format("%s %s (%d)",
						m_arrColName.GetAt(i),
						m_arrColTypeName.GetAt(i),
						m_arrColDataPrecision.GetAt(i));
			else
				szT.Format("%s %s (%d.%d)",
						m_arrColName.GetAt(i),
						m_arrColTypeName.GetAt(i),
						m_arrColDataPrecision.GetAt(i),
						m_arrColDataScale.GetAt(i));
			break;
		case(SQL_CHAR):
		case(SQL_VARCHAR):
			szT.Format("%s %s (%d)",
					m_arrColName.GetAt(i),
					m_arrColTypeName.GetAt(i),
					m_arrColDataLength.GetAt(i));
			break;
		default:
			szT.Format("%s %s",m_arrColName.GetAt(i),m_arrColTypeName.GetAt(i));
			break;
		}
		//szT.Format("%s %s",m_arrColName.GetAt(i),m_arrColTypeName.GetAt(i));
		arrRes.Add(szT);
	}
	return iTotal;
}