Add this to our init function to create the table as soon as our database is created.
function init() {
app.openDb();
app.createTable();
}
We have a database, we have a table, but we don’t have any data. Lets look at inserting some new records:
Inserting a New Record
app.insertRecord = function(t) {
app.db.transaction(function(tx) {
var cDate = new Date();
tx.executeSql(«INSERT INTO MyTable(text_sample, date_sample) VALUES (?,?)»,
[t, cDate],
app.onSuccess,
app.onError);
});
}
This example shows us how we can pass a parameterized query to the SQLite engine. If you remember, we created the id field as a primary key integer, so that column will be auto-incremented each time a new record is inserted. We will pass the variable t into the function which will get inserted in the text_sample column. Finally we create a new JavaScript date object and pass that in as the current date/time to the datesample column.
You’ll also notice that we have two other functions called, app.onSuccess and app.onError. You can probably guess that those are called when the result of the insert was successful or if it failed. For now you can just log those to the console like this:
app.onSuccess = function(tx, r) {
console.log(«Your SQLite query was successful!»);
}
app.onError = function(tx, e) {
console.log(«SQLite Error: » + e.message);
}
Updating an Existing Record
Once we have data in there, we’re probably going to want to update it at some point.
app.updateRecord = function(id, t) {
app.db.transaction(function(tx) {
var mDate = new Date();
tx.executeSql(«UPDATE MyTable SET text_sample = ?, date_sample = ? WHERE id = ?»,
[t, mDate, id],
app.onSuccess,
app.onError);
});
}
If you understood the insertRecord function, this one should make sense as well. We’re simple updating the record with data we are passing, and we are identifying the record by passing the id number. Again we are calling the app.onSuccess and app.onError functions.
Deleting an Existing Record
You should be getting the hang of it by now (especially if you are at all familiar with SQL!). Deleting a record is just as easy – and oh so powerful:
app.deleteRecord = function(id) {
app.db.transaction(function(tx) {
tx.executeSql(«DELETE FROM MyTable WHERE id = ?»,
[id],
app.onSuccess,
app.onError);
});
}
Selecting Records/Querying the Database
We’ve gone over how to get data into the database, updated, and removed. Lets look at how we can select data from the database to use in our app. In this example we’ll query our table and take that recordset to convert it to JSON, as that is usually easier for us to deal with.
app.selectAllRecords = function(fn) {
app.db.transaction(function(tx) {
tx.executeSql(«SELECT * FROM MyTable ORDER BY id», [],
fn,
app.onError);
});
}
This is a little different, as we have to send a callback function (the variable fn) along to do something with the data after the query is executed. Here is an example of how you could call the above query and do something with the data:
function getAllTheData() {
var render = function (tx, rs) {
// rs contains our SQLite recordset, at this point you can do anything with it
// in this case we’ll just loop through it and output the results to the console
for (var i = 0; i < rs.rows.length; i++) {
console.log(rs.rows.item(i));
}
}
app.selectAllRecords(render);
}
Continua leyendo «Adding Dynamic Data with the SQLite Plugin»