How can I get data synchronously from cordova-sqlite?

Question

Is it possible to synchronize data from cordova-sqlite?

I have a caseTable table with fields (ID, caseName, date). Each row in this table corresponds to another table named in the caseName field. I need to go through the caseTable table and get the number of rows in the table.

 function onDeviceReady() { db = window.openDatabase("Casepad", "1.0", "Casepad", 200000); db.transaction(getallTableData, errorCB); } function insertData() { db.transaction(createTable, errorCB, afterSuccessTableCreation); } // create table and insert some record function createTable(tx) { tx.executeSql('CREATE TABLE IF NOT EXISTS CaseTable (id INTEGER PRIMARY KEY AUTOINCREMENT, CaseName TEXT unique NOT NULL ,CaseDate INTEGER ,TextArea TEXT NOT NULL)'); tx.executeSql('INSERT OR IGNORE INTO CaseTable(CaseName,CaseDate,TextArea) VALUES ("' + $('.caseName_h').val() + '", "' + $('.caseDate_h').val() + '","' + $('.caseTextArea_h').val() + '")'); } // function will be called when an error occurred function errorCB(err) { navigator.notification.alert("Error processing SQL: " + err.code); } // function will be called when process succeed function afterSuccessTableCreation() { console.log("success!"); db.transaction(getallTableData, errorCB); } // select all from SoccerPlayer function getallTableData(tx) { tx.executeSql('SELECT * FROM CaseTable', [], querySuccess, errorCB); } function querySuccess(tx, result) { var len = result.rows.length; var t; $('#folderData').empty(); for (var i = 0; i < len; i++) { /* ************************************************************* * Here i need to call a synchronous method which returns the * number of rows in the result.rows.item(i).CaseName table * ************************************************************* */ $('#folderData').append( '<li class="caseRowClick" id="' + result.rows.item(i).id + '" data-rel="popup" data-position-to="window">' + '<a href="#">' + '<img src="img/Blue-Folder.png">' + '<h2>' + result.rows.item(i).CaseName + t+'</h2>' + '<p>' + result.rows.item(i).TextArea + '</p>' + '<p>' + result.rows.item(i).CaseDate + '</p>' + '<span class="ui-li-count">' + i + '</span></a>' + '<span class="ctrl togg"><fieldset data-role="controlgroup" data-type="horizontal" data-mini="true" ><button class="edit button_design">Edit</button><button class="del button_design">Delete</button></fieldset><span>'+'</li>' ); } $('#folderData').listview('refresh'); } 

The sign of displaying the value of "i" in the list view, I need to show how many elements in this table. I need to call synchronization because I need to call some query that counts the number of elements in "result.rows.item (i) .CaseName" in this element ..?

Take an example ...

Case Pad Database Name CaseTable Table Name

  Let assume having entries in caseTable. ID CaseName Case Date caseNote 1 Test 3/77/13 jgjgj 2 Test2 4/34/3 hsadkkadsh Now I have two more table in DB Test , Test2.. **Test** having entries like this ID DocumentName Date Notes 1) ppp 7/33 asdhdfkdshf 2) asdjhad 9/44 dfjasgfsjfj **Test2** having entries like this ID DocumentName Date Notes 1) sad 7/4 asdhdfkdshf 2) assd 3/44 hhhhhh 3) asd 2/22 adgjad 

Now Test, Test2 have a record of 2 and 3.

Now I need to get data from CaseTable. If you want, I need to count the number of elements in my casename table (Test, Test1). here only

  function getallTableData(tx) { tx.executeSql('SELECT * FROM CaseTable', [], querySuccess, errorCB); } function querySuccess(tx, result) { var len = result.rows.length; var t; $('#folderData').empty(); for (var i = 0; i < len; i++) { Here i need to call synchronize method which call the number of element in in that result.rows.item(i).CaseName and insert it in this table **************************************************************************** $('#folderData').append( '<li class="caseRowClick" id="' + result.rows.item(i).id + '" data-rel="popup" data-position-to="window">' + '<a href="#">' + '<img src="img/Blue-Folder.png">' + '<h2>' + result.rows.item(i).CaseName + t+'</h2>' + '<p>' + result.rows.item(i).TextArea + '</p>' + '<p>' + result.rows.item(i).CaseDate + '</p>' + '<span class="ui-li-count">' + i + '</span></a>' + '<span class="ctrl togg"><fieldset data-role="controlgroup" data-type="horizontal" data-mini="true" ><button class="edit button_design">Edit</button><button class="del button_design">Delete</button></fieldset><span>'+'</li>' ); } $('#folderData').listview('refresh'); } 

I need to print case case caseate case Note the number of elements So my list looks like this:

  Test 3/77/13 jgjgj 2 Test2 4/34/3 hsadkkadsh 3 

How to print 2 and 3 in this table ... :( can you answer any question)

Answer 1

You can pass them through asynchronously by doing something like this (not tested, but hopefully you understand):

 var count = 0; var caseTableResult = []; var getallTableData = function (tx) { tx.executeSql('SELECT * FROM CaseTable', [], querySuccess, errorCB); } var querySuccess = function (tx, result) { if (count === 0) { caseTableResult = result; $('#folderData').empty(); } else { var i = count - 1; $('#folderData').append( '<li class="caseRowClick" id="' + caseTableResult.rows.item(i).id + '" data-rel="popup" data-position-to="window">' + '<a href="#">' + '<img src="img/Blue-Folder.png">' + '<h2>' + caseTableResult.rows.item(i).CaseName + t+'</h2>' + '<p>' + caseTableResult.rows.item(i).TextArea + '</p>' + '<p>' + caseTableResult.rows.item(i).CaseDate + '</p>' + '<span class="ui-li-count">' + i + '</span></a>' + '<span class="ctrl togg"><fieldset data-role="controlgroup" data-type="horizontal" data-mini="true" ><button class="edit button_design">Edit</button><button class="del button_design">Delete</button></fieldset><span>'+'</li>' ); } if (count <= caseTableResult.rows.length) { // Call the next query count += 1; tx.executeSql('SELECT count(*) FROM ' + caseTableResult.rows.item(i).CaseName, [], querySuccess, errorCB); } else { // We're done $('#folderData').listview('refresh'); } } 

But in fact, you should not create many tables with the same structure and different names, you should have one table with all the data related to the relations, then you can use my other answer .

Answer 2

I do not think that this can be done synchronously, and probably should not try.

In this case, you can get the value that you use after the subquery, for example:

 SELECT *, (SELECT COUNT(*) FROM CaseTableDetail WHERE CaseTableDetail.CaseID = CaseTable.id) AS CaseCount FROM CaseTable; 

(this is just an assumption since you did not specify your full table structure for the CaseName table)

Edit:

To accomplish the above, you will need the correct relational structure, rather than dynamically adding tables. You should have only 2 tables, I will call them CaseTable and CaseDetailTable.

CaseTable is exactly what you already have.

CaseDetailTable is similar to the Test and Test2 tables above, but has an additional field, CaseID Now I have two more tables in the Test database, Test2 ..

  ID CaseID DocumentName Date Notes 1 1 ppp 7/33 asdhdfkdshf 2 1 asdjhad 9/44 dfjasgfsjfj 3 2 sad 7/4 asdhdfkdshf 4 2 assd 3/44 hhhhhh 5 2 asd 2/22 adgjad 

So, the CaseID field is a pointer to an entry in CaseTable, which includes each line. Using WHERE, JOIN, and subqueries like the one I used above, you can access all the data much more efficiently. You can tell SQLite that this is what you are doing using the REFERENCES keyword. This will tell the database to create indexes to speed up the search for CaseDetails, and make sure you cannot add rows to CaseDetailTable unless you have a matching CaseTable entry.

You can create a CaseDetailTable as follows:

  CREATE TABLE CaseDetailTable ( id INTEGER PRIMARY KEY AUTOINCREMENT, CaseID INTEGER REFERENCES CaseTable (ID), Notes TEXT unique NOT NULL, DocumentName INTEGER, Date TEXT NOT NULL ); 

Answer 3

I like this..!! He works. Thanks for the help.

 function getallTableData(tx) { // tx.executeSql("DROP TABLE IF EXISTS a"); tx.executeSql('SELECT * FROM CaseTable', [], querySuccess, errorCB); } function querySuccess(tx, result) { var len = result.rows.length; var countDoument=0 $('#folderData').empty(); for (var i = 0; i < len; i++) { alert(i) test1=result.rows.item(i).CaseName; Test1(test1, function(i) { return function(result_count) { countDoument = result_count; // here it count value alert(result_count + "result_count") //alert is correct count value alert(i + "i"); $('#folderData').append( '<li class="caseRowClick" id="' + result.rows.item(i).id + '" data-rel="popup" data-position-to="window">' + '<a href="#">' + '<img src="img/Blue-Folder.png">' + '<h2>' + result.rows.item(i).CaseName + countDoument + '</h2>' + '<p>' + result.rows.item(i).TextArea + '</p>' + '<p>' + result.rows.item(i).CaseDate + '</p>' +'<span class="ui-li-count">' + i + '</span>'+ '</a>' + '<span class="ctrl togg"><fieldset data-role="controlgroup" data-type="horizontal" data-mini="true" ><button class="edit button_design">Edit</button><button class="del button_design">Delete</button></fieldset><span>' + '<span class="ui-li-count">' + i + '</span>'+ '</li>' ); $('#folderData').listview('refresh'); }; }(i)); } } function Test1(test, callBack){ var x; db.transaction(function (tx) { $yoursql = 'SELECT * FROM "'+test+'" '; tx.executeSql($yoursql, [], function (tx, results) { x = results.rows.length + "TableName" + test; callBack(x); }); }); }