Posted By

sag333ar on 11/18/10


Tagged

Objective-c iphone SQLite iOS4


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

sag333ar


SQLite DBAccess with Date-Day-Month


 / Published in: Objective C
 

  1. NSString *dayNameString[] = {@"Sunday", @"Monday", @"Tuesday", @"Wednesday", @"Thursday", @"Friday", @"Saturday"};
  2. NSString *dayNameHalfString[] = {@"Sun", @"Mon", @"Tue", @"Wed", @"Thr", @"Frd", @"Sat"};
  3. NSString *monthName[] = {@"January", @"February", @"March", @"April", @"May", @"June", @"July", @"August", @"September", @"October", @"November", @"Decemeber"};
  4.  
  5. #define GETCOLUMNSTRING(_TO_, _FROM_) NSString *_TO_; {const unsigned char *c = sqlite3_column_text(compiledStmt, _FROM_); _TO_ = c ? [NSString stringWithUTF8String:(char*)c] : @""; }
  6. #define PUTCOLUMNSTRING(_TO_, _FROM_) {NSString *str = [dObj valueForKey:_FROM_]; if (!str) str=@""; sqlite3_bind_text(compiledStmt, _TO_, [str UTF8String], -1, SQLITE_TRANSIENT);}
  7.  
  8.  
  9. @implementation Database
  10.  
  11. +(Database *)sharedDatabase
  12. {
  13. if (sharedDatabase == nil)
  14. {
  15. sharedDatabase = [[Database alloc] init];
  16. }
  17. return sharedDatabase;
  18. }
  19.  
  20. - (id)init
  21. {
  22. if (self = [super init])
  23. {
  24. NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
  25. NSString *basePath = ([paths count] > 0) ? [paths objectAtIndex:0] : nil;
  26. NSString *dbPath=[basePath stringByAppendingPathComponent:[kDataBaseName stringByAppendingFormat:@".%@",kDataBaseExt]];
  27. NSFileManager *fm=[NSFileManager defaultManager];
  28.  
  29. if(![fm fileExistsAtPath:dbPath]){
  30. [fm copyItemAtPath:[[NSBundle mainBundle] pathForResource:kDataBaseName ofType:kDataBaseExt] toPath:dbPath error:nil];
  31. }
  32. if (sqlite3_open([dbPath UTF8String], &database) != SQLITE_OK)
  33. NSLog(@"Error opening database!");
  34.  
  35. sqlite3_stmt *compiledStmt;
  36. NSUInteger err = sqlite3_prepare_v2(database, "PRAGMA count_changes=OFF; PRAGMA journal_mode = OFF; PRAGMA synchronous=OFF", -1, &compiledStmt, NULL);
  37. if (!err)
  38. {
  39. err = sqlite3_step(compiledStmt);
  40. if (!err)
  41. sqlite3_finalize(compiledStmt);
  42. }
  43. }
  44. return self;
  45. }
  46.  
  47. -(NSArray*)getMarketsBySearchText:(NSString*)searchText{
  48. NSMutableArray *facultiesArray=[NSMutableArray array];
  49. {
  50. // NSString *tmpStr=[NSString stringWithFormat:@"select id, name, suburb FROM markets where verified is not null and name like '%@%' order by name asc",searchText];
  51. const char *sqlStatement="select id, title, location_text_1 FROM pmd_listings where custom_15 is not null and title like ? order by name asc";
  52. // const char *sqlStatement= [tmpStr UTF8String];
  53. sqlite3_stmt *compiledStmt;
  54. if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStmt, NULL) == SQLITE_OK)
  55. {
  56. //sqlite3_bind_text(compiledStmt, 1, [[NSString stringWithFormat:@"%@%", searchText ] UTF8String], -1, SQLITE_TRANSIENT);
  57. sqlite3_bind_text(compiledStmt, 1, [[NSString stringWithFormat:@"%%%@%%", searchText ] UTF8String], -1, SQLITE_TRANSIENT);
  58. while(sqlite3_step(compiledStmt)==SQLITE_ROW)
  59. {
  60. NSUInteger mNo=(NSUInteger)sqlite3_column_int(compiledStmt, 0);
  61. GETCOLUMNSTRING(mName, 1) //NSString *mName=[NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(compiledStmt, 1)];
  62. GETCOLUMNSTRING(mSuburb, 2) //NSString *mSuburb=[NSString stringWithFormat:@"%s",(char*)sqlite3_column_text(compiledStmt, 2)];
  63. [facultiesArray addObject:
  64. [NSDictionary dictionaryWithObjectsAndKeys:
  65. [NSString stringWithFormat:@"%i",mNo],@"marketid",
  66. mName,@"marketname",
  67. mSuburb,@"marketSuburb",nil
  68. ]
  69. ];
  70. }
  71. }
  72. sqlite3_finalize(compiledStmt);
  73. }
  74. return facultiesArray;
  75. }
  76.  
  77.  
  78. -(void)checkCategoryDetails:(NSArray*)dCategory
  79. {
  80. NSLog(@"START*************** category -%d items", dCategory.count );
  81. sqlite3_stmt *compiledStmt;
  82. sqlite3_prepare_v2(database, "BEGIN TRANSACTION", -1, &compiledStmt, NULL);
  83. sqlite3_step(compiledStmt);
  84. sqlite3_finalize(compiledStmt);
  85. const char *sqlStatement="REPLACE INTO pmd_categories(id,title,description_short,description,keywords,friendly_url,friendly_url_path,title_override,meta_description,meta_keywords,link,hidden,count,level,left_,right_,hits,ip,import_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  86. if (sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStmt, NULL)== SQLITE_OK)
  87. {
  88. for (NSDictionary *dObj in dCategory)
  89. {
  90. NSUInteger cid = [[dObj objectForKey:@"id"] intValue];
  91. if (cid)
  92. {
  93. NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
  94.  
  95. sqlite3_bind_int(compiledStmt, 1, cid);
  96. PUTCOLUMNSTRING(2, @"title")
  97. PUTCOLUMNSTRING(3, @"description_short")
  98. PUTCOLUMNSTRING(4, @"description")
  99. PUTCOLUMNSTRING(5, @"keywords")
  100. PUTCOLUMNSTRING(6, @"friendly_url")
  101. PUTCOLUMNSTRING(7, @"friendly_url_path")
  102. PUTCOLUMNSTRING(8, @"title_override")
  103. PUTCOLUMNSTRING(9, @"meta_description")
  104. PUTCOLUMNSTRING(10, @"meta_keywords")
  105. PUTCOLUMNSTRING(11, @"link")
  106. PUTCOLUMNSTRING(12, @"hidden")
  107. PUTCOLUMNSTRING(13, @"count")
  108. PUTCOLUMNSTRING(14, @"level")
  109. PUTCOLUMNSTRING(15, @"left_")
  110. PUTCOLUMNSTRING(16, @"right_")
  111. PUTCOLUMNSTRING(17, @"hits")
  112. PUTCOLUMNSTRING(18, @"ip")
  113. PUTCOLUMNSTRING(19, @"import_id")
  114.  
  115.  
  116. NSUInteger err = sqlite3_step(compiledStmt);
  117. if (err != SQLITE_DONE)
  118. NSLog(@"replace error %d %s",err, sqlite3_errmsg(database));
  119. sqlite3_reset(compiledStmt);
  120. [pool release];
  121. }
  122.  
  123. }
  124. sqlite3_finalize(compiledStmt);
  125. }
  126. sqlite3_prepare_v2(database, "END TRANSACTION", -1, &compiledStmt, NULL);
  127. sqlite3_step(compiledStmt);
  128. sqlite3_finalize(compiledStmt);
  129. NSLog(@"END*************** category");
  130. }

Report this snippet  

You need to login to post a comment.