IOS: open sqlite from an URL

Question

I want open a sqlite db but it is not in my mainBundle but in a server then I don't have this

NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"info.sqlite"];

but I have

NSString *filePath = @"http://serverAddress/info.sqlite";
NSURL *url = [NSURL URLWithString:filePath];
NSData *myData = [NSData dataWithContentsOfURL:url];

and when I read this db what I can do? If my db is inside mainBundle I do this:

if (sqlite3_open([filePath UTF8String], &database) == SQLITE_OK) {

        const char *sql = "select id, one, two from TABLE";
        sqlite3_stmt *selectstmt;

        if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

            while(sqlite3_step(selectstmt) == SQLITE_ROW) {

                idNum = [NSNumber numberWithInt:(int)sqlite3_column_int(selectstmt, 0)];
                ...
                ...

but if I have a NSData? Can you help me?

redent84 · Answer 1 · 2013-02-25T17:30:13.013

3

SQLite is "server-less", you cannot access from your device to a SQLite file hosted in a server. If it's read-only database, you can download it first or you can use a different database server (and client)

To save the file to documents directory and later read it, add the following code:

NSString *documentsDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString* filePath = [documentsDirectory stringByAppendingPathComponent:@"database.sqlite"];

[data writeToFile:filePath atomically:NO];

Probably your best bet is to publish a thin web-service wrapper around the database. There are more pros that cons using a web-service instead of direct remote database access, take a look to this blog post for further explanations.

edited Feb 25 '13 at 17:30

answered Feb 25 '13 at 17:24

redent84

18,901
4
62
85

aaaa ok...I'm using SQLite manager of mozilla Firefox, where I can set it as a read-only db? – cyclingIsBetter Feb 25 '13 at 17:59
By "read-only" I mean that you do not want the changes reflected in the server. If this is the case you don't need to change anything else. Just remember that the changes made in the Application won't alter the server database. – redent84 Feb 25 '13 at 18:04
ah ok...it's sure.. I want ever only read and never write in this db...but I use this code and it return a null value NSString *filePath = @"http:/ /address/info.sqlite"; NSURL *url = [NSURL URLWithString:filePath]; NSData *myData = [NSData dataWithContentsOfURL:url]; if (!myData) NSLog(@"MY DATA IS NULL"); NSString *path = [NSHomeDirectory() stringByAppendingPathComponent:[@"Documents/" stringByAppendingString:@"info.sqlite"]]; [myData writeToFile:path atomically:YES]; – cyclingIsBetter Feb 25 '13 at 18:06
Make sure that the file is accessible from Internet (check the URL in a web navigator). – redent84 Feb 25 '13 at 18:17

score -1 · Answer 2 · edited May 23 '17 at 12:08

-1

I guess you want to download the latest database bundle instead of bundling it when you releasing the application, so now you have downloaded the database file and it is available in NSData format.

If so the slution is simple, you can simply save the NSData to file in to your document directory (Refer this how to save NSData to file), and contnue loading your bundle as normal way. like this,

NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"info.sqlite"];

remember before you load file "info.sqlite", you need to save to the file from NSData. I haven't try this, but it should work :)

Happy coding!!!

edited May 23 '17 at 12:08

Community

1
1

answered Feb 25 '13 at 17:29

Prasad De Zoysa

2,488
2
23
30

You cannot save a file inside your application bundle. – redent84 Feb 25 '13 at 17:31
I was referred to document directory, it is possible to write a file in to application "Documents" directory. – Prasad De Zoysa Feb 25 '13 at 17:36

IOS: open sqlite from an URL

2 Answers2