CODEWITHSUNDEEP
iosxcodesqlitexcode3.2
user994991
user994991

Reputation: 47

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

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];

}

Upvotes: 2

Views: 6894

Answers (5)

Mahesh Kumar
Mahesh Kumar

Reputation: 1104

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];

Upvotes: 0

Kingsley Sendze
Kingsley Sendze

Reputation: 55

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.

Upvotes: 0

Mutix
Mutix

Reputation: 4196

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"];

Upvotes: 3

Sathe_Nagaraja
Sathe_Nagaraja

Reputation: 163

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

Upvotes: 0

bryanmac
bryanmac

Reputation: 39296

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

Upvotes: 1

Related Questions