导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿
会员中心
收藏
消息
创作中心

很奇怪的问题，同样的代码对数据库操作，第一次总是失几，第二次就好了，为什么

jronald 2007-12-22 09:55:26
很奇怪的问题，同样的代码对数据库操作，第一次总是失几，第二次就好了，为什么
...全文
29 点赞 收藏 4
写回复
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
jronald 2007-12-23
为了假化，我假定try里面不会有异常，只有当表存在时，才会跳到 catch，
catch里代码的作用是追加记录


void CEGraph2Doc::GenGrpTbl(const UINT CntTotalRc,const UINT CntRcPerGrp)

{

    _RecordsetPtr pRs;

    _variant_t var;

    CString SqlCmd;

    CString TmpTblName;

    COleDateTime LastDateTime;

    float FirstVolt;

    float LastVolt;

    float MinVolt;

    float MaxVolt;

    UINT i;

    

    pRs.CreateInstance(__uuidof(Recordset));

    TmpTblName.Format("tmp_%s_%u",m_TableName,CntRcPerGrp);

    

    try    {        

        //try to create temporary table if it doesn't exist

        SqlCmd.Format("CREATE TABLE %s "

            "(ID int PRIMARY KEY,"

            "时刻 datetime,"

            "type char(12),"

            "grprc int,"

            "first real,last real,min real,max real,"

            "ma5 real,ma10 real,ma12 real,ma20 real,ma26 real,ma30 real,"

            "a real,n real,"

            "rsv real,z real,j real,y real)",

            TmpTblName);

        m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);

        

        //fill it            

        SqlCmd.Format("SELECT 时刻,电伏,规格 FROM %s ORDER BY 时刻",m_TableName);

        pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);

        

        UINT CntTotalGrp=(CntTotalRc+CntRcPerGrp-1)/CntRcPerGrp;

        UINT CntLastGrpRc=CntTotalRc%CntRcPerGrp;

        UINT _CntRcPerGrp=CntRcPerGrp;



        for (i=0;i<CntTotalGrp;++i) {//groups that have enough records

            LastDateTime=pRs->GetCollect("时刻");

            FirstVolt=LastVolt=MinVolt=MaxVolt=float(pRs->GetCollect("电伏"));

            if (i==CntTotalGrp-1 && CntLastGrpRc>0) {

                _CntRcPerGrp=CntLastGrpRc;

            }

            for (UINT j=1;j<_CntRcPerGrp;++j) {

                pRs->MoveNext();

                LastVolt=float(pRs->GetCollect("电伏"));

                MaxVolt=max(MaxVolt,LastVolt);

                MinVolt=min(MinVolt,LastVolt);

            }

            CString sType=LPCTSTR(_bstr_t(pRs->GetCollect("规格")));

            SqlCmd.Format("INSERT INTO %s (ID,时刻,type,grprc,first,last,min,max) "

                "VALUES(%d,'%s','%s','%d',%f,%f,%f,%f)",

                TmpTblName,i,LastDateTime.Format(),

                sType,_CntRcPerGrp,FirstVolt,LastVolt,MinVolt,MaxVolt);

            m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);

            pRs->MoveNext();

        }

        pRs->Close();

        

        FillMA(TmpTblName,5,CntTotalGrp,0);

        FillMA(TmpTblName,10,CntTotalGrp,0);

        FillMA(TmpTblName,12,CntTotalGrp,0);

        FillMA(TmpTblName,20,CntTotalGrp,0);

        FillMA(TmpTblName,26,CntTotalGrp,0);

        FillMA(TmpTblName,30,CntTotalGrp,0);

        FillA(TmpTblName,CntTotalGrp,0);

        FillN(TmpTblName,CntTotalGrp,0);

        FillRSV(TmpTblName,CntTotalGrp,0);

        FillZ(TmpTblName,CntTotalGrp,0);

        FillJ(TmpTblName,CntTotalGrp,0);

        FillY(TmpTblName,CntTotalGrp,0);

    } catch (_com_error e) {

        //already created, or hardly other error

        SqlCmd.Format("SELECT SUM(grprc) as TotalRc FROM %s",TmpTblName);

        pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);

        var=pRs->GetCollect("TotalRc");

        pRs->Close();

        

        UINT n=var.uintVal;



        if (CntTotalRc>n) {

            int LastID=(n+CntRcPerGrp-1)/CntRcPerGrp-1;

            UINT NewCntGrp=(CntTotalRc-LastID*CntRcPerGrp+CntRcPerGrp-1)/CntRcPerGrp;

            UINT CntTotalGrp=(CntTotalRc+CntRcPerGrp-1)/CntRcPerGrp;

            UINT CntLastGrpRc=CntTotalRc%CntRcPerGrp;

            UINT _CntRcPerGrp=CntRcPerGrp;



            SqlCmd.Format("DELETE FROM %s WHERE ID=%d",TmpTblName,LastID);

            m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);

            SqlCmd.Format("SELECT 时刻,电伏,规格 FROM "

                    "(SELECT TOP %d 时刻,电伏,规格 FROM %s ORDER BY 时刻 DESC) as t1 ORDER BY 时刻",

                    CntTotalRc-LastID*CntRcPerGrp,m_TableName);

            pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);

            

            for (i=0;i<NewCntGrp;++i) {//groups that have enough records

                LastDateTime=pRs->GetCollect("时刻");

                FirstVolt=LastVolt=MinVolt=MaxVolt=float(pRs->GetCollect("电伏"));

                if (i==NewCntGrp-1 && CntLastGrpRc>0) {

                    _CntRcPerGrp=CntLastGrpRc;

                }

                for (UINT j=1;j<_CntRcPerGrp;++j) {

                    pRs->MoveNext();

                    LastVolt=float(pRs->GetCollect("电伏"));

                    MaxVolt=max(MaxVolt,LastVolt);

                    MinVolt=min(MinVolt,LastVolt);

                }

                CString sType=LPCTSTR(_bstr_t(pRs->GetCollect("规格")));

                SqlCmd.Format("INSERT INTO %s (ID,时刻,type,grprc,first,last,min,max) "

                    "VALUES(%d,'%s','%s','%d',%f,%f,%f,%f)",

                    TmpTblName,LastID+i,LastDateTime.Format(),

                    sType,_CntRcPerGrp,FirstVolt,LastVolt,MinVolt,MaxVolt);

                m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);

                pRs->MoveNext();

            }

            pRs->Close();

            

            FillMA(TmpTblName,5,CntTotalGrp,LastID);

            FillMA(TmpTblName,10,CntTotalGrp,LastID);

            FillMA(TmpTblName,12,CntTotalGrp,LastID);

            FillMA(TmpTblName,20,CntTotalGrp,LastID);

            FillMA(TmpTblName,26,CntTotalGrp,LastID);

            FillMA(TmpTblName,30,CntTotalGrp,LastID);

            

            FillA(TmpTblName,CntTotalGrp,LastID);

            

            FillN(TmpTblName,CntTotalGrp,LastID);

            FillRSV(TmpTblName,CntTotalGrp,LastID);

            

            FillZ(TmpTblName,CntTotalGrp,LastID);

            

            FillJ(TmpTblName,CntTotalGrp,LastID);

            

            FillY(TmpTblName,CntTotalGrp,LastID);

        }

    }

    

    if (pRs->State==adStateOpen) {

        pRs->Close();

    }

    pRs.Release();

}
回复 赞
jronald 2007-12-23
为了假化，我假定try里面不会有异常，只有当表存在时，才会跳到 catch，
catch里代码的作用是追加记录


void CEGraph2Doc::GenGrpTbl(const UINT CntTotalRc,const UINT CntRcPerGrp)

{

	_RecordsetPtr pRs;

	_variant_t var;

	CString SqlCmd;

	CString TmpTblName;

	COleDateTime LastDateTime;

	float FirstVolt;

	float LastVolt;

	float MinVolt;

	float MaxVolt;

	UINT i;

	

	pRs.CreateInstance(__uuidof(Recordset));

	TmpTblName.Format("tmp_%s_%u",m_TableName,CntRcPerGrp);

	

	try	{		

		//try to create temporary table if it doesn't exist

		SqlCmd.Format("CREATE TABLE %s "

			"(ID int PRIMARY KEY,"

			"时刻 datetime,"

			"type char(12),"

			"grprc int,"

			"first real,last real,min real,max real,"

			"ma5 real,ma10 real,ma12 real,ma20 real,ma26 real,ma30 real,"

			"a real,n real,"

			"rsv real,z real,j real,y real)",

			TmpTblName);

		m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);

		

		//fill it			

		SqlCmd.Format("SELECT 时刻,电伏,规格 FROM %s ORDER BY 时刻",m_TableName);

		pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);

		

		UINT CntTotalGrp=(CntTotalRc+CntRcPerGrp-1)/CntRcPerGrp;

		UINT CntLastGrpRc=CntTotalRc%CntRcPerGrp;

		UINT _CntRcPerGrp=CntRcPerGrp;



		for (i=0;i<CntTotalGrp;++i) {//groups that have enough records

			LastDateTime=pRs->GetCollect("时刻");

			FirstVolt=LastVolt=MinVolt=MaxVolt=float(pRs->GetCollect("电伏"));

			if (i==CntTotalGrp-1 && CntLastGrpRc>0) {

				_CntRcPerGrp=CntLastGrpRc;

			}

			for (UINT j=1;j<_CntRcPerGrp;++j) {

				pRs->MoveNext();

				LastVolt=float(pRs->GetCollect("电伏"));

				MaxVolt=max(MaxVolt,LastVolt);

				MinVolt=min(MinVolt,LastVolt);

			}

			CString sType=LPCTSTR(_bstr_t(pRs->GetCollect("规格")));

			SqlCmd.Format("INSERT INTO %s (ID,时刻,type,grprc,first,last,min,max) "

				"VALUES(%d,'%s','%s','%d',%f,%f,%f,%f)",

				TmpTblName,i,LastDateTime.Format(),

				sType,_CntRcPerGrp,FirstVolt,LastVolt,MinVolt,MaxVolt);

			m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);

			pRs->MoveNext();

		}

		pRs->Close();

		

		FillMA(TmpTblName,5,CntTotalGrp,0);

		FillMA(TmpTblName,10,CntTotalGrp,0);

		FillMA(TmpTblName,12,CntTotalGrp,0);

		FillMA(TmpTblName,20,CntTotalGrp,0);

		FillMA(TmpTblName,26,CntTotalGrp,0);

		FillMA(TmpTblName,30,CntTotalGrp,0);

		FillA(TmpTblName,CntTotalGrp,0);

		FillN(TmpTblName,CntTotalGrp,0);

		FillRSV(TmpTblName,CntTotalGrp,0);

		FillZ(TmpTblName,CntTotalGrp,0);

		FillJ(TmpTblName,CntTotalGrp,0);

		FillY(TmpTblName,CntTotalGrp,0);

	} catch (_com_error e) {

		//already created, or hardly other error

		SqlCmd.Format("SELECT SUM(grprc) as TotalRc FROM %s",TmpTblName);

		pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);

		var=pRs->GetCollect("TotalRc");

		pRs->Close();

		

		UINT n=var.uintVal;



		if (CntTotalRc>n) {

			int LastID=(n+CntRcPerGrp-1)/CntRcPerGrp-1;

			UINT NewCntGrp=(CntTotalRc-LastID*CntRcPerGrp+CntRcPerGrp-1)/CntRcPerGrp;

			UINT CntTotalGrp=(CntTotalRc+CntRcPerGrp-1)/CntRcPerGrp;

			UINT CntLastGrpRc=CntTotalRc%CntRcPerGrp;

			UINT _CntRcPerGrp=CntRcPerGrp;



			SqlCmd.Format("DELETE FROM %s WHERE ID=%d",TmpTblName,LastID);

			m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);

			SqlCmd.Format("SELECT 时刻,电伏,规格 FROM "

					"(SELECT TOP %d 时刻,电伏,规格 FROM %s ORDER BY 时刻 DESC) as t1 ORDER BY 时刻",

					CntTotalRc-LastID*CntRcPerGrp,m_TableName);

			pRs->Open(LPCTSTR(SqlCmd),m_pConn.GetInterfacePtr(),adOpenStatic,adLockReadOnly,adCmdText);

			

			for (i=0;i<NewCntGrp;++i) {//groups that have enough records

				LastDateTime=pRs->GetCollect("时刻");

				FirstVolt=LastVolt=MinVolt=MaxVolt=float(pRs->GetCollect("电伏"));

				if (i==NewCntGrp-1 && CntLastGrpRc>0) {

					_CntRcPerGrp=CntLastGrpRc;

				}

				for (UINT j=1;j<_CntRcPerGrp;++j) {

					pRs->MoveNext();

					LastVolt=float(pRs->GetCollect("电伏"));

					MaxVolt=max(MaxVolt,LastVolt);

					MinVolt=min(MinVolt,LastVolt);

				}

				CString sType=LPCTSTR(_bstr_t(pRs->GetCollect("规格")));

				SqlCmd.Format("INSERT INTO %s (ID,时刻,type,grprc,first,last,min,max) "

					"VALUES(%d,'%s','%s','%d',%f,%f,%f,%f)",

					TmpTblName,LastID+i,LastDateTime.Format(),

					sType,_CntRcPerGrp,FirstVolt,LastVolt,MinVolt,MaxVolt);

				m_pConn->Execute(_bstr_t(SqlCmd),NULL,adCmdText);

				pRs->MoveNext();

			}

			pRs->Close();

			

			FillMA(TmpTblName,5,CntTotalGrp,LastID);

			FillMA(TmpTblName,10,CntTotalGrp,LastID);

			FillMA(TmpTblName,12,CntTotalGrp,LastID);

			FillMA(TmpTblName,20,CntTotalGrp,LastID);

			FillMA(TmpTblName,26,CntTotalGrp,LastID);

			FillMA(TmpTblName,30,CntTotalGrp,LastID);

			

			FillA(TmpTblName,CntTotalGrp,LastID);

			

			FillN(TmpTblName,CntTotalGrp,LastID);

			FillRSV(TmpTblName,CntTotalGrp,LastID);

			

			FillZ(TmpTblName,CntTotalGrp,LastID);

			

			FillJ(TmpTblName,CntTotalGrp,LastID);

			

			FillY(TmpTblName,CntTotalGrp,LastID);

		}

	}

	

	if (pRs->State==adStateOpen) {

		pRs->Close();

	}

	pRs.Release();

}
回复 赞
cxmcxm 2007-12-22
什么代码,贴出来看看.
 回复 赞
rouqu 2007-12-22
什么代码 贴出来
 回复 赞
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

25.4w+

社区内容

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告