1 Replies - 1057 Views - Last Post: 30 March 2011 - 06:33 AM

#1 erburrell  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 10
  • View blog
  • Posts: 145
  • Joined: 22-December 09

Trouble with SQLite Query

Posted 28 March 2011 - 06:43 PM

All,

I am trying to develop an iPhone app (Surprise! :-) and I am having trouble with SQLite. I am following some code out of the Big Nerd Ranch Book for accessing the data from SQLite, but can't figure out why my query keeps coming up null. Without a lot of extras, my code is below:

My SubjectViewController.h file:
#import <UIKit/UIKit.h>
#import <sqlite3.h>

@class Subject;

@interface SubjectViewController : UITableViewController {
	NSMutableArray *subjects;
	
	//Database
	sqlite3 *mathData;
	sqlite3_stmt *query;
}

@end


MySubjectViewController.m file:
#import "SubjectViewController.h"
#import "Subject.h"

@implementation SubjectViewController

//*** Initializer Methods START ***//
- (id)init {
	//Call the super class's designated initializer.
	[super initWithStyle:UITableViewStyleGrouped];
	
	NSLog(@"Step 1");
	// ### TO DO:  Add logic here to get subjects from database. ### //
	subjects = [[NSMutableArray alloc] init];
	
	//Get path to documents.
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *path = [paths objectAtIndex:0];
	
	//Name of database file:
	NSString *fullPath = [path stringByAppendingPathComponent:@"math_topics.sqlite"];
		NSLog(@"Step 2");
	//File manager:
	NSFileManager *fm = [NSFileManager defaultManager];
		NSLog(@"Step 3");
	//Does the file exist?
	NSLog(@"%@", fullPath);
	BOOL exists = [fm fileExistsAtPath:fullPath];
	//NSLog(@"exists = %@", exists);	
	NSLog(@"Step 4");
	//NSLog(@"exists = %@", exists);
	if (exists) {
		NSLog(@"%@ exists - just opening", fullPath);
		
	} else {
		NSLog(@"%@ does not exist - copying and opening");
		
		// WHere is the starting database in the wrapper?
		NSString *pathForStartingDB = [[NSBundle mainBundle] pathForResource:@"math_topics" ofType:@"sqlite"];
		
		// Copy it to the documents directory
		BOOL success = [fm copyItemAtPath:pathForStartingDB toPath:fullPath error:NULL];
		
		if (!success) {
			NSLog(@"database copy failed.");
		}
		
	}
		NSLog(@"Step 5");
	// Open the database.
	const char *cFullPath = [fullPath cStringUsingEncoding:NSUTF8StringEncoding];
	if (sqlite3_open(cFullPath, &mathData) != SQLITE_OK) {
		NSLog(@"unable to open database at %@", fullPath);
	} else if (sqlite3_open(cFullPath, &mathData) == SQLITE_OK) {
		NSLog(@"File Open");
	}

	char *cQuery = "SELECT * FROM subject";
	// Prepare the query.
	if (sqlite3_prepare_v2(mathData, cQuery, -1, &query, NULL) != SQLITE_OK) {
		NSLog(@"query error: %s", query);
	}
	
	// loop to get all the rows.
	
	Subject *loader = [[Subject alloc] init];
	while (sqlite3_step(query) == SQLITE_ROW) {
		NSLog(@"Adding Subjects.");
		[loader setId:(int)sqlite3_column_int(query, 0)];
		const char *name = (const char *)sqlite3_column_text(query, 1);
		[loader setName:[[[NSString alloc] initWithUTF8String:name] autorelease]];
		
		[subjects addObject:loader];
	}
	
	return self;
}

- (id)initWithStyle:(UITableViewStyle)style {
	return [self init];
}

// *** Initializer METHODS END *** //

// *** REQUIRED METHODS *** //
- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
	return [subjects count];
}

- (UITableViewCell *)tableView:(UITableView *)tableView
		 cellForRowAtIndexPath:(NSIndexPath *)indexPath {
	// Check for a reusable cell first and use it if it exists.
	UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:@"UITableViewCell"];
	
	// If there is no reusable cell of this type, create a new one.
	if (!cell) {
		cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault 
														reuseIdentifier:@"UITableViewCell"] autorelease];
	}
	
	// Set the text of the cell to the name of the subject.
	Subject *s = [subjects objectAtIndex:[indexPath row]];
	[[cell textLabel] setText:[s Name]];
	
	return cell;
}

/*
// Implement loadView to create a view hierarchy programmatically, without using a nib.
- (void)loadView {
}
*/

/*
// Implement viewDidLoad to do additional setup after loading the view, typically from a nib.
- (void)viewDidLoad {
    [super viewDidLoad];
}
*/

/*
// Override to allow orientations other than the default portrait orientation.
- (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation {
    // Return YES for supported orientations.
    return (interfaceOrientation == UIInterfaceOrientationPortrait);
}
*/

- (void)didReceiveMemoryWarning {
    // Releases the view if it doesn't have a superview.
    [super didReceiveMemoryWarning];
    
    // Release any cached data, images, etc. that aren't in use.
}

- (void)viewDidUnload {
    [super viewDidUnload];
    // Release any retained subviews of the main view.
    // e.g. self.myOutlet = nil;
}


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


@end


and the output of my NSLogs to the console is:

[Session started at 2011-03-28 20:28:24 -0500.]
2011-03-28 20:28:25.632 MathHelper[559:207] Step 1
2011-03-28 20:28:25.633 MathHelper[559:207] Step 2
2011-03-28 20:28:25.634 MathHelper[559:207] Step 3
2011-03-28 20:28:25.634 MathHelper[559:207] /Users/rburrell/Library/Application Support/iPhone Simulator/4.2/Applications/BB63823B-9815-4984-8746-02E6BF5182D9/Documents/math_topics.sqlite
2011-03-28 20:28:25.635 MathHelper[559:207] Step 4
2011-03-28 20:28:25.635 MathHelper[559:207] /Users/rburrell/Library/Application Support/iPhone Simulator/4.2/Applications/BB63823B-9815-4984-8746-02E6BF5182D9/Documents/math_topics.sqlite exists - just opening
2011-03-28 20:28:25.636 MathHelper[559:207] Step 5
2011-03-28 20:28:25.637 MathHelper[559:207] File Open
2011-03-28 20:28:25.638 MathHelper[559:207] query error: (null)

Any help is greatly appreciated!

Thanks,

Ed

Is This A Good Question/Topic? 0
  • +

Replies To: Trouble with SQLite Query

#2 GWatt  Icon User is offline

  • member icon

Reputation: 264
  • View blog
  • Posts: 3,059
  • Joined: 01-December 05

Re: Trouble with SQLite Query

Posted 30 March 2011 - 06:33 AM

I think SQL statements need to end with a semicolon. Also, you can use sqlite3_errmsg to get an actual error message from sqlite.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1