dbmsengine.cpp

SymbianOS的DBMS实例 详细的代码揭示SymbianOS数据库管理系统的使用 很规范

        view.NextL();
		index ++;
        }

	view.Close();

    CleanupStack::PopAndDestroy(); // pop and close database
    CleanupStack::PopAndDestroy(); // pop and close dbSession

	return ETrue;
}

/*
-----------------------------------------------------------------------------

    CDBMSengine::UpdateRecordL()
	Description: Update a record into database
	
	Comments: iName must be specified outside first since it is used as the key to 
			  update the specified record in the database.
    Returns values: N/A
-----------------------------------------------------------------------------
*/
void CDBMSengine::UpdateRecordL()
{
	TInt ret;
	RDbs dbSession;
	User::LeaveIfError( dbSession.Connect() );
	CleanupClosePushL( dbSession );
	RDbNamedDatabase database;
	// User::LeaveIfError( database.Open( dbSession, KDatabaseName ) );	
	ret = database.Open( dbSession, KDatabaseName );
	if ( ret != KErrNone )
		{
		CEikonEnv::Static()->InfoWinL( _L("Information"), _L("Please create database first!"));
		CleanupStack::PopAndDestroy(); // dbSession
		return;
		}

    CleanupClosePushL( database );

#ifdef USE_DBMS_API

    _LIT(KSQLUpdate, "SELECT name, sex, birthday, personal_id FROM employees WHERE name = ");

	TBuf<256> buf;
	buf.Append( KSQLUpdate );
	buf.Append( KSQLQuote1 );
	buf.Append( iOldName );
	buf.Append( KSQLQuote1 );
	RDbView view;
    view.Prepare(database, TDbQuery(buf) );
    view.EvaluateAll();
    view.FirstL();
    view.UpdateL();
    view.SetColL( 1, iName );
    view.SetColL( 2, iSex );
    view.SetColL( 3, iBirthday );
    view.SetColL( 4, iPersonal_id );
    view.PutL();
	view.Close();
#else
	// update-statement : UPDATE table-name SET update-column-comma-list 
	// [ WHERE search-condition ]

    // _LIT(KSQLUpdate, "UPDATE employees SET personal_id = 1, sex = 'female' WHERE name = 'Liu XiaoGuo'");
    _LIT( KSQLUpdate, "UPDATE employees SET " ); 
	_LIT( KWhere, "WHERE name = " );
	_LIT( KPersonalId, "personal_id = " );
	_LIT( KBirthday, "birthday = " );
	_LIT( KSex, "sex = " );
	_LIT( KName1, "name = " );

    TBuf<KMaxSQLLength>  SQLStatement;
    SQLStatement = KSQLUpdate;
	SQLStatement.Append( KName1 );			// Update "name"
	SQLStatement.Append( KSQLQuote1 ); 
	SQLStatement.Append( iName );
	SQLStatement.Append( KSQLQuote2 );
    SQLStatement.Append( KSex );			// Update "sex"
	SQLStatement.AppendNum( iSex );		
	SQLStatement.Append( KComma );
	SQLStatement.Append( KBirthday );		// Update "Birthday"
	SQLStatement.Append( KSQLQuote1 );
	SQLStatement.Append( iBirthday );
	SQLStatement.Append( KSQLQuote2 );
	SQLStatement.Append( KPersonalId );		// Update "Personal ID'
	SQLStatement.AppendNum( iPersonal_id );
	SQLStatement.Append( KWhere );
	SQLStatement.Append( KSQLQuote1 );
	SQLStatement.Append( iOldName );
	SQLStatement.Append( KSQLQuote1 );
	
	User::LeaveIfError( database.Execute( SQLStatement ) );
#endif		

    CleanupStack::PopAndDestroy(); // pop and close database
    CleanupStack::PopAndDestroy(); // pop and close dbSession
}


/*
-----------------------------------------------------------------------------

    CDBMSengine::DeleteRecordL()
	
	NOTE: iName must be specified outside first since it is used as the key to 
		  update the specified record in the database.

    Returns values: N/A

-----------------------------------------------------------------------------
*/
void CDBMSengine::DeleteRecordL()
{
	RDbs dbSession;
	User::LeaveIfError( dbSession.Connect() );
	CleanupClosePushL( dbSession );
	RDbNamedDatabase database;
	User::LeaveIfError( database.Open( dbSession, KDatabaseName ) );
    CleanupClosePushL( database );

#ifdef USE_DBMS_API
	_LIT(KSelect,"SELECT name from employees WHERE name = ");

	TBuf<256> buf;
	buf.Append( KSelect );
	buf.Append( KSQLQuote1 );
	buf.Append( iName );
	buf.Append( KSQLQuote1 );
	database.Begin();
	RDbView view;
	view.Prepare( database, buf );
	view.EvaluateAll();
	while (view.NextL())
		view.DeleteL();
	view.Close(); 
	database.Commit();
#else
	
	_LIT( KSQLDelete,"DELETE FROM employees WHERE name = " ); 
	
	TBuf< KMaxSQLLength >  SQLStatement;
   	SQLStatement = KSQLDelete;
	SQLStatement.Append( KSQLQuote1 );
	SQLStatement.Append( iName );
	SQLStatement.Append( KSQLQuote1 );

	User::LeaveIfError( database.Execute( SQLStatement) );
#endif

    CleanupStack::PopAndDestroy(); // pop and close database
    CleanupStack::PopAndDestroy(); // pop and close dbSession
}

// The following function is used to display a message.
void CDBMSengine::MessageBox(const TDesC &aDes)
{
    // Launch Info window with the specified title and message.
    CEikonEnv* iEnv = CEikonEnv::Static();
	iEnv->AlertWin( aDes );
}

/*
-----------------------------------------------------------------------------

    CDBMSengine::GetFirstRecord()
	
	Description: Get the first record in database.
	Comments:

    Returns values: if it is found, return true, if not, return false.

-----------------------------------------------------------------------------
*/
TBool CDBMSengine::GetFirstRecordL()
{
	if ( !iIsDatabaseOpened )
		return EFalse;

	_LIT(KSQLQuery, "SELECT name, personal_id, sex, birthday FROM employees ORDER BY personal_id");

	iView.Prepare( iDatabase, TDbQuery(KSQLQuery));
	iView.EvaluateAll();

	iView.FirstL();

	TBool found = EFalse;
   
	if ( iView.AtRow() )
		{
		iView.GetL();
		iName = iView.ColDes( 1 );
        iPersonal_id = iView.ColUint32( 2 );
		iSex = iView.ColUint32( 3 );
		iBirthday = iView.ColDes( 4 );
		found = ETrue;
        }

	return found;

}

/*
-----------------------------------------------------------------------------

    CDBMSengine::GetNextRecordL()
	
	Description: Get next record from database
	Comments:

    Returns values: N/A

-----------------------------------------------------------------------------
*/
TBool CDBMSengine::GetNextRecordL()
{
	if ( !iIsDatabaseOpened )
		return EFalse;

	// If view is already in its end, return false
   	if ( iView.AtEnd() )
		return EFalse;

	TBool found = EFalse;

    iView.NextL();
	
	if ( iView.AtRow() )
		{
		iView.GetL();
		iName = iView.ColDes( 1 );
        iPersonal_id = iView.ColUint32( 2 );
		iSex = iView.ColUint32( 3 );
		iBirthday = iView.ColDes( 4 );
		found = ETrue;
        }

	return found;
}

/*
-----------------------------------------------------------------------------

    CDBMSengine::GetNextRecordL()
	
	Get previous record from database
	Comments:

    Returns values: N/A

-----------------------------------------------------------------------------
*/
TBool CDBMSengine::GetPreviousRecordL()
{
	if ( !iIsDatabaseOpened )
		return EFalse;

	// If the view is already at its beginning return false.
	if ( iView.AtBeginning() )
		return EFalse;

	TBool found = EFalse;
    iView.PreviousL();
	
	if ( iView.AtRow() )
		{
		iView.GetL();
		iName = iView.ColDes( 1 );
        iPersonal_id = iView.ColUint32( 2 );
		iSex = iView.ColUint32( 3 );
		iBirthday = iView.ColDes( 4 );
		found = ETrue;
        }

	return found;
}

/*
-------------------------------------------------------------------------------

    ReadRecordTitlesL(CDesC16Array &aDescriptorList)

    Description: read all the tiles and other fields into the descriptor (input)
	Comments:

    Return value: ETrue if it is successful
				  EFalse if it is not successful
-------------------------------------------------------------------------------
*/
TBool CDBMSengine::ReadRecordTitlesL(CDesC16Array &aDescriptorList)
{
	RDbs dbSession;
	User::LeaveIfError( dbSession.Connect() );
	CleanupClosePushL( dbSession );
	RDbNamedDatabase database;

	TInt ret = database.Open( dbSession, KDatabaseName );
	if ( ret != KErrNone )
		{
		CleanupStack::PopAndDestroy(); // dbSession
		return EFalse;
		}

    CleanupClosePushL( database );

	// Retrieve all the information in the database
	_LIT(KSQLQuery, "SELECT name, personal_id, sex, birthday FROM employees ORDER BY personal_id");

	RDbView view;
	view.Prepare( database, TDbQuery(KSQLQuery));
	view.EvaluateAll();
   
	TBuf< KDbNameLen > name;
	TBuf< KDbBirthday >	birthday;
	TInt sex;
	
	TBool b = view.FirstL();
	if ( !b )
		{
		view.Close();

		CleanupStack::PopAndDestroy(); // pop and close database
		CleanupStack::PopAndDestroy(); // pop and close dbSession
		view.Close();
		return EFalse;
		}

    while (view.AtRow())
		{
		view.GetL();
		name = view.ColDes(1);		
		birthday = view.ColDes(4);
		
		// Add to the descriptor list
        aDescriptorList.AppendL( name );
		
		// We need to reformat the birthday here.
		TTime time( birthday );
		_LIT16(KFormat8,"%+0*d");
		TDateTime datetime = time.DateTime();
		TBuf< 40 >	buf;
		TBuf< 5 >	day;
		TBuf< 5 >	month;
		month.Format( KFormat8, 2, datetime.Month() + 1 );
		day.Format( KFormat8, 2, datetime.Day() + 1 );
		buf.Format( _L("%d"), datetime.Year() );
		buf.Append( _L("/") );
		buf.Append( month );
		buf.Append( _L("/") );
		buf.Append( day );

		// Add the sex to the description as well
		sex = view.ColUint32( 3 );
		if( sex )
			buf.Append( _L("  female" ) );
		else
			buf.Append( _L("  male" ) );

        aDescriptorList.AppendL( buf );
        view.NextL();
        }

	view.Close();

    CleanupStack::PopAndDestroy(); // pop and close database
    CleanupStack::PopAndDestroy(); // pop and close dbSession

	return ETrue;
}

/*
-------------------------------------------------------------------------------

    GetSex();

    Description: Returns the sex value.
	Comments:

    Return value: return the sex value

-------------------------------------------------------------------------------
*/
TInt CDBMSengine::GetSex() const
{
	return iSex;
}

/*
-------------------------------------------------------------------------------

    SetSex( TInt aSex );

    Description: Set the sex value into the engine
	Comments:

    Return value: N/A

-------------------------------------------------------------------------------
*/
void CDBMSengine::SetSex(TInt aSex)
{
	iSex = aSex;
}

/*
-------------------------------------------------------------------------------

    GetPersonalId()

    Description: Get the personal ID value.
	Comments:

    Return value: returns personal ID

-------------------------------------------------------------------------------
*/
TInt CDBMSengine::GetPersonalId() const
{
	return iPersonal_id;
}

/*
-------------------------------------------------------------------------------

    SetPersonalId(TInt aPersonalId)

    Description: Set perosona ID.
	Comments:

    Return value: N/A

-------------------------------------------------------------------------------
*/
void CDBMSengine::SetPersonalId(TInt aPersonalId)
{
	iPersonal_id = aPersonalId;
}

/*
-------------------------------------------------------------------------------

    GetName(TDes& aName)

    Description: Get the name field
	Comments:

    Return value: N/A

-------------------------------------------------------------------------------
*/
void CDBMSengine::GetName(TDes& aName) const
{
	aName = iName;
}

/*
-------------------------------------------------------------------------------

    SetName(TDes& aName)

    Description: Set the name field
	Comments:

    Return value: N/A

-------------------------------------------------------------------------------
*/
void CDBMSengine::SetName(TDes& aName)
{
	iName = aName;
}


/*
-------------------------------------------------------------------------------

    GetBirthday(TDes& aBirthday)

    Description: Get "birthday" value
	Comments:

    Return value: N/A

-------------------------------------------------------------------------------
*/
void CDBMSengine::GetBirthday(TDes& aBirthday) const
{
	aBirthday = iBirthday;
}


/*
-------------------------------------------------------------------------------

    SetBirthday(TDes& aBirthday)

    Description: set "birthday" value
	Comments:

    Return value: N/A

-------------------------------------------------------------------------------
*/
void CDBMSengine::SetBirthday(TDes& aBirthday)
{
	iBirthday = aBirthday;
}

/*
-------------------------------------------------------------------------------

    IsDatabaseCreatedL()

    Description: Check whether a named database has been created or not
	Comments:

    Return value: ETrue if the database has been created
				  EFalse if the database has not been created
-------------------------------------------------------------------------------
*/
TBool CDBMSengine::IsDatabaseCreatedL()
{
	RDbs dbSession;
	User::LeaveIfError( dbSession.Connect() );
	CleanupClosePushL( dbSession );
	RDbNamedDatabase database;
	// User::LeaveIfError( database.Open( dbSession, KDatabaseName ) );

	TInt ret = database.Open( dbSession, KDatabaseName );
	if ( ret != KErrNone )
		{
		CleanupStack::PopAndDestroy(); // dbSession
		return EFalse;
		}
	else
		{	
		database.Close();
		CleanupStack::PopAndDestroy(); // dbSession
		return ETrue;
		}
}