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.
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!
To create a new database in SQLite you need to specify databases when opening SQLite, or open an existing file using . open . You can use the . database command to see a list of existing databases.
What is a SQLite File? A file with . sqlite extension is a lightweight SQL database file created with the SQLite software. It is a database in a file itself and implements a self-contained, full-featured, highly-reliable SQL database engine.
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.
// 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
- (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;
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 and BWDB are good and easy to use sqlite wrappers for objective c. I suggest you use one of those .
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.
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: i uploaded BWDB final project ( you have your wrapper there and project to see how it works)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With