Query

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

Query

Postby BerlinerMC » Sun Jan 17, 2010 10:52 pm

Hello community,

is it possible to get a "GROUP BY" clause for

Cursor c = Context.getContentResolver().query()

I want to group MediaStore.Audio.Media.ARTIST

Or is there another way to do that?!

Berliner
BerlinerMC
Freshman
Freshman
 
Posts: 5
Joined: Sun Jan 17, 2010 10:48 pm

Top

Postby jwei512 » Mon Jan 18, 2010 11:03 am

This might be a long shot, but I'm pretty sure the query() method is just a wrapper that generates SQL commands based on your inputs... so it might be possible to inject some additional SQL in the parameters... for instance if you wanted to group by contact name you could try something like:

contentResolver.query(People.CONTENT_URI, new String[] {People._ID, People.NAME, People.NUMBER}, People.NUMBER + " IS NOT NULL " + "GROUP BY " + People.NAME, null, People.NAME + " ASC");

So what I think will happen is this query gets translated into the SQL query:

"select _id, name, number from contacts where number is not null group by name order by name asc"

or something like that

let me know if it works... im curious too
- jwei

http://thinkandroid.wordpress.com
Examples, Code, and Tutorials
jwei512
Senior Developer
Senior Developer
 
Posts: 135
Joined: Mon Jul 27, 2009 9:35 pm
Location: Palo Alto, CA

Postby BerlinerMC » Tue Jan 19, 2010 9:56 am

jwei512 wrote:This might be a long shot, but I'm pretty sure the query() method is just a wrapper that generates SQL commands based on your inputs... so it might be possible to inject some additional SQL in the parameters... for instance if you wanted to group by contact name you could try something like:

contentResolver.query(People.CONTENT_URI, new String[] {People._ID, People.NAME, People.NUMBER}, People.NUMBER + " IS NOT NULL " + "GROUP BY " + People.NAME, null, People.NAME + " ASC");

So what I think will happen is this query gets translated into the SQL query:

"select _id, name, number from contacts where number is not null group by name order by name asc"

or something like that

let me know if it works... im curious too


Hello jwei512,

this don't work because i get this sql query

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. E/AndroidRuntime( 1907): Caused by: android.database.sqlite.SQLiteException: near "GROUP": syntax error: , while compiling: SELECT _id, title, artist, album FROM audio  WHERE (artist IS NOT NULL  GROUP BY artist) ORDER BY artist ASC
Parsed in 0.030 seconds, using GeSHi 1.0.8.4


Any idea?!

Berliner
BerlinerMC
Freshman
Freshman
 
Posts: 5
Joined: Sun Jan 17, 2010 10:48 pm

Postby Draffodx » Tue Jan 19, 2010 10:10 am

Thats a Sql syntax error, it just means that the sql your trying to run is not typed out correctly, can you post the java code that is causing the exception?
Draffodx
Master Developer
Master Developer
 
Posts: 205
Joined: Wed Nov 12, 2008 2:31 pm

Postby jwei512 » Tue Jan 19, 2010 10:15 am

the JAVA code is above (i.e. the cursor)
- jwei

http://thinkandroid.wordpress.com
Examples, Code, and Tutorials
jwei512
Senior Developer
Senior Developer
 
Posts: 135
Joined: Mon Jul 27, 2009 9:35 pm
Location: Palo Alto, CA

Postby BerlinerMC » Tue Jan 19, 2010 10:41 am

Draffodx wrote:Thats a Sql syntax error, it just means that the sql your trying to run is not typed out correctly, can you post the java code that is causing the exception?


This is the same Java code as this:
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. contentResolver.query(People.CONTENT_URI, new String[] {People._ID, People.NAME, People.NUMBER}, People.NUMBER + " IS NOT NULL " + "GROUP BY " + People.NAME, null, People.NAME + " ASC");
Parsed in 0.031 seconds, using GeSHi 1.0.8.4


But here is my Java code
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2. _myContext.getContentResolver().query(media, new String[]       {MediaStore.Audio.Media._ID, MediaStore.Audio.Media.TITLE,                                                                                                              MediaStore.Audio.Media.ARTIST,
  3.  
  4. MediaStore.Audio.Media.ALBUM,                                                                                                                   //MediaStore.Audio.Media.ALBUM_ART                                                                                                     
  5.  
  6. }, MediaStore.Audio.Media.ARTIST + " IS NOT NULL " + " GROUP BY " + MediaStore.Audio.Media.ARTIST, null, MediaStore.Audio.Media.ARTIST + " ASC ");
Parsed in 0.036 seconds, using GeSHi 1.0.8.4
BerlinerMC
Freshman
Freshman
 
Posts: 5
Joined: Sun Jan 17, 2010 10:48 pm

Top

Postby jwei512 » Tue Jan 19, 2010 11:59 am

Yea based off of how the system converts the query() method into a SQL command... it doesn't seem like you can inject a GROUP BY statement any where (it's because of how the system converts it like WHERE(...) and so anything you pass into that 3rd argument will be seen as an "argument" to the WHERE clause.

Perhaps this might help?

http://developer.android.com/reference/ ... ang.String, java.lang.String[])

that's the only thing I can think of that might help
- jwei

http://thinkandroid.wordpress.com
Examples, Code, and Tutorials
jwei512
Senior Developer
Senior Developer
 
Posts: 135
Joined: Mon Jul 27, 2009 9:35 pm
Location: Palo Alto, CA

Postby BerlinerMC » Tue Jan 19, 2010 12:43 pm

jwei512 wrote:Yea based off of how the system converts the query() method into a SQL command... it doesn't seem like you can inject a GROUP BY statement any where (it's because of how the system converts it like WHERE(...) and so anything you pass into that 3rd argument will be seen as an "argument" to the WHERE clause.

Perhaps this might help?

http://developer.android.com/reference/ ... ang.String, java.lang.String[])

that's the only thing I can think of that might help


Yeah i thought the same.
But with this method there is another problem. I know the table now, but which database should i select?!

Berliner
BerlinerMC
Freshman
Freshman
 
Posts: 5
Joined: Sun Jan 17, 2010 10:48 pm

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 3 guests