sqlsetx.cpp

读取oracle的blob数据. 数据库连接以ODBC连接.

/////////////////////////////////////////////////////////////////////
// SQLSet.cpp: implementation of the CSQLSetX class.
// Date: 1999/06/20
// Update: 2000/01/18
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "SQLSetX.h"

#define MAX_REC_NUMBER 1000000000

#define SQL_SQL_NCHAR (-8)
#define SQL_SQL_NVARCHAR (-9)
#define SQL_SQL_NTEXT (-10)
#define SQL_ORA_LOB (0)

#define _INSERT_ONLY_ 1
#define _MODIFY_ONLY_ 2
#define _DELETE_ONLY_ 4
#define _DELETE_FOR_INSERT_ 16

extern BYTE rootdir[256];
void ctod(char *bit, int &y,int &m,int &d, int &h,int &f,int &s, int &w);
int trim0(char *);




AFX_STATIC_DATA const TCHAR _afxODBCTrail[] = _T("ODBC;");
AFX_STATIC_DATA const TCHAR _afxComma[] = _T(",");
AFX_STATIC_DATA const TCHAR _afxLiteralSeparator = '\'';
AFX_STATIC_DATA const TCHAR _afxCall[] = _T("{CALL ");
AFX_STATIC_DATA const TCHAR _afxParamCall[] = _T("{?");
AFX_STATIC_DATA const TCHAR _afxSelect[] = _T("SELECT ");
AFX_STATIC_DATA const TCHAR _afxDistinct[] = _T(" DISTINCT ");
AFX_STATIC_DATA const TCHAR _afxFrom[] = _T(" FROM ");
AFX_STATIC_DATA const TCHAR _afxWhere[] = _T(" WHERE ");
AFX_STATIC_DATA const TCHAR _afxOrderBy[] = _T(" ORDER BY ");
AFX_STATIC_DATA const TCHAR _afxForUpdate[] = _T(" FOR UPDATE ");

AFX_STATIC_DATA const TCHAR _afxRowFetch[] = _T("State:01S01");
AFX_STATIC_DATA const TCHAR _afxDataTruncated[] = _T("State:01004");
AFX_STATIC_DATA const TCHAR _afxInfoRange[] = _T("State:S1096");
AFX_STATIC_DATA const TCHAR _afxOutOfSequence[] = _T("State:S1010");
AFX_STATIC_DATA const TCHAR _afxDriverNotCapable[] = _T("State:S1C00");



LPCTSTR PASCAL FindSQLToken(LPCTSTR lpszSQL, LPCTSTR lpszSQLToken)
{
	BOOL bInLiteral;
	BOOL bInBrackets;
	int nLeftBrackets;
	int nRightBrackets;
	LPCTSTR lpch;
	LPCTSTR lpchSQLStart;
	LPCTSTR lpszFoundToken;
	int nTokenOffset = 0;
	CString strSQL = lpszSQL;

	strSQL.MakeUpper();
	lpszFoundToken = strSQL.GetBuffer(0);
	lpchSQLStart = lpszFoundToken;

	do
	{
		lpszFoundToken = _tcsstr(lpszFoundToken + nTokenOffset, lpszSQLToken);
		if (lpszFoundToken == NULL)
		{
			strSQL.ReleaseBuffer();
			return NULL;
		}

		bInLiteral = bInBrackets = FALSE;
		nLeftBrackets = nRightBrackets = 0;

		// Check if embedded in literal or brackets
		for (lpch = lpchSQLStart; lpch < lpszFoundToken; lpch = _tcsinc(lpch))
		{
			if (*lpch == _afxLiteralSeparator)
			{
				// Skip if escape literal
				if (*_tcsinc(lpch) == _afxLiteralSeparator)
					lpch = _tcsinc(lpch);
				else
					bInLiteral = !bInLiteral;
			}
			else if (!bInLiteral && (*lpch == '['))
			{
				// Skip if escape left bracket
				if (*_tcsinc(lpch) == '[')
					lpch = _tcsinc(lpch);
				else
				{
					nLeftBrackets++;
					if ((nLeftBrackets - nRightBrackets) > 0)
						bInBrackets = TRUE;
					else
						bInBrackets = FALSE;
				}
			}
			else if (!bInLiteral && (*lpch == ']'))
			{
				// Skip if escape right bracket
				if (*_tcsinc(lpch) == ']')
					lpch = _tcsinc(lpch);
				else
				{
					nRightBrackets++;
					if ((nLeftBrackets - nRightBrackets) > 0)
						bInBrackets = TRUE;
					else
						bInBrackets = FALSE;
				}
			}
		}

		// If first iteration, reset the offset to jump over found token
		if (nTokenOffset == 0)
			nTokenOffset = lstrlen(lpszSQLToken);

	} while (bInLiteral || bInBrackets);

	lpszFoundToken = lpszSQL + (lpszFoundToken - lpchSQLStart);
	strSQL.ReleaseBuffer();
	return lpszFoundToken;
}




#define _MIN_Y_ 1800

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif



//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CSQLSetX::CSQLSetX(CDatabaseX* pdb)
{
	m_nOpenType = snapshot;

	m_nDefaultType = snapshot;
	m_dwOptions = none;

	m_bAppendable = FALSE;
	m_bUpdatable = FALSE;
	m_bScrollable = FALSE;
	CloseBindLong = 0;
//	m_bRecordsetDb = FALSE;
//	m_bRebindParams = FALSE;
//	m_bLongBinaryColumns = FALSE;

	m_nLockMode = optimistic;

//	m_dwInitialGetDataLen = 0;

//	m_dwRowsetSize = 25;
//	m_dwAllocatedRowsetSize = 0;

//	m_nParams = 0;
//	m_nFieldsBound = 0;
//	m_lCurrentRecord = AFX_CURRENT_RECORD_UNDEFINED;
//	m_lRecordCount = 0;
	m_bUseUpdateSQL = FALSE;
//	m_bUseODBCCursorLib = FALSE;
//	m_nResultCols = -1;
	m_bCheckCacheForDirtyFields = TRUE;

//	m_strRequeryFilter = "";



	pDatabase	  = pdb;
	m_readHstmt	  = NULL;
	m_updateHstmt = NULL;

	StopGetData=FALSE;
	hasLongB = FALSE;

	opcode = 0;			//0=read, 1=insert, 2=update; 4=delete;
	isOpen = FALSE;
	isAdd  = FALSE;
	isEOF=TRUE;
	m_currentRecNo = 0;
	m_noReadLong = 0;
}

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


CSQLSetX::IsOpen()
{
	return isOpen;
}

CSQLSetX::IsEof()
{
	return isEOF;
}

void CSQLSetX::Close()
{

	if (!isOpen) return ;

	if (m_readHstmt)
	{
		SQLCloseCursor(m_readHstmt);
		SQLFreeStmt(m_readHstmt, SQL_UNBIND);
		SQLFreeStmt(m_readHstmt, SQL_DROP);	
		m_readHstmt = 0;
	}

	if (m_updateHstmt)
	{
		SQLCloseCursor(m_updateHstmt);
		SQLFreeStmt(m_updateHstmt,SQL_RESET_PARAMS);
		SQLFreeStmt(m_updateHstmt, SQL_DROP);	
		m_updateHstmt = 0;
	}
	isOpen = 0;
	isAdd  = 0;
	ResetInit();

}

BOOL CSQLSetX::tell_me_error(SQLHSTMT hstmt)
{
	SQLRETURN		retcode;
	SQLSMALLINT		HandleType	=SQL_HANDLE_STMT;
	SQLHANDLE		Handle		=hstmt;
	SQLSMALLINT		RecNumber	=1;
	unsigned char	Sqlstate[8];
	SQLINTEGER		NativeError;
	unsigned char 	MessageText[512];
	SQLSMALLINT		BufferLength=512;
	SQLSMALLINT		TextLength;

	retcode = SQLGetDiagRec(
		HandleType,
		Handle,
		RecNumber,
		Sqlstate,
		&NativeError,
		MessageText,
		BufferLength,
		&TextLength);


	if (retcode==SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
	{
		strcat((char*)MessageText,"\n");
		strcat((char*)MessageText, (char*)Sqlstate);

		retcode = AfxMessageBox((char*)MessageText,	MB_ICONQUESTION|MB_OKCANCEL|MB_DEFBUTTON1);
	}
	else
		retcode = AfxMessageBox("Error,but no msg",	MB_ICONQUESTION|MB_OKCANCEL|MB_DEFBUTTON1);

	if (retcode ==IDOK)	return 0;

	return 1;

}

void CSQLSetX::ResetInit()
{
	int  i, n;

	n = m_Value.GetSize();
 
	for (i=0; i<n; i++)
	{	

		//删除为未绑定字段而分配的缓冲区
		switch (m_Type[i])
		{
			case	SQL_BIT:
				delete (BOOL *)m_Value[i];
				break;

			case	SQL_TINYINT:
				delete (BYTE *)m_Value[i];
				break;

			case	SQL_SMALLINT:
				delete (short *)m_Value[i];
				break;

			case	SQL_INTEGER:
				delete (long *)m_Value[i];
				break;

			case	SQL_REAL:
				delete (float *)m_Value[i];
				break;

			case	SQL_FLOAT:
			case	SQL_DOUBLE:
				delete (double *)m_Value[i];
				break;

			case	SQL_DATE:
				delete (DATE_STRUCT *)m_Value[i];
				break;

			case	SQL_TIME:
				delete (TIME_STRUCT *)m_Value[i];
				break;

			case	SQL_TIMESTAMP:
				delete (TIMESTAMP_STRUCT *)m_Value[i];
				break;

			case	SQL_CHAR:
			case	SQL_DECIMAL:
			case	SQL_NUMERIC:
			case    SQL_SQL_NCHAR:
				delete (char *)m_Value[i];
				break;
			case	SQL_GUID:
			case	SQL_VARCHAR:
			case    SQL_SQL_NVARCHAR:
				delete (char *)m_Value[i];
				break;

			case	SQL_BINARY:
			case	SQL_VARBINARY:
			case	SQL_LONGVARCHAR:
			case	SQL_LONGVARBINARY:
			case    SQL_SQL_NTEXT:
			case	SQL_ORA_LOB:
				delete (CMyByteArray *)m_Value[i];
				break;

			default:
				break;
		}

	}


	m_Value.RemoveAll();	//指针数组

	m_Name.RemoveAll();		//串数组

	m_Width.RemoveAll();	//字数组	
	m_Type.RemoveAll();		//整数数组

	m_dataLen.RemoveAll();	//字数组	


	m_strName.RemoveAll();	//串数组
	m_strTable.RemoveAll();	//串数组
	m_strType.RemoveAll();	//串数组
	
}


BOOL CSQLSetX::Open(char *sql, int irr)
{
	m_currentRecNo = 0;
	if (isOpen)
	{
		Close();
	}
	m_readHstmt = 0;

	opcode = 0;
	strSQL = sql;

	//分析用户命令:
	LPCTSTR lpchTokenSelect;
	LPCTSTR lpchTokenFrom;
	LPCTSTR lpchTokenWhere;
	LPCTSTR lpchTokenOrderBy;
	LPCTSTR lpchTokenDistinct;

	if ((lpchTokenSelect = FindSQLToken(strSQL, _afxSelect)) ==NULL)
	{
		return FALSE;
	}

	if ((lpchTokenFrom = FindSQLToken(strSQL, _afxFrom))==NULL)
	{
		return FALSE;
	}
	if ((lpchTokenDistinct = FindSQLToken(strSQL, _afxDistinct))==NULL)
	{
	}
	if (lpchTokenDistinct && lpchTokenDistinct<lpchTokenFrom)
	{
		strFields = &lpchTokenDistinct[strlen(_afxDistinct)]; 
		strFields = strFields.Left(lpchTokenFrom-lpchTokenDistinct - strlen(_afxDistinct));
	}
	else
	{
		lpchTokenDistinct = 0; //DISTINCT语句若在FROM语句之后，则不属于当前SELECT.
		strFields = &lpchTokenSelect[strlen(_afxSelect)]; 
		strFields = strFields.Left(lpchTokenFrom-lpchTokenSelect - strlen(_afxSelect));
	}
	strFields.MakeUpper();

	strTable = &lpchTokenFrom[strlen(_afxFrom)];

	if ((lpchTokenWhere = FindSQLToken(strSQL, _afxWhere))!=NULL)
	{
		strTable = strTable.Left((int)lpchTokenWhere- (int)(lpchTokenFrom)+1 - strlen(_afxWhere));
	}
	else
	{
		if ((lpchTokenOrderBy = FindSQLToken(strSQL, _afxOrderBy))!=NULL)
		{
			strTable = strTable.Left((int)lpchTokenOrderBy- (int)(lpchTokenFrom)+1 - strlen(_afxOrderBy)+3);
		}
	}
	
	//构造查询语句:
	int  icol;

	CString fld;
	CString val;
	


	m_col = 0;
	DoFieldExchange(NULL);
#if 0
	selSql= _afxSelect;
	if (lpchTokenDistinct)
	{
		selSql += _afxDistinct;
	}

	for (icol=0; icol<m_col; icol++)
	{
		selSql += &m_fld_name[icol][1];
		selSql.SetAt(selSql.GetLength()-1,',');
	}

	if (m_col==0)
	{
		selSql += "*";
	}
	else
	{
		selSql.SetAt(selSql.GetLength()-1,' ');
	}

	selSql += lpchTokenFrom;
#endif
	selSql = sql;
	/////////////////////////////////////////////////////////////////////////

	//打开输入表：
	SQLRETURN		retcode;
	SQLSMALLINT		ColumnNumber;
	
	SQLCHAR			ColumnName[32];
	SQLUINTEGER		BufferLength;
	
	SQLSMALLINT		NameLengthPtr;
	SQLSMALLINT		DataType;
	SQLUINTEGER		ColumnSize ;
	SQLSMALLINT		DecimalDigitsPtr;
	SQLSMALLINT		NullablePtr;

	SQLUSMALLINT	FieldIdentifier=SQL_COLUMN_TYPE_NAME;
	SQLSMALLINT		StringLengthPtr;
	SQLSMALLINT		TargetType;
	SQLPOINTER		TargetValuePtr;

	SQLUINTEGER isAutoInc;

	SQLHSTMT		hstmt;

	char			columnTypeName[32];	
	char			columnTableName[32];	
	char			NumericAttributePtr[64];
	char			fldnm[64];

	char szIDQuoteChar[8];

	SetState(CSQLSetX::snapshot, NULL, CSQLSetX::none);

	//分配语句:
	retcode = SQLAllocStmt(pDatabase->m_hdbc, &hstmt);
	if (retcode != SQL_SUCCESS) return FALSE;

	//设置选择:
	OnSetOptions(hstmt);

	SWORD nResult;

	retcode = SQLGetInfo(pDatabase->m_hdbc, SQL_IDENTIFIER_QUOTE_CHAR,
		szIDQuoteChar, 2, &nResult);
	if ((retcode==SQL_SUCCESS||retcode==SQL_SUCCESS_WITH_INFO) && nResult == 1)
	{
	}
	else
	{
		szIDQuoteChar[0]=' ';
		szIDQuoteChar[1]=0;
	}

	//执行:
	int m=selSql.GetLength();
	UCHAR *pChar = new UCHAR[m+20];
	strcpy((char*)pChar, selSql);
	for (int i=0; i<m; i++)
	{
		if (pChar[i]<' ') pChar[i]=' ';
	}

	retcode = SQLExecDirect(hstmt, pChar, SQL_NTS);
	delete pChar;

	if (retcode==SQL_ERROR) tell_me_error(hstmt);
	if (retcode != SQL_SUCCESS)	goto err;


	retcode=SQLGetCursorName(hstmt,szCursor,30, &szCursorLen);
	if (retcode != SQL_SUCCESS )
	{ 
		tell_me_error(hstmt);
	}

	//结果集合的列数:
	retcode = SQLNumResultCols(hstmt, &m_nColumn);
	if (retcode != SQL_SUCCESS) goto err;

		
	fld = "";
	val = "";
	updateSql="UPDATE "+strTable+" SET ";

	m_col = 0;
	
	DoFieldExchange(NULL);
	m_col = 0;

	//绑定读变量(将数据库内的字段拷贝来):
	for (ColumnNumber=1; ColumnNumber<=m_nColumn; ColumnNumber++)
	{	
	
		BufferLength = 32;

		//返回列的一般信息：数据的SQL类型，列的宽度，小数位数，NULL允许否
		retcode = SQLDescribeCol(
				hstmt,
				ColumnNumber,
				ColumnName,
				(WORD)BufferLength,
				&NameLengthPtr,

				&DataType,
				&ColumnSize ,
				&DecimalDigitsPtr,
				&NullablePtr);

		_strupr((char*)ColumnName);
		if (ColumnSize==0) ColumnSize=255;
		
if (CloseBindLong)
{

	if (DataType== SQL_SQL_NTEXT ||
		DataType== SQL_BINARY ||
		DataType== SQL_VARBINARY ||
		DataType== SQL_LONGVARCHAR ||
		DataType== SQL_LONGVARBINARY ||
		DataType== SQL_ORA_LOB) continue;
}
m_col++;
		//返回列的特定信息：原来的类型：
		retcode = SQLColAttribute (
				hstmt,
				ColumnNumber,
				SQL_COLUMN_TYPE_NAME,
				columnTypeName,			//
				(WORD)BufferLength,
				&StringLengthPtr,
				NumericAttributePtr);

		//返回列的特定信息：该列所在的表：
		retcode = SQLColAttribute (
				hstmt,
				ColumnNumber,