Update integer values in SQLite database (java)

Question

I am pretty new to SQLite databases, so please forgive me...

I have a database with integer values. When updating a row in the database using the code below, there is somehting I don't understand. whereArgs is of type String[], though the values you are looking for are integers, so I would expect that one should pass in a int[].

    SQ.update(table, values, whereClause, whereArgs)

Where do I go wrong?

Example code (hypothetical):

public void changeOneIntoTwo(DatabaseOperations dop) {
    SQLiteDatabase SQ = dop.getWritableDatabase();
    String selection = "ValuesColumn = ?";
    String[] args = {"1"};
    ContentValues cv = new ContentValues();
    cv.put("ValuesColumn", 2);
    SQ.update("MyTable", cv, selection, args);
}

Looks like it doesn't support ints as parameterised args. It should be safe just to do `selection = "ValuesColumn = " + Integer.toString(myNumber);` — Will Richardson, Oct 20 '15 at 19:15
Just pass the string array, sqlite will "know" if it's an int. http://stackoverflow.com/questions/17481981/using-int-value-in-selection-args-argument-of-sqlite-for-android — Ken Wolf, Oct 20 '15 at 19:20
in sql, string 1 and int 1 both are correctly represented as `'1'`. So no worries to have, the sql engine will know what type to expect for what column. — njzk2, Oct 20 '15 at 19:28
Java being so very precise, I am a bit disappointed that sqlite is all of a sudden interpreting strings as ints... But thanks, anyway! — MWB, Oct 20 '15 at 19:28
@njzk2 This is wrong; the comparison will work only if the column has numeric [affinity](http://www.sqlite.org/datatype3.html#affinity). — CL., Oct 20 '15 at 20:56
@CL. `column = ?` with `"1"` as an arg will work if that column as the value `1`, no matter its type. — njzk2, Oct 20 '15 at 21:19
@njzk2 No [it does not](http://sqliteonline.com/#fiddle-56273c3120fc23c787051fa9a2a98125438b690761ecf68968). — CL., Oct 21 '15 at 07:19
@CL. I am skeptical. what you are showing is different. `column = ? with "1"` is expanded at some point into an actual query. I have used that in various projects, and it works. Plus, with the android sql api, there is no way of passing anything but strings as parameters to the query. (unless you build your query yourself, but what would be the point?) — njzk2, Oct 21 '15 at 13:51
@njzk2 As I said, it works if the column has numeric affinity. In that fiddle, it has not. — CL., Oct 21 '15 at 14:26
@CL. I don't know how to see what type the column is in your fiddle — njzk2, Oct 21 '15 at 14:44
@njzk2 right-click; "SQL Schema". Anyway, here's an [SQLFiddle](http://www.sqlfiddle.com/#!5/da2f8/1) (it was broken this morning). — CL., Oct 21 '15 at 14:51
@CL. thanks, that's quite interesting. I created a table with various columns of various types (yours has no type at all), and all select yield the same result with or without `'`. Has `TEXT` numeric affinity? — njzk2, Oct 21 '15 at 15:00
@njzk2 TEXT has TEXT [affinity](http://www.sqlite.org/datatype3.html#affinity). — CL., Oct 21 '15 at 17:25
@CL. but then why does `create table t(x TEXT); insert into t (1); insert into t ('1'); select * from t where x = '1'; select * from t where x = 1;` both return the 2 lines? (same for other column type, at least int, numeric and varchar). — njzk2, Oct 21 '15 at 17:31
@CL. Ok. But then, in your example, the right-hand operand always has an affinity (`'1'` -> TEXT or `1` -> NUMERIC), which should be passed to the left-hand operand since the column has no affinity. Once the expression has either TEXT or NUMERIC affinity, the comparison should always be true (`'1' = 1` and `1 = '1'`). But your example demonstrate that it is not the case. Why? — njzk2, Oct 21 '15 at 19:44
@CL. not the expression itself, but the right-hand operand should give its affinity to the left-hand (since the right hand operator has either TEXT or NUMERIC affinity) — njzk2, Oct 21 '15 at 20:06
@njzk2 The right hand operator has no affinity (see section 3.2). — CL., Oct 21 '15 at 20:59
@njzk2 Only values from table columns have an affinity; plan expressions have just a type. — CL., Oct 22 '15 at 07:14
@CL. makes sense now. So the thing I get out of all this is mainly to give columns a type so they get affinity too, so the value can be converted when relevant. (and that column type and value type in the column are possible different). Thanks a lot for taking the time to explain. — njzk2, Oct 22 '15 at 13:25

score -1 · Answer 1 · answered Oct 20 '15 at 19:23

selection is the condition that you put in where clause of Sql query and args is what replaced by "?" of selection .

for example : lets assume you need your sql query select * from Mytable where ValuesColumn ='1' and Name = 'MVB'. so in this case

String selection = "ValuesColumn = ? AND Name = ?";

String[] args = {"1","MVB"};

Update integer values in SQLite database (java)

1 Answers1