db2client.cpp

通用的数据库中间库

{
	SQLSMALLINT ColumnCount;
	Idb2Connection::Check(g_db2API.SQLNumResultCols(m_handles.m_hstmt, &ColumnCount), SQL_HANDLE_STMT, m_handles.m_hstmt);

	for(SQLSMALLINT nField = 1; nField <= ColumnCount; ++nField)
	{
		SQLCHAR sColName[1024];
		SQLSMALLINT nColLen;
		SQLSMALLINT DataType;
		SQLUINTEGER ColumnSize;
		SQLSMALLINT Nullable;
		SQLSMALLINT DecimalDigits;

		Idb2Connection::Check(g_db2API.SQLDescribeCol(
			m_handles.m_hstmt,
			nField,
			sColName,
			sizeof(sColName),
			&nColLen,
			&DataType,
			&ColumnSize,
			&DecimalDigits,
			&Nullable), SQL_HANDLE_STMT, m_handles.m_hstmt);

		(m_pCommand->*fn)(
			SAString((const char*)sColName, nColLen),
			CnvtNativeToStd(DataType, 0, nColLen, ColumnSize, DecimalDigits),
			(int)DataType,
			ColumnSize,
			ColumnSize,
			DecimalDigits,
			Nullable == SQL_NO_NULLS);
	}
}

/*virtual */
long Idb2Cursor::GetRowsAffected()
{
	assert(m_handles.m_hstmt != 0);

	SQLINTEGER RowCount = -1;

	Idb2Connection::Check(g_db2API.SQLRowCount(m_handles.m_hstmt, &RowCount), SQL_HANDLE_STMT, m_handles.m_hstmt);
	return RowCount;
}

/*virtual */
unsigned int Idb2Cursor::OutputBufferSize(
	SADataType_t eDataType,
	unsigned int nDataSize) const
{
	switch(eDataType)
	{
	case SA_dtBool:
		return sizeof(unsigned char);	// SQL_C_BIT
	case SA_dtString:
		return nDataSize+1;	// always allocate space for NULL
	case SA_dtDateTime:
		return sizeof(TIMESTAMP_STRUCT);
	case SA_dtLongBinary:
	case SA_dtLongChar:
		return 0;
	case SA_dtBLob:
	case SA_dtCLob:
		return 0;
	default:
		break;
	}

	return ISACursor::OutputBufferSize(eDataType, nDataSize);
}

/*virtual */
void Idb2Cursor::SetFieldBuffer(
	int nCol,	// 1-based
	void *pInd,
	unsigned int nIndSize,
	void * /*pSize*/,
	unsigned int/* nSizeSize*/,
	void *pValue,
	unsigned int nBufSize)
{
	assert(nIndSize == sizeof(SQLINTEGER));
	if(nIndSize != sizeof(SQLINTEGER))
		return;

	SAField &Field = m_pCommand->Field(nCol);

	SQLSMALLINT TargetType;
	bool bLong = false;
	switch(Field.FieldType())
	{
	case SA_dtUnknown:
		throw SAException(SA_Library_Error, -1, -1, IDS_UNKNOWN_COLUMN_TYPE, (const char*)Field.Name());
	case SA_dtBool:
		TargetType = SQL_C_BIT;
		break;
	case SA_dtShort:
		TargetType = SQL_C_SSHORT;
		break;
	case SA_dtLong:
		TargetType = SQL_C_SLONG;
		break;
	case SA_dtDouble:
		TargetType = SQL_C_DOUBLE;
		break;
	case SA_dtDateTime:
		TargetType = SQL_C_TYPE_TIMESTAMP;
		break;
	case SA_dtBytes:
		TargetType = SQL_C_BINARY;
		assert(nBufSize == (unsigned int)Field.FieldSize());
		break;
	case SA_dtString:
		TargetType = SQL_C_CHAR;
		assert(nBufSize == (unsigned int)Field.FieldSize()+1);
		break;
	case SA_dtLongBinary:
		TargetType = SQL_C_BINARY;
		assert(nBufSize == 0);
		bLong = true;
		break;
	case SA_dtLongChar:
		TargetType = SQL_C_CHAR;
		assert(nBufSize == 0);
		bLong = true;
		break;
	case SA_dtBLob:
		TargetType = SQL_C_BINARY;
		assert(nBufSize == 0);
		bLong = true;
		break;
	case SA_dtCLob:
		TargetType = SQL_C_CHAR;
		assert(nBufSize == 0);
		bLong = true;
		break;
	default:
		TargetType = 0;
		assert(false);	// unknown type
	}

	if(!bLong)
		Idb2Connection::Check(
			g_db2API.SQLBindCol(m_handles.m_hstmt, (SQLUSMALLINT)nCol, TargetType,
			pValue, nBufSize, (SQLINTEGER*)pInd), SQL_HANDLE_STMT, m_handles.m_hstmt);
}

/*virtual */
void Idb2Cursor::SetSelectBuffers()
{
	// use default helpers
	AllocSelectBuffer(sizeof(SQLINTEGER), 0);
}

/*virtual */
bool Idb2Cursor::IndicatorIsNull(
	int nPos,	// 1-based
	SAValueRead &vr,
	ValueType_t eValueType,
	void *pInd, unsigned int nIndSize,
	void * /*pSize*/, unsigned int/* nSizeSize*/,
	unsigned int &nRealSize,
	int/* nBulkReadingBufPos*/) const
{
	assert(nIndSize == sizeof(SQLINTEGER));
	if(nIndSize != sizeof(SQLINTEGER))
		return true;

	if(*(SQLINTEGER*)pInd >= 0)
		nRealSize = *(SQLINTEGER*)pInd;

	bool bLong = false;
	SQLSMALLINT TargetType = 0;
	SQLINTEGER StrLen_or_IndPtr;
	bool bAddSpaceForNull = false;

	SADataType_t eDataType;
	switch(eValueType)
	{
	case ISA_FieldValue:
		eDataType = ((SAField&)vr).FieldType();
		break;
	default:
		assert(eValueType == ISA_ParamValue);
		eDataType = ((SAParam&)vr).ParamType();
	}
	switch(eDataType)
	{
	case SA_dtLongBinary:
	case SA_dtBLob:
		bLong = true;
		TargetType = SQL_C_BINARY;
		bAddSpaceForNull = false;
		break;
	case SA_dtLongChar:
	case SA_dtCLob:
		bLong = true;
		TargetType = SQL_C_CHAR;
		bAddSpaceForNull = true;
		break;
	default:
		break;
	}

	if(bLong)
	{
		// also try to get long size
		nRealSize = 0;
		char Buf[1];
		Idb2Connection::Check(g_db2API.SQLGetData(
			m_handles.m_hstmt, (SQLUSMALLINT)nPos,
			TargetType, Buf, bAddSpaceForNull? 1:0,
			&StrLen_or_IndPtr), SQL_HANDLE_STMT, m_handles.m_hstmt);
		if(StrLen_or_IndPtr >= 0)
			nRealSize = StrLen_or_IndPtr;

		return StrLen_or_IndPtr == SQL_NULL_DATA;
	}

	return *(SQLINTEGER*)pInd == SQL_NULL_DATA;
}

/*virtual */
bool Idb2Cursor::FetchNext()
{
	SQLRETURN rc = g_db2API.SQLFetchScroll(
		m_handles.m_hstmt, SQL_FETCH_NEXT, 0);
	if(rc != SQL_NO_DATA)
	{
		Idb2Connection::Check(rc, SQL_HANDLE_STMT, m_handles.m_hstmt);

		// use default helpers
		ConvertSelectBufferToFields(0);
	}
	else
	{
		SQLRETURN rcMoreResults = g_db2API.SQLMoreResults(
			m_handles.m_hstmt);
			
		if(rcMoreResults != SQL_NO_DATA)
			Idb2Connection::Check(rcMoreResults, SQL_HANDLE_STMT, m_handles.m_hstmt);
		else
			m_bResultSetCanBe = false;
	}

	return rc != SQL_NO_DATA;
}

/*virtual */
void Idb2Cursor::ReadLongOrLOB(
	ValueType_t eValueType,
	SAValueRead &vr,
	void * /*pValue*/,
	unsigned int/* nBufSize*/,
	saLongOrLobReader_t fnReader,
	unsigned int nReaderWantedPieceSize,
	void *pAddlData)
{
	if(eValueType == ISA_FieldValue)
	{
		SAField &Field = (SAField &)vr;

		SQLSMALLINT TargetType;
		SQLINTEGER StrLen_or_IndPtr;
		SQLRETURN rc;
		bool bAddSpaceForNull;

		switch(Field.FieldType())
		{
		case SA_dtLongBinary:
		case SA_dtBLob:
			TargetType = SQL_C_BINARY;
			bAddSpaceForNull = false;
			break;
		case SA_dtLongChar:
		case SA_dtCLob:
			TargetType = SQL_C_CHAR;
			bAddSpaceForNull = true;
			break;
		default:
			TargetType = 0;
			bAddSpaceForNull = false;
			assert(false);
		}

		// try to get long size
		unsigned int nLongSize = 0;
		char Buf[1];
		rc = g_db2API.SQLGetData(
			m_handles.m_hstmt, (SQLUSMALLINT)Field.Pos(),
			TargetType, Buf, bAddSpaceForNull? 1:0,
			&StrLen_or_IndPtr);
		if(rc != SQL_NO_DATA)
		{
			Idb2Connection::Check(rc, SQL_HANDLE_STMT, m_handles.m_hstmt);
			if(StrLen_or_IndPtr >= 0)
				nLongSize = StrLen_or_IndPtr;
		}
		
		unsigned char* pBuf;
		unsigned int nPortionSize = vr.PrepareReader(
			nLongSize,
			Idb2Connection::MaxLongAtExecSize,
			pBuf,
			fnReader,
			nReaderWantedPieceSize,
			pAddlData,
			bAddSpaceForNull);
		assert(nPortionSize <= Idb2Connection::MaxLongAtExecSize);

		SAPieceType_t ePieceType = SA_FirstPiece;
		unsigned int nTotalRead = 0;
		do
		{
			if(nLongSize)	// known
				nPortionSize = sa_min(nPortionSize, nLongSize - nTotalRead);

			rc = g_db2API.SQLGetData(
				m_handles.m_hstmt, (SQLUSMALLINT)Field.Pos(),
				TargetType, pBuf, nPortionSize + (bAddSpaceForNull? 1:0),
				&StrLen_or_IndPtr);
			assert(nPortionSize || rc == SQL_NO_DATA);

			if(rc != SQL_NO_DATA)
			{
				Idb2Connection::Check(rc, SQL_HANDLE_STMT, m_handles.m_hstmt);
				int NumBytes =
					((StrLen_or_IndPtr > (int)nPortionSize) || (StrLen_or_IndPtr == SQL_NO_TOTAL))? nPortionSize : StrLen_or_IndPtr;
				assert(NumBytes >= 0);
				nTotalRead += NumBytes;
				vr.InvokeReader(ePieceType, pBuf, (unsigned int)NumBytes);
			}
            else
            {
                ePieceType = SA_LastPiece;
                vr.InvokeReader(ePieceType, pBuf, 0);
            }

		    if(ePieceType == SA_FirstPiece)
			    ePieceType = SA_NextPiece;
		}
		while(rc != SQL_NO_DATA);
	}
	else
	{
		assert(eValueType == ISA_ParamValue);
		assert(false);
	}
}

/*virtual */
void Idb2Cursor::DescribeParamSP()
{
	SAString sText = m_pCommand->CommandText();
	SAString sSchemaName;
	SAString sProcName;

	int n = sText.Find('.');
	sSchemaName = sText.Left(n);
	sProcName = sText.Mid(n+1);

	SACommand cmd(m_pISAConnection->getSAConnection());
	cmd.Open();
	db2CommandHandles *pHandles = (db2CommandHandles *)cmd.NativeHandles();

	Idb2Connection::Check(g_db2API.SQLProcedureColumns(
		pHandles->m_hstmt,
		NULL,
		0,
		sSchemaName.IsEmpty()? (SQLCHAR *)SQL_ALL_SCHEMAS : (SQLCHAR *)(const char*)sSchemaName,
		SQL_NTS,
		(SQLCHAR *)(const char*)sProcName,
		SQL_NTS,
		(SQLCHAR *)"",
		SQL_NTS), SQL_HANDLE_STMT, pHandles->m_hstmt);

	while(cmd.FetchNext())
	{
//		SAString sCat = cmd.Field(1);		// "PROCEDURE_CAT"
//		SAString sSchem = cmd.Field(2);		// "PROCEDURE_SCHEM"
//		SAString sProc = cmd.Field(3);		// "PROCEDURE_NAME"
		SAString sCol = cmd.Field(4);		// "COLUMN_NAME"
		short nColType = cmd.Field(5);		// "COLUMN_TYPE"
		short nDataType = cmd.Field(6);		// "DATA_TYPE"
//		SAString sType = cmd.Field(7);		// "TYPE_NAME"
		long nColSize = cmd.Field(8).isNull()? 0 : (long)cmd.Field(8);		// "COLUMN_SIZE"
		long nBufferLength = cmd.Field(9).isNull()? 0 : (long)cmd.Field(9);	// "BUFFER_LENGTH"
		short nDecDigits = cmd.Field(10).isNull()? (short)0 : (short)cmd.Field(10);	// "DECIMAL_DIGITS"
//		short nNumPrecPadix = cmd.Field(11).isNull()? 0 : (short)cmd.Field(11);// "NUM_PREC_RADIX"
//		short nNullable = cmd.Field(12).isNull()? 0 : (short)cmd.Field(12);	// "NULLABLE"

		SAParamDirType_t eDirType;
		switch(nColType)
		{
		case SQL_PARAM_INPUT:
			eDirType = SA_ParamInput;
			break;
		case SQL_PARAM_INPUT_OUTPUT:
			eDirType = SA_ParamInputOutput;
			break;
		case SQL_PARAM_OUTPUT:
			eDirType = SA_ParamOutput;
			break;
		case SQL_RETURN_VALUE:
			eDirType = SA_ParamReturn;
			break;
		case SQL_PARAM_TYPE_UNKNOWN:
		case SQL_RESULT_COL:
			continue;
		default:
			assert(false);
			continue;
		}
		SADataType_t eParamType = CnvtNativeToStd(nDataType, 0, nBufferLength, nColSize, nDecDigits);

		SAString sParamName;
		if(sCol.IsEmpty())
		{
			assert(eDirType == SA_ParamOutput);
			eDirType = SA_ParamReturn;
			sParamName = "RETURN_VALUE";
		}
		else
			sParamName = sCol;

		m_pCommand->CreateParam(sParamName, eParamType, nDataType, nColSize, eDirType);
	}
}

/*virtual */
saAPI *Idb2Connection::NativeAPI() const
{
	return &g_db2API;
}

/*virtual */
saConnectionHandles *Idb2Connection::NativeHandles()
{
	return &m_handles;
}

/*virtual */
saCommandHandles *Idb2Cursor::NativeHandles()
{
	return &m_handles;
}

/*virtual */
void Idb2Connection::setIsolationLevel(
	SAIsolationLevel_t eIsolationLevel)
{
	Commit();

	issueIsolationLevel(eIsolationLevel);
}

void Idb2Connection::issueIsolationLevel(
	SAIsolationLevel_t eIsolationLevel)
{
	long isolation;
	switch(eIsolationLevel)
	{
	case SA_ReadUncommitted:
		isolation = SQL_TXN_READ_UNCOMMITTED;
		break;
	case SA_ReadCommitted:
		isolation = SQL_TXN_READ_COMMITTED;
		break;
	case SA_RepeatableRead:
		isolation = SQL_TXN_REPEATABLE_READ;
		break;
	case SA_Serializable:
		isolation = SQL_TXN_SERIALIZABLE;
		break;
	default:
		assert(false);
		return;
	}

	Check(g_db2API.SQLSetConnectAttr(
		m_handles.m_hdbc,
		SQL_ATTR_TXN_ISOLATION,
		SQLPOINTER(isolation), 0), SQL_HANDLE_DBC, m_handles.m_hdbc);
}

/*virtual */
void Idb2Connection::setAutoCommit(
	SAAutoCommit_t eAutoCommit)
{
	SQLUINTEGER nAutoCommit;
	switch(eAutoCommit)
	{
	case SA_AutoCommitOff:
		nAutoCommit = SQL_AUTOCOMMIT_OFF;
		break;
	case SA_AutoCommitOn:
		nAutoCommit = SQL_AUTOCOMMIT_ON;
		break;
	default:
		assert(false);
		return;
	}

	Check(g_db2API.SQLSetConnectAttr(
		m_handles.m_hdbc,
		SQL_ATTR_AUTOCOMMIT,
		SQLPOINTER(nAutoCommit), 0), SQL_HANDLE_DBC, m_handles.m_hdbc);
}