Using SQLite Database with Titanium Mobile

If you came to this post through Google or through other means, it would be best to download the code as shown in my github account.


$ git clone git@github.com:HugeAim/titanium-list-app.git
$ git checkout part4

So far, in this series, you were able to create a simple list application with a group header and an add and delete functionality.

In this post, you are going to learn how to save the added and deleted items into a sqlite database.

I assume that you already have the SQLite installed. If not, please go ahead and install it now.

After installation, create a new database, name it as lists.sqlite, then save it in the current List Applications’ Resources folder as shown below


$ sqlite3 Resources/lists.sqlite
SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

That command will open a sqlite prompt. Create a table using it as shown below.


sqlite> CREATE TABLE IF NOT EXISTS lists (id INTEGER PRIMARY KEY, content TEXT NOT NULL);
sqlite>

You can do it also in the terminal tab of Titanium Studio.

Now, modify the current app.js that you have created so far. This time, you will no longer need the “data” variable. That data will come from the database.

//var data = [
//    {title:'List Content 1', hasChild:true, toUrl:'window2.js', header:"Header 1"},
//    {title:'List Content 2'},
//   {title:'List Content 3',header:"Header 2"},
//    {title:'List Content 4'}
//];

Add a database installer in the line after that. Then, query the lists table.

var db = Ti.Database.install('lists.sqlite','lists.sqlite');
var rows = db.execute('SELECT * FROM LISTS;');
var data = [];
while (rows.isValidRow())
{
    data.push({
      title:rows.fieldByName('content'),
      id:rows.fieldByName('id')
    });
    rows.next();
};
rows.close();

Then modify the addBtn click listener located in the buttom of the file.

var count = data.length;
addBtn.addEventListener('click',function(){
        row = Ti.UI.createTableViewRow(	{title:'List Content Appended' + count,id:count});
	db.execute('INSERT INTO lists (content) VALUES (?)',row.title);
	tableview.appendRow(row);
	updateRowCount();
});
function updateRowCount(){
	rows  = db.execute('select count(*) as count from lists');
	while (rows.isValidRow())
	{
		count = rows.fieldByName("count");
		rows.next();
	};
	rows.close();
}

Those highlighted rows basically appends a single row per click then updates the row count in preparation for the next add button operation.

Try to add some rows, close the application and open it again. Notice now that the state is saved to the database.

Deleting

So far you were able to save state of the added rows to the database. Now, modify the delete action by adding the following in the bottom of the app.js file.

 tableview.addEventListener('delete', function(e) {
	db.execute("DELETE FROM lists WHERE id=?",e.row.id);
	updateRowCount();
 });

That code cascades the deleted item from the table view in the database.

The application should work as desired now.

To download the code specific to this Part 5, do.


$ git checkout part5

Part 1: Getting Started with Appcelerator Titanium
Part 2: Sliding Windows with Appcelerator Titanium
Part 3: Grouping the Table View in Titanium Mobile
Part 4: Add/Delete Items in the List with Titanium Mobile
Part 5: Using SQLite Database with Titanium Mobile
Part 6: Create the Database API in Appcelerator Titanium

Though English is my country's official language it is not my native tongue. If you find anything wrong with my phrases, or any incorrect information, write it out loud in the comments section. The world will be a bit better that way.
Posted in Javascript
0 comments on “Using SQLite Database with Titanium Mobile
4 Pings/Trackbacks for "Using SQLite Database with Titanium Mobile"
  1. [...] the Table View in Titanium Mobile Part 4: Add/Delete Items in the List with Titanium Mobile Part 5: Using SQLite Database with Titanium Mobile Share this:DiggRedditStumbleUponFacebook Posted in appcelerator, Javascript.Tagged appcelerator, [...]

  2. [...] the Table View in Titanium Mobile Part 4: Add/Delete Items in the List with Titanium Mobile Part 5: Using SQLite Database with Titanium Mobile Share this:DiggRedditStumbleUponFacebook Posted in appcelerator, Mobile Development.Tagged [...]

  3. [...] the Table View in Titanium Mobile Part 4: Add/Delete Items in the List with Titanium Mobile Part 5: Using SQLite Database with Titanium Mobile Share this:DiggRedditStumbleUponFacebook Posted in appcelerator, Javascript, Mobile Development, [...]

  4. [...] the Table View in Titanium Mobile Part 4: Add/Delete Items in the List with Titanium Mobile Part 5: Using SQLite Database with Titanium Mobile Share this:DiggRedditStumbleUponFacebook Posted in Javascript, Titanium.Tagged javascript, [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre user="" computer="" escaped="">