C++ RSqlStatement::Exec方法代码示例

/**
@SYMTestCaseID			PDS-SQLITE3SEC-UT-4034
@SYMTestCaseDesc		SQL server multi-insert performance test.
						The test inserts 1000 records in a single transaction and stores
						the execution time for later use (comparison and printing).
@SYMTestPriority		High
@SYMTestActions			SQL server multi-insert performance test.
@SYMTestExpectedResults Test must not fail
@SYMREQ					REQ11320
*/
static void SqlServerMultiInsertTest(const char aInsertSql[], TInt aInsertRecCnt)
	{
	TheTest.Next( _L("@SYMTestCaseID:PDS-SQLITE3SEC-UT-4034"));
	(void)KillProcess(KSqlSrvName);

	TInt err = TheDb.Open(KTestDbName);
	TEST2(err, KErrNone);

	RSqlStatement stmt;
	err = stmt.Prepare(TheDb, TPtrC8((const TUint8*)aInsertSql));
	TEST2(err, KErrNone);

	TUint32 fc = FastCounterValue();
	err = TheDb.Exec(_L8("BEGIN"));
	TEST(err >= 0);

	for(TInt i=0;i<aInsertRecCnt;++i)
		{
		err = stmt.BindInt(0, i + 1);
		TEST2(err, KErrNone);
		err = stmt.Exec();
		TEST2(err, 1);
		err = stmt.Reset();
		TEST2(err, KErrNone);
		}

	err = TheDb.Exec(_L8("COMMIT"));
	TEST(err >= 0);
	StorePerfTestResult(EPerfTestSqlMode, EPerfTestMultiInsert, FastCounterValue() - fc);

	stmt.Close();
	TheDb.Close();
	}

/**
Deletes all the communication addresses for a particular contact item. Should be used when
deleting a contact item from the database altogether.

@param aItem The contact item whose communcation addresses are to be deleted.
*/
void CPplCommAddrTable::DeleteL(const CContactItem& aItem, TBool& aLowDiskErrorOccurred)
	{
	const TUid KType = aItem.Type();
    if (KType != KUidContactCard && KType != KUidContactOwnCard && KType != KUidContactICCEntry && KType != KUidContactGroup)
		{
		return;
		}

	RSqlStatement stmnt;
	CleanupClosePushL(stmnt);
	stmnt.PrepareL(iDatabase, iAllForItemDeleteStmnt->SqlStringL() );
	const TInt KContactIdParamIndex(KFirstIndex); // first and only parameter in query
	User::LeaveIfError(stmnt.BindInt(KContactIdParamIndex, aItem.Id() ) );
	TInt err = stmnt.Exec();
	CleanupStack::PopAndDestroy(&stmnt);

	if (err == KErrDiskFull)
		{
		aLowDiskErrorOccurred = ETrue;
		}
	else
		{
		User::LeaveIfError(err);
		}
	}

// -----------------------------------------------------------------------------
// CUpnpSecurityDbConnection::ExecStatementL
// Execute given db command
// -----------------------------------------------------------------------------
//
void CUpnpSecurityDbConnection::ExecStatementL( const TDesC8& aCommand )
    {
    RSqlStatement statement;
    User::LeaveIfError( statement.Prepare( iDatabase, aCommand ) );
    CleanupClosePushL( statement );
    User::LeaveIfError( statement.Exec() );
    CleanupStack::PopAndDestroy( &statement );    
    }

/**
 * Method to change a plugin id in plugin Id table
 * @param aNewPluginID The ID of the new plugin
 * @param aFlag The flag that indicates the plugin id is enables or disabled
 * @param aOldPluginID The ID of the plugin to be replaced
 * @return Returns KErrNone if success. Refer ESqlDbError and system-wide
 * error codes for detailed error description.
 */
TInt CSmfCredMgrDbUser::updatePlugin(const TDesC& aPluginID,
                                     const TBool& aFlag, const TDesC& aOldPluginID)
{
    TInt err(KErrNone);
    RSqlStatement sqlStatement;
    TInt paramIndex(KErrNone);
    TBuf<KMaxBufSize> newPluginBuf(aPluginID);
    TBuf<KMaxBufSize> OldPluginBuf(aOldPluginID);
    RSqlDatabase db;

    err = db.Open(iDbFilePathName);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());

    err = sqlStatement.Prepare(db, KUpdatePluginID);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());

    paramIndex = sqlStatement.ParameterIndex(_L(":iText"));
    err = sqlStatement.BindText(paramIndex, newPluginBuf);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());

    paramIndex = sqlStatement.ParameterIndex(_L(":iFlag"));
    err = sqlStatement.BindInt(paramIndex, aFlag);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());

    paramIndex = sqlStatement.ParameterIndex(_L(":iID"));
    err = sqlStatement.BindText(paramIndex, OldPluginBuf);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());

    err = db.Exec(KBegin);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
    err = sqlStatement.Exec();
    if (KSqlErrConstraint == err)
    {
        //
    }
    else if (err < KErrNone)
    {
        __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
    }

    err = db.Exec(KCommit);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());

    //if commit fails we have to roll back
    if (err < KErrNone)
    {
        err = db.Exec(KRollback);
    }
    sqlStatement.Close();
    db.Close();
    if (err >= 0)
    {
        return KErrNone;
    }
    return err;
}

/**
 * Method to insert AuthParamsTable
 * @param aAuthAppId The ID of the Authentication app
 * @param aKey The Key
 * @param aSecret The Secret
 * @return Returns KErrNone if success. Refer ESqlDbError and system-wide
 * error codes for detailed error description.
 */
TInt CSmfCredMgrDbUser::AuthParamsTableInsert(const TDesC& aAuthAppId,
        const TDesC& aKey, const TDesC& aSecret)
{
    TInt err(KErrNone);
    RSqlStatement sqlStatement;
    RSqlDatabase db;

    TBuf<KMaxBufSize> keyBuf(aKey);
    TBuf<KMaxBufSize> secretBuf(aSecret);

    err = db.Open(iDbFilePathName);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());

    err = sqlStatement.Prepare(db, KSmfAuthParamsTableInsert);
    TInt paramIndex(KErrNone);

    paramIndex = sqlStatement.ParameterIndex(_L(":Val1"));
    err = sqlStatement.BindText(paramIndex, aAuthAppId);

    paramIndex = sqlStatement.ParameterIndex(_L(":Val2"));
    err = sqlStatement.BindText(paramIndex, keyBuf);

    paramIndex = sqlStatement.ParameterIndex(_L(":Val3"));
    err = sqlStatement.BindText(paramIndex, secretBuf);

    err = db.Exec(KBegin);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
    err = sqlStatement.Exec();
    if (KSqlErrConstraint == err)
    {
        //Table already present.
    }
    else if (err < KErrNone)
    {
        __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());
    }

    err = db.Exec(KCommit);
    __ASSERT_DEBUG( (err >= KErrNone), User::Invariant());

    //if commit fails we have to roll back
    if (err < KErrNone)
    {
        err = db.Exec(KRollback);
    }
    sqlStatement.Close();
    db.Close();
    if (err >= 0)
    {
        return KErrNone;
    }
    return err;
}

/**
Performs write (Insert/Update) operations for indiviual communication addresses of type "phone number".
*/
void CPplCommAddrTable::DoPhoneNumWriteOpL(const CPplCommAddrTable::TMatch& aPhoneNum, TCntSqlStatement aType,
                                            TInt aCntId, TInt aCommAddrId,CPplCommAddrTable::TCommAddrExtraInfoType aExtraInfoType)
    {
    // leave if the statement type is not insert or update.
    // also, we can't update if aCommAddrId is 0 as we don't know the record's id
    if ((aType != EUpdate && aType != EInsert) || (aType == EUpdate && aCommAddrId == 0) )
        {
        User::Leave(KErrArgument);
        }

    RSqlStatement stmnt;
    CleanupClosePushL(stmnt);

    // temporary reference to the CCntSqlStatements member variables to take advantage
    // of the commonality between update and insert operations.
    CCntSqlStatement* tempCntStmnt = iUpdateStmnt;
    if (aType == EInsert)
        {
        tempCntStmnt = iInsertStmnt;
        }
    
    User::LeaveIfError(stmnt.Prepare(iDatabase, tempCntStmnt->SqlStringL() ) );
    User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrContactId() ), aCntId) );
    User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrExtraValue() ), aPhoneNum.iUpperDigits) );
    User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrValue() ), aPhoneNum.iLowerSevenDigits) );
    User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrType() ), EPhoneNumber) );
    User::LeaveIfError(stmnt.BindInt(tempCntStmnt->ParameterIndex(KCommAddrExtraTypeInfo() ), aExtraInfoType) );
  
    if (aType == EInsert)
        {
        User::LeaveIfError(stmnt.BindNull(tempCntStmnt->ParameterIndex(KCommAddrId() ) ) );
        }
    else
        {
        // it's the sixth parameter in the query and is in the WHERE
        // clause so we can't get its index from the CCntSqlStatement
        const TInt KCommAddrIdParamIndex(KFirstIndex  + 5);
        User::LeaveIfError(stmnt.BindInt(KCommAddrIdParamIndex, aCommAddrId) );
        }

    User::LeaveIfError(stmnt.Exec() );
    CleanupStack::PopAndDestroy(&stmnt);
    }

/**
Deletes individual communication addresses from the database. In other words, deletes at
the sub-contact item level rather than deleting everything with a specific contact item ID.
*/
void CPplCommAddrTable::DeleteSingleCommAddrL(TInt aCommAddrId, TBool& aLowDiskErrorOccurred)
	{
	RSqlStatement stmnt;
	CleanupClosePushL(stmnt);
	stmnt.PrepareL(iDatabase, iSingleDeleteStmnt->SqlStringL() );
	const TInt KCommAddrIdParamIndex(KFirstIndex); // first and only parameter in the query
	User::LeaveIfError(stmnt.BindInt(KCommAddrIdParamIndex, aCommAddrId ) );
	TInt err = stmnt.Exec();
	CleanupStack::PopAndDestroy(&stmnt);

	if (err == KErrDiskFull)
		{
		aLowDiskErrorOccurred = ETrue;
		}
	else
		{
		User::LeaveIfError(err);
		}
	}

/**
Deletes information about group for the passed contact item id

@param aItemId contact item id
@param aLowDiskErrorOccurred out parameter; will be set to ETrue if there was a deletion in 
		low disk condition
*/
void CPplGroupsTable::DeleteItemL(TContactItemId aItemId, TBool& aLowDiskErrorOccurred)
	{
	RSqlStatement stmnt;
	CleanupClosePushL(stmnt);
	stmnt.PrepareL(iDatabase, iDeleteStmnt->SqlStringL() );

	const TInt KGroupIdIndex(KFirstIndex); 			// first parameter in query...	
	const TInt KMemberIdIndex(KGroupIdIndex + 1); 	// ...and the second parameter
	User::LeaveIfError(stmnt.BindInt(KGroupIdIndex, aItemId) );
	User::LeaveIfError(stmnt.BindInt(KMemberIdIndex, aItemId) );
	TInt err = stmnt.Exec();
	CleanupStack::PopAndDestroy(&stmnt);

	if (err == KErrDiskFull)
		{
		aLowDiskErrorOccurred = ETrue;
		}
	else
		{
		User::LeaveIfError(err);
		}
	}

/**
Persist the items belonging to curent group into group table

@param aGroup referece to a contact group
*/
void CPplGroupsTable::WriteGroupMembersL(const CContactItem& aGroup)
	{
	if (aGroup.Type() != KUidContactGroup)
		{
		return;
		}

	const TContactItemId KGroupId(aGroup.Id() );

	// make sure we clear out any previous, out-of-date data
	TBool lowDiskErr(EFalse);
	DeleteItemL(KGroupId, lowDiskErr);
	if (lowDiskErr)
		{
		User::Leave(KErrDiskFull);
		}

	// build the RSqlStatement
	RSqlStatement stmnt;
	CleanupClosePushL(stmnt);
	stmnt.PrepareL(iDatabase, iInsertStmnt->SqlStringL() );
	const TInt KGroupIdIndex(KFirstIndex); 			// first parameter in query...	
	const TInt KMemberIdIndex(KGroupIdIndex + 1); 	// ...and the second parameter
	
	// copy and sort the member id array so we can see if there are duplicates
	const CContactIdArray* contactIdArray = static_cast<const CContactGroup&>(aGroup).ItemsContained();  //does not take the ownership
	
	const TInt arrayCount = contactIdArray->Count();
	CArrayFixFlat<TContactItemId>* sortedList = new(ELeave) CArrayFixFlat<TContactItemId>(KArrayGranularity);	
	CleanupStack::PushL(sortedList);
    for(TInt loop = 0;loop < arrayCount; ++loop)
    	{
    	sortedList->AppendL((*contactIdArray)[loop]);		
    	}
	TKeyArrayFix key(0,ECmpTInt);
	sortedList->Sort(key);

	// insert the group-member relationships
	const TInt KCountStmntParamIndex(KFirstIndex); // first and only parameter in query
	const TInt listLen(sortedList->Count() );
	TInt lastId(0);
	for (TInt i = 0; i < listLen; ++i)
		{
		TInt itemId((*sortedList)[i]);
		
		//check if a contact item with itemId id really exists in contact database
		RSqlStatement countStmnt;
		CleanupClosePushL(countStmnt);
		countStmnt.PrepareL(iDatabase, iCountContactsStmnt->SqlStringL() );
		User::LeaveIfError(countStmnt.BindInt(KCountStmntParamIndex, itemId) );
		TInt count = 0;
		TInt err = KErrNone;
		if((err = countStmnt.Next() ) == KSqlAtRow)
			{
			count = countStmnt.ColumnInt(iCountContactsStmnt->ParameterIndex(KSqlCount) );
			}
		else
			{
			User::LeaveIfError(err);	
			}	

		if(count == 0) 
			{
			User::Leave(KErrNotFound);	
			}
		CleanupStack::PopAndDestroy(&countStmnt);
			
		// only insert this if we haven't already seen it
		if (itemId != lastId || i == 0)
			{
			User::LeaveIfError(stmnt.BindInt(KGroupIdIndex, KGroupId) );
			User::LeaveIfError(stmnt.BindInt(KMemberIdIndex, itemId) );
			User::LeaveIfError(stmnt.Exec() );
			User::LeaveIfError(stmnt.Reset() );
			}
		lastId = itemId;
		}

	CleanupStack::PopAndDestroy(2, &stmnt); // and sortedList
	}

TInt CMdSSqLiteConnection::ExecuteL( const TDesC& aCommand,
                                     const RRowData& aVariables,
                                     RMdsStatement* aStatement )
    {
    TInt err = KErrNone;

    if ( aVariables.Size() == 0 )
        {
        // no variables
        err = iMdeSqlDb.Exec( aCommand );

		if ( err < KErrNone )
        	{
            _LIT( KMdSExec, "Exec (no variables)" );
            TraceAndLeaveL( KMdSExec, err );
            }
		}
    else if ( aStatement )
    	{
        if ( aStatement->iPrepared == EFalse )
            {
            err = aStatement->iStatement.Prepare( iMdeSqlDb, aCommand );

        	if ( err < KErrNone )
                {
                _LIT( KMdSPrepare, "Prepare" );
                TraceAndLeaveL( KMdSPrepare, err );
                }
            aStatement->iPrepared = ETrue;
            }
        else
            {
            err = aStatement->iStatement.Reset();
         	if ( err < KErrNone )
                {
                _LIT( KMdSResume, "Resume" );
                TraceAndLeaveL( KMdSResume, err );
                }
            }
           
        DoBindL( aStatement->iStatement, aVariables );
        err = aStatement->iStatement.Exec();

        if ( err < KErrNone )
            {
            aStatement->iStatement.Reset();
            aStatement->iPrepared = EFalse;
			_LIT( KMdSExec, "Exec" );
            TraceAndLeaveL( KMdSExec, err );
            }
    	}
    else
        {
        RSqlStatement mdeSqlDbStmt;
        CleanupClosePushL( mdeSqlDbStmt );
        err = mdeSqlDbStmt.Prepare( iMdeSqlDb, aCommand );

        if ( err < KErrNone )
            {
            _LIT( KMdsPrepare, "Prepare (no statement)" );
            TraceAndLeaveL( KMdsPrepare, err );
            }

        DoBindL( mdeSqlDbStmt, aVariables );
        
        err = mdeSqlDbStmt.Exec();

        if ( err < KErrNone )
            {
            _LIT( KMdsExec, "Exec (no statement)" );
            TraceAndLeaveL( KMdsExec, err );
            }

        CleanupStack::PopAndDestroy( &mdeSqlDbStmt );
        }
    return err;
    }