Cordova-sqlite-storage: Error with data affinity

[DualH]

Hi, my problem is when i insert data, change the value type number in column type text like Number .0,

Example:

Table: example
Columns: name (TEXT), telephone (TEXT)

Data for insert: 'Name', '012012012';
Data return from select: 'Name', '012012012.0'

What is the problem? in all data type real add .0 before insert in column (TEXT).

thank's.

[brodybits]

Does not look valid to me. Please show sample code to demonstrate your issue and which platform you see this on. I have gone through extensive testing efforts to avoid this kind of issue.

[DualH]

Hi @brodybits,
My platform is iOS, with framework ionic 3

let sql = 'INSERT INTO example (name, phone) VALUES (?, ?)';
return this.db.executeSql(sql, ['example', '012012012');

So, now when select data inserted, show from consola.log;

let sql = 'SELECT * FROM example';
return this.db.executeSql(sql, [])
.then(response => {
    let data = [];
    for (let index = 0; index < response.rows.length; index++) {
        data.push( response.rows.item(index) );
    }
    console.log('---------------------')
    console.log(data);
    console.log('---------------------')
    return Promise.resolve(data[0]);      
});

this console.log(data); return the data phone like '012012012.0';

[brodybits]

Hi @DualH,

Thanks for the information so far. I would like to ask for a few more things:

Are you using cordova-plugin-wkwebview-engine?

Please show how the table was created. This can affect how data is stored.

The following line of code does not look right to me (missing bracket), please fix:

js return this.db.executeSql(sql, ['example', '012012012');

Also it would be great if you can highlight your JavaScript as follows:

```js
// code here
```

Makes it much easier to read.

[DualH]

Ok, my function to create is this

IMPORTANT: In Android work fine, the problem is in iOS

Are you using cordova-plugin-wkwebview-engine?

No

create(){
    let sql = 'CREATE TABLE IF NOT EXISTS example(name TEXT, telephone TEXT)';
    return this.db.executeSql(sql, []);
}

insert(){
    let sql = 'INSERT INTO example (name, telephone) VALUES (?, ?)';
    return this.db.executeSql(sql, ['example', '012012012']);
}

select(){
    let sql = 'SELECT * FROM example';
    return this.db.executeSql(sql, [])
    .then(response => {
      let data = [];
      console.log(response);
      for (let index = 0; index < response.rows.length; index++) {
        data( response.rows.item(index) );
      }
      return Promise.resolve(data[0]);      
    });
}

Before update plugin the version was 2.1.1 and does work correctly.

This is because change names of variables and data, sorry, but the structure it's the same

return this.db.executeSql(sql, ['example', '012012012');

[brodybits]

Thanks for the more detailed information, I will try it over the weekend (Sunday). I forgot something else:

I generally do not test this plugin with Angular or Ionic, and I have seen some really strange things happen with these frameworks. It would be extra helpful if you could make a reproduction without any such framework, based on https://github.com/brodybits/cordova-sqlite-storage-starter-app for example.

_P.S. Marking this behavior for testing before major release in July 2018 ref: #773_

[brodybits]

The following test, which will be part of the next release of this plugin, shows the TEXT column value binding working as expected:

        it(suiteName + "'012012012' string INSERT value bindings", function(done) {
          var db = openDatabase('012012012-string-INSERT-value-bindings.db');

          var myValue = '012012012';
          var myValueAsWholeNumber = 12012012;

          db.transaction(function(tx) {
            tx.executeSql('DROP TABLE IF EXISTS tt');
            tx.executeSql('CREATE TABLE IF NOT EXISTS tt (data1, data2 TEXT, data3 NUMERIC, data4 INTEGER, data5 REAL)', null, function(ignored1, ignored2) {
              tx.executeSql('INSERT INTO tt VALUES (?,?,?,?,?)',
                  [myValue, myValue, myValue, myValue, myValue], function(ignored, rs1) {
                expect(rs1).toBeDefined();
                expect(rs1.rowsAffected).toBe(1);
                expect(rs1.insertId).toBe(1);

                tx.executeSql('SELECT * FROM tt', [], function(ignored, rs2) {
                  // CHECK BIG INTEGER number was inserted properly:
                  expect(rs2).toBeDefined();
                  expect(rs2.rows).toBeDefined();
                  expect(rs2.rows.length).toBe(1);

                  var resultRow2 = rs2.rows.item(0);
                  expect(resultRow2.data1).toBe(myValue);
                  expect(resultRow2.data2).toBe(myValue);
                  expect(resultRow2.data3).toBe(myValueAsWholeNumber);
                  expect(resultRow2.data4).toBe(myValueAsWholeNumber);
                  expect(resultRow2.data5).toBe(myValueAsWholeNumber);

                  tx.executeSql('SELECT TYPEOF(data1) AS t1, TYPEOF(data2) AS t2, TYPEOF(data3) AS t3, TYPEOF(data4) AS t4, TYPEOF(data5) AS t5 FROM tt', [], function(ignored, rs3) {
                    expect(rs3).toBeDefined();
                    expect(rs3.rows).toBeDefined();
                    expect(rs3.rows.length).toBe(1);

                    var resultRow3 = rs3.rows.item(0);
                    expect(resultRow3.t1).toBe('text');
                    expect(resultRow3.t2).toBe('text');
                    expect(resultRow3.t3).toBe('integer');
                    expect(resultRow3.t4).toBe('integer');
                    expect(resultRow3.t5).toBe('real');

                    // Close (plugin only) & finish:
                    (isWebSql) ? done() : db.close(done, done);
                  });

                });
              });
            });
          }, function(error) {
            // NOT EXPECTED:
            expect(false).toBe(true);
            expect(error.message).toBe('---');
            // Close (plugin only) & finish:
            (isWebSql) ? done() : db.close(done, done);
          });
        }, MYTIMEOUT);

I would suspect either Ionic SQLite or something in your application code is causing the improper data to be stored or retrieved. Closing as invalid. If you can make a demo program that proves me wrong then please post here or in a new issue and I would be happy to investigate further.

[DualH]

One question, this test was from Angular 5?

[brodybits]

No this is a new test will be added to the Jasmine test suite. I hope this is helpful.

If this remains an issue I would recommend you start by playing with https://github.com/brodybits/cordova-sqlite-storage-starter-app (no Ionic), try storing and retrieving some sample data there, then continue with something like https://github.com/iursevla/ionic3-PreDB (with Ionic, uses cordova-sqlite-ext plugin version for pre-populated databases). The general strategy is to try building up and building down until you can pinpoint what does and does not work.

In general https://github.com/litehelpers/Cordova-sqlite-help/issues is the best place to request this kind of help. Commercial support is also available, please contact [e-mail] for more details.

[DualH]

Hello @brodybits my solve like a patch is before insert data change this like string in this way.

Posible solution:

data.telephone = data.telephone.toString();

With one exception when return data from select:

data[0].telephone = data[0].telephone.replace('.0', '');

Regards.

[brodybits]

Very interesting, thanks for reporting.

BTW this indicates to me that your JavaScript was storing the input number as a number, which could possibly lead to some other issues in your application. Something you may want to take a look at.