javascriptsqliteinsertgoogle-gears

Difference when using last_insert_row() in Javascript function vs. in DB Query Tool?


I have created a table in Google Gears (Sqlite):

db.execute('create table if not exists SPALINKS (link_id int PRIMARY KEY, sid1 int, sid2 int, label text, user text, Timestamp int');

When using the Google Gears Database Query Tool (in FF), INSERT with 'last-insert-rowid' work fine. Inserts from Javascript work fine, as long a I don't use 'last-insert-rowid'. But when using 'last-insert-rowid' it doesn't work anymore:

    var sql_stmt = 'INSERT INTO SPALINKS (link_id, sid1, sid2, label, user, Timestamp) VALUES (last_insert_rowid(),?,?,?,?,?)';
var arg_array = [sid1, sid2, label, user, creation_time];
db.execute(sql_stmt, arg_array);

Error: Database operation failed. ERROR: constraint failed DETAILS: constraint failed

Why does SQLite's 'last-insert-rowid' work fine with the DB Tool, but not in a SQL statement that is created and executed from inside a Javascript function?


Solution

  • I think that you do not have to use last_insert_rowid() in that situations. Why just make INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT.

    // if you writing for android and you have something like this:
    //document.addEventListener("deviceready", onDeviceReady, false);
    //function onDeviceReady(){
    // make sure you comment it if you debugging in browser
    
    // connection to the databse
    var db = window.openDatabase("demo.db", "1.0", "Demo Database", 200000);
    
    // There is problem with SQLite recognize new Date() format so I include http://code.google.com/p/datejs/ library in my project and format the date to be recognizable by SQLite
    
    function nowTime(){
            return new Date().toString("yyyy-MM-dd HH:mm:ss");
        }
    
    function errorCallBack(err) {
                alert("Error processing SQL: " + err.code);
            }
    function callBack(){
    
        function readTransactionError(err){
            alert('Read Transaction Error: ' + err);
        }
    
    // you can make your vars global if you want to use them out side of function!!!
    
        function querySuccess(tx, results) {
           var len = results.rows.length;
            for(var i = 0; i < len; i++) {
               var sid1Var = results.rows.item(i).sid1;
               var sid2Var = results.rows.item(i).sid2;
               var labelVar = results.rows.item(i).label;
               var userNameVar = results.rows.item(i).userName;
               var timeStampVar = results.rows.item(i).timeStamp;
        }
      }
    
    tx.readTransaction('select * from SPALINKS;', [], querySuccess, readTransactionError);
    }
    function functionName(sid1, sid2, label, userName) {
        db.transaction(function(tx) {
            db.executeSql('create table if not exists SPALINKS (link_id int INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, sid1, sid2, label, userName, timeStamp');
    
            db.executeSql('insert into SPALINKS (sid1, sid2, label, userName, timeStamp) VALUES (?, ?, ?, ?, ?)', [sid1, sid2, label, userName, nowTime()], callBack, errorCallBack);
        });
    }
    // assuming you use jQuery
    $('#selectorID').click(function(){
    functionName(sid1, sid2, label, userName);
    });
    

    You can use last_insert_rowid() in situation like UPDATE:

    function updateFunction() {
    db.transaction(function(tx) {
        tx.executeSql('update SPALINKS set timeStamp = "' + nowTime() + '" WHERE link_id = last_insert_rowid()');
    });
    }
    

    I hope this is going to work.