开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜