Re: Not returning affect row counts on Android

[Gray Watson]

Folks, please make sure you address android specific questions to the android list in the future.

On Aug 25, 2011, at 12:20 AM, Craig Andrews wrote:

> Many of ORMLite's Dao's methods return int's that indicate how many rows
> were affected by the requested operation (for example, "int
> deleteIds(Collection<ID> ids)"). For Android, the row count is *never*
> returned - it's always 0 or 1.

Yeah. This is a limitation of the Android API. Maybe the documentation should say "returns the number of rows (if supported)".

> Starting at 11, you
> could use android.database.sqlite.SQLiteStatement's executeUpdateDelete()
> method, which return the affected row count.

Did not know that. I'll add it to the TODO list. I should be able to detect the API if >= 11 although I'm not sure how I can get the conditional compile to work without messy reflection.

To be fair, the following are correct: :-)

> Methods that always return 1:
> create
> createIfNotExists
> delete(T data)
> refresh
> update(T data)
> updateId

These are obviously not.

> Methods that always return 1:
> delete(PreparedDelete<T> preparedDelete)
> delete(Collection<T> datas)
> deleteIds(Collection<ID> ids)
> update(PreparedUpdate<T> preparedUpdate)
> updateRaw(String statement, String... arguments)

gray

[Craig Andrews]

> Folks, please make sure you address android specific questions to the
> android list in the future.

Sorry, I had a mental lapse and forgot that there was an Android list.

>
> On Aug 25, 2011, at 12:20 AM, Craig Andrews wrote:
>
>> Many of ORMLite's Dao's methods return int's that indicate how many rows
>> were affected by the requested operation (for example, "int
>> deleteIds(Collection<ID> ids)"). For Android, the row count is *never*
>> returned - it's always 0 or 1.
>
> Yeah. This is a limitation of the Android API. Maybe the documentation
> should say "returns the number of rows (if supported)".
>
>> Starting at 11, you
>> could use android.database.sqlite.SQLiteStatement's
>> executeUpdateDelete()
>> method, which return the affected row count.
>
> Did not know that. I'll add it to the TODO list. I should be able to
> detect the API if >= 11 although I'm not sure how I can get the
> conditional compile to work without messy reflection.
>
> To be fair, the following are correct: :-)

I'm not so sure I agree with that.

"createIfNotExists" should return 0 if the passed data already exists. If
it actually does an insert, it should return 1.

For "update(T data)", if data in the database currently and data passed as
a parameter are the same, the database should return that 0 rows were
affected when the update query is run.

"delete(T data)" should return 0 if data doesn't exist in the database (as
nothing was deleted).

"updateId" would behave the same way as "update(T data)" - if the ID is
"updated" to its current value, then 0 should be returned.

Similar behavior would be expected from the delete* methods.

These rules are what MySQL does, and I believe other databases (I'm
betting they're in the SQL standards somewhere).

[jc]

On Aug 25, 4:54 pm, "Craig Andrews" <candr...@integralblue.com> wrote:
>
> For "update(T data)", if data in the database currently and data passed as
> a parameter are the same, the database should return that 0 rows were
> affected when the update query is run.
>
> "delete(T data)" should return 0 if data doesn't exist in the database (as
> nothing was deleted).
>
> "updateId" would behave the same way as "update(T data)" - if the ID is
> "updated" to its current value, then 0 should be returned.
>
> Similar behavior would be expected from the delete* methods.
>
> These rules are what MySQL does, and I believe other databases (I'm
> betting they're in the SQL standards somewhere).

You are correct that this is what MySQL does per it's own
documentation when none of the field values are different, but this is
not a standard.

[Gray Watson]

On Aug 25, 2011, at 5:54 PM, Craig Andrews wrote:

> "createIfNotExists" should return 0 if the passed data already exists. If
> it actually does an insert, it should return 1.

createIfNotExists returns the data argument passed in or the row from the database.

> For "update(T data)", if data in the database currently and data passed as
> a parameter are the same, the database should return that 0 rows were
> affected when the update query is run.

I don't think this is true and a quick test verified it. This looks to me one of the few times H2 diverges from MySQL. Interesting.

H2, Derby, Hsqldb, Sqlite, Postgres, and SqlServer all return 1 even if nothing has changed in the line. Only MySQL does works this way. Really what the 1 is saying is that it found the line in the first place. The following junit test passes with all of these database types:

assertEquals(1, dao.create(foo));
// no change
assertEquals(1, dao.update(foo));
assertEquals(1, dao.update(foo));

> "delete(T data)" should return 0 if data doesn't exist in the database (as nothing was deleted).

True.

> "updateId" would behave the same way as "update(T data)" - if the ID is "updated" to its current value, then 0 should be returned.

Not true. Same as update above.

> Similar behavior would be expected from the delete* methods.

Agreed.

> These rules are what MySQL does, and I believe other databases (I'm
> betting they're in the SQL standards somewhere).

HA! There's an oxymoron for you. "SQL standard." Stop it. You're killing me. :-)

gray