Web SQL Query

[Jerry Blanchard]

Hello,

In my app I have a list that is populated from a Web SQL database (as learned in the "Building a mobile app with the Appery.io and local SQLite databases" tutorial. I have used the javascript from that tutorial and have successfully copied the two tables I need local and can use a javascript REST service to retrieve the data. But on my second query I need to use a WHERE statement and when I include it, I get no data returned. Without the WHERE statement I get data, just not filtered as I want it.

In my code I set a variable "SelectedSuperCategory" to the value of a local storage variable.

I'm use my problem is just syntax, but I have not been able to find a resource with documentation for the syntax needed.

Please help.

code

var db = window.openDatabase("Advisor 0.1", "", "Advisor 0.1", 1024 * 1000);
var SelectedSuperCategory=(localStorage.getItem('Selected'));

Appery.GetLocalBasicCategory = Appery.createClass(null, {

Code: Select all

 init: function(requestOptions) {
     this.__requestOptions = $.extend({}, requestOptions);
 },

 process: function(settings) {

     if (this.__requestOptions.echo) {
         settings.success(this.__requestOptions.echo);
     } else {
         var ourFinalArray = [];
         db.transaction(function(tx) {
             tx.executeSql('SELECT DISTINCT "BasicCategory" FROM "Categories" WHERE "SuperCategory" = SelectedSuperCategory;', [], function(tx, results) {

                 var len = results.rows.length,
                     i;

                 if (len === 0) {

                 } else {
                     for (i = 0; i < len; i++) { &#47;&#47; for each row
                         ourFinalArray&#46;push(results&#46;rows&#46;item(i)); &#47;&#47; pushing row object to an array                
                     }
                     console&#46;log("Categories: " + JSON&#46;stringify(ourFinalArray));
                     settings&#46;success(JSON&#46;stringify(ourFinalArray));

                 }
                 settings&#46;complete('success');
             });
         });
     }
 }

});

/code

[Jerry Blanchard]

I just tried using

"'+SelectedSuperCategory+'"

and it sort of worked. I do get filtered data, but it is the wrong data.

???

[Evgene Karachevtsev]

Hello Jerry,

We are working on it at the moment. We will get back to you with update.

[Jerry Blanchard]

OK, thanks for your help, but all I need to do was ask the question and then I figured it out. I got rid of the variable and just included the (localStorage.getItem('Selected')) in the query and it world like a charm. Next level I need to do a join, wish me luck.

[Jerry Blanchard]

Yeah, I was able to get the join to work on the Web SQL database, here is the command I used incase others have questions on this subject:

'SELECT Categories.objectId, Categories.BasicCategory, Categories.SubCategory, UserSelectedCategories.Category_Id, UserSelectedCategories.Marked FROM Categories LEFT OUTER JOIN UserSelectedCategories ON Categories.objectId = UserSelectedCategories.Category_Id WHERE Categories.BasicCategory = "'+(localStorage.getItem('BasicCategorySelected'))+'";'

And here is the resource where I found the solution:

http://www.tutorialspoint.com/sqlite/...

[Maryna Brodina]

Hello Jerry!

Thank you for update!

[LuisMa Suárez Gutiérrez]

hi!! i have a Problem of this sort,
I concatenated with nombre = " '+(quien)+ ' "

I added the parenthesis because it worked for jerry. but not for me

var quien= localStorage.getItem("persona"); // pull variable
var db = window.openDatabase(version, "", version, 1024*1000);
db.transaction(

Code: Select all

 function(tx) {   
     tx.executeSql('UPDATE "Tables" SET usadas=usadas+1 WHERE nombre=" '+(quien)+' "  ' , [], function(tx, results) { });   

 } 

);

Jerry Blanchard here pulled BasicCAtegorySelected from Local storage, im trying to do the same and then concatenate into the SQL query, no luck, what im a missing ?

note if i use a literal like nombre ="joe" it works