sqlsetx.cpp

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

				SQL_DESC_TABLE_NAME,
				columnTableName,			//
				(WORD)BufferLength,
				&StringLengthPtr,
				NumericAttributePtr);



		// Add the column's information
		_strupr((char*)ColumnName);
		
		fldnm[0]='['; strcpy(&fldnm[1], (char*)ColumnName); strcat(fldnm,"]");

		m_Name.Add( fldnm );
		m_Width.Add( ColumnSize );
		m_Type.Add( DataType);
		
		Nullable[ColumnNumber-1]=NullablePtr;

		//给用户提供信息:
		m_strName.Add( ColumnName );
		m_strType.Add( columnTypeName );//原始类型
		m_strTable.Add( columnTableName);


	NumericAttributePtr[0] = 0;
		BufferLength = 4;

		//返回列的特定信息：该列是否自动增量：
		retcode = SQLColAttribute (
				hstmt,
				ColumnNumber,
				SQL_DESC_AUTO_UNIQUE_VALUE,
				&isAutoInc,			//
				(WORD)BufferLength,
				&StringLengthPtr,
				NumericAttributePtr);
		isAutoInc = NumericAttributePtr[0];
		m_isAutoInc[ColumnNumber-1] = isAutoInc;
		//构造插入/修改语句:
if (isAutoInc==0)
{
		fld += szIDQuoteChar;
		fld += ColumnName;
		fld += szIDQuoteChar;
		fld += ',';
		
		val += "?,";

		updateSql += szIDQuoteChar; 
		updateSql += ColumnName; 
		updateSql += szIDQuoteChar; 
		updateSql += "=?,";
}
		//给待绑定的数据项分配数据缓冲区:		
		switch (DataType)
		{
			case	SQL_BIT:
				TargetType		= SQL_C_BIT;
				BufferLength	= sizeof(BOOL);
				TargetValuePtr  = new BOOL;
				break;

			case	SQL_TINYINT:
				TargetType		= SQL_C_STINYINT;
				BufferLength	= sizeof(BYTE);
				TargetValuePtr  = new BYTE;
				break;

			case	SQL_SMALLINT:
				TargetType		= SQL_C_SSHORT;
				BufferLength	= sizeof(short);
				TargetValuePtr  = new short;
				break;

			case	SQL_INTEGER:
				TargetType		= SQL_C_SLONG;
				BufferLength	= sizeof(long);
				TargetValuePtr	= new long;
				break;

			case	SQL_REAL:
				TargetType		= SQL_C_FLOAT;
				BufferLength	= sizeof(float);
				TargetValuePtr	= new float;
				break;

			case	SQL_FLOAT:
			case	SQL_DOUBLE:
				TargetType		= SQL_C_DOUBLE;
				BufferLength	= sizeof(double);
				TargetValuePtr	= new double;
				break;

			case	SQL_DATE:
				TargetType		= SQL_C_DATE;
				BufferLength	= sizeof(DATE_STRUCT);
				TargetValuePtr	= new DATE_STRUCT;
				break;

			case	SQL_TIME:
				TargetType		= SQL_C_TIME;
				BufferLength	= sizeof(TIME_STRUCT);
				TargetValuePtr	= new TIME_STRUCT;
				break;

			case	SQL_TIMESTAMP:
				TargetType		= SQL_C_TIMESTAMP;
				BufferLength	= sizeof(TIMESTAMP_STRUCT);
				TargetValuePtr	= new TIMESTAMP_STRUCT;
				break;

			case	SQL_GUID:
			case    SQL_SQL_NCHAR:
			case    SQL_SQL_NVARCHAR:
			case	SQL_CHAR:
			case	SQL_VARCHAR:
				TargetType		= SQL_C_CHAR;
				BufferLength	= ColumnSize+1;
				TargetValuePtr	= new char[BufferLength+1];
				break;
			case	SQL_DECIMAL:
			case	SQL_NUMERIC:
				TargetType		= SQL_C_CHAR;
				BufferLength	= ColumnSize+1;
				TargetValuePtr	= new char[BufferLength+1];
				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;
				BufferLength	= 0;
				TargetValuePtr	= new CMyByteArray;
				((CMyByteArray *)TargetValuePtr)->SetBufferSize(8192);
				hasLongB = TRUE;
				break;

			default:
				TargetType		= SQL_NO_TOTAL;
				BufferLength	= 0;
				TargetValuePtr  = new BOOL;
				break;
		}

		m_Value.Add(TargetValuePtr);
		m_dataLen.Add(BufferLength );

		//长字段不能绑定:
		if (TargetType != SQL_NO_TOTAL)
		{
			retcode = SQLBindCol(
				hstmt,
				ColumnNumber,
				TargetType,
				TargetValuePtr,
				BufferLength,
				&StrLen_or_IndPtr[ColumnNumber-1]);
		}

	}


	//构造Update 或 Insert语句:
	fld.SetAt(fld.GetLength()-1,' ');
	val.SetAt(val.GetLength()-1,' ');
	updateSql.SetAt(updateSql.GetLength()-1,' ');

	updateSql += " WHERE CURRENT OF ";
	updateSql += szCursor;

	insSql = "INSERT into "+strTable+"("+fld+") values("+val+")";
	deleteSql = "DELETE FROM "+strTable+"  WHERE CURRENT OF " + szCursor;

	StopGetData = FALSE;
FILE *fp1;
fp1=fopen("C:\\1.txt","w");
fprintf(fp1,"%s\n%s\n", insSql, updateSql);
fclose(fp1);

	m_readHstmt = hstmt;
	isOpen = TRUE;
	ReadNext(NULL,irr);

	m_nColumn = m_col;
	return TRUE;

err:
	SQLFreeStmt(hstmt,SQL_DROP);	
	return FALSE;

}

int CSQLSetX::ReadNext(SQLSMALLINT org,int irr)
{

	BOOL errcode;
	int i;
	
	m_irr = irr;

	SQLRETURN		retcode;

	if (StopGetData)
	{	isEOF=TRUE;
		return FALSE;
	}

	if (!isOpen)
	{
		isEOF=TRUE;
		return FALSE;
	}

	if (!m_readHstmt)
	{
		CloseUpdateStmt();
		return reOpen();
	}
	
	memset(	nullField, 0, sizeof(BOOL)*256);
	
	if (org==NULL)
		retcode = SQLFetchScroll(m_readHstmt, SQL_FETCH_NEXT,1);
	else
		retcode = SQLFetchScroll(m_readHstmt, org,1);

	if (retcode == SQL_NO_DATA_FOUND)
	{
		for (i=0; i<m_col; i++)
		{
			nullField[i]= TRUE;
		}

		if (org == SQL_FETCH_PRIOR) 
		{
			m_currentRecNo = -1;
		}
		if (org == SQL_FETCH_NEXT) 
		{
			m_currentRecNo = MAX_REC_NUMBER + 1;
		}


		isEOF=TRUE;
		return FALSE;
	}

	
	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
	{
		tell_me_error(m_readHstmt); 
		isEOF=TRUE;
		return FALSE;
	}

	errcode = 0;

	//处理输入数据:
	for (i=0; i<m_col; i++)
	{
		//将输入缓冲区拷贝到用户缓冲区:
		dataptr = (void *)m_Value[i];

		switch (m_fld_c_type[i])
		{
		case	SQL_BIT:
			to_user_Bool(i);				
			break;

		case	SQL_TINYINT:
			to_user_Byte(i);
			break;

		case	SQL_SMALLINT:
			to_user_Short(i);
			break;

		case	SQL_INTEGER:
			to_user_Long(i);
			break;

		case	SQL_REAL:
			to_user_Float(i);
			break;

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

		case	SQL_DATE:
			to_user_Date(i);
			break;

		case	SQL_TIME:
			to_user_Time(i);
			break;

		case	SQL_TIMESTAMP:
			to_user_TimeStamp(i);
			break;

		case    SQL_SQL_NCHAR:
		case	SQL_CHAR:
			to_user_Char(i);
			break;
		case	SQL_DECIMAL:
		case	SQL_NUMERIC:
			to_user_Char(i);
			break;

		case	SQL_GUID:
		case	SQL_VARCHAR:
		case    SQL_SQL_NVARCHAR:
			to_user_Char(i);
			break;
			to_user_String(i);


		case	SQL_BINARY:
		case	SQL_VARBINARY:
		case	SQL_LONGVARCHAR:
		case	SQL_LONGVARBINARY:				
		case    SQL_SQL_NTEXT:
		case	SQL_ORA_LOB:
			if (!m_noReadLong)
				to_user_ByteArray(i);
			break;

		default:
			break;

		}
	}

	//取空未定义的字段:
	for (; i<m_nColumn; i++)
	{
		int m=m_Type.GetSize();
		switch (m_Type[i])
		{
			case	SQL_BIT:
			case	SQL_TINYINT:
			case	SQL_SMALLINT:
			case	SQL_INTEGER:
			case	SQL_REAL:
			case	SQL_DECIMAL:
			case	SQL_NUMERIC:
			case	SQL_FLOAT:
			case	SQL_DOUBLE:
			case	SQL_DATE:
			case	SQL_TIME:
			case	SQL_TIMESTAMP:
			case	SQL_GUID:
			case	SQL_CHAR:
			case	SQL_VARCHAR:
			case    SQL_SQL_NCHAR:
			case    SQL_SQL_NVARCHAR:

				break;

			case	SQL_LONGVARCHAR:		//-1
			case	SQL_BINARY:				//-2
			case	SQL_VARBINARY:			//-3
			case	SQL_LONGVARBINARY:		//-4
			case    SQL_SQL_NTEXT:
			case	SQL_ORA_LOB:
				errcode = getLongBinary(i);
			break;

			default:
				break;
		}
	}
	isEOF= FALSE;
	return !errcode;
}

int CSQLSetX::AddNew()
{
	opcode = _INSERT_ONLY_;
	
	if (m_updateHstmt)	return TRUE;

	return OutPutBind(insSql);
}
int CSQLSetX::Modify()
{
	if (hasLongB && 0)
	{
		opcode = _DELETE_FOR_INSERT_;
		if (m_updateHstmt)	return TRUE;
		OutPutBind(deleteSql);

		return AddNew();	
	}
	else
	{
		opcode = _MODIFY_ONLY_;
		if (m_updateHstmt)	return TRUE;
		return OutPutBind(updateSql);
	}

}
int CSQLSetX::Delete()
{
	opcode = _DELETE_ONLY_;

	if (m_updateHstmt)	return TRUE;
	return OutPutBind(deleteSql);
}

int CSQLSetX::OutPutBind(CString &runSql)
{
	SQLSMALLINT		ColumnNumber;
	SQLSMALLINT		DataType;
	SQLSMALLINT		TargetType;

	SQLUINTEGER		BufferLength;
	SQLPOINTER		TargetValuePtr;



	SQLRETURN		retcode;
	SQLRETURN		rc;
	SQLHSTMT		hstmt;


	if (m_updateHstmt)	return TRUE;

	//插入时，若不关掉老的结果集，则新插入的记录不在其内:
	if (opcode == _INSERT_ONLY_ )	
	{
		CloseReadStmt();
	}
	
	
	retcode = SQLAllocStmt(pDatabase->m_hdbc, &hstmt);
	if (retcode != SQL_SUCCESS)
	{
		return FALSE;
	}

	retcode = SQLPrepare(hstmt, (UCHAR*)(const char*)runSql, SQL_NTS);

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

	if (opcode == _DELETE_ONLY_ || opcode == _DELETE_FOR_INSERT_)	//删除
	{
		m_updateHstmt = hstmt;
		retcode = SQLExecute(m_updateHstmt);

		if (retcode==SQL_SUCCESS_WITH_INFO) tell_me_error(hstmt);
		
		if (opcode == _DELETE_ONLY_ )
		{
			CloseUpdateStmt(); //若不重新读，则记录集内仍有被删除的记录
		}
		else	//有长记录字段的修改:
		{
			CloseUpdateStmt();

		}

		return !retcode;
	}

	//组合输入/输出集合：
	for (ColumnNumber=1; ColumnNumber<=m_nColumn; ColumnNumber++)
	{	
		inLen[ColumnNumber] = 0;
		
		DataType	= m_Type[ColumnNumber-1];
		BufferLength= m_dataLen[ColumnNumber-1];

		switch (DataType)
		{
			case	SQL_BIT:
				TargetType		= SQL_C_BIT;
				TargetValuePtr = (BOOL *)m_Value[ColumnNumber-1];

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					TargetType,  
					DataType,
					0, 0, 
					TargetValuePtr, 0, 
					&inLen[ColumnNumber]);
				break;

			case	SQL_TINYINT:
				TargetType		= SQL_C_STINYINT;
				TargetValuePtr = (BYTE *)m_Value[ColumnNumber-1];

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_STINYINT, SQL_TINYINT,
					0, 0, TargetValuePtr,0,
					&inLen[ColumnNumber]);
				break;

			case	SQL_SMALLINT:
				TargetType		= SQL_C_SSHORT;
				TargetValuePtr = (short *)m_Value[ColumnNumber-1];

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_SSHORT, SQL_SMALLINT,
					0,0, TargetValuePtr,0,
					&inLen[ColumnNumber]);
				break;

			case	SQL_INTEGER:
				TargetType		= SQL_C_SLONG;
				TargetValuePtr = (long *)m_Value[ColumnNumber-1];

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_SLONG, SQL_INTEGER,
					0,0, TargetValuePtr,0,
					&inLen[ColumnNumber]);
				
				break;

			case	SQL_REAL:
				TargetType		= SQL_C_FLOAT;
				TargetValuePtr = (float *)m_Value[ColumnNumber-1];
				

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_FLOAT, SQL_REAL,
					0,0,TargetValuePtr,0, 
					&inLen[ColumnNumber]);

				break;

			case	SQL_FLOAT:
			case	SQL_DOUBLE:
				TargetType		= SQL_C_DOUBLE;
				TargetValuePtr = (double *)m_Value[ColumnNumber-1];

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_DOUBLE, DataType,
					0,0,TargetValuePtr,0, 
					&inLen[ColumnNumber]);

				break;

			case	SQL_DATE:
				TargetType		= SQL_C_DATE;
				TargetValuePtr = (DATE_STRUCT *)m_Value[ColumnNumber-1];
					

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_DATE, SQL_DATE,
					0,0, TargetValuePtr,0,
					&inLen[ColumnNumber]);
				break;

			case	SQL_TIME:
				TargetType		= SQL_C_TIME;
				TargetValuePtr = (TIME_STRUCT *)m_Value[ColumnNumber-1];

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_TIME, SQL_TIME,
					0,0, TargetValuePtr,0, 
					&inLen[ColumnNumber]);

				break;

			case	SQL_TIMESTAMP:
				TargetType		= SQL_C_TIMESTAMP;
				TargetValuePtr = (TIMESTAMP_STRUCT *)m_Value[ColumnNumber-1];

				rc = SQLBindParameter(
					hstmt,ColumnNumber,SQL_PARAM_INPUT,
					SQL_C_TIMESTAMP, SQL_TIMESTAMP,
					0,0,TargetValuePtr,0,
					&inLen[ColumnNumber]);

				break;

			case    SQL_SQL_NCHAR:
			case	SQL_CHAR:
			case	SQL_DECIMAL:
			case	SQL_NUMERIC:
				TargetType		= SQL_C_CHAR;
				inLen[ColumnNumber] = SQL_NTS;

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