C++ CppSQLite3DB::execQuery方法代码示例

void CTerminateNewLocationDialog::OnSelchangeLocationlist() 
{
    int iLocationID = m_ctlLocationList.GetItemData(m_ctlLocationList.GetCurSel());
    CString sSQL;
    sSQL.Format("SELECT Industries.id,Industries.name FROM Industries,Sidings WHERE Industries.Sidings_FK=Sidings.id AND Sidings.Locations_FK=%d",iLocationID);
    TRACE(sSQL);
    //
    CppSQLite3DB* pDB = &((CTrainOpsApp*)AfxGetApp())->m_pDB;
    CppSQLite3Query q = pDB->execQuery(sSQL);
    //
    while( m_ctlIndustryList.GetCount() > 0 )
        m_ctlIndustryList.DeleteString(0);
    //
    while (!q.eof())
    {
        int nIndex = m_ctlIndustryList.AddString(q.getStringField("name"));
        m_ctlIndustryList.SetItemData(nIndex,q.getIntField("id"));
        q.nextRow();
    }
    //
    sSQL.Format("SELECT id,name FROM Sidings WHERE Locations_FK=%d;",iLocationID);
    q = pDB->execQuery(sSQL);
    //
    while( m_ctlSidingList.GetCount() > 0 )
        m_ctlSidingList.DeleteString(0);
    //
    while (!q.eof())
    {
        int nIndex = m_ctlSidingList.AddString(q.getStringField("name"));
        m_ctlSidingList.SetItemData(nIndex,q.getIntField("id"));
        q.nextRow();
    }
    q.finalize();
}

void CIndustryDetailDialog_Commodities::Save()
{
    CppSQLite3DB* pDB = &((CTrainOpsApp*)AfxGetApp())->m_pDB;
    //
    for( int i=0;i<m_ctlCommodityListIn.GetItemCount();i++ )
    {
        if( m_ctlCommodityListIn.GetCheck(i) )
        {
            int iCommodityFK = m_ctlCommodityListIn.GetItemData(i);
            int iLoadEmpty = atoi(m_ctlCommodityListIn.GetItemText(i,1));
            int iQuanHigh = atoi(m_ctlCommodityListIn.GetItemText(i,2));
            int iQuanLow = atoi(m_ctlCommodityListIn.GetItemText(i,3));
            int iPercent = atoi(m_ctlCommodityListIn.GetItemText(i,4));
            CString sSQL;
            sSQL.Format("INSERT INTO Industries_Commodities (Industries_FK,Commodities_FK,InOut,LoadEmptyDays,Quantity_low,Quantity_high,Quantity_percentage) VALUES (%d,%d,0,%d,%d,%d,%d);",m_iIndustryID,iCommodityFK,iLoadEmpty,iQuanLow,iQuanHigh,iPercent);
            CppSQLite3Query q = pDB->execQuery(sSQL);
            q.finalize();
        }
    }
    for( i=0;i<m_ctlCommodityListOut.GetItemCount();i++ )
    {
        if( m_ctlCommodityListOut.GetCheck(i) )
        {
            int iCommodityFK = m_ctlCommodityListOut.GetItemData(i);
            int iLoadEmpty = atoi(m_ctlCommodityListOut.GetItemText(i,1));
            int iQuanHigh = atoi(m_ctlCommodityListOut.GetItemText(i,2));
            int iQuanLow = atoi(m_ctlCommodityListOut.GetItemText(i,3));
            int iPercent = atoi(m_ctlCommodityListOut.GetItemText(i,4));
            CString sSQL;
            sSQL.Format("INSERT INTO Industries_Commodities (Industries_FK,Commodities_FK,InOut,LoadEmptyDays,Quantity_low,Quantity_high,Quantity_percentage) VALUES (%d,%d,1,%d,%d,%d,%d);",m_iIndustryID,iCommodityFK,iLoadEmpty,iQuanLow,iQuanHigh,iPercent);
            CppSQLite3Query q = pDB->execQuery(sSQL);
            q.finalize();
        }
    }
}

//void CDlgProjectNew::OnBnClickedButtonCancel()
//{
//	// TODO: 在此添加控件通知处理程序代码
//	m_bIsSave=false;
//	OnOK();
//}
bool CDlgProjectNew::IsExistProjectName(CString strProjectName)//该产品名称是否存在库存表中 true 存在 false 不存在
{

	CString strSQL;
	strSQL.Format(_T("select count(ProjectName) as COUNTProjectName from Project where ProjectName='%s';"),	strProjectName);
	int nCount=0;
	try
	{

		CppSQLite3DB db;
		db.open(CBoBoDingApp::g_strDatabasePath);
		CppSQLite3Query q = db.execQuery(strSQL);//销售单历史表

		if (!q.eof())
		{
			//nCount=q.fieldValue(_T("COUNTPinMingGuiGe"));
			nCount=q.getIntField(_T("COUNTProjectName"));
		}
	}
	catch (CppSQLite3Exception& e)
	{

		AfxMessageBox(e.errorMessage());
	}
	if(nCount>0) 
		return true;
	else
		return false;

}

void CCarTypesDetailDialog::OnOK() 
{
	// TODO: Add extra validation here

    UpdateData();
    //
    CppSQLite3DB* pDB = &((CTrainOpsApp*)AfxGetApp())->m_pDB;
    CString sSQL;
    //
    //  add?
    //
    if( m_iCarTypeFK == -1 )
    {
        sSQL.Format("SELECT 1 FROM CarTypes WHERE type_id=\"%s\"",m_sCarTypeID);
        CppSQLite3Query q = pDB->execQuery((LPCTSTR)sSQL);
        //
        if(!q.eof())
        {
            MessageBox("Duplcate Type ID!","LSC TrainOps Error",MB_ICONSTOP|MB_OK);
            return;
        }
        sSQL.Format("INSERT INTO CarTypes (id,type_id,description,active,passenger) VALUES (NULL,\"%s\",\"%s\",1,%d)",m_sCarTypeID,m_sCarTypeDescription,m_bPassenger?1:0);
    }
    else
    {
        sSQL.Format("UPDATE CarTypes SET type_id=\"%s\",description=\"%s\",passenger=%d WHERE id=%d",m_sCarTypeID,m_sCarTypeDescription,m_bPassenger?1:0,m_iCarTypeFK);
    }
    pDB->execDML((LPCTSTR)sSQL);
    
	CDialog::OnOK();
}

//##ModelId=474D307602CF
bool CClip_ImportExport::ImportFromSqliteDB(CppSQLite3DB &db, bool bAddToDB, bool bPutOnClipboard)
{
	bool bRet = false;
	CStringA csCF_TEXT;
	CStringW csCF_UNICODETEXT;

	try
	{
		CppSQLite3Query q = db.execQuery(_T("Select * from Main"));
		while(q.eof() == false)
		{
			Clear();

			int nVersion = q.getIntField(_T("lVersion"));
			if(nVersion == 1)
			{
				if(ImportFromSqliteV1(db, q))
				{
					if(bAddToDB)
					{
						MakeLatestTime();
						AddToDB(true);
						bRet = true;
					}
					else if(bPutOnClipboard)
					{
						bRet = true;
					}
				}
			}

			m_lImportCount++;

			//If putting on the clipboard and there are multiple
			//then append cf_text and cf_unicodetext
			if(bPutOnClipboard)
			{
				Append_CF_TEXT_AND_CF_UNICODETEXT(csCF_TEXT, csCF_UNICODETEXT);
			}

			q.nextRow();
		}

		if(bRet && bAddToDB)
		{
			theApp.RefreshView();
		}
		else if(bRet && m_lImportCount == 1 && bPutOnClipboard)
		{
			PlaceFormatsOnclipboard();
		}
		else if(bRet && bPutOnClipboard)
		{
			PlaceCF_TEXT_AND_CF_UNICODETEXT_OnClipboard(csCF_TEXT, csCF_UNICODETEXT);
		}
	}
	CATCH_SQLITE_EXCEPTION_AND_RETURN(false)

	return bRet;
}

char* CreateInfoByid(int id)
{
	static char buffer[1024]={0};
	static char querybuf[1024]={0};
	char* str = "%s  体力%s 武力%s 智力%s 魅力%s 年龄%s 类型 %s";

	memset(querybuf,0,1024);
	try
	{	db.open("database/infodata.db");
		sprintf(querybuf,"select heroinfo.name,ti,wu,zhi,mei,age,herotype.name from "
			" heroinfo,herotype where heroinfo.id=%d and heroinfo.type=herotype.id;",id);
		CppSQLite3Query q = db.execQuery(querybuf);

		//nge_charsets_utf8_to_gbk((uint8*)str, (uint8*)querybuf, strlen(str), 1024);

		if (!q.eof())
	{
		sprintf(buffer,str, q.fieldValue(0),q.fieldValue(1),q.fieldValue(2),q.fieldValue(3),
				q.fieldValue(4),q.fieldValue(5),q.fieldValue(6));
	}
		db.close();
	}catch (CppSQLite3Exception& e){

	printf("%s\n",e.errorMessage());
	}

	return buffer;
}

bool DBSetting::GetValue(long lKey,std::string &strValue)
{
	//打开数据库
	CppSQLite3DB dbTask;
	dbTask.open(m_strDB.c_str());
	CheckCreateSettingTable(dbTask);
	CppSQLite3Buffer strSql;

	try{
		strSql.format("select value from T_Setting where key=%d;",lKey);
		CppSQLite3Query q = dbTask.execQuery(strSql);
		if (q.eof())
		{
			return false;
		}
		strValue = q.getStringField(0);
	}
	catch(CppSQLite3Exception &exp)
	{
		exp;
		ATLTRACE("error:%s\n",exp.errorMessage());
		ATLASSERT(FALSE);
		return false;
	}
	return true;
}

int CDbMeter::JoinTableDelete(const char* dbfilename, char* szID)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		CppSQLite3Buffer bufSQL;
		CppSQLite3Table t;

		db.execDML("CREATE TABLE IF NOT EXISTS jointable (joinkey INTEGER PRIMARY KEY, EUI64ID CHAR(17), jointry INTEGER);");
		bufSQL.format("SELECT * FROM jointable WHERE EUI64ID=%Q", szID);
		t = db.getTable(bufSQL);

		if( t.numRows() == 0 )
		{
			XDEBUG("Failure: Don't have JoinTable %s\r\n", szID);
		}
		else
		{
			bufSQL.clear();
			bufSQL.format("DELETE FROM jointable WHERE EUI64ID=%Q;", szID);
			db.execQuery(bufSQL);
		}
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

BOOL CLocationDetailDialog::OnInitDialog()
{
    CDialog::OnInitDialog();

    if( m_iLocationID == -1 )
        m_ctlOK.EnableWindow(FALSE);
    else
    {
        CString sSQL;
        sSQL.Format("SELECT name,notes FROM Locations WHERE id=%d;",m_iLocationID);
        CppSQLite3DB* pDB = &((CTrainOpsApp*)AfxGetApp())->m_pDB;
        CppSQLite3Query q = pDB->execQuery(sSQL);
        //
        if (!q.eof())
        {
            m_sLocationName = q.getStringField("name");
            m_sLocalInstructions = q.getStringField("notes");
        }
        q.finalize();
        UpdateData(FALSE);
    }

    return TRUE;  // return TRUE unless you set the focus to a control
    // EXCEPTION: OCX Property Pages should return FALSE
}

int CDBObject::getFirstFile(char* name)
{
	try
	{
		if(name != NULL)
		{
			char szSql[]= {"SELECT * from Record"};
			CppSQLite3DB database;
			database.open(g_szFile);
			CppSQLite3Query query = database.execQuery(szSql);
			if(!query.eof())
			{
				string path = query.getStringField(8);
				string file= query.getStringField(7);
				sprintf(name, "%s\\%s", UTF_82ASCII(path).c_str(), UTF_82ASCII(file).c_str());
				return query.getIntField(0);
			}
		}
	}
	catch(...)
	{
		g_pLog->WriteLog("数据库查询异常 getFirstFile\n");
	}
	return -1;
}

BOOL CCarTypesDetailDialog::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here

    if( m_iCarTypeFK != -1 )
    {
        CppSQLite3DB* pDB = &((CTrainOpsApp*)AfxGetApp())->m_pDB;
        CString sSQL;
        sSQL.Format("SELECT * FROM CarTypes WHERE id=%d",m_iCarTypeFK);
        CppSQLite3Query q = pDB->execQuery((LPCTSTR)sSQL);
        if( !q.eof() )
        {
            m_sCarTypeDescription = q.getStringField("description");
            m_sCarTypeID = q.getStringField("type_id");
            m_bPassenger = q.getIntField("passenger")==1?TRUE:FALSE;
        }
        q.finalize();
    }


    UpdateData(FALSE);
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

void CUserAcessSetDlg::InserSingleSetConfig()
{

	CppSQLite3Table table;
	CppSQLite3Query q;

	CppSQLite3DB SqliteDBBuilding;
	SqliteDBBuilding.open((UTF8MBSTR)g_strCurBuildingDatabasefilePath);

	CString strSql;
	strSql.Format(_T("select * from UserLevelSingleSet where MainBuilding_Name='%s' and Building_Name='%s' and username='%s'"),m_strMainBuilding,m_strSubNetName,m_strUserName);
	q = SqliteDBBuilding.execQuery((UTF8MBSTR)strSql);
	_variant_t temp_variant;
	BOOL b_useLogin=false;

	try
	{

		if(q.eof())
		{
			strSql.Format(_T("insert into UserLevelSingleSet values('%s','%s','%s',%i,%i)"),m_strMainBuilding,m_strSubNetName,m_strUserName,0,0);		
			SqliteDBBuilding.execDML((UTF8MBSTR)strSql); 
		}

		SqliteDBBuilding.closedb();
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}



}

BOOL CDlgMemoAdd::OnInitDialog()
{
	CDialogEx::OnInitDialog();

	// TODO:  在此添加额外的初始化
	//SetBackgroundColor(DialogSubBackgroundColor,TRUE);
	m_DateTime_ctlTime.SetFormat(_T("yyyy-MM-dd HH:mm"));

	SetBackgroundColor(DialogSubBackgroundColor,TRUE);
	m_Button_ctlSave.LoadStdImage(IDB_PNG_SAVE, _T("PNG"));
	m_Button_ctlCancel.LoadStdImage(IDB_PNG_CANCEL, _T("PNG"));

	m_Static_ctl1.SetBackgroundColor(DialogSubBackgroundColor,TRUE);
	//m_Static_ctl1.SetTextColor(StaticCaptionColor,TRUE);

	m_Static_ctl2.SetBackgroundColor(DialogSubBackgroundColor,TRUE);
	//m_Static_ctl2.SetTextColor(StaticCaptionColor,TRUE);

	m_Static_ctl3.SetBackgroundColor(DialogSubBackgroundColor,TRUE);
	//m_Static_ctl3.SetTextColor(StaticCaptionColor,TRUE);

	m_Static_ctl4.SetBackgroundColor(DialogSubBackgroundColor,TRUE);
	//m_Static_ctl4.SetTextColor(StaticCaptionColor,TRUE);*/

	/*m_Static_ctl5.SetBackgroundColor(DialogSubBackgroundColor,TRUE);
	m_Static_ctl5.SetTextColor(StaticCaptionColor,TRUE);*/


	try{
		CppSQLite3DB db;
		db.open(CBoBoDingApp::g_strDatabasePath);
		CString strSQL=_T("select distinct * from CategoryMemo order by ID asc;");
		CppSQLite3Query q = db.execQuery(strSQL);
		CString strCategoryName;
		while(!q.eof())
		{
			strCategoryName=(q.fieldValue(_T("CategoryName")));
			if (strCategoryName.Compare(_T("+"))!=0)
			{
				m_Combo_ctlCategory.AddString(strCategoryName);
			}
			
			q.nextRow();

		}
	}

	catch (CppSQLite3Exception& e)
	{

		//AfxMessageBox(e.errorMessage());
	}
	m_Combo_ctlCategory.SetCurSel(0);
	return TRUE;  // return TRUE unless you set the focus to a control
	// 异常: OCX 属性页应返回 FALSE
}

int CDbMeter::GroupDelete(const char* dbfilename, int nGroupKey)
{
	try
	{
		CppSQLite3DB db;
		db.open(dbfilename);
		// 그룹테이블에 그룹이름이 있느지 확인
		CppSQLite3Buffer bufSQL;
		//CppSQLite3Query q;
		CppSQLite3Table t;

		bufSQL.format("SELECT GroupKey FROM groups WHERE GroupKey=%d;", nGroupKey);
		t = db.getTable(bufSQL);

		if( t.numRows() == 0 )
		{
			XDEBUG("ERROR: %d GroupKey doesn't exist in Groups Table!!\r\n", nGroupKey);
			return IF4ERR_GROUP_NAME_NOT_EXIST;
		}
		else
		{
			bufSQL.clear();
			bufSQL.format("DELETE FROM groups WHERE GroupKey=%d;", nGroupKey);
			db.execDML(bufSQL);

			XDEBUG("%d GroupKey deleted in Groups Table!!\r\n", nGroupKey);
			//bufSQL.format("SELECT Groups.GroupName FROM groupmember LEFT OUTER JOIN groups ON groupmember.GroupKey=groups.GroupKey LEFT OUTER JOIN member ON groupmember.EUI64ID=member.EUI64ID WHERE groups.GroupName = %s;", szGroupName);
		}

		bufSQL.clear();
		bufSQL.format("SELECT GroupKey FROM groupmember WHERE GroupKey=%d;", nGroupKey);
		t = db.getTable(bufSQL);

		if( t.numRows() == 0 )
		{
			XDEBUG("ERROR: %d GroupKey doesn't exist!! in Groupmember Table!!\r\n", nGroupKey);
			return IF4ERR_GROUP_NAME_NOT_EXIST;
		}
		else
		{
			bufSQL.clear();
			bufSQL.format("DELETE FROM groupmember WHERE GroupKey=%d;", nGroupKey);
			db.execQuery(bufSQL);
			XDEBUG("%d GroupKey deleted in Groupmember Table!!\r\n", nGroupKey);
		}
		db.close();
	}
	catch( CppSQLite3Exception& e )
	{
		XDEBUG("%s\r\n",e.errorMessage());
		return e.errorCode();
	}
	return SQLITE_OK;
}

CString CEquipmentLocationDialog::GetCarType(int iCarTypeFK)
{
    CppSQLite3DB* pDB = &((CTrainOpsApp*)AfxGetApp())->m_pDB;
    CString sSQL;
    sSQL.Format("SELECT description FROM CarTypes WHERE id=%d;",iCarTypeFK);
    CppSQLite3Query q = pDB->execQuery(sSQL);
    CString sType("");
    if( !q.eof() )
        sType = q.getStringField("description");
    q.finalize();
    return sType;
}