How to sync remote json data with sqlite


#1

Hi friends,

below is my web service, i want to store all this data in sqlite and sync with this service url.
http://tmcs.no-ip.info:82/iAutoCount/Item.svc/json/ItemSynchronization?dbName=2fP1N25PkIgL/D3kqJ/DETaVgM5hLD2GAQDUur5mY5YEiFp6GyuX2GebMFh0Do+7&uDId=iKNColFa+oztAVriouDfvMpoNDwsYn9H4aJxSVWFIWpqyPnE1k47C5VUkpntIVPr&isFullSync=true&lastUpdate=0&pageNo=1&perPage=2000

please any one give me some example.


#2

Hi,

To store data into a Sqlite database first you need to install this plugin into your app https://github.com/brodysoft/Cordova-SQLitePlugin.

Afterwards, use a $http call in the controller which would update sqlite database every time the controller is called. Here goes an example to make a $http request call,

var URL = "http://tmcs.no-ip.info:82/iAutoCount/Item.svc/json/ItemSynchronization?dbName=2fP1N25PkIgL/D3kqJ/DETaVgM5hLD2GAQDUur5mY5YEiFp6GyuX2GebMFh0Do+7&uDId=iKNColFa+oztAVriouDfvMpoNDwsYn9H4aJxSVWFIWpqyPnE1k47C5VUkpntIVPr&isFullSync=true&lastUpdate=0&pageNo=1&perPage=2000"
$http({
	method: 'GET',
	url: ''+URL,
	data: ''
})
.success(function (data, status, headers, config) {
	ifTableDoesNotExist().then(function(){
		tx.executeSql("Create table query", function(tx, result){
			tx.executeSql("Update query"+data, function(tx, result){

			});
		});	
	}, 
	function(){
		tx.executeSql("Update query"+data, function(tx, result){

		});
	})		
})
.error(function (data, status, headers, config) {

});

Use deferred resolve and promise functions, since all the database transactions would be async calls.

Please do let me know, if face any issues with above implementation.


#3

Hi Anurag…

in json data there is many fields so i need to write all this in controller or there is any way to auto create all fields that json data return ?


#4

I would suggest storing whole JSON string into a single table column and converting it into a object whenever required. It will also reduce number of lines of code.

You can convert JSON string into object by using angular.fromJson(JSONString).

Does that answer your question?


#5

yes i want solution like this, so i will convert now remote json file to jsonstring then i use.

i m trying now this if any example you have you can give me, how to implement.

actully i want to store remote data in sqlite . and sync with remote json. so when user open for 2nd time it will take data from sqlite for faster…


#6

HI monugt,

as you said i stored data in sqlite , it is storing like [object object]

then how can i retrive and show in my pages can you tell me, below is my code

var deburl=‘http://tmcs.no-ip.info:82/iAutoCount/Debtor.svc/json/DebtorSynchronization?dbName=2fP1N25PkIgL/D3kqJ/DETaVgM5hLD2GAQDUur5mY5YEiFp6GyuX2GebMFh0Do+7&uDId=iKNColFa+oztAVriouDfvMpoNDwsYn9H4aJxSVWFIWpqyPnE1k47C5VUkpntIVPr&isFullSync=true&lastUpdate=0&pageNo=1&perPage=1000

$http.get(deburl).success(function(data) {
$scope.debtrow = data;
$scope.debtdata = angular.fromJson($scope.debtrow);

  $cordovaSQLite.execute(db, "drop table debtor");
  $cordovaSQLite.execute(db, "CREATE TABLE IF NOT EXISTS debtor (id integer primary key, debtordata text)");
  debtorquery = "INSERT INTO debtor (debtordata) VALUES (?)";
          $cordovaSQLite.execute(db, debtorquery, [$scope.debtdata]).then(function(res) {
  	console.log($scope.debtdata)
  	//alert($scope.debtdata.DebtorSynchronizationResult.AccNo);
    });

})


#7

@jswebtech

We should not use “$scope.debtdata = angular.fromJson($scope.debtrow);” before inserting data into the database. It will convert debtdata into [object] [object].

Instead of it store “data” directly into the database which will be in a form of string.

While retrieving it back from the database, i.e whenever we want to utilize the data in our app which will be in a form of a string. We can use angular.fromJson() and convert it to an object to use them.


#8

before insert i did $scope.debtdata = angular.fromJson($scope.debtrow);

after store in table how to retrive ? it store in [object Object].


#9
 var deburl='http://tmcs.no-ip.info:82/iAutoCount/Debtor.svc/json/DebtorSynchronization?dbName=2fP1N25PkIgL/D3kqJ/DETaVgM5hLD2GAQDUur5mY5YEiFp6GyuX2GebMFh0Do+7&uDId=iKNColFa+oztAVriouDfvMpoNDwsYn9H4aJxSVWFIWpqyPnE1k47C5VUkpntIVPr&isFullSync=true&lastUpdate=0&pageNo=1&perPage=1000'

 $http.get(deburl).success(function(data) {

$cordovaSQLite.execute(db, "drop table debtor");
$cordovaSQLite.execute(db, "CREATE TABLE IF NOT EXISTS debtor (id integer primary key, debtordata text)");
debtorquery = "INSERT INTO debtor (debtordata) VALUES (?)";

        $cordovaSQLite.execute(db, debtorquery, [data]).then(function(res) {
	console.log("Data inserted successfully");
        
          $cordovaSQLite.execute(db,"select * from debtor", []).then(function(tx ,res) {
	   console.log("Number of records "+res.rows.length);
	   console.log("Data "+angular.fromJson(res.rows.item(0).debtordata));
  });

  });

 })

I have modified the code for your understanding, but it is not a correct way of doing it. We have to move all this code into an indiviual service and resolve promises for each transaction, that way our data integrity is maintained all over the app.

Best example is present at link - https://gist.github.com/jgoux/10738978

Please do let me know if you still face any issue.

Thank you.


#10

HI every thing is working now, data insert in sqlite db, but in mobile it is not working. i m unable to check db is created or not. can you explain how to check in device…


#11

Its already fixed or not?


#12

HI , i m still not able to run on device, unable to create db in device.


#14

hello, I already tested with latest source code from @monugt but didn’t worked on mobile and browser either. could you share your source code for me?


#15

I failed to use $cordovaSQLite as well. Instead, I did it the old style way, which seem to work:

.factory("myDb",
[
  function () {
    return window.openDatabase("my.db", "1.0", "TPP", 20000000);
  }
])

Then I can inject and use in my controller:

.controller("MyApp.MyCtrl", ["myDb", function(db) {
    db.transaction(function (tx) {
      tx.executeSql('SELECT firstname FROM myTable WHERE objectid=?', [id], function (tx, res) {
        if (res.rows.length > 0) alert(res.rows.item(0).firstname);
      }, function () {
        // fail
      });
    });
}]