SQL Lite Tutorial

[Ravi Wayne]

Hello,

I am trying to implement sqllite interface based on the tutorial referenced in this link

http://docs.appery.io/tutorials/build...

My goal is to populate the local DB from REST services instead of using the appery DB.

I am trying to implement the javascript logic in the Complete event of the service. Able to get to create the local table, but run into problems with populating data.

I want to see if this is the best place to implement the javascript logic to create local tables and populate data from rest service.

Thank you.

[Alena Prykhodko]

Hello Ravi!

Do you have any errors occurred?
Have you checked whether elements exist in local database?

[Ravi Wayne]

Alena,

Thank you for your e-mail.

When I add the javascript implementation to the DataService Mapping,the routine is not getting called. So I added this to the DataService Complete event. Will this approach work or should I investigate why the javascript is not getting called as illustrated in the tutorial?

When the javascript gets called from the dataservice complete event, I can see a local db created. All the elements are match - between the rest service and create db as well as insert. Any suggestions?

Thank you

var db = window.openDatabase("Clients 0.1", "", "Clients 0.1", 1024 * 1000);
console.log('pass1');
db.transaction(function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS Clients (ClientID TEXT, ClientName TEXT, Address TEXT, BirthDate TEXT, GenderDescription TEXT, StaffID TEXT, Zipcode TEXT,_createdAt DATETIME, _updatedAt DATETIME)');
tx.executeSql('SELECT * FROM Clients WHERE ClientID = "' + value.ClientID + '"', function(tx, results) {
console.log('rows :' + results.rows.length);
if (results.rows.length == 0) {
console.log('Go and add table ' + value.number);
tx.executeSql('INSERT INTO Clients (ClientName, Address, BirthDate, GenderDescription, StaffID, Zipcode, _createdAt, updatedAt) values (?, ?, ?, ?, ?, ?, ?,?)', [value.ClientName, value.Address, value.BirthDate, value.GenderDescription, value.StaffID, value.Zipcode, value.createdAt, value._updatedAt]);
}
});
});

return;

[Alena Prykhodko]

Please try the next:
Delete Complete event;
Go Design tab- for Page on Load event Invoke Service that you have created.

[Ravi Wayne]

I am invoking the service on page load.

I have tried two different ways - (1) to map the response to the js code - no database is created; (2) Add this to the datasource complete event - database gets created, but table is not populated.

[Alena Prykhodko]

We need time to reproduce it. We will reply soon.

[Ravi Wayne]

OK. Thank you.

[Ravi Wayne]

I see a "Uncaught ReferenceError: value is not defined" when running the js from the dataservice success event.

Couple of questions:

tx.executeSql('SELECT * FROM Clients WHERE ClientID = "' + value.ClientID + '"',

Should the name of the table match the rest service? My rest service returns data joining multiple tables, but I am note sure the table names are returned in any way.

What does "value." stand for?

Thank you.

[Igor]

Hello,

[quote:]
I see a "Uncaught ReferenceError: value is not defined" when running the js from the dataservice success event.

Couple of questions:

tx.executeSql('SELECT * FROM Clients WHERE ClientID = "' + value.ClientID + '"',
[/quote]

You should initialize this variable or use this code on service request.

[quote:]
Should the name of the table match the rest service?
[/quote]

Rest service name and table name shouldn't match.

[Ravi Wayne]

Igor,

Thank you for your response. I am able to get past this issue. However, I am still having difficulties with the following:

1) The goal of the js function is to create a local db,table and load data from the service. The create db and table works well. However, I am calling this from the dataservice load success event. Your tutorial illustrates the restservice mapping to the javascript function - this does not work for me.

2) Within the js script, I need to load the data returned by the service.

tx.executeSql('SELECT * FROM "Clients"', [], function(tx, results){
console.log('rows :' + results.rows.length);
if (results.rows.length == 0){
tx.executeSql('INSERT INTO "Clients"....

The script selects records from the table we just created - not from the restservice. How do I refer to the restservice so I can load the returned data?

Thank you.