the SQLiteBooks sample code is missing
I find this link everywhere for SQLite sample code (http://developer.apple.com/library/ios/#samplecode/SQLiteBooks/index.html) but either it has been removed or changed to another location.. I couldn't find it in google searches.. Does anyone know any other link to the code o开发者_C百科r any other good sample code for SQLite?
May be this is useful to you.
http://www.switchonthecode.com/tutorials/using-sqlite-on-the-iphone
http://dblog.com.au/iphone-development-tutorials/iphone-sdk-tutorial-reading-data-from-a-sqlite-database/
http://www.icodeblog.com/2008/08/19/iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1/
You can use this class and send query in this class and get all functionality of sqlite using this class
.h
#import <Foundation/Foundation.h>
#import "sqlite3.h"
@interface DBLib : NSObject {
sqlite3 *database;
NSString *path;
}
- (NSString *)getDatabasePath:(NSString*)DBName;
- (void)createEditableCopyOfDatabaseIfNeeded:(NSString*)DBName;
- (void)initializeDatabase:(NSString*)DBName;
-(NSMutableArray*)GetListBySQL:(NSString*)SQL;
-(BOOL)UpdateData:(NSMutableDictionary*)objDic :(NSString*)PrimaryKey :(NSString*)TABLE_NAME;
-(BOOL)deleteQuery:(NSString *)query;
@end
.m
#import "DBLib.h"
@implementation DBLib
#pragma mark Database methods
- (NSString *)getDatabasePath:(NSString*)DBName
{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) ;
NSString *documentsDirectory = [paths objectAtIndex:0] ;
return [documentsDirectory stringByAppendingPathComponent:DBName];
}
// Creates a writable copy of the bundled default database in the application Documents directory.
- (void)createEditableCopyOfDatabaseIfNeeded:(NSString*)DBName {
// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:DBName];
success = [fileManager fileExistsAtPath:writableDBPath];
if (success) return;
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:DBName];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
NSString *errString = [NSString stringWithFormat:@"%@", [@"Fail" stringByReplacingOccurrencesOfString:@"#" withString:[error localizedDescription] ]];
NSAssert1(0, @"%@", errString);
}
}
// Open the database connection and retrieve minimal information for all objects.
- (void)initializeDatabase:(NSString*)DBName {
// The database is stored in the application bundle.
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
path = [documentsDirectory stringByAppendingPathComponent:DBName];
NSStringEncoding enc = [NSString defaultCStringEncoding];
// Open the database. The database was prepared outside the application.
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK)
{
//TRUE
NSLog(@"Successfully opened-sqlite3");
}
else
{
// Even though the open failed, call close to properly clean up resources.
sqlite3_close(database);
NSLog(@"closed");
NSString *errString = [NSString stringWithFormat:@"%@", [@"Fail" stringByReplacingOccurrencesOfString:@"#" withString:[NSString stringWithCString:sqlite3_errmsg(database) encoding:enc] ]];
NSAssert1(0, @"%@", errString);
// Additional error handling, as appropriate...
}
}
-(NSMutableArray*)GetListBySQL:(NSString*)SQL
{
[self initializeDatabase:@"DBNAME"];
NSMutableArray* Array;
Array=[[NSMutableArray alloc]init];
NSStringEncoding enc = [NSString defaultCStringEncoding];
sqlite3_stmt *select_statement=nil;
if (sqlite3_prepare_v2(database, [SQL UTF8String], -1, &select_statement, NULL) != SQLITE_OK) {
NSString *errString = [NSString stringWithFormat:@"%@", [@"Fail" stringByReplacingOccurrencesOfString:@"#" withString:[NSString stringWithCString:sqlite3_errmsg(database) encoding:enc] ]];
NSAssert1(0, @"%@", errString);
}
int columncount=sqlite3_column_count(select_statement);
NSMutableDictionary* dic;
while (sqlite3_step(select_statement) == SQLITE_ROW)
{
dic=[[NSMutableDictionary alloc]init];
for(int j=0;j<columncount;j++)
{
if(sqlite3_column_text(select_statement, j)!=nil)
[dic setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(select_statement, j)] forKey:[NSString stringWithUTF8String:(char *)sqlite3_column_name(select_statement,j)]];
else
[dic setObject:@"" forKey:[NSString stringWithUTF8String:(char *)sqlite3_column_name(select_statement,j)]];
}
[Array addObject:dic];
[dic release];
}
sqlite3_finalize(select_statement);
NSMutableArray *arr = [[NSMutableArray alloc] initWithArray: Array];
[Array release];
return arr;
}
//Method for Datbase
-(BOOL)UpdateData:(NSMutableDictionary*)objDic :(NSString*)PrimaryKey :(NSString*)TABLE_NAME
{
NSAutoreleasePool* pool=[[NSAutoreleasePool alloc]init];
[self initializeDatabase:DBNAME];
NSString* SQLColumns=@"";
NSString* SQLValues=@"";
NSString* SQL=@"";
//Chekc Wheather Insert or update?
BOOL IsNew=NO;;
if([[objDic valueForKey:PrimaryKey] intValue]==0)
{
IsNew=YES;
}
NSArray* Keys=[objDic allKeys];
NSLog(@"%@",Keys);
if(IsNew)
{
for(int i=0;i<Keys.count;i++)
{
if(![[Keys objectAtIndex:i] isEqual:PrimaryKey])
{
SQLColumns=[NSString stringWithFormat:@"%@%@,",SQLColumns,[Keys objectAtIndex:i]];
SQLValues=[NSString stringWithFormat:@"%@?,",SQLValues];
}
}
if([SQLColumns length]>0)
{
SQLColumns=[SQLColumns substringToIndex:[SQLColumns length]-1];
SQLValues=[SQLValues substringToIndex:[SQLValues length]-1];
}
SQL=[NSString stringWithFormat:@"INSERT INTO %@ (%@) Values(%@)",TABLE_NAME,SQLColumns,SQLValues];
}
else
{
for(int i=0;i<Keys.count;i++)
{
if(![[Keys objectAtIndex:i] isEqual:PrimaryKey])
{
SQLColumns=[NSString stringWithFormat:@"%@%@=?,",SQLColumns,[Keys objectAtIndex:i]];
}
}
if([SQLColumns length]>0)
{
SQLColumns=[SQLColumns substringToIndex:[SQLColumns length]-1];
}
SQL=[NSString stringWithFormat:@"UPDATE %@ SET %@ WHERE %@=?",TABLE_NAME,SQLColumns,PrimaryKey];
//NSLog(sql);
}
sqlite3_stmt *insert_statement=nil;
if (sqlite3_prepare_v2(database, [SQL UTF8String], -1, &insert_statement, NULL) != SQLITE_OK) {
//NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
int intBindIndex=1;
for(int i=0;i<Keys.count;i++)
{
if(![[Keys objectAtIndex:i] isEqual:PrimaryKey])
{
sqlite3_bind_text(insert_statement,intBindIndex,[[objDic valueForKey:[Keys objectAtIndex:i]] UTF8String],-1, SQLITE_STATIC);
intBindIndex++;
}
}
if(!IsNew)
{
sqlite3_bind_text(insert_statement,Keys.count,[[objDic valueForKey:PrimaryKey] UTF8String],-1, SQLITE_STATIC);
}
int result;
result=sqlite3_step(insert_statement);
if(IsNew)
{
[objDic setObject:[NSString stringWithFormat:@"%d",sqlite3_last_insert_rowid(database)] forKey:PrimaryKey];
}
sqlite3_finalize(insert_statement);
[pool release];
NSLog(@"result:%d",result);
if(result==SQLITE_DONE)
return YES;
else
return NO;
}
-(BOOL)deleteQuery:(NSString *)query
{
NSAutoreleasePool* pool=[[NSAutoreleasePool alloc]init];
[self initializeDatabase:DBNAME];
NSString* SQL=@"";
SQL=[NSString stringWithString:query];
sqlite3_stmt *insert_statement=nil;
if (sqlite3_prepare_v2(database, [SQL UTF8String], -1, &insert_statement, NULL) != SQLITE_OK) {
//NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}
int result;
result=sqlite3_step(insert_statement);
sqlite3_finalize(insert_statement);
[pool release];
NSLog(@"result:%d",result);
if(result==SQLITE_DONE)
return YES;
else
return NO;
}
@end
精彩评论