Developers can use several data access approaches to consume data from the mobile database.
One of them is to use raw sql queries against the mobile database.
Using raw SQL queries
The developer can create and execute sql statements against the mobile database in order to use the full power of the mobile database engine. Note that the mobile database engine used by default is SqLite. You can consult with the SqLite Documentation about the supported sql syntax.
In order to send queries to the underlying mobile database the developer should use the dataManager “class”:
$ma.dataManager.fetchSql('select id,title from customer order by title',function(customers){
var customersHtml = "";
for(var i in customers){
customersHtml+=customers[i].title
}
$("#customersDisplay").html(customersHtml)
});
This code snippet executes select sql against the mobile database and returns array of objects containing the requested fields as members. Each instance from the customers array will have two members named id and title, because we have mentioned those fields in the select clause. Note that all member values are presented as string data type in this case.
You can also execute insert, update, delete queries against the mobile database like this:
$ma.dataManager.execSql("update customer set title='newtitle' where id=123",function(){alert('executed')});
This code snippet will change the title of the customer with id 123 in the mobile database.
NOTES
- All $ma.dataManager methods are blocking – the execution of the code is blocked until the operation is completed.
- You can expect various kind of errors especially , when the code is excuted in the web browser. It is a good practice to provide error callback function
$ma.dataManager.execSql("update customer set title='newtitle' where id=123",function(){
alert("executed successfully")
},
function(error){
$('#errordisplay').html(error).css("color","red");
);
- String values must be wrapped in single quote char
- DateTime values must be wrapped in single quote char
- DateTime values used as part of the sql must be formatted as yyyy-mm-dd hh:MM:ss
//using hardcoded date values
$ma.dataManager.execSql("select * from customer where dateCreated>'2011-12-31',function(){
//using the Date.toSqLiteFormat extended method (recommended)
var dt = new Date();
$ma.dataManager.fetchSql("select * from customer where dateCreated>"+dt.toSqLiteFormat()+"'",function(customers){
//..
});
});
A good practice is to use the LIMIT statement as part of SELECT queries to limit the maximum records.