best way to insert data into android sqlite database using large json data coming from server using cordova

Question

I am new to Cordova. I am working on a hybrid app(Targeting Android OS) where on request database tables are populated from the json data coming from the server.

Now my problem is when the json data was lesser, insertion was faster. Now the JSON data size is around 40MB which i am sending from the server in Gzip format, so the device is able to download the json data faster, but it is taking a lot of time to insert the data into the database tables, even sometimes it hangs.

Sometimes device closes the application while inserting data into the sqlite database. I think, the insertion is taking a lot of memory which crashes the app.

var tx = window.sqlitePlugin.openDatabase({ name: "database_name.db" });


tx.transaction(function(db) {
    $.each(result[i],function(j,field){
        var insert_gen_code_desc = "insert or replace  into  table-name ( id , pk_code_group , pk_code , description , created_by , created_on , updated_by , updated_on , last_update , status , lastupdate , sl_no, upload_status ) values ("+result[i][j][0]['id']+",'"+result[i][j][0]['pk_code_group']+"','"+result[i][j][0]['pk_code']+"','"+result[i][j][0]['description']+"','"+result[i][j][0]['created_by']+"','"+result[i][j][0]['created_on']+"','"+result[i][j][0]['updated_by']+"','"+result[i][j][0]['updated_on']+"',"+result[i][j][0]['last_update']+",'"+result[i][j][0]['STATUS']+"','"+result[i][j][0]['lastupdate']+"',"+result[i][j][0]['sl_no']+",'0')";
db.executeSql(insert_gen_code_desc);
});
},errorCallback, successCallback);

Can someone tell me what i am doing wrong because of which my app is getting crashed while inserting large amount of data into the table or suggest me some way for faster insertion using less memory(RAM).

Note: I am using com.brodysoft.sqlitePlugin plugin for sqlite database operations.

Try to use a db transaction while inserting a larger set of data to optimise insertions. — nikhil.thakkar
– nikhil.thakkar, Commented Aug 15, 2015 at 5:28
thanks for reply @nikhil.thakkar. i am using transaction, you can see my code db.executeSql() is inside transaction. I m building sql query and execute the query inside transaction. — AmritM
– AmritM, Commented Aug 15, 2015 at 5:40
Sorry I overlooked. What I meant was this .. INSERT INTO 'tablename' ('column1') VALUES ('val1'), ('val2'), ('val2'), ('val2'); You need to construct a string like this and then execute this sql string in a single executeSql statement. This will make it more optimissed. — nikhil.thakkar
– nikhil.thakkar, Commented Aug 15, 2015 at 5:46
no i dont think it will optimize....i need some trick to free up memory — AmritM
– AmritM, Commented Aug 15, 2015 at 19:53

brodybits · Accepted Answer · 2015-08-21 11:51:44Z

0

This is a known limitation. The version at https://github.com/litehelpers/Cordova-sqlite-enterprise-free (which has a different licensing scheme) can handle larger transactions. As a workaround, you can simply store the data using smaller, multiple transactions.

answered Aug 21, 2015 at 11:51

brodybits

5416 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

best way to insert data into android sqlite database using large json data coming from server using cordova

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related