Xcode not recognizing local database. Can't seem to connect to sqlite3 database file

user1431272 Source

I am trying to connect an sqlite3 database file I called Login.sql to my iphone appilcation I am writing. Xcode doesn't seem to recongize the file even though I tried putting in in several places including the Documents folder, the folder containing the xcode project, and the Dervived Data for the project. The following code is in the AppDelegate File under didFinishLaunchingWithOptions (it used to be in the View Controller but I moved it thinking it would work.

Here is the code: NSString *docsDir;

NSArray *dirPaths;

// databaseName = [[NSBundle mainBundle] pathForResource:@"DBLogin.sql" ofType:@"db"];

databaseName= @"Login.sql";

// Get the documents directory

dirPaths = NSSearchPathForDirectoriesInDomains(

                                               NSDocumentDirectory, NSUserDomainMask, YES);



docsDir = [dirPaths objectAtIndex:0];


NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *documentsDir = [documentPaths objectAtIndex:0];

databasePath = [documentsDir stringByAppendingPathComponent:databaseName];



// Build the path to the database file

//databasePath = [[NSString alloc]                     initWithString: [docsDir stringByAppendingPathComponent:                                     @"DBLogin.sql"]];



NSFileManager *filemgr = [NSFileManager defaultManager];



if ([filemgr fileExistsAtPath: databasePath ] == NO)

{


    const char *dbpath = [databasePath UTF8String];





    if (sqlite3_open(dbpath, &database) == SQLITE_OK)

    {

        NSLog(@"DATABASE LOADED");

        char *errMsg;

        const char *sql_stmt = "CREATE TABLE IF NOT EXISTS User (id varchar(20) PRIMARY KEY , password varchar(20))";



        if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)

        {

            status.text = @"Failed to create table";

        }



        status.text = @"Database Connected";



        sqlite3_close(database);



    } else {

        status.text = @"Failed to open/create database";

    }

}

else{

    NSLog(@"Connect!");

}




return YES;

The code that acceses the database file is here: Xcode login screen sqlite3 database authentication issue I'm just including it in case this code can cause the problem.

Basically everytime I place a new database in this code to see if that was the problem I think the code creates a new temporary sql database file with the a table created in the table does not exist function. I don't know what to do and this is driving me crazy. If anyone has any idea as to how to get this code to recognize my database it is appreciated. I already have the database linked to the copy bundle resources build page and it still doesnt' access it.

Let me know if you need any other information.

Thanks in advance!

iphonedatabasexcodeloginsqlite3

Answers

answered 6 years ago iNoob #1

I'll give you the steps i take to access the sqlite3 database.

1. First i create a blank sqlite database with required tables (using firefox sqlite manager add-on or any other method that is convenient). And add it in my project.

2. Get the path of the database file.

BOOL success;

NSArray*dirPath;
NSString*docDir;
NSString*databasePath;
NSString*[email protected]"yourDB.sqlite";

//path for database
dirPath=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docDir=[dirPath objectAtIndex:0];
databasePath=[docDir stringByAppendingPathComponent:databaseName];

3. Use NSFileManager to check if it is already present in DocumentDirectory. If it doesn't exist (in the first run), replace it with the database saved in the bundle (from the first step).

//check if present
NSFileManager*fm=[NSFileManager defaultManager];
success=[fm fileExistsAtPath:databasePath];

if(success)
{
   NSLog(@"Already present");
}
else
{

   //Copy from bundle to DocumentsDirectory on first run. Where DB won't be available in DocumentsDirectory.
      NSString*bundlePath=[[NSBundle mainBundle] pathForResource:@"yourDB" ofType:@"sqlite"];
NSError*error;
success=[fm copyItemAtPath:bundlePath toPath:databasePath error:&error];

 if(success)
 {
     NSLog(@"Created successfully");
 }

} // End of else when DB not present in documents directory.

After doing this you'll have the blank DB that you added in the bundle manually to documents directory, and all the changes will be performed on it (not on the bundle). After this you can proceed with the rest of sqlite3 database manipulation functions.

Edit: Method to access DB

This is what i use to retrieve values from DB, just try this out.

if(sqlite3_open([dbpath UTF8String], &database)==SQLITE_OK) //Check if it opens properly
{
    NSString*queryStmt=[NSString stringWithFormat:@"select * from sometable"];
    const char*querystmt=[queryStmt UTF8String];
    sqlite3_stmt *statement;

  if(sqlite3_prepare(database, querystmt, -1, &statement, NULL)==SQLITE_OK) // Check if prepare statment has no issues
    {
        while(sqlite3_step(statement)==SQLITE_ROW) // Consists of row.
        {
            NSString*thestring=[[NSString alloc] initWithUTF8String:(const char*)sqlite3_column_text(statement, 0)];// 0 will be first column
            NSLog(@"%@",thestring);
        }

        sqlite3_finalize(statement); //finalize statement
    }

    sqlite3_close(database); //Close database
}

answered 6 years ago Kanan Vora #2

The problem might be your database extension, create a DB called Login.sqlite rather than Login.sql as iPhone sqlite3 framework only recognizes .sqlite databases.

Then what you have coded will work.

comments powered by Disqus