[android-developers] Re: multiple parameters with rawQuery in sqlite = BUG
Sorry i missed out on your question No, i never did get it to work using multiple "?" placeholders. What i ended up doing is taking the parameters i was trying to use in the "selectionArgs" String array and used string concatenation to create one big query string. Then I passed that new string to rawQuery with null as the selectionArgs. For ex... String sql = "SELECT SUM(MIN(fp - " + firstParameter + ", ap)) FROM."; rawQuery(sql, null); Don't know if this was a good way of doing it but it was the only way i could get it to work and i have experienced no problems with this method. Frank On Mar 11, 4:13 pm, Nathan wrote: > On Mar 10, 5:29 pm, Mark Murphy wrote: > > > > > I'm not aware of any. I'm not even sure it's Android that is doing the ? > > replacement -- the Ruby SQLite library has the same feature, so it might > > be handled by SQLite itself. > > It could certainly be in SQlite or its JDBC driver. .NET SQLite > provider worked with the exact same query string - but it had typed > parameters, not an array of strings. > > Given the nature of SQLite, I wouldn't be surprised if it is casting > the column to a string and doing a string comparison. > > If this is the case, to be fixed, I'd have to request a new signature > for rawQuery. > > rawQuery (String sql, Object... params) > > If I come up with a smaller case, I'll post it. It is 100% > reproducible with FLOAT columns and inequality in my code. > > Nathan -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en To unsubscribe, reply using "remove me" as the subject.
[android-developers] Re: multiple parameters with rawQuery in sqlite = BUG
On Mar 10, 5:29 pm, Mark Murphy wrote: > > I'm not aware of any. I'm not even sure it's Android that is doing the ? > replacement -- the Ruby SQLite library has the same feature, so it might > be handled by SQLite itself. > It could certainly be in SQlite or its JDBC driver. .NET SQLite provider worked with the exact same query string - but it had typed parameters, not an array of strings. Given the nature of SQLite, I wouldn't be surprised if it is casting the column to a string and doing a string comparison. If this is the case, to be fixed, I'd have to request a new signature for rawQuery. rawQuery (String sql, Object... params) If I come up with a smaller case, I'll post it. It is 100% reproducible with FLOAT columns and inequality in my code. Nathan -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
Re: [android-developers] Re: multiple parameters with rawQuery in sqlite = BUG
Nathan wrote: > On Mar 10, 3:56 pm, Mark Murphy wrote: >> Create a project that demonstrates the problem, open an issue >> onhttp://b.android.com, and shoot me the issue number when you've done that. >> > > OK. First, though, is there a way to capture what SQL is actually > being sent to the database? I'm not aware of any. I'm not even sure it's Android that is doing the ? replacement -- the Ruby SQLite library has the same feature, so it might be handled by SQLite itself. > And what does 'bound as strings' mean in this: > > 'You may include ?s in where clause in the query, which will be > replaced by the values from selectionArgs. The values will be bound as > Strings.' > > At first glance, I think, well, of course they are bound as strings > because they are strings. > > But if I run this code. > > String[] selectionArgs = {"30.0","50.0"} > > Cursor cursor = bdb.getDb().rawQuery("SELECT X,Y FROM BOXTABLE > WHERE > X < ? AND Y > ?", selectionArgs); > > Will the resultant SQL be the equivalent of "SELECT X,Y FROM BOXTABLE > WHERE X < '30.0' AND Y > '50.0' " ? (notice the single quotes). Not that I am aware of. Certainly, it does integer equality correctly (otherwise all our _ID columns wouldn't work), but I haven't tried > or <. -- Mark Murphy (a Commons Guy) http://commonsware.com | http://twitter.com/commonsguy _The Busy Coder's Guide to *Advanced* Android Development_ Version 1.3 Available! -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
[android-developers] Re: multiple parameters with rawQuery in sqlite = BUG
On Mar 10, 3:56 pm, Mark Murphy wrote: > > Create a project that demonstrates the problem, open an issue > onhttp://b.android.com, and shoot me the issue number when you've done that. > OK. First, though, is there a way to capture what SQL is actually being sent to the database? And what does 'bound as strings' mean in this: 'You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.' At first glance, I think, well, of course they are bound as strings because they are strings. But if I run this code. String[] selectionArgs = {"30.0","50.0"} Cursor cursor = bdb.getDb().rawQuery("SELECT X,Y FROM BOXTABLE WHERE X < ? AND Y > ?", selectionArgs); Will the resultant SQL be the equivalent of "SELECT X,Y FROM BOXTABLE WHERE X < '30.0' AND Y > '50.0' " ? (notice the single quotes). If so, while it sucks, I can't really file this as a bug because it will be considered according to spec. Nathan -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
Re: [android-developers] Re: multiple parameters with rawQuery in sqlite = BUG
Nathan wrote: > I don't know if you've solved this by now, but I see the same behavior > with a completely different query that uses five parameters. The only > thing in common, probably, is the use of floating point. > > I've had okay results using one parameter, or passing a fully > populated string both in code and in sqlite3. > > I see this as a bug in SQLiteDatabase.rawQuery. After all, the > documentation does not state that you can use only one '?'. Because you can use more than one. > Has this bug been reported? Create a project that demonstrates the problem, open an issue on http://b.android.com, and shoot me the issue number when you've done that. -- Mark Murphy (a Commons Guy) http://commonsware.com | http://twitter.com/commonsguy _Android Programming Tutorials_ Version 2.0 Available! -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
[android-developers] Re: multiple parameters with rawQuery in sqlite = BUG
I don't know if you've solved this by now, but I see the same behavior with a completely different query that uses five parameters. The only thing in common, probably, is the use of floating point. I've had okay results using one parameter, or passing a fully populated string both in code and in sqlite3. I see this as a bug in SQLiteDatabase.rawQuery. After all, the documentation does not state that you can use only one '?'. Has this bug been reported? It occurs for me in 1.6(G1) and 2.0.1 (emulator). Nathan -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en