StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQLite (Spatialite) query on iOS works on 64-bit simulator but nothing else
text
Body
copied!<p>I have a Spatialite database generated by a Django management command, which I'm embedding in an iOS app. The db opens perfectly, but I've found that querying the database on iOS only works if I'm running on a 64-bit device. Any other devices, simulated or otherwise, produce the output <code>SQLITE_NOTADB</code> on calling <code>sqlite3_prepare_v2</code>, with the error message <code>file is encrypted or is not a database</code>.</p> <p>Obviously, the Mac that produced this database is a 64-bit machine, but SQLite databases should be bit-agnostic, so I don't see how this should be a problem. Perhaps this doesn't apply to Spatialite databases? Are there any flags I can use with any of the SQLite functions (maybe <code>sqlite3_open</code> or <code>sqlite3_prepare_v2</code> or maybe a <code>pragma</code> command) to make it read the file in the same way as the 64-bit arch iOS does? Or perhaps there is a way to generate the Spatialite db from django in a more platform-compatible format? Any suggestions here are welcome.</p> <p>Here's a snippet of my code, if anyone can find any obvious problems:</p> <pre> @implementation DataModel @synthesize db; - (id) init { self = [super init]; if (self != nil) { spatialite_init(1); sqlite3 *newDbConnection; if (sqlite3_open([[self spatialiteDbPath] UTF8String], &newDbConnection) == SQLITE_OK) { NSLog(@"Database opened successfully"); db = newDbConnection; } else { NSLog(@"Error opening database"); db = NULL; } } return self; } - (NSArray *) getLockupsForRegion: (MKCoordinateRegion) region { NSMutableArray *lockups = [[NSMutableArray alloc] init]; NSString *query = [NSString stringWithFormat:@"\ SELECT name, X(location) as lat, Y(location) as lon, \ covered, type, capacity \ FROM lockups_lockup \ WHERE WITHIN(location, GeomFromText('POLYGON((%f %f, %f %f, %f %f, %f %f))'));", regionCorners[0].latitude, regionCorners[0].longitude, regionCorners[1].latitude, regionCorners[1].longitude, regionCorners[2].latitude, regionCorners[2].longitude, regionCorners[3].latitude, regionCorners[3].longitude]; sqlite3_stmt *statement; if(db && sqlite3_prepare_v2(db, [query UTF8String], -1, &statement, NULL) == SQLITE_OK) { while(sqlite3_step(statement) == SQLITE_ROW) { NSLog(@"A row"); } } else { NSLog(@"Query failed for reason: %s", sqlite3_errmsg(db)); NSLog(@"Sqlite version: %s", sqlite3_version); } return lockups; } @end </pre> <p>Launching the app and running on a non-64bit iOS platform <code>getLockupsForRegion:</code> produces the log output:</p> <pre> SpatiaLite version ..: 4.1.1 Supported Extensions: - 'VirtualShape' [direct Shapefile access] - 'VirtualDbf' [direct DBF access] - 'VirtualText' [direct CSV/TXT access] - 'VirtualNetwork' [Dijkstra shortest path] - 'RTree' [Spatial Index - R*Tree] - 'MbrCache' [Spatial Index - MBR cache] - 'VirtualSpatialIndex' [R*Tree metahandler] - 'VirtualFDO' [FDO-OGR interoperability] - 'SpatiaLite' [Spatial SQL - OGC] PROJ.4 version ......: Rel. 4.8.0, 6 March 2012 GEOS version ........: 3.4.2-CAPI-1.8.2 r3921 2013-12-31 00:29:39.567 App[8320:70b] Database opened successfully 2013-12-31 00:29:48.128 App[8320:70b] Query failed for reason: file is encrypted or is not a database 2013-12-31 00:29:48.710 App[8320:70b] Sqlite version: 3.8.1 </pre> <p>And the log output under arm64:</p> <pre> SpatiaLite version ..: 4.1.1 Supported Extensions: - 'VirtualShape' [direct Shapefile access] - 'VirtualDbf' [direct DBF access] - 'VirtualText' [direct CSV/TXT access] - 'VirtualNetwork' [Dijkstra shortest path] - 'RTree' [Spatial Index - R*Tree] - 'MbrCache' [Spatial Index - MBR cache] - 'VirtualSpatialIndex' [R*Tree metahandler] - 'VirtualFDO' [FDO-OGR interoperability] - 'SpatiaLite' [Spatial SQL - OGC] PROJ.4 version ......: Rel. 4.8.0, 6 March 2012 GEOS version ........: 3.4.2-CAPI-1.8.2 r3921 2013-12-31 01:10:34.491 App[8548:70b] Database opened successfully 2013-12-31 01:10:44.913 App[8548:70b] A row 2013-12-31 01:10:44.913 App[8548:70b] A row 2013-12-31 01:10:44.914 App[8548:70b] A row 2013-12-31 01:10:44.914 App[8548:70b] A row 2013-12-31 01:10:44.915 App[8548:70b] A row 2013-12-31 01:10:44.915 App[8548:70b] A row 2013-12-31 01:10:44.916 App[8548:70b] A row 2013-12-31 01:10:44.916 App[8548:70b] A row </pre> <p>Versions:</p> <ul> <li>Python: 2.7.5</li> <li>Django: 1.6</li> <li>PySqlite: 2.6.3</li> <li>Sqlite (on machine generating Spatialite db): 3.8.1</li> <li>Spatialite (on machine generating Spatialite db): 4.1.1</li> <li>Sqlite (on iOS): 3.8.1</li> <li>Spatialite (on iOS): 4.1.1</li> </ul> <p>Any suggestions on this would be much appreciated!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload