xcode SQLite 使用 FMDB：插入记录不起作用/没有这样的表

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

提问by user994991

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.sqlite3it 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

插入语句不起作用，当我检查保存在/Users/jppangilinan/Library/Application Support/iPhone Simulator/4.3/Applications/61BBA03F- 中的 sqlite 数据库时，它给了我一条错误消息“没有这样的表......” C240-414D-9A64-6CE3B34DF9C2/Documents/person.sqlite3似乎保存在该位置的数据库没有任何表，这就是插入语句不起作用的原因。为什么它没有在我的项目的资源文件夹中复制我的 sqlite 数据库？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];

}

回答by Mutix

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

为了访问应用程序 Documents 文件夹中的资源数据库，您首先需要在应用程序启动时将其复制到那里。

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.

如果数据库文件在您的 xcode 项目中的资源文件夹中，它不会自动复制到应用程序的文档目录中。

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: personerror.

发生的事情是因为现有的 DB 文件没有被复制，所以当您调用时会创建一个新的空文件databaseWithPath，这就是您收到Error 1: no such table: person错误的原因。

From FMDB documentation:

从FMDB 文档：

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

数据库创建

FMDatabase 是使用 SQLite 数据库文件的路径创建的。此路径可以是以下三个路径之一：

文件系统路径。该文件不必存在于磁盘上。如果它不存在，则会为您创建。空字符串 (@"")。在临时位置创建一个空数据库。该数据库在 FMDatabase 连接关闭时被删除。

空值。创建了一个内存数据库。该数据库将随着 FMDatabase 连接的关闭而销毁。

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

回答by bryanmac

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

• 看看使用 params (? in sqlite 而不是 stringWithFormat)
• 您需要检查错误代码：大多数这些函数返回 bool。数据库还有 lastErrorCode (int)、lastErrorMessage (NSString) 和 hadError (bool)。检查并记录这些。
• 对于 uniqueId，您插入的是 const 11。似乎不是唯一的。如果自动播种 id，请检查 lastInsertedRowId 并且不要在插入语句中指定 id。 http://www.sqlite.org/autoinc.html

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

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

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

示例：https: //github.com/ccgus/fmdb/blob/master/src/fmdb.m

回答by Sathe_Nagaraja

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

执行这部分代码后，打开'nslog'路径中打印路径中的sqlite文件将如下所示：/Users/ **/Library/Application Support/iPhone Simulator/4.3/Applications/person。 sqlite3

any help revert back

任何帮助恢复

Thank you

谢谢

回答by Kingsley Sendze

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.

您的“/Users/jppangilinan/Library/Application Support/iPhone Simulator/4.3/Applications/61BBA03F-C240-414D-9A64-6CE3B34DF9C2/Documents/person.sqlite3”中的副本可能与您加载的位置中的内容不匹配数据库来自。在 Xcode 中右键单击您的数据库并查看获取信息。如果此数据库是您希望 exode 从中读取的数据库（好的数据库），请复制它以替换“/Users/jppangilinan/Library/Application Support/iPhone Simulator/4.3/Applications/.../Documents/”上的数据库人.sqlite3'。我遇到了类似的问题。

回答by Maishi Wadhwani

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

我有同样的问题，我通过下面给出的代码解决了这个问题，添加此代码并将您的数据库路径保存在 _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];