1. Are you ready for the Galaxy S20? Here is everything we know so far!

Apps android sqlite union, how to retrive results from the tables?

Discussion in 'Android Development' started by hyungjungsoh, Feb 13, 2011.

  1. hyungjungsoh

    hyungjungsoh Newbie
    Thread Starter

    I have this SQLite query:

    Code (Text):
    1. Cursor orgiCursor = orgiDb.rawQuery("select task,taskDate from meeting where exists
    2. (select `meeting.taskId from Task, meeting where meeting.taskId = Task.taskId and task.name='"+profName+"')
    3. UNION  SELECT subject,date from taskDeadline where exists (select taskDeadline.taskId from taskDeadline,Task where taskDeadline.taskId=Task.taskId and Task.name='"+profName+"') union select
    4. specificOccassion,setDate from AnnualTask where exists (select taskId from Task,AnnualTask where Task.taskId = AnnualTask.taskId and Task.name = '"+profName+"' and AnnualTask.setDate<>'--')", null);
    5.  
    It's quite long really, but it works flawlessly on SQLite Manager. My problem is I get an error in retrieving the data. here is a snippet of my code:

    Code (Text):
    1. try {
    2.  
    3.  
    4.         if (orgiCursor != null )
    5.         {
    6.             if (orgiCursor.moveToFirst()) {
    7.                 do {
    8.  
    9.                     String annualTask = orgiCursor.getString(orgiCursor.getColumnIndex("specificOccassion"));
    10.                     String annualDate = orgiCursor.getString(orgiCursor.getColumnIndex("setDate"));
    11.  
    12.                     String meetingTask = orgiCursor.getString(orgiCursor.getColumnIndex("task"));
    13.                     String meetingDate = orgiCursor.getString(orgiCursor.getColumnIndex("taskDate"));
    14.  
    15.                     String deadlineTask = orgiCursor.getString(orgiCursor.getColumnIndex("subject"));
    16.                     String deadlineDate = orgiCursor.getString(orgiCursor.getColumnIndex("date"));
    17.  
    18.                     SearchResults sr2=new SearchResults();
    19.                     sr2.setName(meetingTask);
    20.                     sr2.settDate(meetingDate);
    21.                     results.add(sr2);
    22.  
    23.                     SearchResults sr3 = new SearchResults();
    24.                     sr3.setName(deadlineTask);
    25.                     sr3.settDate(deadlineDate);
    26.                     results.add(sr3);
    27.  
    28.                     SearchResults sr1 = new SearchResults();
    29.                     sr1.setName(annualTask);
    30.                     sr1.settDate(annualDate);
    31.                     Log.i("Home","added"+annualTask + " & " +annualDate);
    32.                     results.add(sr1);
    33.  
    34.  
    35.  
    36.                 } while (orgiCursor.moveToNext());
    37.             }
    38.         }
    39.  
    40.     } catch (SQLiteException se) {
    41.         displayError1(se.toString());
    42.         Log.e(getClass().getSimpleName(), se.toString());
    43.     } finally {
    44.         if (orgiDb != null) {
    45.             orgiDb.close();
    46.             Log.e(getClass().getSimpleName(),"orgiDB closed()");
    47.         }
    48.     }
    49.  
    50.  
    51.     return results;
    52.  
    It returns this error. :( Please help me.

    Code (Text):
    1. 02-12 19:28:00.809: INFO/Database(14515): sqlite returned: error code = 1, msg = ambiguous column name: taskId
    2. 02-12 19:28:00.849: ERROR/AndroidRuntime(14515): Caused by: android.database.sqlite.SQLiteException: ambiguous column name: taskId: , while compiling: select task,taskDate from meeting where exists (select meeting.taskId from Task, meeting where meeting.taskId = Task.taskId and task.name='Maria Ozawa')UNION  SELECT subject,date from taskDeadline where exists (select taskDeadline.taskId from taskDeadline,Task where taskDeadline.taskId=Task.taskId and Task.name='Maria Ozawa') union select specificOccassion,setDate from AnnualTask where exists (select taskId from Task,AnnualTask where Task.taskId = AnnualTask.taskId and Task.name = 'Maria Ozawa' and AnnualTask.setDate<>'--')
    3.  
    4.     02-12 19:28:01.059: ERROR/Database(14515): close() was never explicitly called on database '/data/data/orgy.organizer.home/databases/orgi'
    5. 02-12 19:28:01.059: ERROR/Database(14515): android.database.sqlite.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here
    Any help would be greatly appreciated... :( So my questions are:
    1. Which part of the code do I exactly need to close the connection/cursor?
    2. Why does eclipse give me this error? I tried running this same statement in SQLite manager and it gave me flawless results. How exactly do I get the results from my query? Is this the right approach?

    Code (Text):
    1. String annualTask = orgiCursor.getString(orgiCursor.getColumnIndex("specificOccassion"));
    I am so desperate right now. Please help. T_T
     

    Advertisement

  2. miXer

    miXer Android Enthusiast

    "ambiguous column name: taskId" means there is more than one table you select from which has this column, and you must specify which table to use taskId from.

    Its in the last part of the SQL: "(select taskId from Task,AnnualTask where Task.taskId = AnnualTask.taskId and Task.name = '"+profName+"' and AnnualTask.setDate<>'--')"

    You must put either Task or AnnualTask in front of taskId
     
Loading...
Similar Threads - android sqlite union
  1. Rob
    Replies:
    11
    Views:
    195
  2. Kamilek123
    Replies:
    2
    Views:
    220
  3. Android News
    Replies:
    0
    Views:
    82
  4. Android News
    Replies:
    0
    Views:
    82
  5. Android News
    Replies:
    0
    Views:
    129
  6. Android News
    Replies:
    0
    Views:
    68
  7. Android News
    Replies:
    0
    Views:
    86
  8. KevinDP
    Replies:
    0
    Views:
    140
  9. Renu Sidhu
    Replies:
    1
    Views:
    189
  10. Bhavin392
    Replies:
    1
    Views:
    45

Share This Page

Loading...