8

I have an Android app, and all my testing so far has been on my Froyo phone. I've just starting testing against 1.6 and 2.1 in the emulator, and it crashes on startup. It can't find a column in one of my views.

05-17 23:31:31.446: ERROR/AndroidRuntime(198): Caused by: 
android.database.sqlite.SQLiteException: no such column: 
categoryTable.currentBal: , while compiling: 
SELECT SUM(categoryTable.currentBal) FROM catDisplayTable 
WHERE masterCategoryName != "__Hidden__"

The schema of the view is as follows:

CREATE VIEW catDisplayTable AS SELECT categoryTable._id, categoryTable.name,
categoryTable.currentBal, categoryTable.sequence, categoryTable.note,
masterCategoryTable.name AS masterCategoryName FROM categoryTable 
LEFT OUTER JOIN masterCategoryTable 
ON categoryTable.masterCategoryId = masterCategoryTable._id;

With adb shell connecting to the various emulator instances, I have confirmed that (1) the correct schema is in place in all cases, and (2) in 1.6 and 2.1, SQLite is just unable to locate the columns in this view, even with something as simple as

SELECT categoryTable.name FROM catDisplayTable;

or 

SELECT name FROM catDisplayTable;

It works fine on 2.2.

My presumption therefore is that something has changed in SQLite between Android 2.1 and 2.2. This answer helpfully gives the SQLite versions which were shipped with each Android API level. It says that SQLite was updated from 3.5.9 to 3.6.22 between 2.1 and 2.2. Looking at the SQLite release history, I don't see anything particularly obvious which might explain the difference.

Can anyone identify exactly what's changed, and suggest how I can work around it so my code works on pre-Froyo devices?

Community
  • 1
  • 1
Graham Borland
  • 60,055
  • 21
  • 138
  • 179
  • I'm just trying to make sure you didn't skip obvious. Did you "Clean Data" on emulator/device before trying? Maybe you got older dev version and this view doesn't have column on that emulator/device? – katit May 18 '11 at 20:53
  • @katit Yes, completely clean. I created new empty AVDs specifically to test this. – Graham Borland May 18 '11 at 21:06

4 Answers4

7

I've run into an issue with SQLite when using DISTINCT that maybe the same issue you are running into with SUM.

On earlier OS versions when using DISTINCT, I had to alias the column names or the logic would fail with a similar if not exactly the same error (I don't remember exactly what the error was), but I guess they fixed it in later OS versions.

example

SELECT DISTINCT _id AS _id, test AS test FROM table

works for all OS versions while

SELECT DISTINCT _id, test FROM table

would fail for earlier OSes

hsz
  • 148,279
  • 62
  • 259
  • 315
mp2526
  • 776
  • 7
  • 9
  • I have an application that supports v2.1 and above. I had added a view to the application that was a combination of unions of joined queries. The view worked fine in v2.2 and above, but not in v2.1. In each of the sub-queries, I aliased each column, and that allowed the view to work in v2.1. THANKS for the advice....it worked great! – BluJ IT Oct 14 '12 at 17:45
2

I have the exact same problem in my app. The common denominator is that I also use Views and have foreign keys.

I have downloaded my database from the emulator. I have no problems running the query outside of the Android OS 2.1.

Gave mp2526's answer a go but querying with "columnx AS columnx" made no difference. I will try to isolate the issue.

Magnus
  • 3,691
  • 5
  • 27
  • 35
  • If I run the same query as in the view standalone it works fine. Maybe it was already obvious, but on _a high level_ it is the combination of Views and Android 2.1 that does not play well. – Magnus May 26 '11 at 21:00
  • I agree, I eventually solved my problem by rebuilding my queries to avoid the Views. – Graham Borland May 28 '11 at 08:06
  • Yeah, that is how I solved it as well in the end. I guess it is not a big deal but it feels easier to manage views in the database. I traced the error down to the native platform implementation of the query. That was enough for me to decide that it was not worth pursuing some kind of magic API call in Java. – Magnus Jun 07 '11 at 14:08
  • mp2526's answer worked great for my view, which was a bunch of unions of queries containing left outer joins of tables. I highly suggest looking at "mp2526"s answer to resolve this issue. – BluJ IT Oct 14 '12 at 17:48
0

The problem is that Android 2.1 returns the columns along with their table names, so a query like

SELECT id FROM table

will return one column with name table.id

The solution, as already pointed out, is to write

SELECT id as id FROM table
rvighne
  • 20,755
  • 11
  • 51
  • 73
user3866289
  • 131
  • 1
  • 2
  • Your statements aren't clear enough and seem not to be an answer. Please use SO answers only to post answers, otherwise comment on existing ones. Please also take a look at our `tour` under the `help` menu. – Aleksei Zyrianov Jul 22 '14 at 21:03
  • As you said, the solution is already provided, no need to make a new answer. – djv Jul 22 '14 at 21:04
0

Just ran into the same issue. It DOES work when I re-did view declaration using Column AS Column notations.

katit
  • 17,375
  • 35
  • 128
  • 256