Google Gears Basics part-3 database
Gear is providing the way to save data within user disk, let’s see Database part of google gear
The DataBase allow to store user’s data on the user’s computer. For security gear allows to access data related to particular domain. Gear is providing two classes to do database related functionality, Database and Resultset.
First you have to create Database object using gear factory.
var database = google.gears.factory.create('beta.database', '1.0');
// open the database
database.open('localApprDB');
// execute the sql
database.execute('create table if not exists users (name varchar(100), user_req varchar(100), status varchar(100), last_modified_date datetime)');
The execute() method use bind parameters (?) to prevent SQL injection attacks and improve performance. SQLite database supports almost all the database functionality. Let’s try some simple insert-fetch with created users table.
database.execute("insert into users values (?, ? , ?, date('now'))", ['vicky', 'request for new laptop.', 'Active']);
The data declared in array will replaced with question marks(?). It’s good practice to bind variable, It will automatically escaped by the SQL engine. Gear adds rowid column to every table it’s unique integer id for every row of your table. After every successful insertion gear update lastInsertRowId readonly attribute of Database Object.
// database.execute() returns Resultset object. It has the results of executing the SQL.
resultset = database.execute(select user_req from users where name=? and status=?",['vicky', 'Active']);
Resultset is providing methods to Iterate over rows like isValidRow(), next(), and close(), And to access columns you have field(), fieldName() and fieldByName() methods.
while (rs.isValidRow()) {
console.log(rs.fieldName(0) + " == " + rs.field(0));
rs.next();
}
rs.close();
The fieldName() and field() takes int index of column, and fieldByName() takes name of the desired field like rs.fieldByName('user_req').
That’s all about database, it’s for storing data on the local machine same way we handle database in any other language.