IBM Worklight API Cordova storage - Sql error: cou

问题:

i have a problem with the insert statement in sqlite. I have an app which get data from remote db (localhost) with an sql adapter and store them into a device. I have created a db into the device which the same schema of the "remote" db. I want to copy all records from remote db to device db but when I run the insert statement with api cordova storare i get the error could not execute statement due to a constaint failure (19 constraint failed). It is strange.

here the function which create db on device

function createLocalDb(size){

    function createDB(tx) {
         tx.executeSql("CREATE TABLE IF NOT EXISTS canti (" +
                       "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                       "titolo VARCHAR(50) NOT NULL, " +
                       "autore VARCHAR(50) NOT NULL, " +
                       "id_categoria VARCHAR(50), " +
                       "testo TEXT," +
                       "UNIQUE(titolo,autore))");
         tx.executeSql("CREATE TABLE IF NOT EXISTS categorie (" +
                       "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                       "titolo VARCHAR(50) NOT NULL UNIQUE, " +
                       "attiva INTEGER(1) DEFAULT '0')");

    }

    function errorCB(err) {
        WL.Logger.debug("Error processing SQL on createLocalDb: " + err.message);
    }

    function successCB() {
        WL.Logger.debug("Database created correctly");

        WL.Client.invokeProcedure({
            adapter : 'DbConnect',
            procedure : 'getCanti',
            parameters: []
        }, {
            onSuccess : success,
            onFailure : failure
        });

        function success(result){
            WL.Logger.debug("Success on invoking getCanti procedure");
            populateCanti(result);
        }
        function failure(result){
            WL.Logger.debug("Failure on invoking getCanti procedure");
        }

        WL.Client.invokeProcedure({
            adapter : 'DbConnect',
            procedure : 'getCategorie',
            parameters: []
        }, {
            onSuccess : success2,
            onFailure : failure2
        });

        function success2(result){
            WL.Logger.debug("Success on invoking getCategorie procedure");
            populateCategorie(result);
        }
        function failure2(result){
            WL.Logger.debug("Failure on invoking getCategorie procedure");
        }

    }   

    if(size>0){
        var db = window.openDatabase("db_canti", "1.0", "db_canti", size);
        db.transaction(createDB, errorCB, successCB);
    }
    else{
        WL.Logger.debug("Cannot create database with size 0");
    }
}

and here the two functions to populate the tables:

function populateCanti(item){

    var db = window.openDatabase("db_canti", "1.0", "db_canti", size);
    db.transaction(populateDB, errorCB, successCB);

    function populateDB(tx){
        for(var i=0;i<item.invocationResult.resultSet.length;i++){
            WL.Logger.debug(i+")Sto inserendo " + item.invocationResult.resultSet[i].titolo + "," + item.invocationResult.resultSet[i].autore + "," + item.invocationResult.resultSet[i].id_categoria);
            tx.executeSql("INSERT INTO canti(titolo,autore,id_categoria,testo) " +
                    "VALUES('"+item.invocationResult.resultSet[i].titolo+"','" +
                            +item.invocationResult.resultSet[i].autore+"','" +
                            +item.invocationResult.resultSet[i].id_categoria+"','" +
                            +item.invocationResult.resultSet[i].testo+"')");
        }
    }

    function successCB(err) {
        WL.Logger.debug("Table 'canti' OK");
        checkLocalDb();
    }

    function errorCB(err) {
        WL.Logger.debug("Error processing SQL on populateCanti: " + err.message);
    }

}

function populateCategorie(item){

    var db = window.openDatabase("db_canti", "1.0", "db_canti", size);
    db.transaction(populateDB, errorCB, successCB);

    function populateDB(tx){
        for(var i=0;i<item.invocationResult.resultSet.length;i++){
            tx.executeSql("INSERT INTO categorie(titolo,attiva) " +
                    "VALUES('"+item.invocationResult.resultSet[i].titolo+"','" +
                            +item.invocationResult.resultSet[i].attiva+"')");
        }
    }

    function successCB(err) {
        WL.Logger.debug("Table 'categorie' OK");
    }

    function errorCB(err) {
        WL.Logger.debug("Error processing SQL on populateCategorie: " + err.message);
    }

}

Also, the table categorie caught the error but the insert works apparently , because when I see the web storage on browser, there are 19 records in it.

回答1:

If the records are in the table, then the UNIQUE constraints prevented you from inserting duplicates.

To prevent getting errors for the duplicates, replace INSERT with INSERT OR IGNORE.

回答2:

Just drop your table and recreate it, or for the safer side check if all the rows you are trying to insert exist or don't exist..

I have had similar situation before, all i did was to drop the table and create it again. what i realise was that i was trying to insert into a table column that does exist or has changed when i update the table rows.