sqlsetx.cpp

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

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_CHAR, SQL_CHAR,
					BufferLength-1,0,
					TargetValuePtr,0,
					&inLen[ColumnNumber]);

				break;

			case	SQL_GUID:
			case    SQL_SQL_NVARCHAR:
			case	SQL_VARCHAR:
				TargetType		= SQL_C_CHAR;
				inLen[ColumnNumber] = SQL_NTS;

				TargetValuePtr = (char *)m_Value[ColumnNumber-1];

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_CHAR,  SQL_VARCHAR,
					min(8000,BufferLength-1), 0, 
					TargetValuePtr,0,
					&inLen[ColumnNumber]);
				
				break;

			case    SQL_SQL_NTEXT:
			case	SQL_BINARY:
			case	SQL_VARBINARY:
			case	SQL_LONGVARCHAR:
			case	SQL_LONGVARBINARY:
			case	SQL_ORA_LOB:
				TargetType		= SQL_NO_TOTAL;
				inLen[ColumnNumber] = SQL_LEN_DATA_AT_EXEC(0);

				TargetValuePtr = (CMyByteArray *)m_Value[ColumnNumber-1];
				BufferLength	= 0;
				
				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_BINARY, DataType,
					m_Width[ColumnNumber-1], 0, (void*)ColumnNumber,0,
					&inLen[ColumnNumber]);

				break;

			default:
				TargetType		= SQL_NO_TOTAL;
				BufferLength	= 0;
				break;
		}

		oStrLen_or_IndPtr[ColumnNumber-1] = inLen[ColumnNumber];

		if (rc<0)
		{
			tell_me_error(hstmt);
			goto err;
		}

	}

	m_updateHstmt = hstmt;
	return TRUE;
err:
	SQLFreeStmt(hstmt,SQL_RESET_PARAMS);
	SQLFreeStmt(hstmt,SQL_DROP);	
	return FALSE;

}

int CSQLSetX::Update()
{
	SQLRETURN		retcode;
	SQLRETURN		rc;
	PTR			pToken;
	PTR			vToken;


	int i;
	int errcode;

	if (m_updateHstmt == 0)
	{
		return FALSE;
	}

	//将用户输入数据拷贝到绑定的变量内:
	for (i=0; i<m_col; i++)
	{
		//若能知道用户的变量赋值情况，应修改inLen[]
		inLen[i+1] = oStrLen_or_IndPtr[i];
		if (nullField[i]) inLen[i+1] = SQL_NULL_DATA;
char *vvvv = (char *)(const char *)this->m_strName[i];
		switch (m_Type[i])
		{
		case	SQL_BIT:
			To_Bool(i);				
			break;

		case	SQL_TINYINT:
			To_Byte(i);
			break;

		case	SQL_SMALLINT:
			To_Short(i);
			break;

		case	SQL_INTEGER:
			To_Long(i);
			break;

		case	SQL_REAL:
			To_Float(i);
			break;

		case	SQL_FLOAT:
		case	SQL_DOUBLE:
			To_Double(i);
			break;

		case	SQL_DATE:
			To_Date(i);
			break;

		case	SQL_TIME:
			To_Time(i);
			break;

		case	SQL_TIMESTAMP:
			To_TimeStamp(i);
			break;

		case	SQL_GUID:
		case	SQL_CHAR:
		case	SQL_VARCHAR:
		case    SQL_SQL_NCHAR:
		case    SQL_SQL_NVARCHAR:
			To_Char(i);
			break;
		case	SQL_DECIMAL:
		case	SQL_NUMERIC:
			to_CharDec(i);
			break;
			

		case    SQL_SQL_NTEXT:
		case	SQL_BINARY:
		case	SQL_VARBINARY:
		case	SQL_LONGVARCHAR:
		case	SQL_LONGVARBINARY:				
		case	SQL_ORA_LOB:
			To_ByteArray(i);
			break;

		default:
			break;

		}
	}


	//处理未定义输入的输出字段:
	int ic=SetDummyColumns(i, m_nColumn );

	for (i=ic; i<m_nColumn; i++)
	{
		inLen[i+1] = oStrLen_or_IndPtr[i];

		switch (m_Type[i])
		{
		case	SQL_BIT:
			dummy_to_Bool(i);				
			break;

		case	SQL_TINYINT:
			dummy_to_Byte(i);
			break;

		case	SQL_SMALLINT:
			dummy_to_Short(i);
			break;

		case	SQL_INTEGER:
			dummy_to_Long(i);
			break;

		case	SQL_REAL:
			dummy_to_Float(i);
			break;

		case	SQL_FLOAT:
		case	SQL_DOUBLE:
			dummy_to_Double(i);
			break;

		case	SQL_DATE:
			dummy_to_Date(i);
			break;

		case	SQL_TIME:			
			dummy_to_Time(i);
			break;

		case	SQL_TIMESTAMP:			
			dummy_to_TimeStamp(i);
			break;

		case	SQL_GUID:
		case	SQL_CHAR:
		case	SQL_DECIMAL:
		case	SQL_NUMERIC:
		case	SQL_VARCHAR:
		case    SQL_SQL_NCHAR:
		case    SQL_SQL_NVARCHAR:
			dummy_to_Char(i);
			break;
			dummy_to_String(i);
			break;


		case    SQL_SQL_NTEXT:
		case	SQL_BINARY:
		case	SQL_VARBINARY:
		case	SQL_LONGVARCHAR:
		case	SQL_LONGVARBINARY:				
		case	SQL_ORA_LOB:
			dummy_to_ByteArray(i);
			break;

		default:
			break;

		}
	}

	//////////////////////////////////////////////////////////////
	//字段准备完毕，准备执行:


	retcode = SQLExecute(m_updateHstmt);

	if (retcode != SQL_SUCCESS && 
		retcode != SQL_SUCCESS_WITH_INFO && 
		retcode != SQL_NEED_DATA)
	{
		tell_me_error(m_updateHstmt);
		return FALSE;
	}
	if (	retcode == SQL_SUCCESS_WITH_INFO ) 
		tell_me_error(m_updateHstmt);
	errcode = 0;

	while(retcode==SQL_NEED_DATA)
	{
		retcode = SQLParamData(m_updateHstmt, &pToken);
		
		if (retcode == SQL_SUCCESS || retcode==SQL_SUCCESS_WITH_INFO) break;
		if (retcode !=SQL_NEED_DATA)
		{
			errcode = tell_me_error(m_updateHstmt);
			break;
		}

		i = (SDWORD)pToken - 1;

		//
		if (i<0 || i>=m_nColumn)
		{
			for (int j=0; j<m_nColumn; j++)
			{
				vToken = (PTR)m_Value[j];
				if (pToken==vToken)
				{
					i=j;
					break;
				}
			}

		}

		//
		if (i>=0 && i<m_nColumn)
		{
			switch (m_Type[i])
			{
				case	SQL_LONGVARCHAR:		//-1
				case	SQL_BINARY:				//-2
				case	SQL_VARBINARY:			//-3
				case	SQL_LONGVARBINARY:		//-4
				case    SQL_SQL_NTEXT:
				case	SQL_ORA_LOB:
					setLongBinary(i,NULL);
				break;

				default:
					rc = SQLPutData(m_updateHstmt, NULL, SQL_NULL_DATA);
					break;
			}
		}
	}

	//修改、追加:
	EndOfUpdate();

	return !errcode;
}

////////////////////////////////////////////////////////////////////////////


void CSQLSetX::SetState(int nOpenType, LPCTSTR lpszSQL, DWORD dwOptions)
{
	if (nOpenType == AFX_DB_USE_DEFAULT_TYPE)
		m_nOpenType = m_nDefaultType;
	else
		m_nOpenType = nOpenType;

	m_bAppendable = (dwOptions & appendOnly) != 0 ||
		(dwOptions & readOnly) == 0;
	m_bUpdatable = (dwOptions & readOnly) == 0 &&
		(dwOptions & appendOnly) == 0;

	// Can turn off dirty field checking via dwOptions
	if (dwOptions & noDirtyFieldCheck || dwOptions & useMultiRowFetch)
		m_bCheckCacheForDirtyFields = FALSE;

	// Set recordset readOnly if forwardOnly
	if (m_nOpenType == forwardOnly && !(dwOptions & readOnly))
	{
		dwOptions |= readOnly;

		// If using multiRowFetch also set useExtendFetch
		if (dwOptions & useMultiRowFetch)
			dwOptions |= useExtendedFetch;
	}

	// Archive info for use in Requery
	m_dwOptions     = dwOptions;
}

void CSQLSetX::OnSetOptions(HSTMT hstmt)
{
	ASSERT(hstmt != SQL_NULL_HSTMT);

	// Inherit options settings from CDatabase
	pDatabase->OnSetOptions(hstmt);


/***
	SQLRETURN		retcode;
	retcode = SQLSetStmtAttr(hstmt,	SQL_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, 2);
	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
	{ 
		tell_me_error(hstmt);
	}

}
***/

	// If fowardOnly recordset and not using SQLExtendedFetch, quit now
	if (m_nOpenType == forwardOnly && !(m_dwOptions & useExtendedFetch))
		return;

	// Turn on bookmark support if necessary
	//EnableBookmarks();

	// If using forwardOnly and extended fetch, quit now
	if (m_nOpenType == forwardOnly)
		return;

	// Make sure driver supports extended fetch, ODBC 2.0 and requested cursor type
	VerifyDriverBehavior();
	DWORD dwScrollType = VerifyCursorSupport();

	// Set the update method, concurrency and cursor type
	SetUpdateMethod();
	SetConcurrencyAndCursorType(hstmt, dwScrollType);
}


// Determine whether to use SQLSetPos or positioned update SQL
void CSQLSetX::SetUpdateMethod()
{
	// Determine update method
	if (pDatabase->m_dwUpdateOptions & AFX_SQL_SETPOSUPDATES)
		m_bUseUpdateSQL = FALSE;
	else if (pDatabase->m_dwUpdateOptions & AFX_SQL_POSITIONEDSQL)
		m_bUseUpdateSQL = TRUE;
	else
		m_bUpdatable = FALSE;
 }

// Determine which type of concurrency to set, set it and cursor type
void CSQLSetX::SetConcurrencyAndCursorType(HSTMT hstmt, DWORD dwScrollOptions)
{
	RETCODE nRetCode;
	SWORD nResult;

	m_dwConcurrency = SQL_CONCUR_READ_ONLY;

	if ((m_bUpdatable || m_bAppendable) && pDatabase->m_bUpdatable)
	{
		nRetCode=SQLGetInfo(pDatabase->m_hdbc, SQL_SCROLL_CONCURRENCY,
			&m_dwDriverConcurrency, sizeof(m_dwDriverConcurrency), &nResult);
		if (!Check(nRetCode))
		{
			TRACE0("Error: ODBC failure checking recordset updatability.\n");
			return;
		}

		if (m_nLockMode == pessimistic)
		{
			if (m_dwDriverConcurrency & SQL_SCCO_LOCK)
				m_dwConcurrency = SQL_CONCUR_LOCK;
		}
		else
		{
			// Use cheapest, most concurrent model
			if (m_dwDriverConcurrency & SQL_SCCO_OPT_ROWVER)
				m_dwConcurrency = SQL_CONCUR_ROWVER;
			else if (m_dwDriverConcurrency & SQL_SCCO_OPT_VALUES)
				m_dwConcurrency = SQL_CONCUR_VALUES;
			else if (m_dwDriverConcurrency & SQL_SCCO_LOCK)
				m_dwConcurrency = SQL_CONCUR_LOCK;
		}
	}

	// Set cursor type (Let rowset size default to 1).
	nRetCode=SQLSetStmtOption(hstmt, SQL_CURSOR_TYPE, dwScrollOptions);
	if (!Check(nRetCode))
	{
		TRACE0("Error: ODBC failure setting recordset cursor type.\n");
		return;
	}

	// Set the concurrency model (NOTE: may have to reset concurrency later).
	nRetCode=SQLSetStmtOption(hstmt, SQL_CONCURRENCY, m_dwConcurrency);
	if (!Check(nRetCode))
	{
		TRACE0("Error: ODBC failure setting recordset concurrency.\n");
		return;
	}
}
void CSQLSetX::VerifyDriverBehavior()
{
	RETCODE nRetCode;
	UWORD wScrollable;
	// If SQLExtendedFetch not supported, use SQLFetch
	nRetCode = SQLGetFunctions(pDatabase->m_hdbc,
		SQL_API_SQLEXTENDEDFETCH, &wScrollable);
	if (!Check(nRetCode))
	{
		TRACE0("Error: ODBC failure determining whether recordset is scrollable.\n");
		return;
	}
	m_bScrollable = wScrollable;
	if (!m_bScrollable)
	{
		m_bUpdatable = FALSE;
		return;
	}

	char szResult[30];
	SWORD nResult;
	// require ODBC v2.0 or later
	nRetCode = SQLGetInfo(pDatabase->m_hdbc, SQL_ODBC_VER,
		&szResult, 30, &nResult);
	if (!Check(nRetCode))
	{
		TRACE0("Error: ODBC failure checking for driver capabilities.\n");
		return;
	}
	if (szResult[0] == '0' && szResult[1] < '2')
		return;

}

DWORD CSQLSetX::VerifyCursorSupport()
{
	RETCODE nRetCode;
	SWORD nResult;
	UDWORD dwDriverScrollOptions;

	nRetCode = SQLGetInfo(pDatabase->m_hdbc, SQL_SCROLL_OPTIONS,
		&dwDriverScrollOptions, sizeof(dwDriverScrollOptions), &nResult);
	if (!Check(nRetCode))
	{
		TRACE0("Error: ODBC failure checking for driver capabilities.\n");
		return SQL_CURSOR_FORWARD_ONLY;
	}

	SDWORD dwScrollOptions = SQL_CURSOR_FORWARD_ONLY;
	if (m_nOpenType == dynaset)
	{
		// Dynaset support requires ODBC's keyset driven cursor model
		if (!(dwDriverScrollOptions & SQL_SO_KEYSET_DRIVEN)) 
			return SQL_CURSOR_FORWARD_ONLY;

		dwScrollOptions = SQL_CURSOR_KEYSET_DRIVEN;
	}
	else if (m_nOpenType == snapshot)
	{
		// Snapshot support requires ODBC's static cursor model
		if (!(dwDriverScrollOptions & SQL_SO_STATIC))
			return SQL_CURSOR_FORWARD_ONLY;
		dwScrollOptions = SQL_CURSOR_STATIC;
	}
	else
	{
		// Dynamic cursor support requires ODBC's dynamic cursor model
		if (!(dwDriverScrollOptions & SQL_SO_DYNAMIC))
			return SQL_CURSOR_FORWARD_ONLY;

		dwScrollOptions = SQL_CURSOR_DYNAMIC;
	}

	return dwScrollOptions;
}
BOOL CSQLSetX::Check(RETCODE nRetCode) const
{
	switch (nRetCode)
	{
	case SQL_SUCCESS_WITH_INFO:
		// Fall through

	case SQL_SUCCESS:
	case SQL_NO_DATA_FOUND:
	case SQL_NEED_DATA:
		return TRUE;
	}

	return FALSE;
}

CString CSQLSetX::GetDefaultSQL()
{
		return _T("");

}
void CSQLSetX::DoFieldExchange(CFieldExchange* pFX)
{
}

BOOL CSQLSetX::SetFX_Test(LPCTSTR pszName)
{ 
	return true;
	if (strFields.Find("*")>=0) return true;
	int jp=0;
	int ip;
	char szName[90];
	strcpy(szName, pszName+1);
	int nc=strlen(szName);
	if (szName[nc-1]==']') 
	{
		szName[nc-1]=0;
		nc--;
	}	
	_strupr((char*)szName);

	while( (ip=strFields.Find(szName,jp))>=0) 
	{
		if (ip==0)
		{
			if (strFields[ip+nc]==' ' || strFields[ip+nc]==',' || strFields[ip+nc]==0)
				return true;
		}
		else
		{
			if (strFields[ip-1]==' ' || strFields[ip-1]==',')
			{ 
				if (strFields[ip+nc]==' ' || strFields[ip+nc]==',' || strFields[ip+nc]==0)
					return true;
			}
		}
		jp = ip+nc;

	}
	return false;

}

void CSQLSetX::SetFX_Bool(LPCTSTR szName, BOOL& value)
{
	if (!SetFX_Test(szName)) return;

	strcpy(m_fld_name[m_col], szName);
	m_fld_ptr[m_col] = (void *) &value; 
	m_fld_c_type[m_col] = SQL_BIT;
	m_col++;
}


void CSQLSetX::SetFX_Byte(LPCTSTR szName, BYTE& value)
{
	if (!SetFX_Test(szName)) return;

	strcpy(m_fld_name[m_col], szName);