Re: [sqlite] Query executes in sqlite manager but not sqlite database in android
Features are not available before they are introduced. For example, CTE's will not work with 3.6.22. The "ignore cruft from brain-dead SQL code generators" feature was not added to the parser until after 3.6.22. select * from A JOIN B ON a = b is mere syntactic sugar for select * from A,B where a = b and as such using brackets (in the from list) is useless. A "feature" was added, after 3.6.22, to ignore the excess cruft generated by many brain-dead SQL code generators. It is and always has been syntactically unacceptable to say: select * from A, (B, C), D where a.a = b.b AND b.b = b.c and b.c = b.d Putting brackets around or re-ordering arbitrary clauses in the where clause also serves no useful purpose or effect. The absolute worst and most brain-dead offenders are Microsoft Products (any and all of them) and "Microsoft SQL". The long and the short is that if the brackets do not do anything (ie, are 100% syntactic sugar/atmosphere) then get rid of them. They will only make your (and anyone who must look at your SQL) life miserable. >-Original Message- >I have used >#sqlite3 --version >at command line and got 3.6.22 as the version >Does that mean the type of query i am trying wont work? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query executes in sqlite manager but not sqlite database in android
Hello I have used #sqlite3 --version at command line and got 3.6.22 as the version Does that mean the type of query i am trying wont work? On Sunday, January 26, 2014 8:57 AM, big stone [via SQLite] <ml-node+s1065341n73477...@n5.nabble.com> wrote: It seems Android use a pretty outdated SQlite motor : SQlite 3.7.1 = march 20th, 2012 http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android SQLite 3.7.11: 19-4.4-KitKat 18-4.3-Jelly Bean 17-4.2-Jelly Bean 16-4.1-Jelly Bean SQLite 3.7.4: 15-4.0.3-Ice Cream Sandwich 14-4.0-Ice Cream Sandwich 13-3.2-Honeycomb 12-3.1-Honeycomb 11-3.0-Honeycomb SQLite 3.6.22: 10-2.3.3-Gingerbread 9-2.3.1-Gingerbread 8-2.2-Froyo SQLite 3.5.9: 7-2.1-Eclair 4-1.6-Donut 3-1.5-Cupcake ___ sqlite-users mailing list [hidden email] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users If you reply to this email, your message will be added to the discussion below:http://sqlite.1065341.n5.nabble.com/Query-executes-in-sqlite-manager-but-not-sqlite-database-in-android-tp73466p73477.html To unsubscribe from Query executes in sqlite manager but not sqlite database in android, click here. NAML -- View this message in context: http://sqlite.1065341.n5.nabble.com/Query-executes-in-sqlite-manager-but-not-sqlite-database-in-android-tp73466p73478.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query executes in sqlite manager but not sqlite database in android
It seems Android use a pretty outdated SQlite motor : SQlite 3.7.1 = march 20th, 2012 http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android SQLite 3.7.11: 19-4.4-KitKat 18-4.3-Jelly Bean 17-4.2-Jelly Bean 16-4.1-Jelly Bean SQLite 3.7.4: 15-4.0.3-Ice Cream Sandwich 14-4.0-Ice Cream Sandwich 13-3.2-Honeycomb 12-3.1-Honeycomb 11-3.0-Honeycomb SQLite 3.6.22: 10-2.3.3-Gingerbread 9-2.3.1-Gingerbread 8-2.2-Froyo SQLite 3.5.9: 7-2.1-Eclair 4-1.6-Donut 3-1.5-Cupcake ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query executes in sqlite manager but not sqlite database in android
Hi Kamulegs, Your SQLiteManager includes a version of SQLite >=3.7.16 , and your android application does not. Indeed the syntax " (b JOIN c ON b.id = c.id)" is only accepted without this errror after 3.7.16. ==> If you can rewrite your syntax without these parenthesis (like below), all should be fine. *SELECT M._id as Meter_id, M.MeterNumber, R1.ReadingDate as ReadingDate, R1.Reading AS CurrentReading, R2.ReadingDate AS PrevReadingDate, R2.Reading AS PrevMeterReading, R2.Rate as Rate, R2._id as _id,R1.TenantMeter_id FROM Meters AS M INNER JOIN TenantMeters ON M._id = TenantMeters.Meter_id INNER JOIN MeterReading AS R1 INNER JOIN MeterReading AS R2 ON R1.TenantMeter_id = R2.TenantMeter_id ON TenantMeters._id = R1.TenantMeter_id WHERE (((R2.ReadingDate)=(SELECT Max(R3.ReadingDate) FROM [MeterReading] AS R3 WHERE (R3.TenantMeter_id = R1.TenantMeter_id) AND (R3.ReadingDate < R1.ReadingDate) ))) OR (((R2.TenantMeter_id) Is Null)) * ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query executes in sqlite manager but not sqlite database in android
Hello Community Hope to get some pointers here because i have a hit a snag! I have 4 tables. Meters:_id, SerialNumber Tenants: _id, FirstName. TenantsMeters: _id,Tenant_id,Meter_id, (basically junction table for linking many to many relationship btn tenants and meters) MeterReading: _id, ReadingDate, Reading, TenantMeter_id(FK for TenantMeters table) This query works in sqlite manager *SELECT M._id as Meter_id, M.MeterNumber, R1.ReadingDate as ReadingDate, R1.Reading AS CurrentReading, R2.ReadingDate AS PrevReadingDate, R2.Reading AS PrevMeterReading, R2.Rate as Rate, R2._id as _id,R1.TenantMeter_id FROM (Meters AS M INNER JOIN TenantMeters ON M._id = TenantMeters.Meter_id) INNER JOIN (MeterReading AS R1 INNER JOIN MeterReading AS R2 ON R1.TenantMeter_id = R2.TenantMeter_id) ON TenantMeters._id = R1.TenantMeter_id WHERE (((R2.ReadingDate)=(SELECT Max(R3.ReadingDate) FROM [MeterReading] AS R3 WHERE (R3.TenantMeter_id = R1.TenantMeter_id) AND (R3.ReadingDate < R1.ReadingDate) ))) OR (((R2.TenantMeter_id) Is Null)) * But fails in sqlite database in android with the error "no such column:R1.ReadingDate" It fails in android app and also fails when i connect to the android sqlite db from command prompt . What can be the likely cause? Any pointers please. Ronald -- View this message in context: http://sqlite.1065341.n5.nabble.com/Query-executes-in-sqlite-manager-but-not-sqlite-database-in-android-tp73466.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
