I have been releasing some iPhone SDK tutorials over the past week or so and I just released one on using SQLite databases.
It goes over how to create the actual database through to pulling the data out and displaying it. It is a simple example with a database of animals with their name, description and an image linked to them.
The actual app is a simple TableView which when you select a row (animal) it takes you to a view with their description and their picture.
I am also in the process of writing an addition to the tutorial to allow you to add/remove animals from the database.
Just a quick update, I have published the first installment of my new RSS Reader series that will take you step-by-step through the application development process through to the actual publishing of an application.
Hi
thank you so much for the great tutorial but my program doesn't go through the second if ( if(sqlite3_prepare_v2(...)) { } )and I can't connect to my database. any idea?
thanx in advance
Check out StepSqlite PL/SQL compiler for Sqlite on iPhone Metatranz StepSqlite alpha It makes writing database interfaces for Sqlite super easy - you can use variables directly in SQL - no more writing binds by hand, write stored procedures, functions, packages, triggers (with procedural code),cursors, even do date operations easily using built in operators -
Wow – really in-depth tutorial and really useful for learning about SQL on the iPhone.
A few little things I noticed (not related to SQL):
1. The tutorial was obviously made for 2.x firmwares, but setText on cells has since been deprecated, so they should be something like this:
Code:
[cell.textLabel setText:animal.name];
2. There's a typo in the AnimalViewController where "animalDescription" is misspelled as "animalDesciption" (the 'r' is missing).
3. Not sure if this is because of the way I created the view controller, but when I was doing the connections in AnimalViewController.xib, I had to click on the View and make a New Referencing Outlet to the File's Owner's view because otherwise the app crashes saying:
Quote:
*** Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: '-[UIViewController _loadViewFromNibNamed:bundle:] loaded the "AnimalViewController" nib but the view outlet was not set.'
Thanks for your tutorial. It was very informative.
I am an absolute newb and it was very helpful.
I noticed that you tutorial App downloads its images from the internet.
I am desperately trying to make an App that stores its images locally so that it will work offline. I have been buying and reading books and scouring the internet but I cannot seem to find out how to do it. Your tutorial is the closest I have been.
I am trying to build an App that has two levels of Drill-down (I assume two tables to the database) and on the Detail View it shows an Address, a Description, and an image.
e.g. State - navigate to - Laundromat - navigate to DetailView showing that particular Laundromats Address, Description and image.
I have been able to find tutorials on two level drill-downs with Detail Views that I have been able to convert to my idea, using Core Data and xml files, sqlite, and even plist files but none of them tell me how to deal with images.
I have even worked out how to get images into a BLOB in a sqlite database using SQLite Manager, but dont know how to access it in my App.
Could you possibly show me an amended tutorial showing me how to store images locally in the sqlite database and read them into the Detail View.
If you could include how to deal with two related tables to make the two level drill-down as well I would be over the moon.
Thank you for your time in reading this.
I await in hopeful anticipation.
Cheers.
I have a simple set of tables which all store one record (e.g. personal details of the iPhone user). So using Arrays does not really make sense for me, since the relationships are all one-to-one.
I would really appreciate some guidance as there is really zip information I can find on anyone doing this...simply reading data straight from the database to to the view objects. Maybe it can't be done on the iPhone and so I am dealing with old programming habits.
Here is my code...hope it helps:
Code:
//
// IfaAppDelegate.h
// Ifa
//
// Created by Marc Tison on 2010/03/21.
// Copyright Marblesharp (Pty) Ltd 2010. All rights reserved.
//
#import "MyDetailsDatamodel.h"
@class IfaViewController;
@class MyDetailsDatamodel;
@interface IfaAppDelegate : NSObject <UIApplicationDelegate> {
UIWindow *window;
IfaViewController *viewController;
//Creat array to hold My Details data item objects
NSMutableArray *mydetailsArray;
}
@property (nonatomic, retain) IBOutlet UIWindow *window;
@property (nonatomic, retain) IBOutlet IfaViewController *viewController;
@property (nonatomic, retain) NSMutableArray *mydetailsArray;
- (void) copyDatabaseIfNeeded;
- (NSString *) getDBPath;
@end
//
// IfaAppDelegate.m
// Ifa
//
// Created by Marc Tison on 2010/03/21.
// Copyright Marblesharp (Pty) Ltd 2010. All rights reserved.
//
#import "IfaViewController.h"
@implementation IfaAppDelegate
@synthesize window, viewController;
@synthesize mydetailsArray;
- (void)applicationDidFinishLaunching:(UIApplication *)application {
//Copy database to the user's phone if needed.
[self copyDatabaseIfNeeded];
//Initialize the My Details array.
NSMutableArray *tempArray = [[NSMutableArray alloc] init];
self.mydetailsArray = tempArray;
[tempArray release];
//Once the db is copied, get the initial data to display on the screen.
[MyDetailsDatamodel getInitialDataToDisplay:[self getDBPath]];
// Configure and show the window
[window addSubview:viewController.view];
[window makeKeyAndVisible];
}
- (void)applicationWillTerminate:(UIApplication *)application {
// Save data if appropriate
[MyDetailsDatamodel finalizeStatements];
}
//we get the NSFileManager object to make a copy of the database on the phone
- (void) copyDatabaseIfNeeded {
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSString *dbPath = [self getDBPath];
BOOL success = [fileManager fileExistsAtPath:dbPath];
if(!success) {
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"iFAdvisor.sql"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];
if (!success)
NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
}
//Gives us the database location on the user's phone
- (NSString *) getDBPath {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
return [documentsDir stringByAppendingPathComponent:@"iFAdvisor.sql"];
}
//Clear memory
- (void)dealloc {
[mydetailsArray release];
[viewController release];
[window release];
[super dealloc];
}
@end
//
// MyDetails.h
// View
//
// Created by marcti on 2010/03/17.
// Copyright 2010 Marblesharp (Pty) Ltd. All rights reserved.
//
#import <sqlite3.h>
@interface MyDetailsDatamodel : NSObject {
//Declare database fields
NSInteger PKMyDetails;
NSString *Name;
NSString *Surname;
NSString *DateOfBirth;
NSString *Gender;
NSString *IdNumber;
NSString *CellNumber;
NSString *EmailAddress;
//Internal variables to keep track of the state of the object.
BOOL isDirty;
BOOL isDetailViewHydrated;
}
@property (nonatomic, readonly) NSInteger PKMyDetails;
@property (nonatomic, retain) NSString *Name;
@property (nonatomic, retain) NSString *Surname;
@property (nonatomic, retain) NSString *DateOfBirth;
@property (nonatomic, retain) NSString *Gender;
@property (nonatomic, retain) NSString *IdNumber;
@property (nonatomic, retain) NSString *CellNumber;
@property (nonatomic, retain) NSString *EmailAddress;
@property (nonatomic, readwrite) BOOL isDirty;
@property (nonatomic, readwrite) BOOL isDetailViewHydrated;
//Static methods.
+ (void) getInitialDataToDisplay:(NSString *)dbPath;
+ (void) finalizeStatements;
//Instance methods.
- (id) initWithPrimaryKey:(NSInteger)pk;
@end
//
// MyDetails.m
// View
//
// Created by marcti on 2010/03/17.
// Copyright 2010 Marblesharp (Pty) Ltd. All rights reserved.
//
#import "MyDetailsDatamodel.h"
static sqlite3 *database = nil;
@implementation MyDetailsDatamodel
@synthesize PKMyDetails, Name,Surname,DateOfBirth,Gender,IdNumber,CellNumber,EmailAddress;
@synthesize isDirty,isDetailViewHydrated;
+ (void) getInitialDataToDisplay:(NSString *)dbPath {
IfaAppDelegate *appDelegate = (IfaAppDelegate *)[[UIApplication sharedApplication] delegate];
if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
const char *sql = "select PKMyDetails, Name, Surname, IdNumber, Gender,DateOfBirth,CellNumber,EmailAddress from MyDetails";
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
while(sqlite3_step(selectstmt) == SQLITE_ROW) {
NSInteger primaryKey = sqlite3_column_int(selectstmt, 0);
MyDetailsDatamodel *mydetailsObj = [[MyDetailsDatamodel alloc] initWithPrimaryKey:primaryKey];
mydetailsObj.Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)];
mydetailsObj.Surname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 2)];
mydetailsObj.IdNumber = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 3)];
mydetailsObj.Gender = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 4)];
mydetailsObj.DateOfBirth = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 5)];
mydetailsObj.CellNumber = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 6)];
mydetailsObj.EmailAddress = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 7)];
mydetailsObj.isDirty = NO;
[appDelegate.mydetailsArray addObject:mydetailsObj];
[mydetailsObj release];
}
}
}
else
sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
}
+ (void) finalizeStatements {
if(database) sqlite3_close(database);
}
//Initialise the primary key
- (id) initWithPrimaryKey:(NSInteger) pk {
[super init];
PKMyDetails = pk;
isDetailViewHydrated = NO;
return self;
}
- (void)dealloc {
[Name release];
[Surname release];
[IdNumber release];
[Gender release];
[DateOfBirth release];
[CellNumber release];
[EmailAddress release];
[super dealloc];
}
@end
// MyDetailsController.m
// iFA
//
// Created by marcti on 2010/03/10.
// Copyright 2010 Marblesharp 147 (Pty) Ltd. All rights reserved.
//
#import "MyDetailsController.h"
#import "MyDetailsDatamodel.h"
@implementation MyDetailsController
@synthesize myhealthController, theScroller;
@synthesize NameField, SurnameField, IdNumberField, CellNumberField,EmailAddressField;
@synthesize soundFileURLRef, soundFileObject;
// Implement viewDidLoad to do additional setup after loading the view
- (void) viewDidLoad {
[super viewDidLoad];
theScroller.contentSize=CGSizeMake(280, 650);
// Get the main bundle for the app
CFBundleRef mainBundle;
mainBundle = CFBundleGetMainBundle ();
// Get the URL to the sound file to play
soundFileURLRef = CFBundleCopyResourceURL (mainBundle,CFSTR ("tap"),CFSTR ("aif"),NULL);
// Create a system sound object representing the sound file
AudioServicesCreateSystemSoundID (soundFileURLRef,&soundFileObject);
//Using the appDelegate we can access the mydetailsArray
appDelegate = (IfaAppDelegate *)[[UIApplication sharedApplication] delegate];
//return appDelegate.mydetailsArray.count;
if(appDelegate.mydetailsArray != nil){
// NSLog(@"My Details Array:%@",appDelegate.mydetailsArray);
//**** we get here, but myDetailsArray is undeclared?
//NameField.text = mydetailsArray.Name;
//SurnameField.text = mydetailsArray.Surname;
//IdNumberField.text = mydetailsArray.IdNumber;
//CellNumberField.text = mydetailsArray.CellNumber;
//EmailAddressField.text = mydetailsArray.EmailAddress;
}
}
- (void)viewDidUnload {
// Release any retained subviews of the main view.
// e.g. self.myOutlet = nil;
}
- (void)dealloc {
[theScroller release];
[myhealthController release];
// [mydetailsDatamodel release];
[super dealloc];
}
@end
Hi all, I've recently accidently deleted my iPhone calendar and all I could find back was one back-up file. I extracted the calender with the iPhone backup extractor . The callender was a sqlitedb file, which I converted into an excell file. Then I sorted the events by date. So I got pretty far in retrieving my appointments
My question is: How do you read the start and end dates? I've tried finding explanations on it, unsuccesfully and with common sense I alse don't get any wiser.
In this code you have use
+ (void) getInitialDataToDisplayNSString *)dbPath for fetching database.
here sqlite fetching data sequentially in row by row, it take much time when data record's more then (approx 100). So application response is very low.
Can you suggest how to increase application response time?
Thanks
Deepak
Quote:
Originally Posted by ManWithMask
Hi Dean
I am new to iPhone SDK.
I have a simple set of tables which all store one record (e.g. personal details of the iPhone user). So using Arrays does not really make sense for me, since the relationships are all one-to-one.
I would really appreciate some guidance as there is really zip information I can find on anyone doing this...simply reading data straight from the database to to the view objects. Maybe it can't be done on the iPhone and so I am dealing with old programming habits.
Here is my code...hope it helps:
Code:
//
// IfaAppDelegate.h
// Ifa
//
// Created by Marc Tison on 2010/03/21.
// Copyright Marblesharp (Pty) Ltd 2010. All rights reserved.
//
#import "MyDetailsDatamodel.h"
@class IfaViewController;
@class MyDetailsDatamodel;
@interface IfaAppDelegate : NSObject <UIApplicationDelegate> {
UIWindow *window;
IfaViewController *viewController;
//Creat array to hold My Details data item objects
NSMutableArray *mydetailsArray;
}
@property (nonatomic, retain) IBOutlet UIWindow *window;
@property (nonatomic, retain) IBOutlet IfaViewController *viewController;
@property (nonatomic, retain) NSMutableArray *mydetailsArray;
- (void) copyDatabaseIfNeeded;
- (NSString *) getDBPath;
@end
//
// IfaAppDelegate.m
// Ifa
//
// Created by Marc Tison on 2010/03/21.
// Copyright Marblesharp (Pty) Ltd 2010. All rights reserved.
//
#import "IfaViewController.h"
@implementation IfaAppDelegate
@synthesize window, viewController;
@synthesize mydetailsArray;
- (void)applicationDidFinishLaunching:(UIApplication *)application {
//Copy database to the user's phone if needed.
[self copyDatabaseIfNeeded];
//Initialize the My Details array.
NSMutableArray *tempArray = [[NSMutableArray alloc] init];
self.mydetailsArray = tempArray;
[tempArray release];
//Once the db is copied, get the initial data to display on the screen.
[MyDetailsDatamodel getInitialDataToDisplay:[self getDBPath]];
// Configure and show the window
[window addSubview:viewController.view];
[window makeKeyAndVisible];
}
- (void)applicationWillTerminate:(UIApplication *)application {
// Save data if appropriate
[MyDetailsDatamodel finalizeStatements];
}
//we get the NSFileManager object to make a copy of the database on the phone
- (void) copyDatabaseIfNeeded {
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSString *dbPath = [self getDBPath];
BOOL success = [fileManager fileExistsAtPath:dbPath];
if(!success) {
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"iFAdvisor.sql"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];
if (!success)
NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
}
//Gives us the database location on the user's phone
- (NSString *) getDBPath {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
return [documentsDir stringByAppendingPathComponent:@"iFAdvisor.sql"];
}
//Clear memory
- (void)dealloc {
[mydetailsArray release];
[viewController release];
[window release];
[super dealloc];
}
@end
//
// MyDetails.h
// View
//
// Created by marcti on 2010/03/17.
// Copyright 2010 Marblesharp (Pty) Ltd. All rights reserved.
//
#import <sqlite3.h>
@interface MyDetailsDatamodel : NSObject {
//Declare database fields
NSInteger PKMyDetails;
NSString *Name;
NSString *Surname;
NSString *DateOfBirth;
NSString *Gender;
NSString *IdNumber;
NSString *CellNumber;
NSString *EmailAddress;
//Internal variables to keep track of the state of the object.
BOOL isDirty;
BOOL isDetailViewHydrated;
}
@property (nonatomic, readonly) NSInteger PKMyDetails;
@property (nonatomic, retain) NSString *Name;
@property (nonatomic, retain) NSString *Surname;
@property (nonatomic, retain) NSString *DateOfBirth;
@property (nonatomic, retain) NSString *Gender;
@property (nonatomic, retain) NSString *IdNumber;
@property (nonatomic, retain) NSString *CellNumber;
@property (nonatomic, retain) NSString *EmailAddress;
@property (nonatomic, readwrite) BOOL isDirty;
@property (nonatomic, readwrite) BOOL isDetailViewHydrated;
//Static methods.
+ (void) getInitialDataToDisplay:(NSString *)dbPath;
+ (void) finalizeStatements;
//Instance methods.
- (id) initWithPrimaryKey:(NSInteger)pk;
@end
//
// MyDetails.m
// View
//
// Created by marcti on 2010/03/17.
// Copyright 2010 Marblesharp (Pty) Ltd. All rights reserved.
//
#import "MyDetailsDatamodel.h"
static sqlite3 *database = nil;
@implementation MyDetailsDatamodel
@synthesize PKMyDetails, Name,Surname,DateOfBirth,Gender,IdNumber,CellNumber,EmailAddress;
@synthesize isDirty,isDetailViewHydrated;
+ (void) getInitialDataToDisplay:(NSString *)dbPath {
IfaAppDelegate *appDelegate = (IfaAppDelegate *)[[UIApplication sharedApplication] delegate];
if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
const char *sql = "select PKMyDetails, Name, Surname, IdNumber, Gender,DateOfBirth,CellNumber,EmailAddress from MyDetails";
sqlite3_stmt *selectstmt;
if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
while(sqlite3_step(selectstmt) == SQLITE_ROW) {
NSInteger primaryKey = sqlite3_column_int(selectstmt, 0);
MyDetailsDatamodel *mydetailsObj = [[MyDetailsDatamodel alloc] initWithPrimaryKey:primaryKey];
mydetailsObj.Name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)];
mydetailsObj.Surname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 2)];
mydetailsObj.IdNumber = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 3)];
mydetailsObj.Gender = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 4)];
mydetailsObj.DateOfBirth = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 5)];
mydetailsObj.CellNumber = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 6)];
mydetailsObj.EmailAddress = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 7)];
mydetailsObj.isDirty = NO;
[appDelegate.mydetailsArray addObject:mydetailsObj];
[mydetailsObj release];
}
}
}
else
sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
}
+ (void) finalizeStatements {
if(database) sqlite3_close(database);
}
//Initialise the primary key
- (id) initWithPrimaryKey:(NSInteger) pk {
[super init];
PKMyDetails = pk;
isDetailViewHydrated = NO;
return self;
}
- (void)dealloc {
[Name release];
[Surname release];
[IdNumber release];
[Gender release];
[DateOfBirth release];
[CellNumber release];
[EmailAddress release];
[super dealloc];
}
@end
// MyDetailsController.m
// iFA
//
// Created by marcti on 2010/03/10.
// Copyright 2010 Marblesharp 147 (Pty) Ltd. All rights reserved.
//
#import "MyDetailsController.h"
#import "MyDetailsDatamodel.h"
@implementation MyDetailsController
@synthesize myhealthController, theScroller;
@synthesize NameField, SurnameField, IdNumberField, CellNumberField,EmailAddressField;
@synthesize soundFileURLRef, soundFileObject;
// Implement viewDidLoad to do additional setup after loading the view
- (void) viewDidLoad {
[super viewDidLoad];
theScroller.contentSize=CGSizeMake(280, 650);
// Get the main bundle for the app
CFBundleRef mainBundle;
mainBundle = CFBundleGetMainBundle ();
// Get the URL to the sound file to play
soundFileURLRef = CFBundleCopyResourceURL (mainBundle,CFSTR ("tap"),CFSTR ("aif"),NULL);
// Create a system sound object representing the sound file
AudioServicesCreateSystemSoundID (soundFileURLRef,&soundFileObject);
//Using the appDelegate we can access the mydetailsArray
appDelegate = (IfaAppDelegate *)[[UIApplication sharedApplication] delegate];
//return appDelegate.mydetailsArray.count;
if(appDelegate.mydetailsArray != nil){
// NSLog(@"My Details Array:%@",appDelegate.mydetailsArray);
//**** we get here, but myDetailsArray is undeclared?
//NameField.text = mydetailsArray.Name;
//SurnameField.text = mydetailsArray.Surname;
//IdNumberField.text = mydetailsArray.IdNumber;
//CellNumberField.text = mydetailsArray.CellNumber;
//EmailAddressField.text = mydetailsArray.EmailAddress;
}
}
- (void)viewDidUnload {
// Release any retained subviews of the main view.
// e.g. self.myOutlet = nil;
}
- (void)dealloc {
[theScroller release];
[myhealthController release];
// [mydetailsDatamodel release];
[super dealloc];
}
@end
My iPhone SQLite Tutorial
Sorted
NSString *)dbPath for fetching database.
Linear Mode
