SQLite using FMDB: Insert record not working/ no such table

Question

Please help me identify the problem.

The insert statement doesn't work, it gives me a error message that "No Such Table..." when I checked the sqlite db saved in /Users/jppangilinan/Library/Application Support/iPhone Simulator/4.3/Applications/61BBA03F-C240-414D-9A64-6CE3B34DF9C2/Documents/person.sqlite3 it seems that the database save in that location doesn't have any tables that's why the insert statement is not working. Why did it not copy my sqlite db in my Resource Folder in my project? TIA

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *path = [docsPath stringByAppendingPathComponent:@"person.sqlite3"];

FMDatabase *database = [FMDatabase databaseWithPath:path];
[database open];

[database beginTransaction];

NSString *query = [NSString stringWithFormat:@"insert into person(rowid,fname,lname,address) values (null, '%@','%@','%@')",fname.text,lname.text,address.text];

NSLog(@" %@",path);
NSLog(@" %@",query);

BOOL y= [database executeUpdate:query];

if (!y)
{
    NSLog(@"insert failed!!");
}

NSLog(@"Error %d: %@", [database lastErrorCode], [database lastErrorMessage]);



[database commit];
[database close];

}

have you found the solution to this problem ? – Mutix Feb 07 '12 at 13:22 — Mutix, Feb 07 '12 at 13:22

Mutix · Answer 1 · 2011-12-15T00:29:22.483

In order to access your resources database in the Documents folder of application, you first need to copy it there when the application launches.

If the database file is in the resources folder in your xcode project, it is not copied automatically into the documents directory of the app.

To do so you can use:

NSString *documentsDirectory = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];

NSError *error;

NSString *databasePath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"person.sqlite3"];

[[NSFileManager defaultManager] copyItemAtPath:databasePath 
                                                toPath:[NSString stringWithFormat:@"%@/%@",documentsDirectory,@"person.sqlite3"]
                                                 error:&error];

You will then be able to access the database that's now in the documents folder using your existing code.

What was happening is that because the existing DB file wasn't copied, a new empty one is created when you call databaseWithPath, which is why you are getting the Error 1: no such table: person error.

From FMDB documentation:

Database Creation

An FMDatabase is created with a path to a SQLite database file. This path can be one of these three:

A file system path. The file does not have to exist on disk. If it does not exist, it is created for you. An empty string (@""). An empty database is created at a temporary location. This database is deleted with the FMDatabase connection is closed.

NULL. An in-memory database is created. This database will be destroyed with the FMDatabase connection is closed.

FMDatabase *db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];

bryanmac · Answer 2 · 2011-12-14T05:23:32.047

1

Some things to look at:

Look at using params (? in sqlite instead of stringWithFormat)
You need to check error codes: most of those functions return bool. database also has lastErrorCode (int),lastErrorMessage (NSString), and hadError (bool). Check and log those.
for uniqueId, you're inserting a const 11. Doesn't seem unique. If auto seeded id, check lastInsertedRowId and don't specify the id in the insert statement. http://www.sqlite.org/autoinc.html

https://github.com/ccgus/fmdb/blob/master/src/FMDatabase.h

sample: https://github.com/ccgus/fmdb/blob/master/src/fmdb.m

edited Dec 14 '11 at 05:23

answered Dec 14 '11 at 05:18

bryanmac

38,941
11
91
99

thanks bryanmac, will check may codes and thanks for the informative link! =) – user994991 Dec 14 '11 at 05:39
here's my updated code. but it returns "Error 1: no such table: person" – user994991 Dec 14 '11 at 06:01
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *docsPath = [paths objectAtIndex:0]; NSString *path = [docsPath stringByAppendingPathComponent:@"person.sqlite3"]; FMDatabase *database = [FMDatabase databaseWithPath:path]; if (![database open]) { NSLog(@"Could not open db."); } NSLog(@"Error %d: %@", [database lastErrorCode], [database lastErrorMessage]); [database executeUpdate:@"insert into person (uniqueId,fname) values(?,?)",[NSNumber numberWithInt:11],@"Paul",nil]; – user994991 Dec 14 '11 at 06:08
NSLog(@"Error %d: %@", [database lastErrorCode], [database lastErrorMessage]); [database close]; – user994991 Dec 14 '11 at 06:14
You should add an "EDIT:" section to your question with additional code and info. It's very hard to read in a comment – bryanmac Dec 14 '11 at 06:15
hi bryan mac, i update may question could help me again identify my error. thanks! please see update code above. – user994991 Dec 14 '11 at 07:22

score 0 · Answer 3 · answered Nov 05 '14 at 07:38

I have same issues I resolved this issue by given below code, add this code and save your database path in _dbPath:

- (void) FMDBInitializer{
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDir = [documentPaths objectAtIndex:0];
NSString *databasePath = [documentDir stringByAppendingPathComponent:DATABASENAME];

NSLog(@"%@",databasePath);

BOOL success;

NSFileManager *fileManager = [NSFileManager defaultManager];

success = [fileManager fileExistsAtPath:databasePath];

self._dbPath = databasePath;

if(!success){
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:DATABASENAME];
    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];

    //self._db = [FMDatabase databaseWithPath:databasePath];
}
else {
    //self._db = [FMDatabase databaseWithPath:databasePath];
}

}

And add this single line of code in your every query:

FMDatabase *database = [FMDatabase databaseWithPath:self._dbPath];

score 0 · Answer 4 · answered Dec 14 '11 at 05:39

0

Try this, begin transaction and commit:

FMDatabase *database = [FMDatabase databaseWithPath:path];
[database open];

[database beginTransaction];

NSString *query = [NSString stringWithFormat:@"insert into person(uniqueId,fname) values (%d, '%@')",11,@"Paul"];

NSLog(@" %@",path);

BOOL y= [database executeUpdate:query];

if (!y)
{
    NSLog(@"insert failed!!");
}

[database commit];
[database close];

after this part of the code code is executed open the sqlite file which is there in the printed path in 'nslog' path will be like this: /Users/**/Library/Application Support/iPhone Simulator/4.3/Applications/person.sqlite3

any help revert back

Thank you

answered Dec 14 '11 at 05:39

Sathe_Nagaraja

163
1
9

what does wrapping in a transaction give you? In sqlite, single statements are implicitly in a transaction. – bryanmac Dec 14 '11 at 05:54
hi Nagaraja Sathe, i've used your code. however it returns "INSER FAILED". I did check the last error using this NSLog(@"Error %d: %@", [database lastErrorCode], [database lastErrorMessage]); Error 1: no such table: person I did check my database and im sure i have a person table on it. – user994991 Dec 14 '11 at 05:57
i have checked the path location of the sqlite database. it seems that the copy in /Users/jppangilinan/Library/Application Support/iPhone Simulator/4.3/Applications/61BBA03F-C240-414D-9A64-6CE3B34DF9C2/Documents/person.sqlite3 dont have any table??? – user994991 Dec 14 '11 at 06:18
but when i check may person.sqlite3 in xcode where it is saved in RESOURCES it has a table and data. – user994991 Dec 14 '11 at 06:19
did you insert the data into sqlite manually or programmatically? – Sathe_Nagaraja Dec 14 '11 at 06:45
if INSERT Failed is coming no need to check for data inside the table check with the syntax, yry this: NSString *query = [NSString stringWithFormat:@"insert into person(uniqueId,fname) values (?,?)",11,@"Paul"]; replace '%d' and '%@' with '?' – Sathe_Nagaraja Dec 14 '11 at 06:48
i've insert data into sqlite programmatically? i keep on getting error "NO SUCH TABLE FOUND". when ive checked the NSLog(@" %@",path) it seems that the database in that location doesant have a table on it. it's like that in did not copy the actual sqlite database i have in the resource folder i saved. – user994991 Dec 14 '11 at 06:54
when u copied sqlite file into project did u save it in the project folder also? – Sathe_Nagaraja Dec 14 '11 at 07:00
OK - you need to add code to copy it from resources to documents or library. @Mutix just answered below on how to copy. Then you need to construct the path using the same logic to open it. – bryanmac Dec 15 '11 at 00:33

score 0 · Answer 5 · answered Dec 29 '11 at 21:33

The copy in your '/Users/jppangilinan/Library/Application Support/iPhone Simulator/4.3/Applications/61BBA03F-C240-414D-9A64-6CE3B34DF9C2/Documents/person.sqlite3'probably does not match what you have in the location you loaded the database from. Right-click on your database in Xcode and look at the get info. If this database is the one you you expect exode to read from (the good one), copy it to replace the one on '/Users/jppangilinan/Library/Application Support/iPhone Simulator/4.3/Applications/.../Documents/person.sqlite3'. I ran into a similar issue.

SQLite using FMDB: Insert record not working/ no such table

5 Answers5