title: Sqlite objective c singleton database connection closes before executing prepared statement
body: <p>Sqlite3 objective c singleton database connection closes before executing prepared statement
  I have created a singleton database connection in objective c but it closes before executing the prepared statement.</p>
  
  <p>I'm from a java background and this is my first xcode project. Using xcode 4.5.2. 
  I just want to make one database connection that all files can use. I've looked at many message boards on the topic and spent an unbelievable amount of time trying to resolve it, I simply can't seem to resolve this. I would be so grateful if someone could explain what I need to do for the sql connection not to close before executing the prepared statement. I've included the code and runtime output below.</p>
  
  <p>I have made a database singleton and successfully initialises it in appDelegate.  Then in my classes which need the connection, I refer to to the database connection like this:</p>
  
  <pre><code>[Database database];
  db = [database getSqlite3Connection];
  </code></pre>
  
  <p>before I used:</p>
  
  <pre><code>AppDelegate *appDelegate =  (AppDelegate *)[[UIApplication sharedApplication] 
      delegate];
  db = [appDelegate db];
  </code></pre>
  
  <p>In both cases the code runs but fails to execute the prepared statement. However when I put the create database connection code into the class that needs to use the connection, the connection executes the prepared statement with no problem: i.e. :</p>
  
  <pre><code>NSString *dbPath = [[[NSBundle mainBundle] resourcePath]                     
                       stringByAppendingPathComponent:@"hsk1.sqlite"];
  if (sqlite3_open([dbPath UTF8String], &amp;dbConnection) != SQLITE_OK)
  </code></pre>
  
  <p>I am  confused on how the classes can receive a global sqlite3 connection. Could somebody tell if this is the correct method:</p>
  
  <p>In my database class I use this code to pass the database object I created to sqlite3, so I can use it for all my classes' prepared statements:</p>
  
  <pre><code>sqlite3 *dbConnection = (__bridge sqlite3 *)(database);
  </code></pre>
  
  <p>My code:</p>
  
  <pre><code>//  AppDelegate.h
  //  StoryboardTutorial
  
  #import &lt;UIKit/UIKit.h&gt;
  #import "Database.h"
  #import &lt;sqlite3.h&gt;
  
  @interface AppDelegate : UIResponder &lt;UIApplicationDelegate&gt;
  @property (strong, nonatomic) UIWindow *window;
  @property (nonatomic,readonly) Database *database;
  @property (nonatomic,readonly) sqlite3 *db;
  
  @end
  
  //  AppDelegate.m
  //  StoryboardTutorial
  #import "AppDelegate.h"
  @implementation AppDelegate
  @synthesize database;
  @synthesize db;
  @synthesize window = _window;
  
  - (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:
  (NSDictionary *)launchOptions {
      // Override point for customization after application launch.
      [Database database];
      db = [database getSqlite3Connection];
  
      return YES;
  }
  
  //  Database.h
  #import &lt;Foundation/Foundation.h&gt;
  #import &lt;sqlite3.h&gt;
  
  @interface Database : NSObject {
  }
  + (Database *)database;
  - (id)init;
  + (Database *)getInstance;
  -(sqlite3 *)getSqlite3Connection;
  - (void)dealloc;
  @end
  
  //
  //  database.m
  //
  #import "Database.h"
  
  @implementation Database
  
  static Database *database;
  
  sqlite3 *dbConnection =nil;
  
  + (Database*)database {
      if (database == nil) {
          database = [[Database alloc] init];
      }
      return database;
  }
  
  - (id)init{
      if (self = [super init]) {
  
          NSString *dbPath = [[[NSBundle mainBundle] resourcePath ]
                              stringByAppendingPathComponent:@"hsk1.sqlite"];
          NSLog(@"DB CONNECTION - INTIALISING NEW CONNECTION");
          if (sqlite3_open([dbPath UTF8String], &amp;dbConnection) != SQLITE_OK) {
  
              NSLog(@"[SQLITE] Unable to open database!");
              return nil; // if it fails, return nil obj
          }
          dbConnection = (__bridge sqlite3 *)(database);
  
          NSLog(@"*DB CONNECTION Address: %p", database);
      }
      return self;
  }
  
  +(Database *)getInstance {
      @synchronized(self)
      {
          NSLog(@"*DB CONNECTION getInstance:");
          if (database == nil){
              NSLog(@"DB CONNECTION - none");
              database = [[self alloc] init];
              NSLog(@"DB CONNECTION getInstance() Address: %p", database);
          }
      }
      return(database);
  }
  
  -(sqlite3*)getSqlite3Connection {
      return dbConnection;
  }
  
  - (void)dealloc {
      NSLog(@"DB CONNECTION*Closing Connection");
      sqlite3_close(dbConnection);
  }
  
  @end
  
  //  ViewController.h
  //
  
  #import &lt;UIKit/UIKit.h&gt;
  #import &lt;AVFoundation/AVFoundation.h&gt;
  
  @interface ViewController : UIViewController&lt;UITableViewDelegate,
  UITableViewDataSource,  AVAudioPlayerDelegate&gt;{
      AVAudioPlayer *audioPlayer;
  }
  @property(nonatomic, retain)NSMutableArray *wordListsArray;
  @end
  
  //  ViewController.m
  //
  
  #import "ViewController.h"
  #import "WordLists.h"
  #import "Database.h"
  #import "AppDelegate.h"
  
  @implementation ViewController
  
  #pragma mark - View lifecycle
  
  - (void)viewDidLoad
  {
      [super viewDidLoad];
  
      int wordid =1;
  
      WordLists * mywords =[[WordLists alloc] init];
  
      self.wordListsArray = [mywords getWordforId:(NSInteger)wordid];
  }
  
  //  WordLists.h
  //
  
  #import &lt;Foundation/Foundation.h&gt;
  
  @interface WordLists : NSObject{
  }
  - (NSMutableArray *) getWordforId :(NSInteger)wordid;
  @end
  
  //  WordLists.m
  //
  
  #import "AppDelegate.h"
  #import "WordLists.h"
  #import "Word.h"
  #import "Database.h"
  
  @implementation WordLists
  Database *database =nil;
  sqlite3 *db = nil;
  
  - (id)init {
      Database *database  = [Database getInstance];
      db = [database getSqlite3Connection];
  
      if (db == nil) {
          database = [[Database alloc] init];
          NSLog(@"**Wordlists:init: db is nil");
      }
      return self;
  }
  
  - (NSMutableArray *) getWordforId :(NSInteger)wid{
  
      int wordid = wid;
      sqlite3_stmt *sqlStatement;
  
      NSMutableArray *WordArray = [[NSMutableArray alloc] init];
      @try {
  
          const char *sql = "SELECT id,ename,pname,hname,hsound FROM words WHERE id=?";
  
          if(sqlite3_prepare(db, sql, -1, &amp;sqlStatement, NULL) != SQLITE_OK)
          {
              NSLog(@"WordsList:getWordforId: Problem with prepare statement");
          }
  
          sqlite3_bind_int(sqlStatement,1, wordid);
  
          while (sqlite3_step(sqlStatement)==SQLITE_ROW) {
              NSLog(@"MyWords:getMyWords: Record numbers");
  
              Word *MyWord = [[Word alloc]init];
              MyWord.wordId = sqlite3_column_int(sqlStatement, 0);
              MyWord.ename = [NSString stringWithUTF8String:(char *)
                              sqlite3_column_text(sqlStatement,1)];
              MyWord.pname = [NSString stringWithUTF8String:(char *)
                              sqlite3_column_text(sqlStatement, 2)];
              MyWord.hname = [NSString stringWithUTF8String:(char *)
                              sqlite3_column_text(sqlStatement, 3)];
              MyWord.hsound = [NSString stringWithUTF8String:(char *)
                               sqlite3_column_text(sqlStatement, 4)];
              [WordArray addObject:MyWord];
  
              NSLog(@"wineId : %d", MyWord.wordId);
          }
          sqlite3_reset(sqlStatement);
          sqlite3_finalize(sqlStatement);
  
          return WordArray;
      }@catch (NSException *exception) {
          NSLog(@"An exception occured: %@", [exception reason]);
      }
      @finally {
  
      }
  }
  @end
  </code></pre>
  
  <p>Runtime Output:</p>
  
  <pre>
  2013-08-03 22:01:05.320 StoryboardTutorial[716:c07] DB CONNECTION - INTIALISING NEW  
       CONNECTION
  2013-08-03 22:01:05.322 StoryboardTutorial[716:c07] *DB CONNECTION Address: 0x0
  2013-08-03 22:01:05.328 StoryboardTutorial[716:c07] *DB CONNECTION getInstance:
  2013-08-03 22:01:05.329 StoryboardTutorial[716:c07] DB CONNECTION INTIALISING NEW 
        CONNECTION
  2013-08-03 22:01:05.330 StoryboardTutorial[716:c07] *DB CONNECTION Address: 0x7d78ea0
  2013-08-03 22:01:05.330 StoryboardTutorial[716:c07] **Wordlists:init: db is nil
  2013-08-03 22:01:05.331 StoryboardTutorial[716:c07] DB CONNECTION*Closing Connection
  2013-08-03 22:01:05.331 StoryboardTutorial[716:c07]     WordsList:getWordforId: 
      Problem with prepare statement
  </pre>