How to save data from external server (JSON) into sqlite database in ionic framework?

Hello guys, I need your help to fix my problem. Now I’m working on ionic framework and SQLite database. I need to save data from JSON format into SQLite mobile database. JSON data come from web service which are from external server. This is some example JSON data I create :

[{"id":"6","fullname":"sadad","ic_no":"12313","private_phone":"2221","office_phone":"4443","position":"fff","email":"af@gmail.com","password":"12345"}]

This is my controller.js

.controller('AppCtrl', function($scope,$ionicPlatform,$location,$http,$ionicHistory,$cordovaSQLite) {
      $ionicHistory.nextViewOptions({
        disableAnimate:true,
        disableBack:true
      });
      $ionicPlatform.ready(function(){
        //$scope.loginData = {};
        $scope.sync = [];
        $scope.doLogin =function(){
          if(window.Connection) {
            //no connection to internet no sync process happen
            if(navigator.connection.type == Connection.NONE) {
                $location.path('/app/playlists');
            }
            //connection available sync process will execute
            else{
              $http.get('https://ip-server/pokok_apps/kdma/fetch.php')
                .success(function(data){
                  $scope.li = data;
                  $scope.debtdata =  angular.fromJson($scope.li);
                    var query = "INSERT INTO user_kdma (id,fullname,ic_no,private_phone,office_phone,position,email,password) VALUES (?,?,?,?,?,?,?,?)";
                    $cordovaSQLite.execute(db, query, [$scope.debtdata]).then(function(res) {
                      $scope.sync.push({id: $scope.debtdata.id,fullname: $scope.debtdata.fullname,ic_no: $scope.debtdata.ic_no,private_phone: $scope.debtdata.private_phone,office_phone: $scope.debtdata.office_phone,position: $scope.debtdata.position,email: $scope.debtdata.email,password: $scope.debtdata.password});
                      console.log("insertId: " + res.insertId);
                    }, function (err) {
                      console.error(err);
                    });
                  //$location.path('/app/browse');
                })
                .error(function(data){
                  alert('error');
                });
              
            }
          }
        }
      });
     
    })

This is my structure database in SQLite mobile

CREATE TABLE IF NOT EXISTS user_kdma (id integer primary key autoincrement, fullname text null,ic_no text null,private_phone text null, office_phone text null, position text null, email text null, password int null)

After I run at android emulator, I got this error on adb logcat when I press some button to make that code execute.

V/sqlg    ( 3590): prepare db 0x7fca821fad08 sql BEGIN
V/sqlg    ( 3590): sqlc_st_finish 0x7fca82301dc8
V/sqlg    ( 3590): prepare db 0x7fca821fad08 sql INSERT INTO user_kdma (id,fullname,ic_no,private_phone,office_phone,position,email,password) VALUES (?,?,?,?,?,?,?,?)
V/sqlg    ( 3590): sqlc_st_bind_text_native 0x7fca82301f08 1 [object Object]
W/System.err( 3590): java.sql.SQLException: step failed with error: 20
W/System.err( 3590): 	at io.liteglue.SQLiteGlueConnection$SQLGStatement.step(SQLiteGlueConnection.java:132)
W/System.err( 3590): 	at io.liteglue.SQLitePlugin$SQLiteDatabaseNDK.executeSqlStatementNDK(SQLitePlugin.java:514)
W/System.err( 3590): 	at io.liteglue.SQLitePlugin$SQLiteDatabaseNDK.executeSqlBatch(SQLitePlugin.java:434)
W/System.err( 3590): 	at io.liteglue.SQLitePlugin$DBRunner.run(SQLitePlugin.java:618)
W/System.err( 3590): 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
W/System.err( 3590): 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
W/System.err( 3590): 	at java.lang.Thread.run(Thread.java:818)
V/executeSqlBatch( 3590): SQLitePlugin.executeSql[Batch](): Error=step failed with error: 20
V/sqlg    ( 3590): sqlc_st_finish 0x7fca82301f08
W/System.err( 3590): java.sql.SQLException: finish failed with error: 20
W/System.err( 3590): 	at io.liteglue.SQLiteGlueConnection$SQLGStatement.dispose(SQLiteGlueConnection.java:217)
W/System.err( 3590): 	at io.liteglue.SQLitePlugin$SQLiteDatabaseNDK.executeSqlStatementNDK(SQLitePlugin.java:521)
W/System.err( 3590): 	at io.liteglue.SQLitePlugin$SQLiteDatabaseNDK.executeSqlBatch(SQLitePlugin.java:434)
W/System.err( 3590): 	at io.liteglue.SQLitePlugin$DBRunner.run(SQLitePlugin.java:618)
W/System.err( 3590): 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
W/System.err( 3590): 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
W/System.err( 3590): 	at java.lang.Thread.run(Thread.java:818)
V/executeSqlBatch( 3590): SQLitePlugin.executeSql[Batch](): Error=finish failed with error: 20
V/sqlg    ( 3590): prepare db 0x7fca821fad08 sql ROLLBACK
V/sqlg    ( 3590): sqlc_st_finish 0x7fca82301f08
D/SystemWebChromeClient( 3590): file:///android_asset/www/plugins/cordova-plugin-console/www/console-via-logger.js: Line 173 : [object Object]
I/chromium( 3590): [INFO:CONSOLE(173)] "[object Object]", source: file:///android_asset/www/plugins/cordova-plugin-console/www/console-via-logger.js (173)
D/PluginManager( 3590): exec() call to unknown plugin: Console

So, anyone else can help me to fix this problem ? Sorry, bad english.

Thanks in advance.

[object Object]

You should check these error object, for some error message or something.

But if i think i found the problem here:

 $cordovaSQLite.execute(db, query, [$scope.debtdata])

The number of ‘?’ in your query need to match the number of params you put it into execute method

You got all these VALUES (?,?,?,?,?,?,?,?) values waiting for some input, but you just supply that one ‘$scope.debtdata’ object.

1 Like

if you have correctly configured your request… you do not need to run angular.fromJSON because angulars default contenttype is application/json.

And like inv123 said… you need to but every preparedkey in the array… and not only put the whole object in it.
There are only simple datatypes allowed like integer, string, char, boolean…

So you have to do something like that:

$cordovaSQLite.execute(db, query, [$scope.debtdata.id, $scope.debtdata.anotherKey, ...])

This is my new Controller.js

.controller('AppCtrl', function($scope,$ionicPlatform,$location,$http,$ionicHistory, $ionicModal, $timeout,$cordovaSQLite) {
  $ionicHistory.nextViewOptions({
    disableAnimate:true,
    disableBack:true
  });
  $ionicPlatform.ready(function(){
    $scope.loginData = {};
    $scope.sync = [];
    $scope.doLogin =function(){
      if(window.Connection) {
        //no connection to internet no sync process happen
        if(navigator.connection.type == Connection.NONE) {
            $location.path('/app/playlists');
        }
        //connection available sync process will execute
        else{
          $http.get('http://ip-server/pokok_apps/kdma/fetch.php', {params:{"ic_no": $scope.loginData.ic_no,"password": $scope.loginData.password}})
            .success(function(data){
              $scope.li = data;
                var query = "INSERT INTO user_kdma (id,fullname,ic_no,private_phone,office_phone,position,email,password) VALUES (?,?,?,?,?,?,?,?)";
                $cordovaSQLite.execute(db, query, [$scope.li.id,$scope.li.fullname,$scope.li.ic_no,$scope.li.private_phone,$scope.li.office_phone,$scope.li.position,$scope.li.email,$scope.li.password]).then(function(res) {
                  $scope.sync.push({id: $scope.li.id,fullname: $scope.li.fullname,ic_no: $scope.li.ic_no,private_phone: $scope.li.private_phone,office_phone: $scope.li.office_phone,position: $scope.li.position,email: $scope.li.email,password: $scope.li.password});
                  console.log(data);
                }, function (err) {
                  console.error(err);
                });
              //$location.path('/app/browse');
            })
            .error(function(data){
              alert('error');
            });
          
        }
      }
    }
      }); 
)}

ADB LOGCAT

V/sqlg    ( 5396): prepare db 0x7fca821f8b88 sql BEGIN
V/sqlg    ( 5396): sqlc_st_finish 0x7fca82302148
V/sqlg    ( 5396): prepare db 0x7fca821f8b88 sql INSERT INTO user_kdma (id,fullname,ic_no,private_phone,office_phone,position,email,password) VALUES (?,?,?,?,?,?,?,?)
V/sqlg    ( 5396): sqlc_st_finish 0x7fca82302288
V/sqlg    ( 5396): prepare db 0x7fca821f8b88 sql COMMIT
D/SystemWebChromeClient( 5396): file:///android_asset/www/plugins/cordova-plugin-console/www/console-via-logger.js: Line 173 : [object Object]
I/chromium( 5396): [INFO:CONSOLE(173)] "[object Object]", source: file:///android_asset/www/plugins/cordova-plugin-console/www/console-via-logger.js (173)
V/sqlg    ( 5396): sqlc_st_finish 0x7fca82302288

[object Object] come from console.log(data); . What thats mean ? I should convert that data to string ?

I also check in SQLite mobile. See at photo below

Its look like the SQL query executed but the data from JSON not inserted. I try to figure out one by one . In index.html , I try to display the data and its works, the data from JSON successfully display at index.html. I use ng-repeat=‘list in li’ method. So where the actually error come from ? I think some code or others method I not using it to insert that data into SQLite database. I hope you and another members in this forum come to help me to fix this problem .

Problem solved.

I try create my own code and logic. This is my solution how to insert data from JSON format into SQLite
controller.js

.controller('AppCtrl', function($scope,$ionicPlatform,$location,$http,$ionicHistory, $ionicModal, $timeout,$cordovaSQLite) {
  $ionicHistory.nextViewOptions({
    disableAnimate:true,
    disableBack:true
  });
  $ionicPlatform.ready(function(){
    $scope.loginData = {};
    $scope.doLogin =function(){
      if(window.Connection) {
        //no connection to internet no sync process happen
        if(navigator.connection.type == Connection.NONE) {
            $location.path('/app/playlists');
        }
        //connection available sync process will execute
        else{
          $http.get('http://ip-server/pokok_apps/kdma/fetch.php', {params:{"ic_no": $scope.loginData.ic_no,"password": $scope.loginData.password}})
            .success(function(data,status,headers,config){
              var user = data;
              
              for(i = 0; i<user.length; i++){
                var id = user[i].id;
                var fullname = user[i].fullname;
                var ic_no = user[i].ic_no;
                var private_phone = user[i].private_phone;
                var office_phone = user[i].office_phone;
                var position = user[i].position;
                var email = user[i].email;
                var password = user[i].password;
              }
                var query = "INSERT INTO user_kdma (id,fullname,ic_no,private_phone,office_phone,position,email,password) VALUES (?,?,?,?,?,?,?,?)";
                $cordovaSQLite.execute(db, query, [id,fullname,ic_no,private_phone,office_phone,position,email,password]).then(function(res) {
                  console.log(fullname);
                }, function (err) {
                  console.error(err);
                });
              $location.path('/app/browse');
            })
            .error(function(data,status,headers,config){
              alert('error');
            });
          
        }
      }
    }
  });

With this code, my problem solved. Maybe you guys have another solution apart of from the method I’m using. Thanks in advance

1 Like

When you got [object Object], it come from console.log(data);

When you see [object Object] it means its not a basic type, its an object, you should have a look at it in debug mode, putting the break point on the console.log. Or have a look at this: http://stackoverflow.com/questions/208016/how-to-list-the-properties-of-a-javascript-object.

So getting that object Object in console doesn’t mean there is an error, also notice the info tag on it.

Thanks a lot for sharing this solution. it was so useful to me. Thanks. :slight_smile:

for ionic 2 , typescript code . Please share it.