[Bada] Using the Bada Database

The bada platform provides a database tha apps can use to store their data. To work with a database in bada, you will need to use the Database class. The example used in this tutorial looks at setting up and working with a database in bada. The stored data is simply a list of names that are displayed in a list. The source for the demo may be downloaded here.

Using the Database

The demo provides a DataSource class that deals directly with the database. The MainForm uses this to retrieve and store the data. The contents of the DataSource‘s header file:

class DataSource : Database
{
private:
    /* The query for inserting new entries into the database. */
    static const String INSERT_QUERY_FORMAT;

    /* The name for the database. */
    static const String DATABASE_NAME;

    /* Reference to the database. */
    Database *_pDatabase;

public:
    DataSource(void);

    /**
     * Initialise the database (e.g. create the database and tables, if they
     * do not yet exist). The database will have a couple of initial entries.
     */
    result Initialise(void);

    /**
     * Stores an entry in the database.
     */
    result AddEntry(Entry& newEntry);

    /**
     * Retrieve a list of Entries that are available.
     */
    IListT<Entry> *GetEntries(void);

    /**
     * Deletes the database.
     */
    void ClearDatabase();
    virtual ~DataSource();

private:
    /**
     * Internal method that will store the given information in the database.
     */
    result InsertEntry(const String& firstName, const String& lastName);
};

Working with a database

A Database object provides a handle to database and is used to perform all of the database operations. Each database requires a name. The Database‘s Construct method will create the database, if it does not exist. Each database also requires a name. The documentation for the Construct method states that the names of the database needs to begin with “/Home” or “/Home/Share”.

Once created, you will need to set up the tables for the database. This is done by executing CREATE TABLE queries. However, there are a few different methods that will execute SQL queries. For creating tables, the ExecuteSql method can be used. After the tables are created, the demo inserts a couple of initial entries, as shown in the listing below:

...
const String DataSource::DATABASE_NAME = L"/Home/DatabaseDemo";
...

result DataSource::Initialise(void)
{
    result resultCode = E_SUCCESS;
    _pDatabase = new Database();

    // Check if the database already exists.
    bool construct = !Database::Exists(DATABASE_NAME);

    // This will create the database if needed.
    resultCode = _pDatabase->Construct(DATABASE_NAME, true);
    if (resultCode != E_SUCCESS)
    {
        AppLog("Could not create or open database");
        return resultCode;
    }

    if (construct)
    {
        // Need to initialise the database.
        resultCode = _pDatabase->ExecuteSql(L"CREATE TABLE IF NOT EXISTS names "
            "(id INTEGER PRIMARY KEY AUTOINCREMENT, first VARCHAR, "
            "last VARCHAR)", true);
        if (resultCode != E_SUCCESS)
        {
            AppLog("Could not create table");
            return resultCode;
        }

        resultCode = InsertEntry(L"John", L"Doe");
        if (resultCode != E_SUCCESS)
        {
            AppLog("Could not create the first entry.");
            return resultCode;
        }

        resultCode = InsertEntry(L"James", L"Kirk");
        if (resultCode != E_SUCCESS)
        {
            AppLog("Could not create the entry James Kirk");
        }
    }

    return resultCode;
}
...

For inserting entries, the ExecuteSql method is also used:

...

const String DataSource::INSERT_QUERY_FORMAT = L"INSERT INTO names (first, last) VALUES ('::FIRST::', '::LAST::')";

...

result DataSource::AddEntry(Entry& newEntry)
{
    AppLog("Inserting the entry %S %S", newEntry.GetFirstName().GetPointer(),
            newEntry.GetLastName().GetPointer());
    return InsertEntry(newEntry.GetFirstName(), newEntry.GetLastName());
}

result DataSource::InsertEntry(const String& firstName, const String& lastName)
{
    String query = String(INSERT_QUERY_FORMAT);
    query.Replace(L"::FIRST::", firstName);
    query.Replace(L"::LAST::", lastName);
    return _pDatabase->ExecuteSql(query, true);
}

...

To retrieve the data back, you can use either QueryN or ExecuteStatementN. Both will give back the results in the form of a DbEnumerator. The QueryN method is used here:

...
IListT<Entry>* DataSource::GetEntries(void)
{
    LinkedListT<Entry> *entries = new LinkedListT<Entry> ();
    DbEnumerator *rawResults = _pDatabase->QueryN(L"SELECT n.first, n.last "
    "FROM names n");

    if (rawResults != null)
    {
        AppLog("Processing entries");
        while (rawResults->MoveNext() == E_SUCCESS)
        {
            // These are place holders for the first and last name components.
            String firstName;
            String lastName;
            rawResults->GetStringAt(0, firstName);
            rawResults->GetStringAt(1, lastName);

            entries->Add(Entry(firstName, lastName));
        }

        delete rawResults;
    }
    return entries;
}
...

The database can also be deleted using the Delete method:

...
void DataSource::ClearDatabase()
{
    if (_pDatabase != null)
    {
        String name = _pDatabase->GetName();
        delete _pDatabase;
        _pDatabase = NULL;
    }
    Database::Delete(DATABASE_NAME);
}
...

Need to Check Inputs

The demo code allows the user to specify new entries. It then inserts a new entry based on the inputs. However, it currentlydoes NOT perform any validation or encoding. This could have some unexpected consequences, since it would allow the user to manipulate the executed SQL query. For example, the user name simply enter the following into the first name field:

    James', 'Price'); INSERT INTO names (first, last) VALUES ('another', 'one');

The insertion SQL query would look like this:

    INSERT INTO names (first, last) VALUES (' James', 'Price'); INSERT INTO names (first, last) VALUES ('another', 'one');', '::LAST::')

Now, there are two valid SQL statements that would be executed! Indeed executing this results in the entries “James Price” and “another one” being created. Of course, this could have been any other query!

Alternatively, Use a Compiled Statement

One way to get around the problem just mentioned is to use compiled statements. Compiled statements may also be useful for queries that is used repeatedly. To create one, use Database‘s CreateStatementN to create the basic query. The query will usually have question marks (‘?’) to denote parameters that may vary with each execution. The method will return a DbStatement. The values are bound to the parameters in the query. Compiled queries can only be executed with Database‘s ExecuteStatementN method. The following example shows how the example could have made use of a compiled statement in this manner:

    DbStatement *statement = _pDatabase->CreateStatementN(L"INSERT INTO names (first, last) VALUES (?, ?)");

    // The parameters have 0-based index.
    statement->BindString(0, firstName);
    statement->BindString(1, lastName);
    DbEnumerator *rawResults = _pDatabase->ExecuteStatementN(*statement);

The bindings will escape the relevant characters in the given string. This means that if the user tried to manipulate the SQL query as described in Need to Check Inputs, the entire input will be treated as the first name!

Beginning, Commit and Rollback Transactions

The ability to commit or rollback a set of transactions was also NOT used in the example. It is useful for situations where a set of queries must be executed atomically (i.e. all queries are executed successfully and are commmitted or none of them do). To use this feature, first call BeginTransaction before the first query in the set. After executing the rest of the queries, call CommitTransaction to commit them, or use RollbackTransaction to roll them back. The following example shows how the “John Doe” entry is committed and “James Kirk” is rolled back:

    _pDatabase->BeginTransaction();
    resultCode = InsertEntry(L"John", L"Doe");
    if (resultCode != E_SUCCESS)
    {
        AppLog("Could not create the first entry.");
        return resultCode;
    }
    _pDatabase->CommitTransaction();

    _pDatabase->BeginTransaction();
    resultCode = InsertEntry(L"James", L"Kirk");
    if (resultCode != E_SUCCESS)
    {
        AppLog("Could not create the entry James Kirk");
    }
    _pDatabase->RollbackTransaction();

It is important to call BeginTransaction to tell the system where the first entry in the set is. If you do not do this, you will find get one of the following errors:

0029.136,EXCEPTION,P26,T03,A93,Osp::Io::Database::RollbackTransaction (809) > [E_INVALID_STATE] Not in transaction mode.

0029.136,EXCEPTION,P26,T03,A93,Osp::Io::Database::RollbackTransaction (809) > [E_INVALID_STATE] Not in transaction mode.

More Information

There are also further examples of how to set up and use a database in the Bada documentation (see the documentation for the Database class and Using bada Features -> Basic bada Features -gt; Io: Input, Output, and Data Handle -> Use Cases -> Handling Databases) and the Build your own Database powered applicaion on bada tutorial on the bada Developers blog.

Advertisements

3 Responses to [Bada] Using the Bada Database

  1. Pingback: Bada Programming – A Start « Crypto Code

  2. Antonio says:

    If i just have my SQLite db developed wuth SQLiteBrowser can i insert it in my bada application? How can i do?

    • kahgoh says:

      Sorry I have to reply to this one so late. Unfortunately, I also don’t know how to do this – I haven’t heard or seen how to do this. Perhaps you could try asking in the Bada forums.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: