App Showing Failed Bank Data from SQLite DB
This is the second part of a two-part series where we show how to make an app that displays a list of failed US banks from a SQLite database.
In the first part of the series, we talked about what SQLite is and why we’d want to use it, how to use the sqlite3 command-line utility, and how to import data programatically via Python.
This time, we will cover how to make an iPhone app that reads data from the SQLite database we created, and display data in a table view and a drill down detail view.
Reading the SQLite Data
Start by creating a new project in XCode using the Window-based Application template, and name the project FailedBanks.
Then let’s link in the sqlite3 framework. Right click on Frameworks, click “Add\Existing Frameworks…”, and select “libsqlite3.dylib” from the dropdown.
Next, add in the database file that we created last time. Right click on Resources, click “Add\Existing Files…”, browse to where your banklist.sqlite3 file is saved, make sure “Copy items to destination group’s folder (if needed)” is checked, and click Add.
We’re going to have two screens for our app. The first will show a list of banks in a table view. Tapping on a bank in the table view should bring up another view with details on that bank.
Rather than retrieving all of the data from the database, we’re just going to pull out the specific subset we need to display the first table view to save memory. So let’s create a class to store this:
Click on Classes and click “File\New File…”, choose “Cocoa Touch Class” on the side, pick “Objective-C class”, select “Subclass of NSObject”, and click “Next”. Name the class “FailedBankInfo.”
Replace FailedBankInfo.h with the following:
#import <Foundation/Foundation.h> @interface FailedBankInfo : NSObject { int _uniqueId; NSString *_name; NSString *_city; NSString *_state; } @property (nonatomic, assign) int uniqueId; @property (nonatomic, copy) NSString *name; @property (nonatomic, copy) NSString *city; @property (nonatomic, copy) NSString *state; - (id)initWithUniqueId:(int)uniqueId name:(NSString *)name city:(NSString *)city state:(NSString *)state; @end |
And replace FailedBankInfo.m with the following:
#import "FailedBankInfo.h" @implementation FailedBankInfo @synthesize uniqueId = _uniqueId; @synthesize name = _name; @synthesize city = _city; @synthesize state = _state; - (id)initWithUniqueId:(int)uniqueId name:(NSString *)name city:(NSString *)city state:(NSString *)state { if ((self = [super init])) { self.uniqueId = uniqueId; self.name = name; self.city = city; self.state = state; } return self; } - (void) dealloc { self.name = nil; self.city = nil; self.state = nil; [super dealloc]; } @end |
This is pretty standard Objective-C – there should be no surprises here. We’re just creating a class to store the few pieces of data we’ll be displaying in our table view, and make a convenience constructor.
Next we’re going to create a helper class to handle all of the interaction with our sqlite3 database. This is good practice because by keeping everything abstracted, it makes it easier to switch to another storage method if we wanted to in the future.
So make another subclass of NSObject like you did above, but name it FailedBankDatabase.h. Replace FailedBankDatabase.h with the following:
#import <Foundation/Foundation.h> #import <sqlite3.h> @interface FailedBankDatabase : NSObject { sqlite3 *_database; } + (FailedBankDatabase*)database; - (NSArray *)failedBankInfos; @end |
Here we include the header file for sqlite3 at the top, and keep a member variable to store the pointer to our SQLite database. We also declare a static function to return the singleton instance of our FailedBankDatabase object, and declare a method to return an array of all of the FailedBankInfos from our database.
Erase everything in FailedBankDatabase.m and add the following to the top:
#import "FailedBankDatabase.h" #import "FailedBankInfo.h" @implementation FailedBankDatabase static FailedBankDatabase *_database; + (FailedBankDatabase*)database { if (_database == nil) { _database = [[FailedBankDatabase alloc] init]; } return _database; } |
First we import our header files, then we add the standard code to create a singleton instance of FailedBankDatabase for ease of access.
Add the following next:
- (id)init { if ((self = [super init])) { NSString *sqLiteDb = [[NSBundle mainBundle] pathForResource:@"banklist" ofType:@"sqlite3"]; if (sqlite3_open([sqLiteDb UTF8String], &_database) != SQLITE_OK) { NSLog(@"Failed to open database!"); } } return self; } - (void)dealloc { sqlite3_close(_database); [super dealloc]; } |
When we initialize our object, we construct a path to our database file. We’re storing the database in our application’s bundle, so we use the pathForResource method to obtain the path.
Note that since the database is stored in our main bundle, that means we couldn’t write to the database. This is fine for this app, but if you have an app that you need to both read and write to the database, check out my GDataXML tutorial for an example of how to save your bundled data to the documents directory for editing.
Once we have the path to the database, we open it up with the sqlite3_open API call. It will return an error if anything goes wrong – otherwise we’re good to go! Note that when we’re done we should close the database handle with sqlite3_close – I put that in the dealloc method.
Now for the fun part – retrieving the data from the database!
- (NSArray *)failedBankInfos { NSMutableArray *retval = [[[NSMutableArray alloc] init] autorelease]; NSString *query = @"SELECT id, name, city, state FROM failed_banks ORDER BY close_date DESC"; sqlite3_stmt *statement; if (sqlite3_prepare_v2(_database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) { while (sqlite3_step(statement) == SQLITE_ROW) { int uniqueId = sqlite3_column_int(statement, 0); char *nameChars = (char *) sqlite3_column_text(statement, 1); char *cityChars = (char *) sqlite3_column_text(statement, 2); char *stateChars = (char *) sqlite3_column_text(statement, 3); NSString *name = [[NSString alloc] initWithUTF8String:nameChars]; NSString *city = [[NSString alloc] initWithUTF8String:cityChars]; NSString *state = [[NSString alloc] initWithUTF8String:stateChars]; FailedBankInfo *info = [[FailedBankInfo alloc] initWithUniqueId:uniqueId name:name city:city state:state]; [retval addObject:info]; [name release]; [city release]; [state release]; [info release]; } sqlite3_finalize(statement); } return retval; } @end |
Here we construct our SQL string, and execute it with the sqlite3_prepare_v2 API call. We then step through each row, and pull out the return values one by one. We have to do a little conversion here to get the data from UTF8 strings into NSStrings, then we construct FailedBankInfo objects based on the data and add it to our array.
We have to call sqlite3_finalize to clean up the memory used for the statement, then we return the data.
So let’s see if this works. Open up FailedBanksAppDelegate.m and add the following imports to the top of the file:
#import "FailedBankDatabase.h" #import "FailedBankInfo.h" |
Then add the following inside applicationDidFinishLaunching:
NSArray *failedBankInfos = [FailedBankDatabase database].failedBankInfos; for (FailedBankInfo *info in failedBankInfos) { NSLog(@"%d: %@, %@, %@", info.uniqueId, info.name, info.city, info.state); } |
If all goes well, you should see lines like the following in your deubug log:
1: Desert Hills Bank, Phoenix, AZ 2: Unity National Bank, Cartersville, GA 3: Key West Bank, Key West, FL
Creating a Table View
Now that we have the code to read in our data set, it’s pretty easy to create a table view to display the data.
Right click on Classes and click “Add\New File…” and pick “UIViewController subclass”, making sure “UITableVIewController subclass” is checked and “With XIB for user interface” is NOT checked. Name the class FailedBanksListViewController.
Open up FailedBanksListViewController.h and add a member variable/property for the failedBankInfos which we’ll retrieve from the database. When you’re done it should look like the following:
#import <UIKit/UIKit.h> @interface FailedBanksListViewController : UITableViewController { NSArray *_failedBankInfos; } @property (nonatomic, retain) NSArray *failedBankInfos; @end |
Switch over to FailedBanksListViewController.m and add some imports, your synthesize statement, and your cleanup code:
// At very top, in import section #import "FailedBankDatabase.h" #import "FailedBankInfo.h" // At top, under @implementation @synthesize failedBankInfos = _failedBankInfos; // In dealloc self.failedBankInfos = nil; |
Then uncomment viewDidLoad and modify it to look like the following:
- (void)viewDidLoad { [super viewDidLoad]; self.failedBankInfos = [FailedBankDatabase database].failedBankInfos; self.title = @"Failed Banks"; } |
Make a slight tweak to numberOfRowsInSection to return the number of items in the array:
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section { return [_failedBankInfos count]; } |
Finally modify cellForRowAtIndexPath to look like the following:
- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath { static NSString *CellIdentifier = @"Cell"; UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier]; if (cell == nil) { cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:CellIdentifier] autorelease]; } // Set up the cell... FailedBankInfo *info = [_failedBankInfos objectAtIndex:indexPath.row]; cell.textLabel.text = info.name; cell.detailTextLabel.text = [NSString stringWithFormat:@"%@, %@", info.city, info.state]; return cell; } |
All we did here was set the style of the table view cell to be the subtitle style, get the object in the array corresponding to the current row, and set the title and subtitle on the cell appropriately.
Now, we just need to hook our table view into the app. What we want to do to add a navigation controller to our app, and have the root view controller be our FailedBanksListViewController. So first, let’s add an outlet into FailedBanksAppDelegate.h for the UINavigationController we’re about to add:
#import <UIKit/UIKit.h> @interface FailedBanksAppDelegate : NSObject <UIApplicationDelegate> { UIWindow *window; UINavigationController *_navController; } @property (nonatomic, retain) IBOutlet UIWindow *window; @property (nonatomic, retain) IBOutlet UINavigationController *navController; @end |
Open up Resources and double click on MainWindow.xib. Drag a Navigation Controller from the library into the MainWindow.xib. Click on the down arrow on the Navigation Controller that you just added, click on the View Controller, over in the attribute panel switch to the fourth tab, and switch the “Class” to “FailedBanksListViewController.”
Finally, control-drag from “Failed Banks App Delegate” in MainWindow.xib to “Navigation Controller”, and connect it to the “navController” outlet. Save the xib and close.
Now all we need to do is add a few lines to our FailedBanksAppDelegate.m:
// Under @implementation @synthesize navController = _navController; // In applicationDisFinishLaunching, before makeKeyAndVisible: [window addSubview:_navController.view]; // In dealloc self.navController = nil; |
If all goes well, you should be able to compile your app and see the following:
Adding a Detail View
Now, let’s extend the app so that when you tap a particular row, it loads up the details for that row in a second view controller.
First, we’re going to need another model class to store ALL of the information for a failed bank row, rather than our info class which just held a subset. Create a new subclass of NSObject named FailedBankDetails. Replace FailedBankDetails.h with the following:
#import <Foundation/Foundation.h> @interface FailedBankDetails : NSObject { int _uniqueId; NSString *_name; NSString *_city; NSString *_state; int _zip; NSDate *_closeDate; NSDate *_updatedDate; } @property (nonatomic, assign) int uniqueId; @property (nonatomic, copy) NSString *name; @property (nonatomic, copy) NSString *city; @property (nonatomic, copy) NSString *state; @property (nonatomic, assign) int zip; @property (nonatomic, retain) NSDate *closeDate; @property (nonatomic, retain) NSDate *updatedDate; - (id)initWithUniqueId:(int)uniqueId name:(NSString *)name city:(NSString *)city state:(NSString *)state zip:(int)zip closeDate:(NSDate *)closeDate updatedDate:(NSDate *)updatedDate; @end |
And replace FailedBankDetails.m with the following:
#import "FailedBankDetails.h" @implementation FailedBankDetails @synthesize uniqueId = _uniqueId; @synthesize name = _name; @synthesize city = _city; @synthesize state = _state; @synthesize zip = _zip; @synthesize closeDate = _closeDate; @synthesize updatedDate = _updatedDate; - (id)initWithUniqueId:(int)uniqueId name:(NSString *)name city:(NSString *)city state:(NSString *)state zip:(int)zip closeDate:(NSDate *)closeDate updatedDate:(NSDate *)updatedDate { if ((self = [super init])) { self.uniqueId = uniqueId; self.name = name; self.city = city; self.state = state; self.zip = zip; self.closeDate = closeDate; self.updatedDate = updatedDate; } return self; } - (void) dealloc { self.name = nil; self.city = nil; self.state = nil; self.closeDate = nil; self.updatedDate = nil; [super dealloc]; } @end |
Again, nothing particularly interesting here – just an object to hold our information.
Next, let’s add a new function inside our FailedBankDatabase to retrieve the FailedBankDetails for a particular uniqueId. Add the following to FailedBankDatabase.h:
// Before the @interface delc @class FailedBankDetails; // After the failedBankInfos decl - (FailedBankDetails *)failedBankDetails:(int)uniqueId; |
Then add the following code into FailedBankDatabase.m:
// In the #import section #import "FailedBankDetails.h" // Anywhere inside the @implementation - (FailedBankDetails *)failedBankDetails:(int)uniqueId { FailedBankDetails *retval = nil; NSString *query = [NSString stringWithFormat:@"SELECT id, name, city, state, zip, close_date, updated_date FROM failed_banks WHERE id=%d", uniqueId]; sqlite3_stmt *statement; if (sqlite3_prepare_v2(_database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) { while (sqlite3_step(statement) == SQLITE_ROW) { int uniqueId = sqlite3_column_int(statement, 0); char *nameChars = (char *) sqlite3_column_text(statement, 1); char *cityChars = (char *) sqlite3_column_text(statement, 2); char *stateChars = (char *) sqlite3_column_text(statement, 3); int zip = sqlite3_column_int(statement, 4); char *closeDateChars = (char *) sqlite3_column_text(statement, 5); char *updatedDateChars = (char *) sqlite3_column_text(statement, 6); NSString *name = [[NSString alloc] initWithUTF8String:nameChars]; NSString *city = [[NSString alloc] initWithUTF8String:cityChars]; NSString *state = [[NSString alloc] initWithUTF8String:stateChars]; NSString *closeDateString = [[NSString alloc] initWithUTF8String:closeDateChars]; NSString *updatedDateString = [[NSString alloc] initWithUTF8String:updatedDateChars]; NSDateFormatter *formatter = [[NSDateFormatter alloc] init]; [formatter setDateFormat:@"yyyy-MM-dd hh:mm:ss"]; NSDate *closeDate = [formatter dateFromString:closeDateString]; NSDate *updateDate = [formatter dateFromString:updatedDateString]; retval = [[[FailedBankDetails alloc] initWithUniqueId:uniqueId name:name city:city state:state zip:zip closeDate:closeDate updatedDate:updateDate] autorelease]; [name release]; [city release]; [state release]; [closeDateString release]; [updatedDateString release]; [formatter release]; break; } sqlite3_finalize(statement); } return retval; } |
This is very similar to failedBankInfos, except our SQL statement is modified to just get one particular ID, and we pull out all of the fields. Notice we stil specify the fields to pull rather than doing a SELECT *, so that if we ever update our database and add another column, it won’t break our code.
Also note that we use NSDateFormatter to convert from our date string values to NSDates. See the date format patterns reference for more info here.
Once that is done, right click on Classes and click “Add\New File…” and pick “UIViewController subclass”, making sure “UITableVIewController subclass” is NOT checked and “With XIB for user interface” IS checked. Name the class FailedBanksDetailViewController.
Then modify FailedBanksDetailViewController.h to look like the following:
#import <UIKit/UIKit.h> @interface FailedBanksDetailViewController : UIViewController { UILabel *_nameLabel; UILabel *_cityLabel; UILabel *_stateLabel; UILabel *_zipLabel; UILabel *_closedLabel; UILabel *_updatedLabel; int _uniqueId; } @property (nonatomic, retain) IBOutlet UILabel *nameLabel; @property (nonatomic, retain) IBOutlet UILabel *cityLabel; @property (nonatomic, retain) IBOutlet UILabel *stateLabel; @property (nonatomic, retain) IBOutlet UILabel *zipLabel; @property (nonatomic, retain) IBOutlet UILabel *closedLabel; @property (nonatomic, retain) IBOutlet UILabel *updatedLabel; @property (nonatomic, assign) int uniqueId; @end |
Then open up FailedBanksDetailViewController and drag a bunch of labels out so they look like the following. By the way, I find it helpful to turn on “TopBar\Navigation Bar” in the Attributes Inspector for the view to see better the actual screen space available.
Then control-drag from File’s Owner to each of the labels on the right, connecting the nameLabel, cityLabel, etc. to the appropriate positions. When you’re done, save and close the XIB.
Then open up FailedBanksDetailViewController.m and finish up your properties:
// In the #import section #import "FailedBankDatabase.h" #import "FailedBankDetails.h" // In the @implementation section @synthesize nameLabel = _nameLabel; @synthesize cityLabel = _cityLabel; @synthesize stateLabel = _stateLabel; @synthesize zipLabel = _zipLabel; @synthesize closedLabel = _closedLabel; @synthesize updatedLabel = _updatedLabel; @synthesize uniqueId = _uniqueId; // In the dealloc section AND the viewDidUnload section self.nameLabel = nil; self.cityLabel = nil; self.stateLabel = nil; self.zipLabel = nil; self.closedLabel = nil; self.updatedLabel = nil; |
Next, add a viewWillAppear method to look up the entry in the database and set the labels appropriately:
- (void)viewWillAppear:(BOOL)animated { FailedBankDetails *details = [[FailedBankDatabase database] failedBankDetails:_uniqueId]; if (details != nil) { [_nameLabel setText:details.name]; [_cityLabel setText:details.city]; [_stateLabel setText:details.state]; [_zipLabel setText:[NSString stringWithFormat:@"%d", details.name]]; NSDateFormatter *formatter = [[NSDateFormatter alloc] init]; [formatter setDateFormat:@"MMMM dd, yyyy"]; [_closedLabel setText:[formatter stringFromDate:details.closeDate]]; [_updatedLabel setText:[formatter stringFromDate:details.updatedDate]]; } } |
The only thing left is to add in the code inside the table view controller to push this view controller onto the stack when a row gets selected.
Modify FailedBanksListViewController.h like the following:
// Before the @interface @class FailedBanksDetailViewController; // Inside the @interface FailedBanksDetailViewController *_details; // After the @interface @property (nonatomic, retain) FailedBanksDetailViewController *details; |
Then modify FailedBanksListViewController.m like the following:
// In the import section // After the @implementation @synthesize details = _details; // In viewDidUnload AND dealloc self.details = nil; |
And finally, modify the didSelectRowAtIndexPath like the following:
- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath { if (self.details == nil) { self.details = [[[FailedBanksDetailViewController alloc] initWithNibName:@"FailedBanksDetailViewController" bundle:nil] autorelease]; } FailedBankInfo *info = [_failedBankInfos objectAtIndex:indexPath.row]; _details.uniqueId = info.uniqueId; [self.navigationController pushViewController:_details animated:YES]; } |
If all goes well, you should see the following:
Gimme the Code!
Here’s a a project file with all of the code we’ve developed in this tutorial so far.
What’s Next?
With this as a foundation, you should feel quite comfortable using SQLite in your projects. I may write some articles about Core Data and its advantages over SQLite in the future.
in the meantime, I’d like to hear from you! How have you used SQLite in your projects, if at all? Which do you prefer between SQLite and Core Data, and why?
Category: iPhone
Loading ...
I’m interested in using SQLite for an iPhone project, but am concerned about what happens when I upgrade the app. It looks like I might need to have separate databases for the program and for any user data.
Ray, have you tried FMDB, an objective-c wrapper for SQLite ? it seems to be a good alternative to work directly with SQLite. The developer of NetNewsWire explains he use it in its post about its switch away from Core Data :
http://inessential.com/2010/02/26/on_switching_away_from_core_data
For my projects, I use Core Data. The only thing that I miss in Core Data is its lack of native ordered relationships (you retrieve NSSet and not NSArray for to-many relationship).
@Steve – Yeah, having separate databases for static data that ships with the app and user data can make things a lot simpler. That way you can always update the static data table format without having to worry about updating any existing database on the device.
@Eric – I hadn’t heard of FMDB – I just took a quick glance, it looks pretty useful, thanks! I like that blog post too, a great read!
Hello…Thanks for the tutorial!
I followed your steps but I don’t seem to be pulling anything from the database even though I have data in there. Any idea what could be happening?
Thanks in advance
@Ubong: Have you tried downloading the sample project and comparing its code to your own?
brilliant; straight forward and very helpful. thank you!
Thanks for this material, I’ve been search many samples coding and try to get the result exactly this one produce. I will try with multiple tables with search functions and show into different pages with SQLite & iPhone X-Code.
Do you have such info? Please, let me know
Best Regards
Joe
@Joe: Thanks! Right now I just have three tutorials on this site related to SQLite:
http://www.raywenderlich.com/tag/sqlite
Hi Ray,
I used your code but changed things around for a database that I’m working with. However, I’m getting the following errors with the statement @”SELECT workout_id, workout_name, workout_desc FROM workout ORDER BY workout_name DESC”.
Any ideas:
- (NSArray *)SpinWorkoutInfos {
NSMutableArray *retval = [[[NSMutableArray alloc] init] autorelease];
NSString *query = @”SELECT workout_id, workout_name, workout_desc FROM workout
ORDER BY workout_name DESC”;
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(_database, [query UTF8String], -1, &statement, nil)
== SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
int uniqueWorkout_id = sqlite3_column_int(statement, 0);
char *workout_nameChars = (char *) sqlite3_column_text(statement, 1);
char *workout_descChars = (char *) sqlite3_column_text(statement, 2);
NSString *workout_name = [[NSString alloc] initWithUTF8String:workout_nameChars];
NSString *workout_desc = [[NSString alloc] initWithUTF8String:workout_descChars];
SpinWorkoutInfo *info = [[SpinWorkoutInfo alloc]
initWithUniqueWorkout_id:uniqueWorkout_id workout_name:workout_name workout_desc:workout_desc];
[retval addObject:info];
[workout_name release];
[workout_desc release];
[info release];
}
sqlite3_finalize(statement);
}
return retval;
}
@end
@Dave: What errors are you getting?
Ray,
Not only has this tutorial helped me establish more of an understanding of using SQLite on the iPhone, but it is also the best tutorial out there of its kind IMO.
I can now do something useful, along the lines of the SQL I learnt during PHP development; with the iPhone. Superb.
So, many thanks. Keep up the excellent work!
I might encounter something that requires me to ask a question later but I’ll try and figure out anything untowards.
Regards,
Mitsi
Lets say you have grouped the banks by state (or any other field) and display the banks in a grouped listing; now let’s say the user edits the list and moves a bank from state A to state B. Would this change be reflected in the SQL database, and if not; how easy is it to do this.
Another example could be a list of sales agents in different states, and you could move a sales agent from state A to state B; this movement should update the DB.
Thanks
ray
I want to save data in applicationWillTerminate,and it is ok in SDK3.2,but not working in SDK4,please help me? what should I do?
Hi Ad,
Whatever flag is being used to differentiate the banks for your grouping, would need to be updated. So, for example if you were to use the moveRowAtIndexPath method, you would probably have to add an update query method – updating is something which I have been struggling with and I know SQL inside out :)
@Mitsi: Thanks so much for the kind words! Thanks for helping out @AD too – your response is exactly what I was about to type!
@steven: On iOS4+ with the advent of multitasking, now applicationWillTerminate will not always be called for your app. In fact, the more frequent thing that will happen is applicationWillEnterBackground will be called (where you should do any final cleanup/saving of state you need to do), and then later the phone will get low on memory, so it will kill your app without giving you any further notice.
So TLDR version is: on iOS4 apps make sure to save your state in applicationWillEnterBackground.
Ray,
This is a fantastic tut! Thanks so much.
The singleton was exactly what I needed for my App. The books all skip over that part when they’re doing the sqlite3 section. Putting this together with the chapter on Data Persistence in the Appress Beginning iPhone Dev book has almost got me there.
This comment was my final procrastination in actually instantiating my database class and seeing if it would create the db and the tables…
One question, though…in the header of your FailedBankDatabase.h, you declare:
`
sqlite3 *_database;
`
But in the implementation (FailedBankDatabase.m) it is:
`
static FailedBankDatabase *_database;
`
I would have thought they would need to be the same Type, but then the sqlite3 isn’t declared anywhere else. Could you please explain what is going on here?
Thanks a ton,
Byron
I have a question. I’m wanting to use SQLite as a game saving/loading facility.
Let’s say we have an app where there are 2 options, 1 = Start, and 2 = Continue.
When the user presses ‘Start’ it will copy the database that came with the phone and create a wholly new database from which the user will add/edit/delete from. The original database *never* gets touched and should be treated as read only.
When the user presses ‘Continue’, it will find the cloned database and use this.
Naturally, the app should only ever have 2 databases at a maximum, so it should be dropping the cloned database on application start up.
I’m wondering what the best process for cloning and dropping existing databases?
Thanks for your help
@Byron: Nice catch! The type should have been FailedBankDatabase, you are correct. I have updated the tutorial and sample code, thanks!
@Amarjit: Keep in mind that an sqlite3 database is just a file, so you can keep the original (pristine) copy of the game database in memory and save out the user-modified version to disk. Check out this tutorial where we do something similar using GDataXML:
http://www.raywenderlich.com/725/how-to-read-and-write-xml-documents-with-gdataxml
Thanks for your help. So you recommend simply copying the file using NSFileManager upon Application start, once the original pristine database is loaded onto the phone?
@Amarjit: Yeah, or just copy it to disk from the bundle, overwriting the existing copy, whenever they select “new game”.
Thanks for your help, I will experiment with this concept as soon as I can.
Ray,
Thanks so much for the tutorial. I was wondering if you could help me with an addition to this tutorial? How could I take this tutorial and display the table to look like the Iphone address book? In other words, I would like the results to be in sections with headers that have A-Z and the results would be grouped within those sections in order by First Letter. On the right side of the table would be A-Z and when you clicked on a letter it would jump to that section. Thanks
@JB: You’ll want to break down your info into sections for each letter, and implement sectionIndexTitlesForTableView to return a list of indexes to show up on the right, that match up to the sections you provide. Chapter 8 of Beginning iPhone Development by Dave Mark and Jeff LaMarche covers this quite nicely.
Iam the beginner of using xcode in iphone. How to store the iphone database to a web server inorder to store bulk of datas and to retrive it easily…
Hi Jeni,
That sounds like you need to use a web service such as XML or JSON.
The server would have a script that deals with requests from the iPhone and then a class could parse this information for use in table views etc..
Cheers :)
@Mitsi: Thanks for helping out @jeni!
I want the program for sms process in xcode(3.2)and iphone simulator(4.0). I tried , but its not supporting MFMessageComposeViewController .It displaying link error.
Thanks in advance…
hi ,
iam trying to download ur source code but its says code does not exist why it is so.
Is there a way to stagger the output from an SQLite database and thus reduce the load from Objective C?
For example, if you have 100,000+ failed banks it would be silly to put all of that into an array.
What is the best way to stagger this data to say 50 at a time? Would you recommend using JSON, or something else?
> Next, add in the database file that we created last
> time. Right click on Resources, click “Add\Existing
> Files…”, browse to where your banklist.sqlite3 file
> is saved,
I created my sqlite database/tables with c-code inside my app in xCode.
Where will my database/tables be?
After I copy them into my resources folder… how do I tell my code “don’t look where they USED TO BE”… “now look in this new location” (resources folder).
@jeni: That sounds like it doesn’t have anything to do with SQLite, but that you may have forgotten to link the MessageUI framework to your project.
@sam: I just tried downloading the sample project and it works OK for me, are you still having troubles?
@Amarjit: Similar to your comment with NSFetchedResultsController, if you’re using raw SQLite (and not Core Data) you can accomplish a similar effect by handling the batch loading yourself, via the SQL LIMIT and OFFSET clauses.
@Alice: In the next part of the series we discuss that a bit:
http://www.raywenderlich.com/980/core-data-tutorial-how-to-preloadimport-existing-data
Ray, I’ve never seen how to use SQL LIMIT/OFFSET for a tableView; some people have mentioned it but one caveat is that I’ve heard that it can sometimes slow the scrolling speed or at least affect it.
In short I am unsure if using SQL Limit’s is the right way to do it; my app is going to be very complex in build and I think Core Data/object modeling might help me build it better.
I’m looking at both your tutorial and CoreBooks from Apple’s developer site to see if I can compare and contrast the different ways to help manage big record sets.
Ray,
How could I implement a search bar into this tutorial?
@Amarjit: It’s true that it can slow the scrolling speed if you load data in batches, since there will have to be an occasional DB lookup as you scroll to new rows. The tradeoff is memory cost (of preloading all rows) vs. scroll speed.
@JB: That is a broad question – what part are you having trouble with? However I had to do this recently on one of my projects, and the general idea is you add a search bar (and/or Search Display Controller) to your view controller, implement the UISearchDisplayController methods, run a new SQL query based on the text search, and reload the table view.
I am having trouble with the didSelectRowAtIndexPath to get the correct data for a detailed view. I get the search bar working correctly but I am not fetching the correct objectAtIndex.indexPath.row to push to the next view. I guess I need to do another sql query based on the selection. Thanks for your help.
Ray this tutorial for db and sqlite3 is excellent, but when I tried to compile I have 2 errors:
Error: Request for member ‘FailedBankInfos’ in something not a structure or union.
This errors are in classes:
FailedBanksAppDelegate.m
FailedBanksListViewController.m
I followed all steps but don’t know where is the error.
Hope you can help me..
Thanks again. Regards
Hi Luismed, have you got the relevant ‘.h’ header files included in your ‘.m’ files?
by included I actually mean imported:
#import “nameOfFile.h”
Hi Mitsi,
I found the error:
The FailedBankDatabase.h has the following declaration in web page code:
- (NSArray *)failedBankNames;
and if you check the example code, the declaration must be:
- (NSArray *)failedBankInfos;
The error is resolved. and now is working fine so far.
Thanks for your help.
Regards.
Hello, The other issue was when I tried to connect in MainWindow.xib the “Navigation Controller” to the “navController” outlet, it was not exist.
I had to create the outlet manually in library classes. Is this normal?, or is there a section to say xcode to create outlet automatically with code?.
regards.
I referred your this tutorial …..can u send me a simple code to connect to database..not 2 move from table view to other view ..but just to display data of database in my main view…i just want to check whether my code is retrieving data from database
Thank You,
Vidhya Jain
@Mitsi: Thanks for helping out Luismed!
@Luismed: We add the outlet for navController in the tutorial in the part starting with “So first, let’s add an outlet into FailedBanksAppDelegate.h…”. Is that what you did or did you do something different?
@Vidhya: I don’t have time to write sample code upon request – please try the official apple developer forums for questions such as this.
@Ray,
Yes I did it, I added the outlet in FailedBanksAppDelegate.h code, but when I executed Interface builder the outlet did not exist, I had to create it in library classes. Is this is correct?
Thanks again.
@Luismed: What do you mean you “had to create it in library classes”…?
Thanks for all your great tuts Ray!
When I compile I get a few errors of this kind: warning: passing argument 2 of ‘sqlite3_open’ from incompatible pointer type
on a line simple as sqlite3_close(_database);
any idea of what could be the issue?
@amok: For sqlite3_open, does your call look something like this:
Note the ampersand before _database.
yes, I actually did copy and paste from your post and your project, attached to the post, has the same issue. Can’t figure out what’s the issue!
@amok
In project FailedBanks, go to SDK, in Frameworks folder, remove libsqlite3.0.dylib and then:
–> Add –> Existing Frameworks –> libsqlite3.dylib
Save, Compile the project and try again.
Regards
Luismed.
@Luismed: Thanks for helping out amok!
Hi Ray, per a previous comment, yeah you need to fix this:
The FailedBankDatabase.h has the following declaration in web page code:
- (NSArray *)failedBankNames;
and if you check the example code, the declaration must be:
- (NSArray *)failedBankInfos;
Great info! This is pretty much what I needed to complete what I didn’t know. However I have been looking for two days now on something. Maybe you can help.
With UITableView how would you poputlate rows in section with data from sqlite if you already populated the number of sections in the table with sqlite?
I was thinking there would have to be a switch of some sort with case (variable) but I have no idea where to start on this one. Is this even possible?
@Julian: Whoops! Thanks guys for pointing that out, fixed!
@Nick: If your data is small enough, the easiest thing to do would just load all of your data from the DB, and store it in a nice data structure (sectionObject: name and array of rowObjects, rowObject: whatever your row data is), that way it is easy to query from the table view code.
Hi Ray,
Its awesome and verymuch helpful to me,but I need the same app from the second page Ie.,I need a button on first view[which is a plain view with only one button] and then from the next page this app must start.Am not getting this from a month.Can u help me.
smrafiqsmd@gmail.com
@rafiq: Dude, kam’on it’s a blog and it’s free – don’t you think it’s too much to ask for personal support on a specific issue that affects only you? – Try to be practical…
Hi Ray – Great Tutorial!! I am a complete newbie and am struggling with something… I have a view based app and I want to incorporate an SQLite search (Primary Key) column 1 and display the result (Column 2) in a new window I have been trying for weeks and this gets me sooooo closed i can taste it….. Is there any guidance you can give me in making this work – I was supposed to have it done by Dec 1st DOH!
@Rob – this series of tutorials is great to get your head around some more SQLite stuff. http://www.iphonesdkarticles.com/2008/10/sqlite-tutorial-selecting-data.html
I’m having the same issue as amok did earlier – all sqlite3 operations, such as, sqlite3_open give a compile warning regarding incompatible pointer type.
I’ve made sure that I included the correct framework – libsqlite3.dylib.
Also, I downloaded the project code at the end of this tutorial and I get the same warnings.
Any ideas? Thanks
same thing here – I recreated everything from scratch and used the proper framework and I am still getting those warnings.
Update concerning Xcode warning of sqlite3 and incompatible pointers type: since these were just warnings I decided to continue on with the tutorial (figured if it didn’t work then maybe I’d at least still learn something – Ray’s tutorials are always good). After adding the SELECT statement I was able to successfully obtain data from the database. So, the warnings appear to be just that, warnings, and the tutorial seems to still work as described. If anybody knows of the cause to these warnings please post – I’ll continue to investigate and do likewise. Thanks.
@rafiq: You have emailed me about this as well, and I have responded.
@Nick: Thanks for helping out Rob!
@Jon/@amok: Whoops! Thanks guys, looks like I had a typo in the project. The instance variable for FailedBankDatabase used to be:
However it should have been:
I have updated the tutorial and sample code with this and now it compiles with no warnings, thanks for pointing this out!
Nice tutorial! Just a few remarks:
* instead of [[NSString alloc] initWithUTF8String:whatever] and then calling release on it, you could immediately call autorelease
* …or even better, use stringWithUTF8String :-))
And a question:
* why define the NSString-based properties as ‘copy’ instead of ‘retain’?
This is a very, very lovely tutorial, and thanks for posting it!
Thaks for your greate tutorial… I have a question, how can I order the bank`s name and display them by alphabet with an Index….
(sorry my english)