Uploaded image for project: 'Titanium SDK/CLI'
  1. Titanium SDK/CLI
  2. TIMOB-27848

Android: Ti.Database wrongly stores booleans/numbers as strings via query parameters

    Details

      Description

      Summary:
      The Ti.Database module's DB.execute() method wrongly stores values of type boolean and number as type string when passed via query parameters.

      // Will store boolean false as string "false". Should be stored as number 0.
      db.execute("INSERT INTO data(value) VALUES (?)", false);
       
      // Will store number as string "123.456". Should be stored as number.
      db.execute("INSERT INTO data(value) VALUES (?)", 123.456);
      

      Note:
      This is not an issue on iOS. Values of type boolean and number are stored as numbers. Also note that SQLite does not natively support boolean types and will be stored as integers instead.

      Steps to reproduce:

      1. Build and run the below code on Android.
      2. Look in the log.

      var dbConnection = Ti.Database.open("test.db");
      dbConnection.execute("CREATE TABLE IF NOT EXISTS properties(name PRIMARY KEY, value);");
      var sqlInsertStatement = "INSERT OR REPLACE INTO properties(name, value) VALUES (?, ?);";
      dbConnection.execute(sqlInsertStatement, "null", null);
      dbConnection.execute(sqlInsertStatement, "boolean-false", false); // uh-oh!
      dbConnection.execute(sqlInsertStatement, "boolean-true", true);   // uh-oh!
      dbConnection.execute(sqlInsertStatement, "integer-0", 0);         // uh-oh!
      dbConnection.execute(sqlInsertStatement, "integer-2", 2);         // uh-oh!
      dbConnection.execute(sqlInsertStatement, "float", 123.456);       // uh-oh!
      dbConnection.execute(sqlInsertStatement, "string-empty", "");
      dbConnection.execute(sqlInsertStatement, "string-not-empty", "Hello World");
      var resultSet = dbConnection.execute("SELECT name, value FROM properties;");
      while (resultSet.isValidRow()) {
      	var name = resultSet.field(0);
      	var value = resultSet.field(1);
      	Ti.API.info(`@@@ db entry "${name}": ${value} (type ${typeof value})`);
      	resultSet.next();
      }
      dbConnection.close();
      

      Results from Android:
      Notice entries of type boolean, integer, and float are of type string. They should all be of type number.

      [INFO]  @@@ db entry "null": null (type object)
      [INFO]  @@@ db entry "boolean-false": false (type string)
      [INFO]  @@@ db entry "boolean-true": true (type string)
      [INFO]  @@@ db entry "integer-0": 0 (type string)
      [INFO]  @@@ db entry "integer-2": 2 (type string)
      [INFO]  @@@ db entry "float": 123.456 (type string)
      [INFO]  @@@ db entry "string-empty":  (type string)
      [INFO]  @@@ db entry "string-not-empty": Hello World (type string)
      

      Results from iOS:

      [INFO]  @@@ db entry "null": null (type object)
      [INFO]  @@@ db entry "boolean-false": 0 (type number)
      [INFO]  @@@ db entry "boolean-true": 1 (type number)
      [INFO]  @@@ db entry "integer-0": 0 (type number)
      [INFO]  @@@ db entry "integer-2": 2 (type number)
      [INFO]  @@@ db entry "float": 123.456 (type number)
      [INFO]  @@@ db entry "string-empty":  (type string)
      [INFO]  @@@ db entry "string-not-empty": Hello World (type string)
      

      Native Android Limitations:
      There's a HUGE limitation on Google's end where its Java SQLiteDatabase class only supports query parameters of type String for its SELECT query methods.
      SQLiteDatabase.rawQuery()

      This class does not have this limitation for its SQL execution methods which take Object[] arrays for query params which we can use for INSERT, UPDATE, DELETE, etc., but these methods cannot returns rows.
      SQLiteDatabase.execSQL()
      SQLiteStatement.execute()

      So, Google not providing an API to allow a SELECT with a numeric query parameter is a huge blocker for this ticket.

      Work-Around:
      When creating a table, assign the column a numeric affinity such as NUMERIC, INTEGER, or REAL. SQLite will automatically convert parameter values to that numeric affinity if possible. You can still store strings to a numeric column. However, a boolean will still be stored as a string.

      CREATE TABLE IF NOT EXISTS properties(name PRIMARY KEY, value NUMERIC);
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jquick Joshua Quick
                Reporter:
                jquick Joshua Quick
              • Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Backbone Issue Sync

                  • Backbone Issue Sync is enabled for your project, but we do not have any synchronization info for this issue.

                    Git Integration