Read a text file with JQuery and store in DataBase

Question

I have a file containing

1 : "Benz"
2 : "Bmw"
3 : "Porche"
4 : "Ferrari"

And I would like to read it with Jquery and store them in Local Database in OpenDataBase, with 1 will be a number of step and Benz will be the other field in data base.

my code for reading the file

jQuery.get('file/words.txt', function(data) {
    alert(data)
});

and my code for creating database

var db = openDatabase( ’mydb’, ’1.0’,
                   ’database for test’,
                   2 * 1024 * 1024
                 );
db.transaction(function (tx) {
tx.executeSql(’CREATE TABLE IF NOT EXISTS Car
                (number INT, name VARCHAR(100))’);

});

I don't know how can i separate the data and put them in database with javascript.

Thanks.

Answer 1

Here's the code do you what you want:

// assume this is your data (I've added the newline as well)
var textData = '1 : "Benz" \n2 : "Bmw" \n3 : "Porche"\n4 : "Ferrari" ';

// turn data into array
var ary = textData.split('\n').map(function(v) {
    return v.split(':').map(function(v2) {
        // make sure we remove the quotes and spaces
        return v2.replace(/"/g, '').trim();
    });
})

// function to escape double quotes to prevent sql injection
function escapeSql(str) {
    return (typeof str === 'number' ? str : str.replace(/"/g, '"'));
}

// open database
var db = openDatabase('mydb', '1.0', 'database for test', 2 * 1024 * 1024);

// create table
db.transaction(function(tx) {
    tx.executeSql('create table if not exists Car(step, make)');
});

// insert data
db.transaction(function(tx) {
    // loop through each item and insert the data, notice how we call escapeSql to prevent sql injection
    ary.forEach(function(item) {
        var sql = 'insert into Car(step, make) values(' + escapeSql(item[0]) + ', "' + escapeSql(item[1]) + '")';
        tx.executeSql(sql);
    });
});

var sql,
    cars = [];

// read data from table
db.transaction(function(tx) {
    tx.executeSql('select * from Car', [], function(tx, results) {
        var len = results.rows.length;
        for (var i = 0; i < len; i++) {
            cars.push({
                step: results.rows.item(i).step,
                make: results.rows.item(i).make
            });
        }

        console.log(cars);
    }, null);
});

Output: