SQLite storage plugin, can't insert data from a form

Melvin_R_ · ‎01-02-2016

I have tried a lot a ways to trigger the insert query, but failed everytime, the closest one is the following code

document.addEventListener("deviceready", onDeviceReady, false);
           function onDeviceReady(){
               var db = window.sqlitePlugin.openDatabase({name: "users.db"});
           }
           function registrar() {
               nombre = document.getElementById("nombre").value;
               telefono = document.getElementById("telefono").value;
               correo = document.getElementById("correo").value;

               var db = window.sqlitePlugin.openDatabase({name: "users.db"});
               db.transaction(populateDB, errorCB, successCB);
           }

           // create table
           function populateDB(tx) {
               tx.executeSql('DROP TABLE IF EXISTS data');
               tx.executeSql('CREATE TABLE IF NOT EXISTS data (id integer primary key autoincrement, nombre text, telefono integer, correo text)');
               tx.executeSql('INSERT INTO data (nombre, telefono, correo) VALUES ("'+nombre+'","'+telefono+'","'+correo+'")');
               queryDB(tx);
           }

           // form the query
           function queryDB(tx) {
               tx.executeSql("SELECT * from data;", [], querySuccess, errorCB);
           }

           // Display the results
           function querySuccess(tx, results) {
               var len = results.rows.length;
               alert("Datos: " + results.rows.length + " [should be 1]");
                   for (var i = 0; i < len; i++) { // loop as many times as there are row results
                   document.getElementById("output").innerHTML +=
                   "<table><tr><td>ID = " + results.rows.item(i).nombre +
                   "</td><td>telefono = " + results.rows.item(i).telefono +
                   "</td><td>correo = " + results.rows.item(i).correo + "</td></tr></table>";
               }
           }
           // Transaction error callback
           function errorCB(err) {
               alert("Error procesando Base de Datos : " + err.code);
           }
           // Success error callback
           function successCB() {
               alert('Gracias por Registrarte');
           }

This code is not inserting any data, but when I hit the button calling registrar() function with no data in the input, it launches the success callback. So I guees it registered when is blank.

What am I doing wrong here?

What I need is the user to register, and to show the last inserted row, fetching it from the database. Help please

I'm using this plugin https://www.npmjs.com/package/cordova-sqlite-storage

Rodrigo_M_1 · ‎01-04-2016

Melvin , I recommend you to use the Intel XDK Featured Plugins. There you can add the SQLite!

Go to Projects, Plugin Management, Add Plugins to this Project, Featured Plugins. And Select the SQLite Native Storage that the Version is 0.7.14.

Another question: Which device are you trying to test it ?

Melvin_R_ · ‎01-04-2016

Rodrigo M. wrote:

Melvin , I recommend you to use the Intel XDK Featured Plugins. There you can add the SQLite!

Go to Projects, Plugin Management, Add Plugins to this Project, Featured Plugins. And Select the SQLite Native Storage that the Version is 0.7.14.

Another question: Which device are you trying to test it ?

I did that, and I'm testing with my Sony Xperia Z1, not an emulator.

Look, when I use the example below it runs fine, but when I try to call the function to insert from the form, doesnt work.

https://iphonedevlog.wordpress.com/2014/04/07/installing-chris-brodys-sqlite-database-with-cordova-cli-android/

So there must be something wrong with my code. I can't figure out what.

Rodrigo_M_1 · ‎01-04-2016

Definitely!

Check my template code:

// Wait for Cordova to load
var db;
document.addEventListener("deviceready", onDeviceReady, false);

// Cordova is ready
function onDeviceReady() {
    //This Enable/Disable the Screen Orientation
    intel.xdk.device.setAutoRotate(true);
    // This will create the Database.  
    //db = window.sqlitePlugin.openDatabase("Database", "1.0", "Demo", -1);
    db = window.sqlitePlugin.openDatabase(
    // options
    {
      name: "MyDatabase.db",
      location: 0 // for iOS (0=Documents (default, visible in iTunes, backed up by iCloud), 1=Library (not visible in iTunes, backed up by iCloud, 2=Library/LocalDatabase (not visible in iTunes, not backed up by iCloud))
    },
    // success callback
    function (msg) {
      //alert("success Creating DB: " + msg);
    },
    // error callback
    function (msg) {
      alert("error on Create DB: " + msg);
    }
);
    
    createDB();
}

function createDB(){
    db.transaction(function(tx) {
        //tx.executeSql('DROP TABLE IF EXISTS test_table');
        tx.executeSql('CREATE TABLE IF NOT EXISTS test_table(UserID integer, UserName text, TechID text, BranchID text, BranchCode text, DeviceID text, Password text, LogInDate datetime, TruckID text)');
    }, function(e) {
        alert("ERROR: " + e.message);
    });
}

//Call this function to insert:
function InsertUserLog(pUserID, pUserName, pTechID, pBranchID, pBranchCode, pDeviceID, pPassword, pLogInDate, pTruckID){
    db.transaction(function(tx) {
        tx.executeSql("INSERT INTO TblUserLog (UserID, UserName, TechID, BranchID, BranchCode, DeviceID, Password, LogInDate, TruckID) VALUES (?,?,?,?,?,?,?,?,?)", [pUserID, pUserName, pTechID, pBranchID, pBranchCode, pDeviceID, pPassword, pLogInDate, pTruckID], function(tx, res) {
            //alert("insertId: " + res.insertId + " -- probably 1");
            //alert("rowsAffected: " + res.rowsAffected + " -- should be 1");
            updateProgressBar(50);
        }, function(e) {
            alert("ERROR: " + e.message);
        });
    });
}

Melvin_R_ · ‎01-06-2016

Rodrigo M. wrote:

Definitely!

Check my template code:

// Wait for Cordova to load
var db;
document.addEventListener("deviceready", onDeviceReady, false);

// Cordova is ready
function onDeviceReady() {
    //This Enable/Disable the Screen Orientation
    intel.xdk.device.setAutoRotate(true);
    // This will create the Database.  
    //db = window.sqlitePlugin.openDatabase("Database", "1.0", "Demo", -1);
    db = window.sqlitePlugin.openDatabase(
    // options
    {
      name: "MyDatabase.db",
      location: 0 // for iOS (0=Documents (default, visible in iTunes, backed up by iCloud), 1=Library (not visible in iTunes, backed up by iCloud, 2=Library/LocalDatabase (not visible in iTunes, not backed up by iCloud))
    },
    // success callback
    function (msg) {
      //alert("success Creating DB: " + msg);
    },
    // error callback
    function (msg) {
      alert("error on Create DB: " + msg);
    }
);
    
    createDB();
}

function createDB(){
    db.transaction(function(tx) {
        //tx.executeSql('DROP TABLE IF EXISTS test_table');
        tx.executeSql('CREATE TABLE IF NOT EXISTS test_table(UserID integer, UserName text, TechID text, BranchID text, BranchCode text, DeviceID text, Password text, LogInDate datetime, TruckID text)');
    }, function(e) {
        alert("ERROR: " + e.message);
    });
}

//Call this function to insert:
function InsertUserLog(pUserID, pUserName, pTechID, pBranchID, pBranchCode, pDeviceID, pPassword, pLogInDate, pTruckID){
    db.transaction(function(tx) {
        tx.executeSql("INSERT INTO TblUserLog (UserID, UserName, TechID, BranchID, BranchCode, DeviceID, Password, LogInDate, TruckID) VALUES (?,?,?,?,?,?,?,?,?)", [pUserID, pUserName, pTechID, pBranchID, pBranchCode, pDeviceID, pPassword, pLogInDate, pTruckID], function(tx, res) {
            //alert("insertId: " + res.insertId + " -- probably 1");
            //alert("rowsAffected: " + res.rowsAffected + " -- should be 1");
            updateProgressBar(50);
        }, function(e) {
            alert("ERROR: " + e.message);
        });
    });
}

can you give me a full example, I mean a full js and html file? I cant make it work.

Hamilton_Tenório_da_ · ‎01-06-2016

Try insert only one field of the table. See the result.