StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCreating an SQLite3 database file through Objective-C
text
Body
copied!<p>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. </p> <p>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"?</p> <p>This is what I have currently:</p> <pre><code>-(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; } </code></pre> <p>When I run my app, I get the following errors:</p> <pre><code>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 </code></pre> <p>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!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload