Go to Content



[Recipe] Save to/ restore from database

By bada Master | 5324 views | Apr 04, 2011

Using the database

Problem description

You want a flexible and easy way to store and retrieve your application’s data.

The recipe

The following configuration is required for this recipe:

Namespaces used:
Osp::App, Osp::Base, Osp::Io

Header files to #include:
FBase.h, FApp.h, FIo.h

Required privilege level:
NORMAL

Required privilege groups:
NONE

Note: The code in this recipe was written for version 1.0.0 of the bada SDK. For SDK versions 1.0b3 and earlier you will need to include the FApp, FBase and FIo libraries in your project when building for the Simulator. For SDK version 1.0.0 and later, FOsp is the only library required for both Simulator and target.

If your application handles any kind of data that needs to be stored and retrieved easily, then the classes in methods in the Io::File class provide one way of saving and restoring your data. However, a more flexible approach is to use a database, and bada provides SQLite compatible commands using a powerful and lightweight database engine that allows you to create and modify databases on a device using SQL.

In this example we’ve created a simple expenses database that just stores an item amount and description. We let the user view, edit, create and delete items as well as allowing each item to be updated. Some of the information for each item is read into memory while some is just read directly from the database and displayed in the form.

The classes that implement database functionality are Io::Database, Io::DbEnumerator, and Io::DbStatement. All of these are demonstrated in the code examples.

While this is a simple example, it demonstrates most of what you need to do to use a database in your application. We won’t cover the details of displaying the data in a form or creating our own data structures. Instead we’ll focus on the details of creating, reading from, updating and deleting items in a database.

The recipe consists of the following steps:

  • 1. Create the database and a table and add an initial set of rows
  • 2. Read data from the database
  • 3. Update rows in the database
  • 4. Delete rows from the database
1. Creating the database and adding rows

First we create the database. pDatabaseName is a member variable of our DatabaseForm class and is initialized to L"/Home/testingDatabase". We call the Construct method of Database to open the database and pass true as the second parameter so that the database is created if it does not already exist. The Database object is stored in the member variable pDatabase to make it easy to access later.

Next we create our table by sending a SQLite Create Table command:

bool DatabaseForm::CreateDatabase()
{
	String sql;
	result r = E_SUCCESS;

	// create the database if it doesn't exist
	pDatabase = new Database();
	if (pDatabase == null) return false;
	r = pDatabase->Construct(pDatabaseName,true);
	if (IsFailed(r)) return false;
	
	// Create the main table
	sql.Append(L"CREATE TABLE IF NOT EXISTS myTable1 
		         (id INTEGER PRIMARY KEY AUTOINCREMENT, amount DOUBLE, 
               description TEXT)");
   
	r = pDatabase -> ExecuteSql(sql, true);
	if (IsFailed(r)) return false;

	return true;
}

Notice that id is defined as the primary key. This key uniquely identifies a row in the table. We could keep track of the ids ourselves, but it is simpler to let the database engine take care of it by using id INTEGER PRIMARY KEY AUTOINCREMENT. The database engine then automatically generates an integer key for us when we insert a row and ensures that it is unique within the table.

The simplest way to send SQLite statements to the database engine is to use the ExecuteSql() method as in the code above. However, when it comes to adding rows to a table, the SQL statements that we are executing become a little more complex:

bool DatabaseForm::AddItemToDatabase(DatabaseItem* newItem)
{
	
	DbStatement*  pStmt;
	DbEnumerator* pEnum;
	String        statement;
	result        r = E_SUCCESS;

	pDatabase -> BeginTransaction();

	// Prepare the INSERT statement
	statement.Append(L"INSERT INTO myTable1(amount, description) VALUES (?, ?)");
	pStmt = pDatabase -> CreateStatementN(statement);

   // Bind the values from the current entry into this statement
	pStmt->BindDouble(0, newItem->mAmount.ToDouble());
	pStmt->BindString(1, newItem->mDescription);
	
	// Now execute the statement with our values included
	pEnum = pDatabase -> ExecuteStatementN(*pStmt);
	pDatabase -> CommitTransaction();

	delete pEnum;
	delete pStmt;
	return true;
}

To add rows to our table, we need to use the SQL INSERT statement. However, we obviously don’t know what data we need to add to the table in advance, so we need to prepare the INSERT statement and then add the specific values at runtime. The DbStatement class provides us with the flexibility we need. We set up a string containing the following SQL:

INSERT INTO myTable1 (amount, description) VALUES (?, ?)

We then pass the string containing this statement into CreateStatementN() which returns a DbStatement object representing the pre-compiled SQL statement, but with the actual values missing. Notice that there are 2 ‘?’s in the VALUES part of the statement, corresponding to the columns amount and description. The BindDouble() and BindString() methods are used with an index of 0 and 1 in the above code example to replace each ‘?’ with actual data. ExecuteStatementN()sends the complete SQL to the database and adds our row to the table.

The main form with some default data is shown in Figure 1.

Figure 1. We set up our database with some default data

2. Reading from the database

We read the data from the database as follows. Here we’ll store the values of a few columns in our own internal data structure. The rest we’ll retrieve from the database when we need it, using the id:

void DatabaseForm::ReadItemsFromDatabase()
{
	DbEnumerator* pEnum;
  	String sql;
	String statement;
	
	sql.Append(L"select id, amount, description from myTable1");
	pEnum = pDatabase -> QueryN(sql);
	String description;
	double amount;
	int	   id;

   while( pEnum->MoveNext()== E_SUCCESS )
	{
		pEnum->GetIntAt(0, id);
		pEnum->GetDoubleAt(1, amount);
		pEnum->GetStringAt(2, description);
		
		// Store the item in our data structure
		DatabaseItem* newItem = new DatabaseItem(description, amount);
		newItem->SetIndex(id);
		// Display the item in our user interface using our own method
		AddItemToList(newItem);
		}
	if (pEnum != null)
		delete pEnum;
}

Retrieving information from the database is done using the SQL SELECT statement:

SELECT id, amount, description from myTable1

This retrieves the amount, description and id from all the rows in our table. To send a SQL query, we use the QueryN() method which returns its result as a DbEnumerator object. In our code the DbEnumerator result consists of the number of rows matching the query with the columns we specified: id, amount. The MoveNext() function is used to move through the rows returned by the SELECT query and we use the GetIntAt(), GetStringAt(), and GetDoubleAt() methods to retrieve the values from each column. This data is then stored in our data structure and displayed in a form. We use the DatabaseItem class to represent each item in the database or each row in the table. The items are displayed in a list within the form (as shown in Figure 1) and are added to the list as they're read in.

3. Updating data in the database

If an item is updated, we read the data from the form and then update the database using the following code:

	DbEnumerator* pEnum;
	DbStatement*  pStmt;
	String statement;
	pDatabase -> BeginTransaction();
	
	// Prepare the update statement
	statement.Append(L"UPDATE myTable1 SET amount = ?,description = ? WHERE id =?");
	pStmt = pDatabase -> CreateStatementN(statement);
	
	// Bind our SQL statement to the updated values
	// pCurrentItem is the DatabaseItem to be updated
	pStmt->BindDouble(0, pCurrentItem->mAmount.ToDouble());
	pStmt->BindString(1, pCurrentItem->mDescription);

	// Ensure that we update the item with the correct index
	pStmt->BindInt(2, pCurrentItem->GetIndex());
	
	// Execute the update
	pEnum = pDatabase -> ExecuteStatementN(*pStmt);
	myDatabase.CommitTransaction();

The Update query is: Update myTable1 SET amount = ?, description = ? Where id = ?

As with the Insert query, we prepare this statement using CreateStatementN() and then use BindDouble(), BindString(), and BindInt() (for the id to retrieve) to complete the query with the updated data. We then call ExecuteStatementN() to update the specified row in the database.

4. Deleting rows

The last type of functionality we need to implement is deleting items from the database. This is actually quite simple, as shown below:

void DetailForm::DeleteCurrentItem()
{
	DbEnumerator* pEnum;
	DbStatement*  pStmt;
	String statement;
	
	pDatabase -> BeginTransaction();

	statement.Append(L"DELETE FROM myTable1 WHERE id = ?");
	pStmt = pDatabase -> CreateStatementN(statement);

	pStmt->BindInt(0, pCurrentItem->GetIndex());
	pEnum = pDatabase -> ExecuteStatementN(*pStmt);
	pDatabase -> CommitTransaction();
}

The SQL we use is: Delete From myTable1 Where id = ?

We just need to bind the id of the item to our prepared Delete statement and then called CreateStatementN() to delete the item.

Hints, pitfalls, and related topics

You will notice that any operation that changes the database, such as an insert or delete, is enclosed by BeginTransaction() and EndTransaction(). There are several performance issues relating to the proper use of transactions which are beyond the scope of this recipe, but which are explained in more detail at the SQLite website (http://sqlite.org/).

Several of the API calls used in this recipe, such as ExecuteStatementN() and CreateStatementN() have an 'N' prefix which indicates that the calling application is responsible for deallocating the object returned by the function. So, you must delete the returned object when you have finished with it to prevent a memory leak.

File Attachments : recipe-UsingDatabase.zip

Did this document help you? Yes | It's good,but ... | Not Helpful