using sqlite database in iphone is having the following steps
1. first we need to create the database using commandline or firefox addon
2. now copy the database to the xcode project(drag and drop to project files)
3. write a funtion to get the database path.
-(NSString *)getDataBasePath
{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory , NSUserDomainMask, YES);
NSString *dbPath = [paths objectAtIndex:0];
return [dbPath stringByAppendingPathComponent:@”databaseName.sqlite”];
}
here i used NSLibraryDirectory, read the post so that we can decide where the database will be stored and then decided which is suitable for you.
-(NSString *)getDataBasePath
{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory , NSUserDomainMask, YES);
NSString *dbPath = [paths objectAtIndex:0];
return [dbPath stringByAppendingPathComponent:@”databaseName.sqlite”];
}
here i used NSLibraryDirectory, read the post so that we can decide where the database will be stored and then decided which is suitable for you.
4. now check weather database is available at the above path if not copy the data base from the resources
-(void)copyDatabaseIfNeeded
{
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSString *dbPath = [self getDataBasePath];
NSString *resPath;
BOOL success = [fileManager fileExistsAtPath:dbPath];
if(!success)
{
resPath = [[NSBundle mainBundle] pathForResource:@”databaseName” ofType:@”sqlite”];
success = [fileManager copyItemAtPath:resPath toPath:dbPath error:&error];
if(!success)
{
NSLog(@”Failed to copy ‘%@’.”, [error localizedDescription]);
}
else
{
NSLog(@”successfully copied”);
}
}
}
-(void)copyDatabaseIfNeeded
{
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSString *dbPath = [self getDataBasePath];
NSString *resPath;
BOOL success = [fileManager fileExistsAtPath:dbPath];
if(!success)
{
resPath = [[NSBundle mainBundle] pathForResource:@”databaseName” ofType:@”sqlite”];
success = [fileManager copyItemAtPath:resPath toPath:dbPath error:&error];
if(!success)
{
NSLog(@”Failed to copy ‘%@’.”, [error localizedDescription]);
}
else
{
NSLog(@”successfully copied”);
}
}
}
5. now open the databasesqlite3 *dbConnect;
NSString *dbPath=[self getDataBasePath];
if (sqlite3_open([dbPath UTF8String], &dbConnect) == SQLITE_OK)
{
NSLog(@”opened the data base”);
}
else {
NSLog(@”failed to open the database”);
}
sqlite3_open will take two arguments. first argument is database path and is of type const char *, second one is database connection, it is of type splite3.
NSString *dbPath=[self getDataBasePath];
if (sqlite3_open([dbPath UTF8String], &dbConnect) == SQLITE_OK)
{
NSLog(@”opened the data base”);
}
else {
NSLog(@”failed to open the database”);
}
sqlite3_open will take two arguments. first argument is database path and is of type const char *, second one is database connection, it is of type splite3.
6. now we need to prepare the statement as follows
NSString *query = @”insert into emp values(123,123)”;
const char *pre_query = [query UTF8String];
sqlite3_stmt statement=nil;
if (sqlite3_prepare_v2(dbConnect, pre_query, -1, &statement, NULL) == SQLITE_OK)
{
NSLog(@”prepared”);
}
else
{
NSLog(@”error in preparing”);
}
NSString *query = @”insert into emp values(123,123)”;
const char *pre_query = [query UTF8String];
sqlite3_stmt statement=nil;
if (sqlite3_prepare_v2(dbConnect, pre_query, -1, &statement, NULL) == SQLITE_OK)
{
NSLog(@”prepared”);
}
else
{
NSLog(@”error in preparing”);
}
sqlite3_prepare_v2 will take 5 arguments, first one is database connection, second one is prepared query of type const char *, fouth one is the pointer to the prepared statement result will be placed in this argument,
7. above preparation statement is static, most of the times we need to prepare the statement dynamically at that time we need to create the char pointer as
const char *stmt=”insert into xyz values(?,?)”;
so that we can bind the arguments to ‘?’ dynamically. to bind the value to ‘?’ use the following syntax.
const char *stmt=”insert into xyz values(?,?)”;
so that we can bind the arguments to ‘?’ dynamically. to bind the value to ‘?’ use the following syntax.
sqlite3_bind_text(<sqlite statement>,<position>,<value>,-1,SQLITE_TRANSIENT);
we maight face some problems when binding the arguments, at that time i preferred to use the statement as follows
NSString *str=[NSString stringWithFormat:@”insert into xyz values(%@,%@)”,val1, val2];
const char *stmt=[str utf8String];
8. now execute the statement which was prepared in the above steps
sqlite3_step(<statement>);
depends on the statement it will return the relusts if the statement returns rows than sqlite3_step will return SQLITE_ROW or if it is insert or update or delete statement it will return SQLITE_DONE, if it fails SQLITE_ERROR, there are other return values too,
to get the colomn values
sqlite3_column_text(<sqlite statement>,<colomn number>);//for text
depends on the statement it will return the relusts if the statement returns rows than sqlite3_step will return SQLITE_ROW or if it is insert or update or delete statement it will return SQLITE_DONE, if it fails SQLITE_ERROR, there are other return values too,
to get the colomn values
sqlite3_column_text(<sqlite statement>,<colomn number>);//for text
9. reset the statement to nil
sqlite3_finalize(<compiled statement>);
10. finally close the database connection sqlite3_colose(<database name>);
iPhone Developer 