正确的方式插入数据到SQLite数据库iPhone [英] Right way of inserting data into SQLite db iPhone

问题描述

如何在sqlite中插入数据到iPhone中？

我试图追踪，但是失败：

< NSString * query = [NSString stringWithFormat：@insert into％@（name）values（'％@'），table name，myName]; pre $


sqlite3 *数据库;

sqlite3_stmt * createStmt = nil; （sqlite3_open（[databasePath UTF8String]，& database）== SQLITE_OK）{
if（createStmt == nil）{

if（sqlite3_prepare_v2（数据库，[query UTF8String]，-1，& createStmt，NULL）！= SQLITE_OK）{
return NO;

sqlite3_exec（database，[query UTF8String]，NULL，NULL，NULL）;
返回YES;
}

return YES;
} else {
return NO;



$ b我已经按以下方式创建了表格：



 创建表（如果不存在）myDets（dets_id整数主键asc，名称文本）; 
  

我也使用Firefox SQLite插件来检查数据库。当我尝试通过Firefox插入记录到我的数据库，它给了我以下错误：



 未能插入值
例外名称：NS_ERROR_STORAGE_IOERR 
异常消息：组件返回失败代码：0x80630002（NS_ERROR_STORAGE_IOERR）[mozIStorageStatement.execute] 
  





另外，我在iPhone模拟器上运行这个，这有什么关系吗？

请帮助

在此先感谢。

解决方案

使用参数，不要字符串格式化你的更新语句

添加大量的跟踪，检查返回码和检查错误代码（你问题不清楚是什么错误，在iPhone上点击的问题）。

使用sqlite3_errmsg获取错误消息

构建您的dbPath并将其注销。确保您可以打开从模拟器下的终端数据库。请记住，sqlite将创建一个数据库e在内存中被动，所以如果你的路径是关闭的，数据库不存在，这可能是混淆。

打印出你的更新语句，并尝试从sqlite cmdline应用程序在终端如果您将数据库放在主包资源中，则它是一个模板，需要复制才能打开并写入。

下面是我的一个示例的更新函数：

 <$ c 
 {
 if（！[self ensureDatabaseOpen：error]）
 {
（$）返回; 
 
 
 NSLog（@>> ContactManager :: updateContact）; 
 
 //准备语句
 sqlite3_stmt *语句; 
 NSString * querySQL = @update contacts set name =？，address =？，phone =？where id =？; 
 NSLog（@query：％@，querySQL）; 
 const char * query_stmt = [querySQL UTF8String]; 
 
 //准备查询编译查询，以便重用。 
 sqlite3_prepare_v2（_contactDb，query_stmt，-1，& statement，NULL）; 
 sqlite3_bind_text（statement，1，[[contact name] UTF8String]，-1，SQLITE_STATIC）; 
 sqlite3_bind_text（语句，2，[[联系地址] UTF8String]，-1，SQLITE_STATIC）; 
 sqlite3_bind_text（语句，3，[[联系电话] UTF8String]，-1，SQLITE_STATIC）; 
 sqlite3_bind_int64（statement，4，[[contact id] longLongValue]）; 
 
 NSLog（@bind name：％@，[contact name]）; 
 NSLog（@bind address：％@，[contact address]）; 
 NSLog（@bind phone：％@，[contact phone]）; 
 NSLog（@bind int64：％qi，[[contact id] longLongValue]）; 
 
 //处理结果
 if（sqlite3_step（statement）！= SQLITE_DONE）
 {
 NSLog（@error：％s，sqlite3_errmsg（_contactDb）） ; 
} 
 
 sqlite3_finalize（statement）; 
 
 
 
 
 
 $ b在示例中，将数据库从资源复制到路径并打开。这里是保证打开的函数，我用来做到这一点： 
 
 
   - （BOOL）ensureDatabaseOpen：（NSError **）error 
 {
 //已经创建了db连接
 if（_contactDb！= nil）
 {
 return YES; 
} 
 
 NSLog（@>> ContactManager :: ensureDatabaseOpen）; 
 if（！[self ensureDatabasePrepared：error]）
 {
 return NO; 
} 
 
 const char * dbpath = [_dbPath UTF8String]; 
 if（sqlite3_open（dbpath，& _contactDb）！= SQLITE_OK&& 
 error！= nil）
 {
 * error = [[[NSError alloc] initWithDomain： @ContactsManager代码：1000 userInfo：nil] autorelease]; 
 return NO; 
} 
 
 NSLog（@opened）; 
 
 return YES; 
 
 $ b  - （BOOL）ensureDatabasePrepared：（NSError **）error 
 {
 //已经准备好
 if（（_dbPath！= nil） && 
（[[NSFileManager defaultManager] fileExistsAtPath：_dbPath]））
 {
 return YES; 
} 
 
 //主包中的数据库 - 无法编辑。复制到库如果存在
 NSString * dbTemplatePath = [[NSBundle mainBundle] pathForResource：@contactsofType：@db]; 
 NSLog（@％@，dbTemplatePath）; 
 
 NSString * libraryPath = [NSSearchPathForDirectoriesInDomains（NSLibraryDirectory，NSUserDomainMask，YES）lastObject]; 
 _dbPath = [libraryPath stringByAppendingPathComponent：@contacts.db]; 
 
 NSLog（@dbPath：％@，_dbPath）; 
 $ b $ //将db从模板复制到库
 if（！[[NSFileManager defaultManager] fileExistsAtPath：_dbPath]）
 {
 NSLog（@db not exists ）; 
 NSError * error = nil; 
 if（！[[NSFileManager defaultManager] copyItemAtPath：dbTemplatePath toPath：_dbPath error：& error]）
 {
 return NO; 
} 
 
 NSLog（@copied）; 
} 
 
 return YES; 
} 
  
 
How to insert data into table in sqlite iPhone?

I am trying following, but its failing:
 NSString *query=[NSString stringWithFormat:@"insert into %@ (name) values ('%@')", table name,myName ];


 sqlite3 *database;

sqlite3_stmt *createStmt = nil;

if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
    if (createStmt == nil) {

        if (sqlite3_prepare_v2(database, [query UTF8String], -1, &createStmt, NULL) != SQLITE_OK) {
            return NO;
        }
        sqlite3_exec(database, [query UTF8String], NULL, NULL, NULL);
        return YES;
    }

    return YES;
}else {
    return NO;
}
I have created table in following manner:
create table if not exists myDets (dets_id integer primary key asc, name text);
I am also using Firefox SQLite plugin to check db. When I try to insert record via firefox into my db it gives me following error:
Failed to insert values
Exception Name: NS_ERROR_STORAGE_IOERR
Exception Message: Component returned failure code: 0x80630002 (NS_ERROR_STORAGE_IOERR)        [mozIStorageStatement.execute]
Badly stuck :( 

Also, i am running this on iPhone simulator, does it matter?

Please help

Thanks in advance.
解决方案

Use parameters and don't string format your update statement
Add lots of tracing, check return codes and check for error codes (you're question isn't clear what error, issue you're hitting on iPhone).
use sqlite3_errmsg to get error messages
construct your dbPath and log it out.  Ensure you can open the db from terminal under the emulator.  Remember that sqlite will create a database in memory passively so if you're path is off and the db doesn't exist it can be confusing.
print out your update statement and try it from sqlite cmdline app in terminal in the path you logged.
if you're putting the db in the main bundle resources, it's a template and needs to be copied in order to open and write to it.


Here's an update function from a sample of mine:
- (void)updateContact: (Contact*)contact error:(NSError**)error
{
    if (![self ensureDatabaseOpen:error])
    {
        return;
    }

    NSLog(@">> ContactManager::updateContact");

    // prep statement
    sqlite3_stmt    *statement;
    NSString *querySQL = @"update contacts set name=?,address=?,phone=? where id=?";
    NSLog(@"query: %@", querySQL);
    const char *query_stmt = [querySQL UTF8String];

    // preparing a query compiles the query so it can be re-used.
    sqlite3_prepare_v2(_contactDb, query_stmt, -1, &statement, NULL);     
    sqlite3_bind_text(statement, 1, [[contact name] UTF8String], -1, SQLITE_STATIC);
    sqlite3_bind_text(statement, 2, [[contact address] UTF8String], -1, SQLITE_STATIC);
    sqlite3_bind_text(statement, 3, [[contact phone] UTF8String], -1, SQLITE_STATIC);
    sqlite3_bind_int64(statement, 4, [[contact id] longLongValue]);

    NSLog(@"bind name: %@", [contact name]);
    NSLog(@"bind address: %@", [contact address]);
    NSLog(@"bind phone: %@", [contact phone]);
    NSLog(@"bind int64: %qi", [[contact id] longLongValue]);

    // process result
    if (sqlite3_step(statement) != SQLITE_DONE)
    {
        NSLog(@"error: %s", sqlite3_errmsg(_contactDb));
    }

    sqlite3_finalize(statement);
}
In the sample, the db is copied from resources to a path and opened.  Here's the ensure opened function I use to do that:
- (BOOL)ensureDatabaseOpen: (NSError **)error
{
    // already created db connection
    if (_contactDb != nil)
    {
        return YES;
    }

    NSLog(@">> ContactManager::ensureDatabaseOpen");    
    if (![self ensureDatabasePrepared:error])
    {
        return NO;
    }

    const char *dbpath = [_dbPath UTF8String]; 
    if (sqlite3_open(dbpath, &_contactDb) != SQLITE_OK &&
        error != nil)
    {
        *error = [[[NSError alloc] initWithDomain:@"ContactsManager" code:1000 userInfo:nil] autorelease];
        return NO;
    }

    NSLog(@"opened");

    return YES;
}

- (BOOL)ensureDatabasePrepared: (NSError **)error
{
    // already prepared
    if ((_dbPath != nil) &&
        ([[NSFileManager defaultManager] fileExistsAtPath:_dbPath]))
    {
        return YES;
    }

    // db in main bundle - cant edit.  copy to library if !exist
    NSString *dbTemplatePath = [[NSBundle mainBundle] pathForResource:@"contacts" ofType:@"db"];
    NSLog(@"%@", dbTemplatePath);

    NSString *libraryPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
    _dbPath = [libraryPath stringByAppendingPathComponent:@"contacts.db"];

    NSLog(@"dbPath: %@", _dbPath);

    // copy db from template to library
    if (![[NSFileManager defaultManager] fileExistsAtPath:_dbPath])
    {
        NSLog(@"db not exists");
        NSError *error = nil;
        if (![[NSFileManager defaultManager] copyItemAtPath:dbTemplatePath toPath:_dbPath error:&error])
        {
            return NO;
        }

        NSLog(@"copied");
    }    

    return YES;    
}


                        
这篇关于正确的方式插入数据到SQLite数据库iPhone的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！