Can't save string into SQLite database (SQLITE_DONE doesn't return true)

Question

I'm trying to store strings in a SQLite database for an iOS 6 iPhone Application. It's fairly simple: a joke is displayed in a textview when clicking a button. When clicking a second button, I want to save that textview into the SQLite database (saveJoke). However, SQLITE_DONE is never returned, indicating this is not working. So, looking at my alerts, I always get "Fail" when saveJoke is executed below.

Any idea why this is not working? I have a feeling I might be missing something basic in the creation of and inserting into the SQLite database. Very thankful for help!

My code:

JokeFirstViewController.m:

#import "JokeFirstViewController.h"

@interface JokeFirstViewController ()

@end

@implementation JokeFirstViewController

@synthesize joke = _joke;

- (void)viewDidLoad
    {
    [super viewDidLoad];

    NSString *docsDir;
    NSArray *dirPaths;

    dirPaths = NSSearchPathForDirectoriesInDomains(
                                               NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = dirPaths[0];

    // Build the path to the database file
    _databasePath = [[NSString alloc]
                 initWithString: [docsDir stringByAppendingPathComponent:
                                  @"contacts.db"]];

    NSFileManager *filemgr = [NSFileManager defaultManager];

    if ([filemgr fileExistsAtPath: _databasePath ] == NO)
    {
    const char *dbpath = [_databasePath UTF8String];

    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
    {
        char *errMsg;
        const char *sql_stmt =
        "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, JOKESAVED TEXT)"; //look into

        sqlite3_close(_contactDB);

    }
  }
}

- (IBAction)saveJoke:(id)sender {

    /* get current joke displayed */
    self.joke = self.text.text;
    NSString *currentJoke = self.joke;
    NSString *jokeSaved = [[NSString alloc] initWithFormat:currentJoke];

    sqlite3_stmt    *statement;
    const char *dbpath = [_databasePath UTF8String];

    if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
    {

        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO CONTACTS (jokesaved) VALUES (?)",
                               jokeSaved];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(_contactDB, insert_stmt,
                           -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            void AlertWithMessage(NSString *message);
            {
                UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Database" message:@"Success" delegate:nil cancelButtonTitle:@"Ok" otherButtonTitles:nil];
                [alert show];
            }
        } else {
            void AlertWithMessage(NSString *message);
            {
                UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Database" message:@"Fail" delegate:nil cancelButtonTitle:@"Ok" otherButtonTitles:nil];
                [alert show];
            }

        }
        sqlite3_finalize(statement);
        sqlite3_close(_contactDB);
    }

    }

}

JokeFirstViewController.h:

#import <UIKit/UIKit.h>
#import <MessageUI/MFMessageComposeViewController.h>
#import <sqlite3.h>

@interface JokeFirstViewController : UIViewController <MFMessageComposeViewControllerDelegate>


- (IBAction)saveJoke:(id)sender;

- (IBAction)shareJoke:(id)sender;

- (IBAction)generateJoke:(id)sender;

@property (strong, nonatomic) IBOutlet UITextView *text;

@property (copy, nonatomic) NSString *joke;

@property (strong, nonatomic) NSString *databasePath;

@property (nonatomic) sqlite3 *contactDB;

@end

has you realized that you are not running the create table sentence? — tkanzakic
– tkanzakic, Commented May 22, 2013 at 10:24
You should check the result of sqlite3_prepare_v2 and make sure that's SQLITE_OK. And if not, look at sqlite3_errmsg() to get a nice explanation of why it failed. — Rob
– Rob, Commented May 22, 2013 at 10:36
But, tkanzanic is right, you really should sqlite3_exec that create table SQL if you want it to create the table. And you really should always check the result codes for all sqlite3 function calls, and if they fail, do the NSLog(@"%s: %s", __FUNCTION__, sqlite3_errmsg(contactDB)); — Rob
– Rob, Commented May 22, 2013 at 10:40
Thank you. Strange, this guide I've followed has left out sqlite3_exec (only present in an if statement). Is it inaccurate? techotopia.com/index.php/… — user1854291
– user1854291, Commented May 22, 2013 at 12:19
Could you please show me how to run the create table sentence in the above code to get it working? — user1854291
– user1854291, Commented May 22, 2013 at 12:31

Mat · Accepted Answer · 2013-05-22 11:19:22Z

1

You have also to check the INSERT query, your stringFormat is wrong:

Change:

NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO CONTACTS (jokesaved) VALUES (?)", jokeSaved];

to:

NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO CONTACTS (jokesaved) VALUES ('%@')", jokeSaved];

answered May 22, 2013 at 11:19

Mat

7,6414 gold badges43 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sharme · Accepted Answer · 2013-05-22 11:32:48Z

1

if (insert_statement == nil)
{  
    const char * sql = "INSERT INTO CONTACTS (jokesaved) VALUES (?)";
    if (sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK) 
    {
       NSAssert1(0, @"Error: Failed to prepare SQL statement: %s.",sqlite3_errmsg(database));
    }
}
@try 
{
   sqlite3_bind_text (insert_statement, 1, [jokesSaved UTF8String], -1, SQLITE_TRANSIENT );
}
@catch (NSException *exception)
{
  ;
}
int val = sqlite3_step(insert_statement);
if ( val != SQLITE_DONE) 
{
   NSAssert1(0, @"Failed to prepare SQL property insert statement: %s.", sqlite3_errmsg(database));
} 
else 
{

}

answered May 22, 2013 at 11:32

Sharme

6374 gold badges10 silver badges30 bronze badges

2 Comments

user1854291 Over a year ago

Do you have a full working example? Having some trouble putting this in the context of my code above.

rmaddy Over a year ago

Why are you wrapping the call to sqlite3_bind_text in a try/catch block? There is no need for that.

Collectives™ on Stack Overflow

Can't save string into SQLite database (SQLITE_DONE doesn't return true)

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related