Documentation of single-statement API

[brodycj]

From https://github.com/litehelpers/Cordova-sqlite-storage/issues/666#issuecomment-294448281:

Does anyone happen to know where I can find documentation for the single-statement transaction methodology?

My answer:

I thought I documented this by example in https://github.com/litehelpers/Cordova-sqlite-storage#single-statement-transactions:

Sample with INSERT:

db.executeSql('INSERT INTO MyTable VALUES (?)', ['test-value'], function (resultSet) {
 console.log('resultSet.insertId: ' + resultSet.insertId);
 console.log('resultSet.rowsAffected: ' + resultSet.rowsAffected);
}, function(error) {
 console.log('SELECT error: ' + error.message);
});

Sample with SELECT:

db.executeSql("SELECT LENGTH('tenletters') AS stringlength", [], function (resultSet) {
 console.log('got stringlength: ' + resultSet.rows.item(0).stringlength);
}, function(error) {
 console.log('SELECT error: ' + error.message);
});

This is evidently not clear enough. I think I will really need to start a real multi-page guide that people can follow more easily.

I cannot promise when I will get a chance to deal with this properly. Priority goes to paid users. For more information please contact brodybits@litehelpers.net or sales@litehelpers.net.

[svranch]

Hi Chris... I get the part that this is the way to handle multiple-page apps. And I did see the doc'n which u pasted in above. But, unfortunately, that didn't answer the 2nd part of my question -

-Or where/what are it's origins? <<<.

You see, "single-statement" versus "standard" could use a few more words of explanation. Cuz what I read leaves me wondering:

1. I have syntax issues that are not covered in your examples referenced above. So I was looking for syntax documentation, and wondered where did the "Single-Statement" method come from (so I might find some the documentation source). Therefore, my related wonderings/questions:
   • Is this standard to "SQLite"?

• And was the "Single-Statement" method standard only to Web-SQL or maybe another language / technology?

• If it is unique and applicable only to "CORDOVA-SQLITE-STORAGE, then perhaps you can add a sentence to the section entitled, "Single-statement-transactions", such as:

The term "Single-statement-transactions" refers to an alternate method for executing SQL with the CORDOVA-SQLITE-STORAGE addon. All documentation for this alternate methodology are contained on this page.

• Or are both unique only to "CORDOVA-SQLITE-STORAGE?

Your examples are great but it seems maybe they don't cover all possible syntax differences. For example... strQueryItemTypes = "SELECT * FROM xh_item_type_lookup" + " WHERE it_category = ? " + " ORDER BY it_sort_order;";

this_apps_db.executeSql(strQueryItemTypes,[varPulledCatNum], function(rs_populate_html) {

Please note that the WHERE statement ... WHERE it_category = ? finds it's completion in the executeSQL arguments, by placing a variable (varPulledCatNum) there.

I didn't see that example in your examples. I appreciate so much you putting examples there - but in my case it didn't cover everything I needed to know.

I don't think it's necessary to have a guide just for multiple page apps at this time. Rather, perhaps we can work together to clarify some text. I am very happy to assist with this however I can.

Again - thanks so much for all your excellent and generous help!

[svranch]

Chris, jw if u saw my post above?

[brodycj]

My apologies, I think I misread something and had thought there was another part coming. The "single-statement" method is unique to this plugin and its derivatives.

In terms of history: I added it to deal with PRAGMA statements that should not be contained within a BEGIN...COMMIT transaction. (It was part of the original iOS version by Davide Bertola though with the SQL and parameter values all together in a single array.)

I will try to make a more general description of the usage in case it helps:

db.executeSql(sql, optionalArgumentValues, optionalSuccessCallback, optionalErrorCallback);

where:

• db is the database object from sqlitePlugin.openDatabase
• sql is a string with the sql statement to be processed by sqlite, just like you can use in the standard transaction API and may have ? argument value placeholders
• optionalArgumentValues may be absent, null or an array of argument values, just like the standard transaction API
• optionalSuccessCallback may be absent, null, or a function that takes a resultSet argument
• optionalErrorCallback may be absent, null, or a function that takes an error argument

IMPORTANT: Unlike transaction.executeSql in the standard transaction API the single statement executeSql callbacks should NOT expect a "tx" (transaction object) argument.

From your description I cannot see what you may be going wrong. You may want to post a small test program, ideally based on https://github.com/brodybits/cordova-sqlite-test-app, to demonstrate the issue.

I will integrate this in README.md for cordova-sqlite-storage when I get a chance. I would be happy if you raise a PR on cordova-sqlite-storage (or one of the derivatives) otherwise I will do this myself when I get a chance.