Creating A database with 2 tables ios

Question

Hello i am developing a database dbmain with 2 tables my code

NSString *docsDir;
NSArray *dirPaths;
dirPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);


// docsDir=[dirPaths objectAtIndex:0];
docsDir = dirPaths[0];
databasePath=[[NSString alloc]initWithString:[docsDir stringByAppendingPathComponent:@"dbDare.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 tblUser(ID INTEGER PRIMARY KEY AUTOINCREMENT, StrName TEXT,Strusername  TEXT)";
        if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
        {



            UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Failed to create table"
                                                            message:@"Failed to create table"
                                                           delegate:nil
                                                  cancelButtonTitle:@"OK"
                                                  otherButtonTitles:nil];
            [alert show];
            [alert release];
            //status.text = @"Failed to create table";
        }



            char *errMsg1;
            const char *sql_stmt1 =
            "CREATE TABLE IF NOT EXISTS tblVanish (ID INTEGER PRIMARY KEY AUTOINCREMENT, strfilepath TEXT,datesaved  TEXT)";
            if (sqlite3_exec(contactDB, sql_stmt1, NULL, NULL, &errMsg1) != SQLITE_OK)
            {

                UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Failed to create table"
                                                                message:@"Failed to create table"
                                                               delegate:nil
                                                      cancelButtonTitle:@"OK"
                                                      otherButtonTitles:nil];
                [alert show];
                [alert release];
                //status.text = @"Failed to create table";
            }
            sqlite3_close(contactDB);
    } else {
        // status.text = @"Failed to open/create database";
        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Failed to create open/create database"
                                                        message:@"Failed to create open/create database"
                                                       delegate:nil
                                              cancelButtonTitle:@"OK"
                                              otherButtonTitles:nil];
        [alert show];
        [alert release];

}

after adding records to the table i can see the rows using lita software but when retriving the rows via this code

 const char *dbpath=[databasePath UTF8String];
int iVal=0;
  sqlite3_stmt *statement;
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
    NSString *querySQL = @"SELECT Strusername FROM tblUser";
    const char *query_stmt = [querySQL UTF8String];

    if (sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            while (sqlite3_step(statement) == SQLITE_ROW) {

               strname = [NSString stringWithFormat:@"Welcome %@" ,[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)]];
            }



        } else {

        }
        sqlite3_finalize(statement);
    }
    sqlite3_close(contactDB);
}

i am not able to fetch any thing as there is no row displayed inside the table using ios but i can see the rows inside the table using lita please suggest where i am getting this wrong

I would recommend to use FMDB, a really nice wrapper for sqlite. A good tutorial — Anupdas
– Anupdas, Commented Jun 17, 2013 at 8:29

Midhun MP · Accepted Answer · 2013-06-17 08:37:20Z

3

Where are you copying the database file to document directory ?

And this condition looks very strange to me:

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

If the database is not existing there, why don't you copy it to document directory?

Also you are creating table inside that if condition, means you are trying to create table on a non-existing db file.

Possibly you want to copy the db file first, then you need to create the tables.

Or

You need to change the condition like:

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

answered Jun 17, 2013 at 8:37

Midhun MP

108k31 gold badges157 silver badges207 bronze badges

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

8 Comments

Rahul Over a year ago

Yes the db is stored in documents directory

Midhun MP Over a year ago

@Rahul: are you sure the db is there ? If yes, then it won't work (because your if condition is wrong, please check my answer)

Rahul Over a year ago

My db is in documents directory i had checked it's been created under /Users/Rahul/Library/Application Support/iPhone Simulator/6.1/Applications/AA3FE47D-A062-4631-9E0A/Documents/ path

Rahul Over a year ago

and i did checked the tables they are been formated under dbfile but i am not able to retrive them

Midhun MP Over a year ago

@Rahul: put a breakpoint or NSLog inside the if condition and check whether it is executing or not. Surely it won't execute if execute check the db open fails or not (it'll fail). Please check

|

Collectives™ on Stack Overflow

Creating A database with 2 tables ios

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related