通过Objective-C创建SQLite3数据库文件 [英] Creating an SQLite3 database file through Objective-C

问题描述

我正在尝试在运行时通过Objective-C创建一个SQLite3数据库文件。我正在尝试创建一个名为tblStore的表。我希望字段名称被称为strStoreNumber和strStoreReg。我是iOS和SQLite的新手，所以我很难找到执行此操作的语法。除了创建表之外，我希望创建的表不驻留在应用程序包中，而是将其驻留/存储在手机上的某个位置。该表需要是可读/可写的。我已经对用户沙箱以及文档目录进行了一些阅读。我不确定我理解两者之间的区别。理想情况下，我的应用程序将使用一个按钮从文本字段中获取输入。将文本字段的输入放入字符串后，将进行检查以查看我的tblStoreSQLite表是否存在，如果不存在，则将创建该表。

I'm trying to create an SQLite3 database file through Objective-C at run time. I am trying to create a table called "tblStore". I want the field names to be called "strStoreNumber" and "strStoreReg". I'm new to iOS and SQLite, so I'm having a hard time finding the syntax to perform this. In addition to creating the table, I want the created table to reside NOT in the app bundle, but rather it would reside/be stored somewhere on the phone. The table needs to be readable/writeable. I've done some reading on "user sandbox" and also a "documents directory". I'm not sure I understand the difference between the two. Ideally, my app would use a button to take input from Text Fields. After the input from the texts fields is put into strings, a check would be done to see if my "tblStore" SQLite table exists, and if it doesn't, the table will be created.

概括：
1. Obj-C / SQLite创建名为tblStore的表的语法是什么，字段为strStoreNumber和 strStoreReg？
2. db文件应该驻留在哪里？我需要读取和写入tblStore db文件。
3.用户沙箱和文档目录之间有什么区别？

To recap: 1. What is the syntax for Obj-C/SQLite to create a table called "tblStore" with the fields "strStoreNumber" and "strStoreReg"? 2. Where should the db file reside? I need to read from and write to the tblStore db file. 3. What is the difference between a "user sandbox" and a "documents directory"?

这就是我目前所拥有的：

This is what I have currently:

-(IBAction)setInput:(id)sender
{
    NSString *strStoreNumber;
    NSString *strRegNumber;
    NSString *tableName = @"tblStore";
    NSString *dbStrStore = @"strStore";
    NSString *dbStrReg = @"strReg";


    strStoreNumber = StoreNumber.text;
    strRegNumber = RegNumber.text;

    NSArray* paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString* documentsDirectory = [paths lastObject];
    NSString* databasePath = [documentsDirectory stringByAppendingPathComponent:@"tblStore.sqlite"];
//  NSString* databasePath = [[NSBundle mainBundle] pathForResource:@"tblStore" ofType:@"sqlite"];

    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) 
    {
        NSLog(@"Opened sqlite database at %@", databasePath);

        char *err; 
        NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' TEXT PRIMARY KEY, '%@' TEXT);", tableName, dbStrStore, dbStrReg];
        if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) 
        { 
            sqlite3_close(database);
            NSAssert(0, @"Table failed to create.");
        }
        //...stuff
    } 
    else 
    {
        NSLog(@"Failed to open database at %@ with error %s", databasePath, sqlite3_errmsg(database));
        sqlite3_close (database);
    }

    NSString *querystring;

    // create your statement
    querystring = [NSString stringWithFormat:@"SELECT strStore, strReg FROM tblStore WHERE strStore = %@ AND strReg = %@;", strStoreNumber, strRegNumber];  

    const char *sql = [querystring UTF8String];

    NSString *szStore = nil;
    NSString *szReg = nil;

    sqlite3_stmt *statement = nil;
    if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL)!=SQLITE_OK) //queryString = Statement
    {
        NSLog(@"sql problem occured with: %s", sql);
        NSLog(@"%s", sqlite3_errmsg(database));
    }
    else
    {
        // you could handle multiple rows here
        while (sqlite3_step(statement) == SQLITE_ROW) 
        {            
            szStore = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 0)];
            szReg = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)];
        }        
    }

    sqlite3_finalize(statement);

    lblStoreNumber.text = szStore;
    lblRegNumber.text = szReg;   
} 

当我运行我的应用程序时，出现以下错误：

When I run my app, I get the following errors:

2012-05-10 14:58:38.169 CCoDBTry[355:f803] Opened sqlite database at /Users/Matt****/Library/Application Support/iPhone Simulator/5.1/Applications/5DB7A218-A0F6-   485F-B366-91FD2F9BC062/Documents/tblStore.sqlite
2012-05-10 14:58:38.307 CCoDBTry[355:f803] sql problem occured with: SELECT strStore, strReg FROM tblStore WHERE strStore = 8053 AND strReg = 4;
2012-05-10 14:58:38.308 CCoDBTry[355:f803] no such column: strStore

我感谢任何花时间解释其中一些内容的人，因为我很新，并且未能成功完成我尝试过的一些事情。非常感谢你们的帮助！

I appreciate anyone who takes the time out to explain some of this stuff, as I am very new and have been unsuccessful in accomplishing some of the things I've tried. Thanks so much for the help!

推荐答案

如果你不知道自己在做什么，sqlite会很痛苦。我也遇到了一些sqlite c函数的问题，但后来我决定使用sqlite包装器。

sqlite is a pain if you dont know what you're doing. I also had some problems with sqlite c functions but then i decided to use sqlite wrapper.

FMDB 和 BWDB 对于目标c来说，它是好的且易于使用的sqlite包装器。我建议你使用其中一个。

FMDB and BWDB are good and easy to use sqlite wrappers for objective c. I suggest you use one of those .

请注意，BWDB在lynda.com教程中（这个）如果你没有在网上找到它。 ..发表评论，我会将其上传到某个地方。

Note that BWDB is in a lynda.com tutorial (this one) and if you don't find it on the web...leave a comment and i'll upload it somewhere.

编辑：你可以在你的应用程序中编写内容的唯一地方是你的文档目录......所以。 .it plain terms ...如果db不在你的文档目录中......是只读的...当你读/写你的db..the操作系统会复制文件目录中的数据库..并且所有的阅读和写作，所以你可以在你的应用程序包中有一个数据库，但你不能编辑那个...所以你最终会得到2 db.I我自己有同样的问题..我修复它当我更新应用程序时合并2 db

edit: the only place you can write stuff in your app is in your documents directory...so..it plain terms...if the db is not in your documents directory..is read-only..also..when you read/write to your db..the OS copies the db in the documents directory..and does all the reading and writing there so you can have a db in your app bundle but you can't edit that one...so you'll end up with 2 db.I had the same problem myself..and i fixed it by merging the 2 db when i updated the app

edit2：我上传了BWDB最终项目（你有你的包装器和项目，看它是如何工作的）

edit2: i uploaded BWDB final project ( you have your wrapper there and project to see how it works)

这篇关于通过Objective-C创建SQLite3数据库文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！