[TIMOB-27848] Android: Ti.Database wrongly stores booleans/numbers as strings via query parameters
| GitHub Issue | n/a |
|---|---|
| Type | Bug |
| Priority | Low |
| Status | Open |
| Resolution | Unresolved |
| Affected Version/s | n/a |
| Fix Version/s | n/a |
| Components | Android |
| Labels | android, database, parameters, parity, query, type |
| Reporter | Joshua Quick |
| Assignee | Joshua Quick |
| Created | 2020-04-14T02:27:55.000+0000 |
| Updated | 2020-04-17T18:19:22.000+0000 |
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);
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:*
Build and run the below code on Android.
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();
[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)
[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)
String for its SELECT query methods.
[SQLiteDatabase.rawQuery()](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#rawQuery(java.lang.String,%20java.lang.String%5B%5D))
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()](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#execSQL(java.lang.String,%20java.lang.Object%5B%5D))
[SQLiteStatement.execute()](https://developer.android.com/reference/android/database/sqlite/SQLiteStatement)
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);
No comments