C++ RSqlStatement类代码示例

TBool CPredictiveSearchSynchronizer::CheckIfPredSearchTableExistsL(
	const TDesC& aTableName) const
	{
	PRINT1(_L("CPredictiveSearchSynchronizer::CheckIfPredSearchTableExistsL table='%S'"),
		   &aTableName);

	_LIT(KSelectFirstTableFormat,
		 "SELECT name FROM sqlite_master WHERE type='table' AND name='%S';");

	TInt bufSize = KSelectFirstTableFormat().Length() +
				   aTableName.Length();
	HBufC* select = HBufC::NewLC(bufSize);
	select->Des().AppendFormat(KSelectFirstTableFormat, &aTableName);
	RSqlStatement stmnt;
	CleanupClosePushL(stmnt);
    stmnt.PrepareL(iDatabase, *select);

	TBool tableExists = (stmnt.Next() == KSqlAtRow);

	CleanupStack::PopAndDestroy(&stmnt);
	CleanupStack::PopAndDestroy(select);

	PRINT1(_L("CPredictiveSearchSynchronizer::CheckIfPredSearchTablesExistL return %d"),
		   tableExists);
	return tableExists;
	}

// -----------------------------------------------------------------------------
// CUpnpSecurityDbConnection::GetAllFilenamesL
// Get all filenames. 
// -----------------------------------------------------------------------------
//
void CUpnpSecurityDbConnection::GetAllFilenamesL(
    RPointerArray<HBufC>& aFilenameArray )
    {
    TInt err;
    RSqlStatement statement;
    User::LeaveIfError( statement.Prepare( iDatabase, KUpnpSecSqlSelectAllFiles ) );
    CleanupClosePushL( statement );

    TInt columnIndex = statement.ColumnIndex( KUpnpSecSqlFilename );

    while ( (err = statement.Next()) == KSqlAtRow )
        {
        TPtrC data = statement.ColumnTextL( columnIndex );
        HBufC* filename = data.AllocLC();
        aFilenameArray.AppendL( filename );
        CleanupStack::Pop( filename );
        }

    if ( err != KSqlAtEnd )
        {
        User::LeaveIfError( err );
        }

    CleanupStack::PopAndDestroy( &statement );
    }

/**
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);
		}
	}

/** 
Retrieve the field text for the given field type and contact item ID.

The behaviour differs when a specific field type is not given i.e. when
aFieldType is KUidContactFieldMatchAll:

- First tries to find an email for the given contact item ID.
- If there is no email then it retrieves the first entry in Fast Access fields
for the given contact item ID.
- If there is no Fast Access fields then it retrieves the first entry in the
text fields blob for the given contact item ID.

Text for all other field types are retrieved from the text fields blob.

The caller must determine that the given contact item ID exists before calling
this method.
*/
void CCntPplViewSession::TextFieldL(RSqlStatement& aSqlStatement, const CCntSqlStatement& aCntSqlStmt, const CContactTemplate& aSystemTemplate, TFieldType aFieldType, TDes& aText)
	{
	TPtrC8 textHeader;
	aSqlStatement.ColumnBinary(aCntSqlStmt.ParameterIndex(KContactTextFieldHeader()), textHeader);
	RDesReadStream textHeaderStream(textHeader);
	CleanupClosePushL(textHeaderStream);	
    CEmbeddedStore* textHeaderStore = CEmbeddedStore::FromLC(textHeaderStream);
    
	RStoreReadStream textHeaderStoreStream;
   	textHeaderStoreStream.OpenLC(*textHeaderStore,textHeaderStore->Root());

	TPtrC textFieldPtrC = aSqlStatement.ColumnTextL(aCntSqlStmt.ParameterIndex(KContactTextFields()));
	HBufC* textFieldsBuf = textFieldPtrC.AllocLC();
	
	if(aFieldType == KUidContactFieldMatchAll)
		{
		if (TCntPersistenceUtility::FindTxtFieldInTextBlobL(textHeaderStoreStream, textFieldsBuf, aSystemTemplate, aFieldType, aText) == EFalse)
			{
			CContactDatabase::TTextFieldMinimal	fastAccessText;
			if (HasTxtFieldInFastAccessFieldsL(aSqlStatement, aCntSqlStmt, fastAccessText))
				{
				aText.Copy(fastAccessText);		
				}
			}
		} 
	else
		{
		if (SpecificTxtFieldInFastAccessFieldsL(aSqlStatement, aCntSqlStmt, aFieldType, aText) == EFalse)
			{
			TCntPersistenceUtility::FindTxtFieldInTextBlobL(textHeaderStoreStream, textFieldsBuf, aSystemTemplate, aFieldType, aText);
			}
		}
		
	CleanupStack::PopAndDestroy(4, &textHeaderStream); //textHeaderStore, textHeaderStream, textHeaderStoreStream, textFieldsBuf
	}

TBool CPredictiveSearchSynchronizer::ReadMailAddressesL(CContactItem& aContact)
	{
	PRINT(_L("CPredictiveSearchSynchronizer::ReadMailAddressesL"));

	// SELECT value FROM comm_addr
	//	 WHERE contact_id = [contact id value] AND type = [type value];
    _LIT(KSelectMailAddrFormat, "SELECT %S FROM %S WHERE %S = %d AND %S = %d;");
	const TInt KContactIdLength = 10;
	const TInt KCommAddrTypeLength = 2; // CPplCommAddrTable::EEmailAddress is enum
	TInt bufSize = KSelectMailAddrFormat().Length() +
				   KCommAddrValue().Length() +
				   KSqlContactCommAddrTableName().Length() +
				   KCommAddrContactId().Length() +
				   KContactIdLength +
				   KCommAddrType().Length() +
				   KCommAddrTypeLength;
	HBufC* sqlStatement = HBufC::NewLC(bufSize);
	sqlStatement->Des().AppendFormat(KSelectMailAddrFormat,
		&KCommAddrValue,
		&KSqlContactCommAddrTableName,
		&KCommAddrContactId,
		aContact.Id(),
		&KCommAddrType,
		CPplCommAddrTable::EEmailAddress);

	RSqlStatement stmnt;
	CleanupClosePushL(stmnt);
	PRINT1(_L("prepare SQL statement:%S"), sqlStatement);
    stmnt.PrepareL(iDatabase, *sqlStatement);

	const TInt KValueIndex = 0;
	TBool foundMailAddress(EFalse);
	TInt err(KErrNone);
    while ((err = stmnt.Next()) == KSqlAtRow)
        {
		TPtrC value;
		if (stmnt.ColumnText(KValueIndex, value) == KErrNone)
			{
			PRINT2(_L("  id=%d, found mail address=%S"), aContact.Id(), &value);
			CContactItemField* field =
				CContactItemField::NewLC(KStorageTypeText, KUidContactFieldEMail);
			CContactTextField* textfield = field->TextStorage();
			textfield->SetTextL(value);
			aContact.AddFieldL(*field); // Takes ownership
			CleanupStack::Pop(field);
			foundMailAddress = ETrue;
			}
        }

    if (err != KSqlAtEnd)
        {
		PRINT1(_L("CPredictiveSearchSynchronizer::ReadMailAddressesL SQL err=%d"), err);
        User::Leave(err);
        }
    CleanupStack::PopAndDestroy(&stmnt);
	CleanupStack::PopAndDestroy(sqlStatement);
	PRINT1(_L("CPredictiveSearchSynchronizer::ReadMailAddressesL return %d"), foundMailAddress);
	return foundMailAddress;
	}

// -----------------------------------------------------------------------------
// 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 );    
    }

// -----------------------------------------------------------------------------
// CUpnpSecurityDbConnection::Validate
// Check validity of database.
// -----------------------------------------------------------------------------
//
TBool CUpnpSecurityDbConnection::Validate()
    {
    TBool result = EFalse;
    RSqlStatement statement;
    TInt err = statement.Prepare( iDatabase, KUpnpSecSqlValidateTableExistence );
    result = !err && (statement.Next() == KSqlAtRow);
    statement.Close();
    return result;
    }

/**
 * 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;
}

/**
Create view contact object based on given sql statement.

@return CViewContact object or NULL the contact is not found.
*/
CViewContact* CCntPplViewSession::CreateViewItemL(RSqlStatement& aSqlStmt, const CCntSqlStatement& aCntSqlStmt, const TContactViewPreferences& aViewPrefs)
	{
	if (! iContactsFile.IsOpened())
		{
		User::Leave(KErrInUse);
		}
	
	CViewContact* viewContact = InitialiseViewItemL(aSqlStmt, aCntSqlStmt, aViewPrefs);
	if(!viewContact)
		{
		//Couldn't find the contact.
		return NULL;
		}
	
	CleanupStack::PushL(viewContact);
	if(viewContact->ContactType() == CViewContact::EContactItem)
		{
		FillViewItemL(*viewContact, aSqlStmt, aViewPrefs);
		}
	
	TUid typeUid = GetContactFieldMatchUid(*viewContact, aViewPrefs);	
	if(typeUid != KUidContactFieldNone)
		{
		RSqlStatement contactSqlStmt;
		CleanupClosePushL(contactSqlStmt);
		
		/* we have to reload the row from SQL database to cache in text header and text blob */ 	
		contactSqlStmt.PrepareL(iContactsFile.NamedDatabase(),  iSqlSmtSelectAllFieldsById.SqlStringL());
		User::LeaveIfError(contactSqlStmt.BindInt(KFirstIndex, viewContact->Id())); //Bind item id into the condition.	
		
		TInt err = contactSqlStmt.Next();
		if(err == KSqlAtEnd)
			{
			//Should never be here, we found it in InitialiseViewItemL 
			//but couldn't find the same contact in same database.
			CleanupStack::PopAndDestroy(viewContact);
			return NULL;
			}			
			
		User::LeaveIfError(err);
		
		/* set first field with possible content for group or unsorted contact */	
		CContactDatabase::TTextFieldMinimal buf;
		TextFieldL(contactSqlStmt, iSqlSmtSelectAllFieldsById, iContactProperties.SystemTemplateL(), typeUid, buf);
		viewContact->SetFirstFieldForBlankContactL(buf);
		CleanupStack::PopAndDestroy(&contactSqlStmt);
		} //if(typeUid != 0)
	
	CleanupStack::Pop(viewContact);
	return viewContact;
	}

/**
GetListForItemL has a dual nature. If aIsGroup is ETrue, a list of contact items belonging to
specified group is returned. Otherwise a list of group ids to which contact id belongs is returned.

@param aItemId contact item id
@param aIsGroup ETrue if the method will fill a group.
*/
CContactIdArray* CPplGroupsTable::GetListForItemL(TContactItemId aItemId, TBool aIsGroup)
	{
	/*
	// Check if group membership information was not requested or if the item
	// is not derived from CContactItemPlusGroup.
	if (!(aType == KUidContactGroup	  || aType == KUidContactCard ||
		  aType == KUidContactOwnCard || aType == KUidContactICCEntry) )
		{
		return NULL; 
		}
	*/	

	// build the RSqlStatement
	RSqlStatement stmnt;
	CleanupClosePushL(stmnt);
	TInt idIndex;

	// build the CCntSqlStatement statement
	const TInt KWhereParamIndex(KFirstIndex); // only one parameter in the query
	if (aIsGroup)
		{
		// group -> select members
		stmnt.PrepareL(iDatabase, iSelectMembersStmnt->SqlStringL() );
		User::LeaveIfError(stmnt.BindInt(KWhereParamIndex, aItemId ) );
		idIndex = stmnt.ColumnIndex(KGroupContactGroupMemberId() );
		}
	else
		{
		// member -> select groups
		stmnt.PrepareL(iDatabase, iSelectGroupsStmnt->SqlStringL() );
		User::LeaveIfError(stmnt.BindInt(KWhereParamIndex, aItemId ) );
		idIndex = stmnt.ColumnIndex(KGroupContactGroupId() );
		}
	User::LeaveIfError(idIndex);
	// fetch the list of any matching ids
	CContactIdArray* items = CContactIdArray::NewLC();
	TInt err(KErrNone);
	while ((err = stmnt.Next() ) == KSqlAtRow)
		{
		items->AddL(stmnt.ColumnInt(idIndex) );
		}

	// leave if we didn't complete going through the results properly
	if(err != KSqlAtEnd)
		{
		User::Leave(err);
		}
	
	CleanupStack::Pop(items);
	CleanupStack::PopAndDestroy(&stmnt);
	return items;
	}

/**
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);
		}
	}

/**
Checks if the given field type is in the Fast Access fields and if so the field text
is returned via the parameter aText.

@param aCntItemId The contact ID for which the specific text field is required.
@param aText On return contains the field text for the first Fast Access fields
with field type aFieldType.
@param aFieldType The specific field requested.

@return ETrue if the specific fast access text field was found, EFalse otherwise.
*/
TBool CCntPplViewSession::SpecificTxtFieldInFastAccessFieldsL(RSqlStatement& aSelectStmt, const CCntSqlStatement& aCntSqlStmt, const TFieldType aFieldType, TDes& aText)
	{
    const TDesC& KColumnName = TCntPersistenceUtility::GetFastAccessColumnNameById(aFieldType.iUid);
	if(KColumnName.Length() > 0)
		{
		// This is a fast access field.
		TPtrC fastAccessText = aSelectStmt.ColumnTextL(aCntSqlStmt.ParameterIndex(KColumnName));
		TCntPersistenceUtility::CopyMinFieldText(fastAccessText, aText);
		return ETrue;
		}
	return EFalse;
	}

/**
Searches the contacts database to find any contact items with an exact match on the address supplied.

@param aCommAddr A descriptor containing the address to be found in the database.
@param aAddrType The type of addresses that is being sought.
@return An array of contact IDs which match the supplied address.
*/
CContactIdArray* CPplCommAddrTable::MatchNonPhoneAddrL(const TDesC& aCommAddr, TCommAddrType aAddrType)
	{

	// build statement
	RSqlStatement stmnt;
	CleanupClosePushL(stmnt);
	stmnt.PrepareL(iDatabase, iMatchSelectStmnt->SqlStringL() );

	const TInt KValueParamIndex(KFirstParam);					// first parameter in query...
	const TInt KTypeParamIndex(KValueParamIndex + 1);	// ...and the second.
	User::LeaveIfError(stmnt.BindText(KValueParamIndex, aCommAddr) );
	User::LeaveIfError(stmnt.BindInt(KTypeParamIndex, aAddrType) );

	// fetch the list of any matching contact ids
	CContactIdArray* idArray = CContactIdArray::NewLC();
	TInt err(KErrNone);
	const TInt KContactIdIdx(iMatchSelectStmnt->ParameterIndex(KCommAddrContactId() ) );
	while ((err = stmnt.Next() ) == KSqlAtRow)
		{
		idArray->AddL(stmnt.ColumnInt(KContactIdIdx) );
		}

	// leave if we didn't complete going through the results properly
	if(err != KSqlAtEnd)
		{
		User::Leave(err);
		}

	CleanupStack::Pop(idArray);
	CleanupStack::PopAndDestroy(&stmnt);

	return idArray;
	}

/**
@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 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);
		}
	}