Persistent SQLite Database

#1

My native app uses the cordova-sqlite-evcore-extbuild-free plugin.

I have Database.db,DB,loadGrid,NSB.overwriteAlways in extraFiles.

The app and the database work fine together; while the app is running CRUD works as expected.
When I close the app, the database changes are not saved. What have I missed?

Given that a user might close an app at any time, what if anything needs to be done after every SQL transaction to ensure that data is saved?

Thank you

#2

What method are you using to insert/query/delete etc, transactions? Are you closing the db?

#3 
cmd = ["DELETE FROM HealthScreen WHERE MemberID = '4'"]
Sql(DB,cmd);

    for (var i = 0; i < rows.length; i++) {
        var row = rows[i]; 
          row.MemberID = MemberID;
         args = [row.MemberID,row.Test,row.Year,row.Result];
        sqlList[i] = [ "INSERT INTO HealthScreen (MemberID,Test,Year,Result) VALUES (?,?,?,?)", args]; 
        console.log(sqlList[i])
     }
     
  Sql(DB,sqlList);

Based on my initial question, should I close the database after each transaction is complete to ensure the changes will be persistent?

Thank you

#4

Hint: use 3 backticks (```) around code, not single quotes (’’’).

#5

What platform are you on (iOS or Android)?

Do you have an SQLite plugin defined in your config.xml?

What version of AppStudio are you using?

AppStudio 8.5 has breaking changes which may affect some people using SQLite.

#6

8.5.1.2b2
iOS 12… and Android 10
Yes, it’s defined and as I mentioned, the SQL statements read/write to the database and update the jqxGrid as they should.

The app works as it should. Do I need to close the database after each transaction?
Should the database persist when it runs in the AppStudio browser?

#7

That’s not really a transaction in the sqllite meaning of the term.

db = an open SQLlite database

  db.transaction(function(tx)
  {
    var addedOn = new Date();
    // database table
    // msgid, channel, type
    tx.executeSql("INSERT OR REPLACE INTO mytbl (msgid, channel, type) VALUES (?,?,?)",
        [msg_id, channel, type],
        onSuccess,
        onError);
  });

When the onSuccess is called, the transaction is complete and the db operation is complete.

#8

I appreciate and agree with the explanation.

I am using the "AppStudio: syntax…https://wiki.appstudio.dev/Using_SQLite

I assume it works as advertised. If not, I need to understand what to do.

Thank you for your help…

#9

As is often the case, the answer is simple…once you find it…

Database.db,DB,loadGrid,NSB.overwriteNever - This only overwrites the DB if it doesn’t exist.

Once the app has “saved” the data in the database, you can quit the app and the data is in the database when you restart the app. No need to close the database.

This is true for native apps and when the app runs on the AppStudio browser.

Thank you for your assistance.