CODEWITHSUNDEEP
androidandroid-sqlite
thelawnmowerman
thelawnmowerman

Reputation: 12136

Bind a number with a left padding of zeros as a String using SQLiteStatement

Surprisingly, I've just realised that SQLiteStatement can't properly bind a number with a left padding of zeros as a String.

Example:

SQLiteStatement insertSqlStatement = 
    db.compileStatement("INSERT INTO table(column) VALUES(?)");
insertSqlStatement.bindString(0, "00012");
insertSqlStatement.executeInsert();
// "12" is inserted, instead of "00012"

This doesn't work:

insertSqlStatement.bindString(0, "'00012'");
// "'12'" is inserted

This leads to an error:

db.compileStatement("INSERT INTO table(column) VALUES('?')");

Any ideas?

Upvotes: 0

Views: 48

Answers (1)

MikeT
MikeT

Reputation: 56958

I believe that your issue, asssuming the "error" is the loss of the 0's, is a misunderstanding of how SQLite minimises the amount of data stored.

How that data is stored, it's storage class, is affected by the column's type (aka affinity). In short if the column does not have a type affinity of TEXT or BLOB then it will have a numerical type affinity and store the number in the smallest form so dropping the numerically useless 0's.

If you need to store the 0's then ideally you should utilise a column with a type affinity of TEXT which means that the column should be declared as anything that contains TEXT, CHAR or CLOB but does not contain INT or INTEGER.

as per :-

3.1. Determination Of Column Affinity

The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

  1. If the declared type contains the string "INT" then it is assigned INTEGER affinity.

  2. If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

  3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.

  4. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.

  5. Otherwise, the affinity is NUMERIC.

Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.

You should have a look at Datatypes In SQLite Version 3

Upvotes: 1

Related Questions