ios - 在 ios 上的 SQLite 数据库中查找条目

Question

我遵循了有关创建 SQLite 数据库、存储内容和查找内容的教程。我没有做的是创建界面，因为控制台日志记录可以快得多。(这也不需要其他人构建交互，但不要忘记导入库!:))

这是一个重要的教程: http://www.techotopia.com/index.php/An_Example_SQLite_based_iOS_6_iPhone_Application

我在查找数据时遇到问题，有人知道它是什么吗？

- (void) findContact {
    const char *dbpath = [_databasePath UTF8String];
    sqlite3_stmt *statement;

    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {

        NSString *querySQL = [NSString stringWithFormat:
                              @"SELECT adress, phone FROM contacts WHERE name=\"%@\"", @"DoekeW"];

        const char *query_stmt = [querySQL UTF8String];

        if(sqlite3_prepare_v2(_contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW) {
                NSString *adressField = [[NSString alloc] initWithUTF8String:
                                         (const char*) sqlite3_column_text(statement, 0)];
                NSString *phoneField = [[NSString alloc] initWithUTF8String:
                                        (const char*) sqlite3_column_text(statement, 1)];
                NSLog(@"adressField: %@ phoneField: %@", adressField, phoneField);
            }
            else {
                NSLog(@"no match");
            }
            sqlite3_finalize(statement);
        }
        else {
            // 1, whatever that is
            NSLog(@"hmm %d", sqlite3_prepare_v2(_contactDB, query_stmt, -1, &statement, NULL));
        }
        sqlite3_close(_contactDB);
    }
}

这里是完整的代码:

.h 文件:

#import <UIKit/UIKit.h>
#import <sqlite3.h>

@interface IGViewController : UIViewController

@property (strong, nonatomic) NSString *databasePath;
@property (nonatomic) sqlite3 *contactDB;

@end

.m 文件

#import "IGViewController.h"

@interface IGViewController ()

@end

@implementation IGViewController

- (void)viewDidLoad
{
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.

    NSString *docsDir;
    NSArray *dirPaths;

    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = dirPaths[0];

    // Build the path to the database file
    _databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent:@"contacts.db"]];

    NSLog(@"%@", _databasePath);

    NSFileManager *filemgr = [NSFileManager defaultManager];

    if ([filemgr fileExistsAtPath: _databasePath] == NO) {

        const char *dbpath = [_databasePath UTF8String];

        if(sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {
            char *errMsg;
            const char *sql_stmt =
            "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)";

            if (sqlite3_exec(_contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                //_status.text = @"Failed to create table";
                NSLog(@"Failed to create table");

            }
            sqlite3_close(_contactDB);
        } else {
           // _status.text = @"Failed to open/create database";
            NSLog(@"Failed to open/create database");
        }
    }

    else {
        NSLog(@"database allready existed");
    }

    [self saveData];

    [self findContact];
}


- (void) saveData {
    sqlite3_stmt *statement;
    const char *dbpath = [_databasePath UTF8String];

    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {
        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO CONTACTS (name, address, phone) VALUES (\"%@\", \"%@\", \"%@\")",
                               @"DoekeW", @"Molenstraat", @"483577"];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(_contactDB, insert_stmt, -1, &statement, NULL);
        if(sqlite3_step(statement) == SQLITE_DONE) {
            NSLog(@"saved data!");
        }
        else {
            NSLog(@"something wrong, no data saved");
        }
        sqlite3_finalize(statement);
        sqlite3_close(_contactDB);
    }
}

- (void) findContact {
    const char *dbpath = [_databasePath UTF8String];
    sqlite3_stmt *statement;

    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK) {

        NSString *querySQL = [NSString stringWithFormat:
                              @"SELECT adress, phone FROM contacts WHERE name=\"%@\"", @"DoekeW"];

        const char *query_stmt = [querySQL UTF8String];

        if(sqlite3_prepare_v2(_contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW) {
                NSString *adressField = [[NSString alloc] initWithUTF8String:
                                         (const char*) sqlite3_column_text(statement, 0)];
                NSString *phoneField = [[NSString alloc] initWithUTF8String:
                                        (const char*) sqlite3_column_text(statement, 1)];
                NSLog(@"adressField: %@ phoneField: %@", adressField, phoneField);
            }
            else {
                NSLog(@"no match");
            }
            sqlite3_finalize(statement);
        }
        else {
            // 1, whatever that is
            NSLog(@"hmm %d", sqlite3_prepare_v2(_contactDB, query_stmt, -1, &statement, NULL));
        }
        sqlite3_close(_contactDB);
    }
}



- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end

Answer 1

您有一个NSLog findContact中的声明上面写着:

NSLog(@"hmm %d", sqlite3_prepare_v2(_contactDB, query_stmt, -1, &statement, NULL));

你绝对不想在失败时再次做好准备。您只想检索错误消息。因此应该是:

NSLog(@"prepare failed: %s", sqlite3_errmsg(_contactDB));

如果您这样做，您将收到一条错误消息，指出 address 的拼写错误。在您的 SQL 语句中。

坦率地说，几乎每个 SQLite 调用都应该检查其结果，如果没有 SQLITE_OK (或 SQLITE_ROW 或 SQLITE_DONE 对于 sqlite3_step )应该报告 sqlite3_errmsg .

当您采用 sqlite3_errmsg 时，您会更快地发现错误。 。让 SQLite 准确地告诉您问题所在!

<小时/>

顺便说一句，我还建议:

1. 我还建议您不要使用 stringWithFormat 构建 SQL 语句。 。例如，如果此人的姓名是Dwayne "The Rock" Johnson，那么您就会遇到问题。 。相反，您应该使用 ?占位符。 (您还容易受到 SQL 注入(inject)攻击。)底线，而不是:

   NSString *insertSQL = [NSString stringWithFormat:
                          @"INSERT INTO CONTACTS (name, address, phone) VALUES (\"%@\", \"%@\", \"%@\")",
                          @"DoekeW", @"Molenstraat", @"483577"];
   
   const char *insert_stmt = [insertSQL UTF8String];
   sqlite3_prepare_v2(_contactDB, insert_stmt, -1, &statement, NULL);
   if(sqlite3_step(statement) == SQLITE_DONE) {
       NSLog(@"saved data!");
   }
   else {
       NSLog(@"something wrong, no data saved");
   }
   sqlite3_finalize(statement);
   

   你应该使用:

   const char *insert_stmt = "INSERT INTO CONTACTS (name, address, phone) VALUES (?, ?, ?)";
   
   if (sqlite3_prepare_v2(_contactDB, insert_stmt, -1, &statement, NULL) != SQLITE_OK)
       NSLog(@"%s: prepare error: %s", __FUNCTION__, sqlite3_errmsg(_contactDB));
   
   if (sqlite3_bind_text(statement, 1, [@"DoekeW" UTF8String], -1, NULL) != SQLITE_OK)
       NSLog(@"%s: bind 1 error: %s", __FUNCTION__, sqlite3_errmsg(_contactDB));
   
   if (sqlite3_bind_text(statement, 2, [@"Molenstraat" UTF8String], -1, NULL) != SQLITE_OK)
       NSLog(@"%s: bind 2 error: %s", __FUNCTION__, sqlite3_errmsg(_contactDB));
   
   if (sqlite3_bind_text(statement, 3, [@"483577" UTF8String], -1, NULL) != SQLITE_OK)
       NSLog(@"%s: bind 3 error: %s", __FUNCTION__, sqlite3_errmsg(_contactDB));
   
   if(sqlite3_step(statement) == SQLITE_DONE) {
       NSLog(@"saved data!");
   }
   else {
       NSLog(@"%s: step error: %s", __FUNCTION__, sqlite3_errmsg(_contactDB));
   }
   
   sqlite3_finalize(statement);
   

   基本上，使用 sqlite3_bind_text 将值绑定(bind)到 ?占位符并消除 stringWithFormat共。 WHERE 也是如此。 SELECT 的条款声明。

2. 我赞同 Anupdas 使用 FMDB 的建议。 。它使 SQLite 编码变得更加容易。