15413

ios - sqlite prepare statement

Question:

I am trying to read data from a sqlite database in an ios app. When I run the app, it is able to open the database but in the log file it shows the message - "Problem with the prepare statement". I don't know what is wrong with my prepare statement Here's my code -

-(NSString *)dataFilePath{ NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; return [documentsDirectory stringByAppendingPathComponent:kFilename]; }

In the <strong>viewDidLoad</strong> I have -

- (void)viewDidLoad { [super viewDidLoad]; // Do any additional setup after loading the view, typically from a nib. myarray = [[NSMutableArray alloc]init]; sqlite3 *database; if(sqlite3_open([[self dataFilePath]UTF8String], &database)!=SQLITE_OK){ sqlite3_close(database); NSAssert(0, @"Failed to open database"); } const char *createSQL = @"SELECT ID, TITLE FROM FIRST ORDER BY TITLE;"; //first is the table in the database sqlite3_stmt *sqlStmt; if(sqlite3_prepare_v2(database, [createSQL UTF8String], -1, &sqlStmt, nil)!=SQLITE_OK){ NSLog(@"Problem with prepare statement"); //this is where the code gets stuck and I don't know why }else{ while(sqlite3_step(sqlStmt)==SQLITE_ROW){ NSInteger number = sqlite3_column_int(sqlStmt, 0); NSString *title = [NSString stringWithUTF8String:(char *) sqlite3_column_text(sqlStmt, 1)]; [myarray addObject:title]; } sqlite3_finalize(sqlStmt); } sqlite3_close(database); }

Answer1:

If your prepare statement fails, rather than just reporting "Problem with prepare statement", try retrieving the error message, e.g.,

NSLog(@"%s Prepare failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(database), sqlite3_errcode(database));

This might give you a better indication of the problem.

A problem I've seen in the past is that the database might not be found (because it wasn't included in the bundle, typo in the name, etc.) but the standard sqlite3_open function will create it if it's not there, and thus the sqlite3_open will succeed, but the table in question won't be found in the blank, newly created database. Better than sqlite3_open would be:

sqlite3 *database; if (sqlite3_open_v2([[self dataFilePath] UTF8String], &database, SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK) { sqlite3_close(database); // not sure you need to close if the open failed NSAssert(0, @"Failed to open database"); }

That way you get a warning if the database is not found. But if you've done sqlite3_open already, you might have a blank database, so you might want to reset your simulator and/or remove the app from your device, before trying it with sqlite3_open_v2.

Answer2:

Several things you can try.

<ol><li>

Clean your app, Remove from simulator or device & try installing a fresh copy again and see if it works.

</li> <li>

Open your DB in Terminal & try to run your sql statement at there. Check wether you are getting desired output.

</li> </ol>

Answer3:

Try changing the nil to NULL. Also, try defining the SQL statement as a const char.

.... const char *sql = "SELECT ID, TITLE FROM FIRST ORDER BY TITLE"; sqlite3_stmt *sqlStmt; if(sqlite3_prepare_v2(database, sql, -1, &sqlStmt, NULL)!=SQLITE_OK){ ....

Recommend

  • Sqlite3 update query not working in ios app
  • SQLite3: the database was created empty?
  • Use of Frame Pointer MIPS
  • What is the best way to read binary image from blob SQLite and decode it using OpenCV imdecode?
  • Sqlite iPhone data insertion problem
  • Why does SQLite not bring back any results from my database
  • MIPS Assembly - Arrays?
  • Why we cant compare (bne-beq) a register with an immediate (Assembly-MIPS)?
  • unrecognized selector sent to instance
  • getting Data From SQLite Database;
  • What needs finalization in a multidimensional dynamic array?
  • Check if an array of custom objects contains an object with a certain date
  • Error in all unit and functional tests after altering column names of a table
  • Is there a need to prepend setTimeout and setInterval with window object?
  • Weird UIView coordinate issue
  • Unable to extract string using NSRegularExpression (regex pattern) - iphone
  • sqlite3_prepare_v2 is getting SQLITE_ERROR
  • curl - How to escape < in parameter value
  • rails 3.1, capybara-webkit, how to execute javascript inside link?
  • running median of constant size array
  • Html select multiple get all values at onchange event
  • Rails 3.2 from SQLite locally to Postgres on Heroku
  • iOS 6 dateFromString returns wrong date
  • “A GKScore must specify a leaderboard.”
  • Prevent page break in text block with iText, XMLWorker
  • Python 3.2.2, error(scripts to exe)
  • NSIS decompiler
  • Question about instantiating object
  • Convert SQLite database to XML
  • NSScanner Loop Question
  • how to save the state in userdefaults of accessory checkmark-iphone
  • Problem while Building a Setup Project for a windows Service?
  • Pass value from viewmodel to script in zk
  • Eraser for UIBezierPath
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Circular dependency while pushing http interceptor
  • Compare two NSDates in iPhone
  • Linker errors when using intrinsic function via function pointer
  • Load html files in TinyMce
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET