db2client.cpp

通用的数据库中间库

	switch(dbtype)
	{
	case SQL_CHAR:		// Character string of fixed length
	case SQL_VARCHAR:	// Variable-length character string
		eDataType = SA_dtString;
		break;
	case SQL_BINARY:
	case SQL_VARBINARY:
		eDataType = SA_dtBytes;
		break;
	case SQL_LONGVARCHAR:	// Variable-length character data
		eDataType = SA_dtLongChar;
		break;
	case SQL_LONGVARBINARY: // Variable-length binary data
		eDataType = SA_dtLongBinary;
		break;
	case SQL_DECIMAL:
	case SQL_NUMERIC:
		if(scale <= 0) {	// check for exact type
			if(prec <= 5)
				eDataType = SA_dtShort;
			else if(prec <= 10)
				eDataType = SA_dtLong;
			else
				eDataType = SA_dtDouble;
		}
		else
			eDataType = SA_dtDouble;
		break;
	case SQL_SMALLINT:
		eDataType = SA_dtShort;
		break;
	case SQL_INTEGER:
		eDataType = SA_dtLong;
		break;
	case SQL_REAL:
		eDataType = SA_dtDouble;
		break;
	case SQL_FLOAT:
		eDataType = SA_dtDouble;
		break;
	case SQL_DOUBLE:
		eDataType = SA_dtDouble;
		break;
	case SQL_BIT:	// Single bit binary data
		eDataType = SA_dtBool;
		break;
	case SQL_TINYINT:
		eDataType = SA_dtShort;
		break;
	case SQL_BIGINT:
		eDataType = SA_dtDouble;
		break;
	case SQL_TIME:
	case SQL_DATE:		// == SQL_DATETIME
	case SQL_TIMESTAMP:
	case SQL_TYPE_DATE:
	case SQL_TYPE_TIME:
	case SQL_TYPE_TIMESTAMP:
		eDataType = SA_dtDateTime;
		break;
	case SQL_BLOB:
		eDataType = SA_dtBLob;
		break;
	case SQL_CLOB:
	case SQL_DBCLOB:
		eDataType = SA_dtCLob;
		break;
	default:
		assert(false);
	}

	return eDataType;
}

int Idb2Cursor::CnvtStdToNativeValueType(SADataType_t eDataType) const
{
	SQLSMALLINT ValueType;

	switch(eDataType)
	{
	case SA_dtUnknown:
		throw SAException(SA_Library_Error, -1, -1, IDS_UNKNOWN_DATA_TYPE);
	case SA_dtBool:
		ValueType = SQL_C_BIT;
		break;
	case SA_dtShort:
		ValueType = SQL_C_SSHORT;
		break;
	case SA_dtLong:
		ValueType = SQL_C_SLONG;
		break;
	case SA_dtDouble:
		ValueType = SQL_C_DOUBLE;
		break;
	case SA_dtDateTime:
		ValueType = SQL_C_TYPE_TIMESTAMP;
		break;
	case SA_dtString:
		ValueType = SQL_C_CHAR;
		break;
	case SA_dtBytes:
		ValueType = SQL_C_BINARY;
		break;
	case SA_dtLongBinary:
	case SA_dtBLob:
		ValueType = SQL_C_BINARY;
		break;
	case SA_dtLongChar:
	case SA_dtCLob:
		ValueType = SQL_C_CHAR;
		break;
	default:
		ValueType = 0;
		assert(false);
	}
	
	return ValueType;
}

/*virtual */
int Idb2Cursor::CnvtStdToNative(SADataType_t eDataType) const
{
	SQLSMALLINT dbtype;

	switch(eDataType)
	{
	case SA_dtUnknown:
		throw SAException(SA_Library_Error, -1, -1, IDS_UNKNOWN_DATA_TYPE);
	case SA_dtBool:
		dbtype = SQL_BIT;
		break;
	case SA_dtShort:
		dbtype = SQL_SMALLINT;
		break;
	case SA_dtLong:
		dbtype = SQL_INTEGER;
		break;
	case SA_dtDouble:
		dbtype = SQL_DOUBLE;
		break;
	case SA_dtDateTime:
		dbtype = SQL_TYPE_TIMESTAMP;
		break;
	case SA_dtString:
		dbtype = SQL_CHAR;
		break;
	case SA_dtBytes:
		dbtype = SQL_BINARY;
		break;
	case SA_dtLongBinary:
		dbtype = SQL_LONGVARBINARY;
		break;
	case SA_dtLongChar:
		dbtype = SQL_LONGVARCHAR;
		break;
	case SA_dtBLob:
		dbtype = SQL_BLOB;
		break;
	case SA_dtCLob:
		dbtype = SQL_CLOB;
		break;
	default:
		dbtype = 0;
		assert(false);
	}
	
	return dbtype;
}

/*virtual */
bool Idb2Cursor::IsOpened()
{
	return m_handles.m_hstmt != 0;
}

/*virtual */
void Idb2Cursor::Open()
{
	assert(m_handles.m_hstmt == 0);
	Idb2Connection::Check(
		g_db2API.SQLAllocHandle(SQL_HANDLE_STMT, ((Idb2Connection*)m_pISAConnection)->m_handles.m_hdbc, &m_handles.m_hstmt),
		SQL_HANDLE_DBC, ((Idb2Connection*)m_pISAConnection)->m_handles.m_hdbc);
	assert(m_handles.m_hstmt != 0);
}

/*virtual */
void Idb2Cursor::Close()
{
	assert(m_handles.m_hstmt != 0);
	Idb2Connection::Check(g_db2API.SQLFreeHandle(SQL_HANDLE_STMT, m_handles.m_hstmt), SQL_HANDLE_STMT, m_handles.m_hstmt);
	m_handles.m_hstmt = (SQLHSTMT)NULL;
}

/*virtual */
ISACursor *Idb2Connection::NewCursor(SACommand *m_pCommand)
{
	return new Idb2Cursor(this, m_pCommand);
}

/*virtual */
void Idb2Cursor::Prepare(
	const SAString &sStmt,
	SACommandType_t eCmdType,
	int nPlaceHolderCount,
	saPlaceHolder**	ppPlaceHolders)
{
	SAString sStmtDB2;
	int nPos = 0;
	int i;
	switch(eCmdType)
	{
	case SA_CmdSQLStmt:
		// replace bind variables with '?' place holder
		for(i = 0; i < nPlaceHolderCount; i++)
		{
			sStmtDB2 += sStmt.Mid(nPos, ppPlaceHolders[i]->getStart()-nPos);
			sStmtDB2 += "?";
			nPos = ppPlaceHolders[i]->getEnd() + 1;
		}
		// copy tail
		if(nPos < sStmt.GetLength())
			sStmtDB2 += sStmt.Mid(nPos);
		break;
	case SA_CmdStoredProc:
		sStmtDB2 = CallSubProgramSQL();
		break;
	default:
		assert(false);
	}

	// a bit of clean up
	Idb2Connection::Check(g_db2API.SQLFreeStmt(m_handles.m_hstmt, SQL_CLOSE), SQL_HANDLE_STMT, m_handles.m_hstmt);
	Idb2Connection::Check(g_db2API.SQLFreeStmt(m_handles.m_hstmt, SQL_UNBIND), SQL_HANDLE_STMT, m_handles.m_hstmt);
	Idb2Connection::Check(g_db2API.SQLFreeStmt(m_handles.m_hstmt, SQL_RESET_PARAMS), SQL_HANDLE_STMT, m_handles.m_hstmt);

	Idb2Connection::Check(g_db2API.SQLPrepare(
		m_handles.m_hstmt, (SQLTCHAR*)(const char*)sStmtDB2, SQL_NTS), SQL_HANDLE_STMT, m_handles.m_hstmt);
}

SAString Idb2Cursor::CallSubProgramSQL()
{
	int nParams = m_pCommand->ParamCount();

	SAString sSQL = "{";

	// check for Return parameter
	int i;
	for(i = 0; i < nParams; ++i)
	{
		SAParam &Param = m_pCommand->ParamByIndex(i);
		if(Param.ParamDirType() == SA_ParamReturn)
		{
			sSQL += "?=";
			break;
		}
	}
	sSQL += "call ";
	sSQL += m_pCommand->CommandText();

	// specify parameters
	SAString sParams;
	for(i = 0; i < nParams; ++i)
	{
		SAParam &Param = m_pCommand->ParamByIndex(i);
		if(Param.ParamDirType() == SA_ParamReturn)
			continue;

		if(!sParams.IsEmpty())
			sParams += ", ";
		sParams += "?";
	}
	if(!sParams.IsEmpty())
	{
		sSQL += "(";
		sSQL += sParams;
		sSQL += ")";
	}

	sSQL += "}";
	return sSQL;
}

void Idb2Cursor::BindLongs()
{
	SQLRETURN retcode;
	SQLPOINTER ValuePtr;

	try
	{
		while((retcode = g_db2API.SQLParamData(m_handles.m_hstmt, &ValuePtr)) == SQL_NEED_DATA)
		{
			SAParam *pParam = (SAParam *)ValuePtr;
			unsigned int nActualWrite;
			SAPieceType_t ePieceType = SA_FirstPiece;
			void *pBuf;
			while((nActualWrite = pParam->InvokeWriter(
				ePieceType,
				Idb2Connection::MaxLongAtExecSize, pBuf)) != 0)
			{
				Idb2Connection::Check(g_db2API.SQLPutData(
					m_handles.m_hstmt, pBuf, nActualWrite), SQL_HANDLE_STMT, m_handles.m_hstmt);

				if(ePieceType == SA_LastPiece)
					break;
			}
		}
	}
	catch(SAException&)
	{
		g_db2API.SQLCancel(m_handles.m_hstmt);
		throw;
	}

	Idb2Connection::Check(retcode, SQL_HANDLE_STMT, m_handles.m_hstmt);
}

/*virtual */
void Idb2Cursor::UnExecute()
{
	m_bResultSetCanBe = false;
}

void Idb2Cursor::Bind(
		int nPlaceHolderCount,
		saPlaceHolder**	ppPlaceHolders)
{
	// we should bind for every place holder ('?')
	AllocBindBuffer(nPlaceHolderCount, ppPlaceHolders,
		sizeof(SQLINTEGER), 0);
	void *pBuf = m_pParamBuffer;

	for(int i = 0; i < nPlaceHolderCount; ++i)
	{
		SAParam &Param = *ppPlaceHolders[i]->getParam();

		void *pInd;
		void *pSize;
		unsigned int nDataBufSize;
		void *pValue;
		IncParamBuffer(pBuf, pInd, pSize, nDataBufSize, pValue);

		SADataType_t eDataType = Param.DataType();
		SQLSMALLINT ParameterType = (SQLSMALLINT)(eDataType == SA_dtUnknown?
			SQL_CHAR : // some type should be set
			CnvtStdToNative(eDataType));
		SQLSMALLINT ValueType = (SQLSMALLINT)(eDataType == SA_dtUnknown?
			SQL_C_CHAR : // some type should be set
			CnvtStdToNativeValueType(eDataType));

		SQLINTEGER *StrLen_or_IndPtr = (SQLINTEGER *)pInd;
		SQLPOINTER ParameterValuePtr = (SQLPOINTER)pValue;
		SQLINTEGER BufferLength = (SQLINTEGER)nDataBufSize;
		SQLUINTEGER ColumnSize = Param.ParamSize();
		if(!ColumnSize)	// not set explicitly
			ColumnSize = BufferLength;

		SQLSMALLINT InputOutputType;
		switch(Param.ParamDirType())
		{
		case SA_ParamInput:
			InputOutputType = SQL_PARAM_INPUT;
			break;
		case SA_ParamOutput:
			InputOutputType = SQL_PARAM_OUTPUT;
			break;
		case SA_ParamInputOutput:
			InputOutputType = SQL_PARAM_INPUT_OUTPUT;
			break;
		default:
			assert(Param.ParamDirType() == SA_ParamReturn);
			InputOutputType = SQL_PARAM_OUTPUT;
		}

		if(isInputParam(Param))
		{
			if(Param.isNull())
				*StrLen_or_IndPtr = SQL_NULL_DATA;			// field is null
			else
				*StrLen_or_IndPtr = InputBufferSize(Param);	// field is not null

			if(!Param.isNull())
			{
				switch(eDataType)
				{
				case SA_dtUnknown:
					throw SAException(SA_Library_Error, -1, -1, IDS_UNKNOWN_PARAMETER_TYPE, (const char*)Param.Name());
				case SA_dtBool:
					assert(*StrLen_or_IndPtr == (SQLINTEGER)sizeof(unsigned char));
					*(unsigned char*)ParameterValuePtr = (unsigned char)Param.asBool();
					break;
				case SA_dtShort:
					assert(*StrLen_or_IndPtr == (SQLINTEGER)sizeof(short));
					*(short*)ParameterValuePtr = Param.asShort();
					break;
				case SA_dtLong:
					assert(*StrLen_or_IndPtr == (SQLINTEGER)sizeof(long));
					*(long*)ParameterValuePtr = Param.asLong();
					break;
				case SA_dtDouble:
					assert(*StrLen_or_IndPtr == (SQLINTEGER)sizeof(double));
					*(double*)ParameterValuePtr = Param.asDouble();
					break;
				case SA_dtDateTime:
					assert(*StrLen_or_IndPtr == (SQLINTEGER)sizeof(TIMESTAMP_STRUCT));
					Idb2Connection::CnvtDateTimeToInternal(
						Param.asDateTime(),
						*(TIMESTAMP_STRUCT*)ParameterValuePtr);
					break;
				case SA_dtString:
					assert(*StrLen_or_IndPtr == Param.asString().GetLength());
					memcpy(ParameterValuePtr, (const char*)Param.asString(), *StrLen_or_IndPtr);
					break;
				case SA_dtBytes:
					assert(*StrLen_or_IndPtr == Param.asBytes().GetLength());
					memcpy(ParameterValuePtr, (const char*)Param.asBytes(), *StrLen_or_IndPtr);
					break;
				case SA_dtLongBinary:
				case SA_dtBLob:
				case SA_dtLongChar:
				case SA_dtCLob:
					assert(*StrLen_or_IndPtr == 0);
					break;
				default:
					ValueType = 0;
					ParameterType = 0;
					assert(false);
				}
			}
		}

		if(isLongOrLob(eDataType))
		{
			*StrLen_or_IndPtr = ((Idb2Connection*)m_pISAConnection)->LenDataAtExec();
			Idb2Connection::Check(g_db2API.SQLBindParameter(
				m_handles.m_hstmt,
				(SQLUSMALLINT)(i+1), InputOutputType,
				ValueType,
				ParameterType,
				0, // ColumnSize
				0, // DecimalDigits
				&Param,	// ParameterValuePtr - context
				0, // Buffer length
				StrLen_or_IndPtr), SQL_HANDLE_STMT, m_handles.m_hstmt);
		}
		else
		{
			Idb2Connection::Check(g_db2API.SQLBindParameter(
				m_handles.m_hstmt,
				(SQLUSMALLINT)(i+1), InputOutputType,
				ValueType,
				ParameterType,
				ColumnSize,
				0,
				ParameterValuePtr,
				BufferLength,
				StrLen_or_IndPtr), SQL_HANDLE_STMT, m_handles.m_hstmt);
		}
	}
}

/*virtual */
void Idb2Cursor::Execute(
	int nPlaceHolderCount,
	saPlaceHolder **ppPlaceHolders)
{
	if(nPlaceHolderCount)
		Bind(nPlaceHolderCount, ppPlaceHolders);

	SQLRETURN rc;
	Idb2Connection::Check(g_db2API.SQLFreeStmt(m_handles.m_hstmt, SQL_CLOSE), SQL_HANDLE_STMT, m_handles.m_hstmt);
	rc = g_db2API.SQLExecute(m_handles.m_hstmt);

	if(rc != SQL_NEED_DATA)
	{
		if(rc != SQL_NO_DATA)
			Idb2Connection::Check(rc, SQL_HANDLE_STMT, m_handles.m_hstmt);
	}
	else
		BindLongs();

	m_bResultSetCanBe = true;

	// if SP: in DB2 output parametrs are available
	// immediately after execute
	// even if there are result sets from SP
	ConvertOutputParams();	// if any
}

/*virtual */
void Idb2Cursor::Cancel()
{
	Idb2Connection::Check(g_db2API.SQLCancel(
		m_handles.m_hstmt), SQL_HANDLE_STMT, m_handles.m_hstmt);
}

/*virtual */
bool Idb2Cursor::ResultSetExists()
{
	if(!m_bResultSetCanBe)
		return false;

	SQLSMALLINT ColumnCount;
	Idb2Connection::Check(g_db2API.SQLNumResultCols(m_handles.m_hstmt, &ColumnCount), SQL_HANDLE_STMT, m_handles.m_hstmt);
	return ColumnCount > 0;
}

/*virtual */
void Idb2Cursor::DescribeFields(
	DescribeFields_cb_t fn)