ios 通过 Objective-C 创建 SQLite3 数据库文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10540728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating an SQLite3 database file through Objective-C
提问by Skizz
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.
我试图通过创建的Objective-C在运行时sqlite3的数据库文件。我正在尝试创建一个名为“tblStore”的表。我希望将字段名称称为“strStoreNumber”和“strStoreReg”。我是 iOS 和 SQLite 的新手，所以我很难找到执行此操作的语法。除了创建表之外，我还希望创建的表不驻留在应用程序包中，而是驻留在/存储在手机上的某个地方。该表需要可读/可写。我已经阅读了“用户沙箱”和“文档目录”。我不确定我是否理解两者之间的区别。理想情况下，我的应用程序将使用一个按钮从文本字段中获取输入。将来自文本字段的输入放入字符串后，
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"?
回顾一下： 1. Obj-C/SQLite 创建一个名为“tblStore”的表的语法是什么，其中包含“strStoreNumber”和“strStoreReg”字段？2. db 文件应该存放在哪里？我需要读取和写入 tblStore db 文件。3. “用户沙箱”和“文档目录”有什么区别？
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!
我感谢任何花时间解释其中一些内容的人，因为我很新，并且未能成功完成我尝试过的一些事情。非常感谢你的帮忙！
采纳答案by skytz
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.
如果您不知道自己在做什么，sqlite 会很痛苦。我在使用 sqlite c 函数时也遇到了一些问题，但后来我决定使用 sqlite 包装器。
FMDBand BWDBare good and easy to use sqlite wrappers for objective c. I suggest you use one of those .
FMDB和BWDB是用于目标 c 的良好且易于使用的 sqlite 包装器。我建议你使用其中之一。
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.
请注意，BWDB 位于 lynda.com 教程（本教程）中，如果您在网上找不到它...请发表评论，我会将其上传到某个地方。
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
编辑：你可以在你的应用程序中写东西的唯一地方是你的文档目录......所以......简单来说......如果数据库不在你的文档目录中......是只读......也是......当你读/写你的数据库..操作系统复制文档目录中的数据库..并在那里完成所有的读写，所以你可以在你的应用程序包中有一个数据库，但你不能编辑那个......所以你最终会得到 2 db。我自己也遇到了同样的问题。我在更新应用程序时通过合并 2 db 来修复它
edit2: i uploaded BWDB final project ( you have your wrapper there and project to see how it works)
编辑 2：我上传了 BWDB 最终项目（你在那里有你的包装器和项目，看看它是如何工作的）
回答by Vineesh TP
// Create DB
// 创建数据库
-(NSString *) filePath
{
NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDirectory=[paths objectAtIndex:0];
return [documentDirectory stringByAppendingPathComponent:@"LoginDatabase.sql"];
}
// Open DB
// 打开数据库
-(void)openDB
{
if(sqlite3_open([[self filePath]UTF8String], &db) !=SQLITE_OK)
{
sqlite3_close(db);
NSAssert(0, @"Database failed to Open");
}
}
// Create Table
// 创建表
-(void) createTableNamed:(NSString*)tableName withField1:(NSString*) field1 withField2:(NSString*) field2
{
char *err;
NSString *sql=[NSString stringWithFormat:@" CREATE TABLE IF NOT EXISTS '%@'('%@' TEXT PRIMARY KEY,'%@' TEXT);",tableName,field1,field2];
if(sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) !=SQLITE_OK)
{
sqlite3_close(db);
NSAssert(0, @"Table failed to create");
}
}
// Inserting records
// 插入记录
-(void)insertrecordIntoTable:(NSString*) tableName withField1:(NSString*) field1 field1Value:(NSString*)field1Vaue andField2:(NSString*)field2 field2Value:(NSString*)field2Value
{
NSString *sqlStr=[NSString stringWithFormat:@"INSERT INTO '%@'('%@','%@')VALUES(?,?)",tableName,field1,field2];
const char *sql=[sqlStr UTF8String];
sqlite3_stmt *statement1;
if(sqlite3_prepare_v2(db, sql, -1, &statement1, nil)==SQLITE_OK)
{
sqlite3_bind_text(statement1, 1, [field1Vaue UTF8String], -1, nil);
sqlite3_bind_text(statement1, 2, [field2Value UTF8String], -1, nil);
}
if(sqlite3_step(statement1) !=SQLITE_DONE)
NSAssert(0, @"Error upadating table");
sqlite3_finalize(statement1);
}
// Retrieve data from table
// 从表中获取数据
-(void)getAllRowsFromTableNamed:(NSString *)tableName
{
NSString *field1Str,*field2Str;
NSString *qsql=[NSString stringWithFormat:@"SELECT * FROM %@",tableName];
sqlite3_stmt *statement;
if(sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, nil)==SQLITE_OK)
{
while(sqlite3_step(statement) ==SQLITE_ROW)
{
char *field1=(char *) sqlite3_column_text(statement, 0);
char *field2=(char *) sqlite3_column_text(statement, 1);
field1Str=[[NSString alloc]initWithUTF8String:field1];
field2Str=[[NSString alloc] initWithUTF8String:field2];
NSString *str=[NSString stringWithFormat:@"%@ - %@",field1Str,field2Str];
NSLog(@"%@",str);
}
}
}
}
In viewDidLoad call the methods
在 viewDidLoad 中调用方法
- (void)viewDidLoad
{
[self openDB];
[self createTableNamed:@"Login" withField1:@"USERNAME" withField2:@"PASSWORD"];
[self insertrecordIntoTable:@"Login" withField1:@"USERNAME" field1Value:username andField2:@"PASSWORD" field2Value:password];
}
Where username and password are NSString values;
其中用户名和密码是 NSString 值；
回答by mithlesh jha
You can use following code to get the database created at in Documents folder. Just pass a path in documents folder and the function will copy the sqlite database in Documents folder at the given path if required. You can then use this path to create and query database tables.
您可以使用以下代码获取在 Documents 文件夹中创建的数据库。只需在文档文件夹中传递一个路径，如果需要，该函数将在给定路径的文档文件夹中复制 sqlite 数据库。然后您可以使用此路径来创建和查询数据库表。
+ (NSString*) createDatabaseIfRequiredAtPath:(NSString*)databasePath {
if (databasePath == nil)
return nil;
NSString *path = [NSString stringWithFormat:@"%@/%@", databasePath, kMainDBName];
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error = nil;
if ([fileManager fileExistsAtPath:path] == NO)
{
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[NSBundle mainBundle] pathForResource:kMainDBName
ofType:nil];
BOOL success = [fileManager copyItemAtPath:defaultDBPath
toPath:path
error:&error];
if (!success)
{
NSCAssert1(0, @"Failed to create writable database file with message '%@'.", [ error localizedDescription]);
return nil;
}
}
return path;
回答by Baalu
maybe you forgot Table Insertions. Created Database and Table but its empty table . you trying to read the records from Table which is empty.
也许你忘记了表插入。创建了数据库和表，但它的表是空的。您试图从空的表中读取记录。
回答by Jaybit
you can open terminal and cd to /Users/Matt**/Library/Application Support/iPhone Simulator/5.1/Applications/5DB7A218-A0F6-485F-B366-91FD2F9BC062/Documents/
你可以打开终端和 cd 到 /Users/Matt* */Library/Application Support/iPhone Simulator/5.1/Applications/5DB7A218-A0F6-485F-B366-91FD2F9BC062/Documents/
then sqlite3 tblStore.sqlite
然后 sqlite3 tblStore.sqlite
then use .schema tblStore should show your table schema and you can see if it was built correctly.
然后使用 .schema tblStore 应该显示您的表架构，您可以查看它是否正确构建。
Your database needs to be in the documents directory for write access. If your database was only going to be read, never written to it could be in your application bundle. One way to accomplishing this is to create your database.sqlite file and add it to the bundle and copy it to the documents directory (if it doesn't already exist there) on launch.
您的数据库需要位于文档目录中才能进行写访问。如果您的数据库只是要被读取，则永远不会写入它可能在您的应用程序包中。完成此操作的一种方法是创建 database.sqlite 文件并将其添加到包中，然后在启动时将其复制到文档目录（如果该目录尚不存在）。
回答by isaac
Is there a specific reason you'd like to use SQLLite directly, as opposed to using CoreData? CoreData uses an SQLLite database but is itself a higher-level API, and particularly with table views and such, you get a lot of functionality and template methods that are already set up for it in Xcode. Defining data models is trivial, you get tons of boilerplate code, and it's all optimized.
与使用 CoreData 相比，您是否有特定的原因想要直接使用 SQLLite？CoreData 使用 SQLLite 数据库，但它本身是一个更高级别的 API，特别是对于表视图等，您将获得许多已在 Xcode 中为其设置的功能和模板方法。定义数据模型是微不足道的，你会得到大量的样板代码，而且都是经过优化的。
http://www.raywenderlich.com/934/core-data-on-ios-5-tutorial-getting-started
http://www.raywenderlich.com/934/core-data-on-ios-5-tutorial-getting-started
CoreData is sometimes described as having a steep learning curve. I disagree. If you're considering writing SQL yourself in your app, you're not going to have any trouble with CoreData.
CoreData 有时被描述为具有陡峭的学习曲线。我不同意。如果您正在考虑在您的应用程序中自己编写 SQL，那么使用 CoreData 不会有任何问题。
The term "sandbox" is an abstract term for the portion of the device's filesystem that your application has read/write access to. The "Documents" directory is a specific directory within your application's sandbox. There are other files in your sandbox than just the documents directory, but most applications that save data to the filesystem in iOS do so in the documents directory.
术语“沙箱”是您的应用程序具有读/写访问权限的设备文件系统部分的抽象术语。“Documents”目录是应用程序沙箱中的特定目录。您的沙箱中还有其他文件，而不仅仅是文档目录，但大多数将数据保存到 iOS 文件系统的应用程序都是在文档目录中进行的。
