Insert data in sqlite cordova in related tables by foreign keys

Question

Insert data in sqlite cordova in related tables by foreign keys

Navigation

#1 by (2 votes)

1

I am trying to insert information into a database on a mobile device using a cordova plugin to make use of the sqlite APIs, but I have not been able to do so. I am developing my application using the Ionic development framework.

The code I have is the following:

insertarTablas: function(db) {

            var datosPersona, datosTelefono, insertarPersona, insertarTelefono, largoPer, largoTel, i, j;

            datosPersona = [{
                cedula: 123456,
                nombre: "pepe",
                apellido: "perez"
            }];

            datosTelefono = [{
                numero: 30020025,
                cedula: 123456
            }, {
                numero: 6998877,
                cedula: 123456
            }, {
                numero: 58965475,
                cedula: 123456
            }];

            insertarPersona = "INSERT INTO Persona(cedula, nombre, apellido) VALUES(?,?,?)";
            insertarTelefono = "INSERT INTO Telefono(numero, cedula_tel) VALUES(?,?)";

            largoPer = datosPersona.length;
            largoTel = datosTelefono.length;

            for (i = 0; i < largoPer; i++) {
                $cordovaSQLite.execute(db, insertarPersona, [datosPersona[i].cedula, datosPersona[i].nombre, datosPersona[i].apellido]);
            }

            for (i = 0; i < largoTel; i++) {
                $cordovaSQLite.execute(db, insertarTelefono, [datosTelefono[i].numero, datosTelefono[i].cedula]);
            }

        }

In this image, differentiate with colors the insert and consult processes.

The red color is the record that is inserted in the person, that is, the person object.
The green color shows that it shows that the three registers are inserted in three, always starting from the 0 position.
The blue sky, the number of rows affected by the query, knowing that I put as criterion * that specifies all, and so I throw 1, which is the number of records.
Finally a purple color, which shows a single record in the phone, which is supposed to keep 3.

cordova ionic sqlite ng-cordova

asked by Pedro Miguel Pimienta Morales 23.12.2016 в 01:46

source

1 answer

Error in Java zombie game Excel Conditional Format Icons, without Conditional Format

score 2 · Accepted Answer

Try this way:

var db = window.sqlitePlugin.openDatabase({name: "my.db"});

insertarPersona = "INSERT INTO Persona(cedula, nombre, apellido) VALUES(?,?,?)";
insertarTelefono = "INSERT INTO Telefono(numero, cedula_tel) VALUES(?,?)";


for (i = 0; i < largoPer; i++) {
db.transaction(function(tx) {
    tx.executeSql(insertarPersona, [datosPersona[i].cedula, datosPersona[i].nombre, datosPersona[i].apellido], function(tx, res) {
        console.log("id insertada: " + res.insertId);
    }, function(e) {
         console.log("ERROR: " + e.message);
    });
 });
}

And to count the records

  db.transaction(function(tx) {
      tx.executeSql("select count(*) as cnt from Telefono;", [], function(tx, res) {
            console.log("cantidad de registros : "+  res.rows.item(0).cnt);
      });
  })

EDIT

consultarTelefono: function(db) { 
    var consultaTelefono; 
    consultaTelefono = "select count(*) as cnt from Telefono;";     

    $cordovaSQLite.execute(db, consultaTelefono, []).then(function(resultado) { 
      console.log(resultado.rows.item(0).cnt);
    });
}

EDIT 2

As we talked about it apparently the error was not how the queries were executed nor what query was it, simply that in your code you had incorrectly attached the item with the counter of your for

for (j = 0; j < largoTel; j++) {
      $cordovaSQLite.execute(db, insertarTelefono, [datosTelefono[j].numero, datosTelefono[j].cedula]);