Advertise here




Advertise here

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

retrieving data from sqlite in Xcode

ashukaushikashukaushik Posts: 1New Users
edited September 2012 in iPhone SDK Development
HI, i want to retrieve data from sqlite database.. i defined the database in my project and included the library. just i m confused in retrieving the data from database. i need syntax to retrieve the data from my data base.. in my database there are 5 questions with their 4 options each . i want to retrieve question in textview and options in button text.with one correct option. please help with this i m looking for the nice reply. i am attaching my code herewith.


.........


#import "DBQuestion.h"
#import "Domain.h"
#import
#import "DBAccess.h"

@implementation DBQuestion

@synthesize items,dbname,dbpath;



-(NSMutableArray *)readItems {

[self openDatabase];

// build select statement
if (!selStmt)
{
const char *sql = "SELECT quizid, question, answer1, answer2, answer3, answer4, answer from quizmaster order by quizid;";
if (sqlite3_prepare_v2(contactDB, sql, -1, &selStmt, NULL) != SQLITE_OK)
{
selStmt = nil;
}
NSLog(@"SQL query is: [%s]", sql);
}
if (!selStmt)
{
NSAssert1(0, @"Can't build SQL to read domains [%s]", sqlite3_errmsg(contactDB));
}

// loop reading items from list
[items removeAllObjects]; // clear list for rebuild
int ret;
while ((ret=sqlite3_step(selStmt))==SQLITE_ROW)
{ // get the fields from the record set and assign to item
// primary key
NSInteger n = sqlite3_column_int(selStmt, 0);
Domain *item = [[Domain alloc] initWithPrimaryKey:n]; // create item

// keyphrase name
char *s = (char *)sqlite3_column_text(selStmt, 1);
if (s==NULL) s = "";
item.question = [NSString stringWithUTF8String:(char *)s];

// keyphrase name
char *s1 = (char *)sqlite3_column_text(selStmt, 2);
if (s1==NULL) s1 = "";
item.answer1 = [NSString stringWithUTF8String:(char *)s1];


// keyphrase name
char *s2 = (char *)sqlite3_column_text(selStmt, 3);
if (s2==NULL) s2 = "";
item.answer2 = [NSString stringWithUTF8String:(char *)s2];


// keyphrase name
char *s3 = (char *)sqlite3_column_text(selStmt, 4);
if (s3==NULL) s3 = "";
item.answer3 = [NSString stringWithUTF8String:(char *)s3];


// keyphrase name
char *s4 = (char *)sqlite3_column_text(selStmt, 5);
if (s4==NULL) s4 = "";
item.answer4 = [NSString stringWithUTF8String:(char *)s4];

// keyphrase name
char *s5 = (char *)sqlite3_column_text(selStmt, 6);
if (s5==NULL) s5 = "";
item.answer = [NSString stringWithUTF8String:(char *)s5];





[items addObject:item]; // add to list
[item release]; // free item
}
sqlite3_reset(selStmt); // reset (unbind) statement



[self closeDatabase];

return items;
}

-(void)dealloc{
[items release];
[super dealloc];
}

-(void)openDatabase {



copyDb = TRUE; // set copy / create flag

// set up for app
self.dbname = @"quizshow.sqlite3"; // database name

// get full path of database in documents directory
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *path = [paths objectAtIndex:0];
self.dbpath = [[path stringByAppendingPathComponent:dbname] retain];

//
// open (and possibly create) database
// then read shopping list into array
//
contactDB = nil; // not opened yet
selStmt = nil; // no compiled statement yet




BOOL ok;
NSError *error;
items = [[NSMutableArray alloc] init]; // array for items

/*
* determine if database exists.
* create a file manager object to test existence
*
*/
NSFileManager *fm = [NSFileManager defaultManager]; // file manager
ok = [fm fileExistsAtPath:dbpath];
//[fm release];
// if database not there, copy from resource to path
if (!ok)
{
if (copyDb)
{ // copy the database
// location in resource bundle
NSString *appPath = [[[NSBundle mainBundle] resourcePath]
stringByAppendingPathComponent:dbname];
// copy from resource to where it should be
ok = [fm copyItemAtPath:appPath toPath:dbpath error:&error];
}
}


// open database
if (sqlite3_open([dbpath UTF8String], &contactDB) != SQLITE_OK)
{
sqlite3_close(contactDB); // in case partially opened
contactDB = nil; // signal open error
}

if (!copyDb && !ok)
{ // first time and database not copied
ok = TRUE;//[self createDatabase]; // create empty database
}

if (!ok)
{ // problems creating database
//NSAssert1(0, @"Problem creating database [%@]", [error localizedDescription]);
}

}


-(void)closeDatabase {
sqlite3_finalize(selStmt); // release memory
sqlite3_close(contactDB);
}



@end
Sign In or Register to comment.