Cordova-sqlite-storage: Multiple rows insertion single query

[ghost]

I am trying to insert multiple rows in one query. In order to do this I have a variable containing all the data that I want inserted, which I give as a parameter to executeSql.

This is the code I am using right now:

function retrieveUser() {

$.getJSON("url", function (json) {
    var resultJSON = json;
    var data = [];

        $.each(resultJSON.user, function (index, element) {
            data.push(element.id);
            data.push(element.name);
        });

    app.db.sqlBatch([
        [ 'INSERT INTO user (id,name) VALUES (?,?)', [data] ]
    ]);

Variable contains all the data that I want. I want its content mapped to rows 1:n of column A and B, but it just dumps everything into row 1 of column A. I tried using multiple variables, so 1 for id and 1 for name, but it then just dumps everything into row 1 of column A and row 1 of column B.

How do I make it recognize they are separate rows?

[brodybits]

I would modify the code to something like this (sorry not tested):

function retrieveUser() {

  $.getJSON("url", function (json) {
    var resultJSON = json;
    var ph = '';
    var data = [];

        $.each(resultJSON.user, function (index, element) {
            data.push(element.id);
            data.push(element.name);
            if (index !== 0) ph += ',';
            ph += '(?,?)';
        });

    app.db.sqlBatch([
        [ 'INSERT INTO user (id,name) VALUES '+ph, data ]
    ]);
  });
}

This multiple insert syntax is discussed in #282. I will add this to the documentation when I get a chance.

[ghost]

Thank you for the reply, it is well appreciated.

I tried using your code, but it returns the following error:
Populate table error: a statement with no error handler failed: sqlite3_prepare_v2 failure: too many SQL variables

I tried looking up what it could mean, but I'm stumped. I'm retrieving 658 rows, so 'ph' contains 3947 characters (658 x "(?,?),", if that matters.

[brodybits]

I forgot to mention: I think the limit is 500 parameters. I wish they would fix this.

[ghost]

Forgive my ignorance, but does that mean that until they fix the limit it is not possible to insert more than 500 rows(?) in a single execute query?

[brodybits]

does that mean that until they fix the limit it is not possible to insert more than 500 rows(?) in a single execute query?

I think the limit is 500 ? parameters. If each row takes 2 parameters then I think it would be 250 rows. I hope I got the number right, you may want to check the sqlite docs or maybe just experiment.

[reconka]

SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.

https://www.sqlite.org/limits.html

split your query, its very simple

[ghost]

Thank you both for the help. I understand now what the problem is.

My current case table holds 658 rows, each of them having 14 fields. So I would be looking at splitting 9.212 parameters. How would the code be able to keep track of which of the JSON file it has already gone through, though?

I assume it would be best to use a counter to keep track of how many it has already done, but how would it know _which_ ones are already done? If I split them into separate queries, I would have to make sure it does not scrape and insert the same JSON data into the table.

[reconka]

count ph variable and data variable, and push to array, than put that array into sqlBatch.