📄 myodbc.cpp

📁 用ODBC写的数据库接口,支持SQlserver,mysql
💻 CPP
📖 第 1 页 / 共 2 页
字号:
上一页 12
        SQLFetch(m_hstmt);
        }
    if(m_pSet->m_rows>0)
        return ConvertDataToChar();
    else
	    return TRUE;//FetchData();
}

BOOL CMyODBC::AddSql(char *cpSql)
{
	if(this->m_hstmt != NULL)
	{
		SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
		m_hstmt = NULL;
	}
	if(this->m_hdbc == NULL)
	{
		strcpy(m_Err,"没有连接数据库，请先进行联接!");
		return FALSE;
	}

	m_retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"分配语句句柄失败,不能执行");
		return FALSE;
	}
    m_retcode = SQLPrepare(m_hstmt,cpSql,SQL_NTS);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"准备语句失败");
		return FALSE;
	}
    return TRUE;
}

BOOL CMyODBC::SetParam(int idx,char *value,int size,int type,SQLINTEGER *cbStatus)
{
    if(type==1)
    {
        *cbStatus = size;//SQL_BINARY,//SQL_LONGVARBINARY
        m_retcode = SQLBindParameter(m_hstmt,idx,SQL_PARAM_INPUT,
            SQL_C_BINARY,SQL_LONGVARBINARY,
           size,0,(SQLPOINTER)value,0,cbStatus);
        //*cbStatus = size;
    }
    else if(type==2)
    {
        *cbStatus = 0;
        m_retcode = SQLBindParameter(m_hstmt,idx,SQL_PARAM_INPUT,
            SQL_C_ULONG,   SQL_INTEGER,
            0,   0,   (SQLPOINTER)value,   0,  cbStatus);
    }
    else
    {
        *cbStatus = SQL_NTS;
        m_retcode = SQLBindParameter(m_hstmt,idx,SQL_PARAM_INPUT,
            SQL_C_CHAR,SQL_CHAR,
            size,0,(SQLPOINTER)value,0,cbStatus);
    }
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hstmt, SQL_HANDLE_STMT, "执行sql语句失败,不能执行");
		return FALSE;
	}
   return TRUE;
}

BOOL CMyODBC::Run()
{
	m_retcode = SQLExecute(m_hstmt);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO) && (m_retcode != SQL_NO_DATA))
	{
		ReportError(m_hstmt, SQL_HANDLE_STMT, "执行sql语句失败,不能执行");
		return FALSE;
	}
	return TRUE;
}

BOOL CMyODBC::FetchFirst()
{
    m_retcode = SQLFetchScroll(m_hstmt,SQL_FETCH_FIRST,0);
    if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
    {
        ReportError(m_hstmt,SQL_HANDLE_STMT, m_Err);
        return FALSE;
    }
    if(m_pSet->m_rows>0)
        ConvertDataToChar();
    return TRUE;
}

BOOL CMyODBC::FetchData()
{
//	SQLFetchScroll
//    if(m_FetchType==0)
        m_retcode = SQLFetch(m_hstmt);
//    else
//        m_retcode = SQLFetchScroll(m_hstmt,SQL_FETCH_NEXT,0);
//        m_retcode = SQLFetch(m_hstmt);
//while(( == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO
//    if((m_retcode = SQLFetch(m_hstmt)) != SQL_NO_DATA)
    if(m_retcode == SQL_SUCCESS || m_retcode == SQL_SUCCESS_WITH_INFO)
	{
		return ConvertDataToChar();
//		return TRUE;

	}
	else
	{
		if(m_hstmt != NULL)
		{
//			ReportError(m_hstmt,SQL_HANDLE_STMT, m_Err);
//			SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
//			m_hstmt = NULL;
		}
		return FALSE;
	}

}

//iColumnIndex用来表示列的序号，从1开始计数
SQLSMALLINT   CMyODBC::GetDefaultCType(long iODBCType)
{
//	CString strWarn;
	SQLSMALLINT      iResult;
	switch(iODBCType)
	{
	case SQL_VARCHAR:
	case SQL_CHAR:
	case SQL_DECIMAL:
	case SQL_NUMERIC:
	case SQL_DOUBLE:
	case SQL_SMALLINT:
	case SQL_INTEGER:
	case SQL_FLOAT:
	case SQL_REAL:
    case SQL_LONGVARCHAR:
		iResult = SQL_C_CHAR;
		break;
	case SQL_DATETIME:
		iResult = SQL_C_TYPE_TIMESTAMP;
		break;
    case SQL_BINARY:
    case SQL_VARBINARY:
    case SQL_LONGVARBINARY:
//#define SQL_C_BINARY     SQL_BINARY
//#define SQL_C_BIT        SQL_BIT
		iResult = SQL_C_BINARY;
		break;
	default:
		iResult = SQL_C_CHAR;
//		sprintf(m_Err,"不支持这种转换--%d", iODBCType);
		//AfxMessageBox(strWarn);
//		iResult = -1;
		break;
	}
	return iResult;

}

/*long CMyODBC::GetColLength(SQLHSTMT &hstmt,int iDataType, int iCol)
{
	long lTemp = 0, lResult = -1;
//    return 100;
	switch(iDataType)
	{

	case SQL_CHAR:
	case SQL_VARCHAR:
		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_OCTET_LENGTH, NULL, 0, NULL, (SQLPOINTER)&lResult);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			strcpy(m_Err,"取列长度时失败");
			return FALSE;
		}
		break;
	case SQL_NUMERIC:
	case SQL_DECIMAL:
	case SQL_DOUBLE:
	case SQL_SMALLINT:
	case SQL_INTEGER:
	case SQL_FLOAT:
	case SQL_REAL:    
		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_PRECISION, NULL, 0, NULL, (SQLPOINTER)&lResult);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			strcpy(m_Err,"取列整数部分的长度时失败");
			return FALSE;
		}

		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_SCALE, NULL, 0, NULL, (SQLPOINTER)&lTemp);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			strcpy(m_Err,"取列小数长度时失败");
			return FALSE;
		}
		lResult += lTemp;
		break;

	case SQL_DATETIME:
		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_PRECISION, NULL, 0, NULL, (SQLPOINTER)&lResult);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			strcpy(m_Err,"取列整数部分的长度时失败");
			return FALSE;
		}

		m_retcode = SQLColAttribute(hstmt, (SQLUSMALLINT)iCol, SQL_DESC_SCALE, NULL, 0, NULL, (SQLPOINTER)&lTemp);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			strcpy(m_Err,"取列小数长度时失败");
			return FALSE;
		}
		lResult += lTemp + 1;
		break;

		default:
			strcpy(m_Err,"不支持这种类型");
			break;
	}

	return lResult ;

}*/

void CMyODBC::ReportError(SQLHSTMT &hstmt, int iHandleType ,char *strAlert)
{
	
/*	unsigned char *SQLState = new unsigned char[6];
	if(SQLState == NULL)
	{
		strcpy(m_Err,"报告发生错误的原因时，分配sqlstat内存失败");
		return;
	}*/
	char Message[500] = "\0";
	short iMesLen;
	//CString strError;
	SQLGetDiagRec(iHandleType, hstmt, 1, m_SQLState, NULL, (unsigned char  *)Message, 500, &iMesLen);
	sprintf(m_Err,"%s,%s" , strAlert, Message);
	//AfxMessageBox(strError);
	//delete SQLState; SQLState= NULL;
}

BOOL CMyODBC::ConvertDataToChar()
{
	int i;
	TIMESTAMP_STRUCT timeStamp;

	for(i = 0; i < m_pSet->GetCols(); i++)
	{
		/*if(m_pSet->m_coldata[i].value && (m_pSet->m_coldata[i].valuelen) == -1)//处理的值为空
		{
//			memset(m_pSet->m_coldata[i].value, 0, m_pSet->m_coldatafmt[i].maxlength);
//			(m_pSet->m_coldata[i].valuelen) = m_pSet->m_coldatafmt[i].maxlength - 1;
            if(m_pSet->m_coldata[i].value)
                delete m_pSet->m_coldata[i].value;
            m_pSet->m_coldata[i].value = NULL;
			continue;
		}*/

		switch (m_pSet->m_coldatafmt[i].c_datatype)
		{
		case SQL_C_CHAR:
			break;
        case SQL_C_BINARY:
            if(m_pSet->m_coldata[i].value)
                delete m_pSet->m_coldata[i].value;
            m_pSet->m_coldata[i].value = NULL;
            SQLRETURN ret;
            ret=SQLGetData(m_hstmt,i+1,SQL_C_BINARY,(void *)1,0,&m_pSet->m_coldata[i].valuelen);
            if(ret== SQL_SUCCESS_WITH_INFO||ret== SQL_SUCCESS)
            {
                  //   Get   all   the   data   at   once.
                if(m_pSet->m_coldata[i].valuelen>0)
                {
                    m_pSet->m_coldata[i].valuelen = 4000;
                    m_pSet->m_coldata[i].value   =   new   BYTE[m_pSet->m_coldata[i].valuelen+1];
                    if(SQLGetData(m_hstmt,i+1,SQL_C_BINARY/*SQL_C_DEFAULT*/,m_pSet->m_coldata[i].value,
                        m_pSet->m_coldata[i].valuelen,&m_pSet->m_coldata[i].valuelen)   !=   SQL_SUCCESS)
                    {
                        //   Handle   error   and   continue.
                        ReportError(m_hstmt, SQL_HANDLE_STMT, "提取BLOB数据失败");
                        return FALSE;
                    }
                    m_pSet->m_coldata[i].value[m_pSet->m_coldata[i].valuelen] = 0;
                }
            }
            else
            {
//            m_retcode = SQLBindCol(m_hstmt, ((SQLUSMALLINT)i)+1,
//                (SQLSMALLINT)m_pSet->m_coldatafmt[i].c_datatype ,
//                m_pSet->m_coldata[i].value, m_pSet->m_coldatafmt[i].maxlength,
//                (long *)&(m_pSet->m_coldata[i].valuelen));
                ReportError(m_hstmt, SQL_HANDLE_STMT, "提取BLOB数据失败");
                  //   Handle   error   on   attempt   to   get   data   length.
                return FALSE;
            }
            break;
		case SQL_C_TYPE_TIMESTAMP:
			timeStamp.year = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->year;
			timeStamp.month = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->month;
			timeStamp.day = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->day;
			timeStamp.hour = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->hour;
			timeStamp.minute = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->minute;
			timeStamp.second = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->second;
			timeStamp.fraction = ((TIMESTAMP_STRUCT *)m_pSet->m_coldata[i].value)->fraction;
			sprintf(m_pSet->m_coldata[i].value, "%4u-%2u-%2u %2u:%2u:%2u", timeStamp.year, timeStamp.month, timeStamp.day, timeStamp.hour, timeStamp.minute, timeStamp.second);
			(m_pSet->m_coldata[i].valuelen) = 19;
			break;
		default:
			strcpy(m_Err,"还没有支持这种数据类型的转换");
			return FALSE;
		}
	}
	return TRUE;
}

BOOL CMyODBC::BeginTrans()
{
	if(m_hdbc == NULL)
	{
		strcpy(m_Err,"连接句柄为空，不能执行");
		return FALSE;
	}
	//设置提交方式为手动
	m_retcode = ::SQLSetConnectOption(m_hdbc, SQL_AUTOCOMMIT,	SQL_AUTOCOMMIT_OFF);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"设置手动提交方式失败");
		return FALSE;
	}
    return TRUE;
}

BOOL CMyODBC::CommitTrans()
{
	if(m_hdbc == NULL)
	{
		strcpy(m_Err,"连接句柄为空，不能执行");
		return FALSE;
	}
	//提交
	m_retcode = ::SQLEndTran(SQL_HANDLE_DBC, m_hdbc, SQL_COMMIT);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"执行事务完成后，提交失败");
		return FALSE;
	}
	//再把提交方式设为自动
	m_retcode = ::SQLSetConnectOption(m_hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"设置自动提交方式失败");
		return FALSE;
	}
	return TRUE;
}

BOOL CMyODBC::Rollback()
{
	if(m_hdbc == NULL)
	{
		strcpy(m_Err,"连接句柄为空，不能执行");
		return FALSE;
	}
    m_retcode = ::SQLEndTran(SQL_HANDLE_DBC, m_hdbc, SQL_ROLLBACK);
    if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
    {
        ReportError(m_hdbc, SQL_HANDLE_DBC,"执行事务失败，并且回滚失败");
		return FALSE;
    }
	//再把提交方式设为自动
	m_retcode = ::SQLSetConnectOption(m_hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"设置自动提交方式失败");
		return FALSE;
	}
	return TRUE;
}
/*
int CMyODBC::ExecTrans(CStringList &strSqlList)
{
	POSITION pos;
	CString strSql;
	BOOL bStatus;
	int iReturn = 0;
	if(m_hdbc == NULL)
	{
		AfxMessageBox("连接句柄为空，不能执行");
		return -1;
	}
	//设置提交方式为手动
	m_retcode = ::SQLSetConnectOption(m_hdbc, SQL_AUTOCOMMIT,	SQL_AUTOCOMMIT_OFF);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"设置手动提交方式失败");
		return -1;
	}
	
	//执行sql
	for(pos = strSqlList.GetHeadPosition(); pos != NULL;)
	{
		strSql = strSqlList.GetNext(pos);
		bStatus = this->ExeSqlDirect(strSql); 
		if(bStatus == FALSE)//执行失败，回滚
		{
			m_retcode = ::SQLEndTran(SQL_HANDLE_DBC, m_hdbc, SQL_ROLLBACK);
			if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
			{
				ReportError(m_hdbc, SQL_HANDLE_DBC,"执行事务失败，并且回滚失败");
			}
			iReturn = -1;
			goto RESET_AUTO_COMMIT;
		}
	}
	//提交
	m_retcode = ::SQLEndTran(SQL_HANDLE_DBC, m_hdbc, SQL_COMMIT);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"执行事务完成后，提交失败");
		iReturn = -1;
	}
	//再把提交方式设为自动
RESET_AUTO_COMMIT:
	m_retcode = ::SQLSetConnectOption(m_hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"设置自动提交方式失败");
		iReturn = -1;
	}

	return iReturn;

}
*/
//下面的这个不能检查到数据库是否已经关了。

/*BOOL CMyODBC::IsOpen()
{
	if(this->m_henv == NULL)
	{
		return FALSE;
	}

	if(this->m_hdbc == NULL)
	{
		return FALSE;
	}
	return TRUE;

} */
上一页 12
💿 文件大小 203 K
👤 上传用户 dasdasdc
📂 所属分类 SQL Server
🏷️ 相关标签

#SQlserver #mysql #ODBC #数据库
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -