Create SQLite DB with multiple tables in iOS

Question

In my development of an ipad applicacion, I need a database with 5 tables, but, I don't know why in my code, only execute 1 statement and don't return any error:

            const char *sqlStatement = "CREATE TABLE IF NOT EXISTS CATEGORIAS (ID INTEGER PRIMARY KEY, NOMBRE TEXT)";

            char *error;

            if(sqlite3_exec(dieneDB, sqlStatement, NULL, NULL, &error) == SQLITE_OK){

                **sqlStatement = "CREATE TABLE IF NOT EXIST SUBCATEGORIAS (ID INTEGER PRIMARY KEY, ID_CATEGORIA INTEGER, NOMBRE TEXT)";**

                if(sqlite3_exec(dieneDB, sqlStatement, NULL, NULL, &error) == SQLITE_OK){

                    sqlStatement = "CREATE TABLE IF NOT EXIST FORMATOS (ID INTEGER PRIMARY KEY, NOMBRE TEXT)";

                    if(sqlite3_exec(dieneDB, sqlStatement, NULL, NULL, &error) == SQLITE_OK){

                        sqlStatement = "CREATE TABLE IF NOT EXIST BEBIDAS (ID INTEGER PRIMARY KEY, ID_CATEGORIA INTEGER, ID_SUBCATEGORIA INTEGER, NOMBRE TEXT, DESCRIPCION TEXT, RUTA_IMAGEN TEXT)";

                        if(sqlite3_exec(dieneDB, sqlStatement, NULL, NULL, &error) == SQLITE_OK){

                            sqlStatement = "CREATE TABLE IF NOT EXIST BEBIDASFORMATOS (ID INTEGER PRIMARY KEY, ID_BEBIDA INTEGER, ID_FORMATO INTEGER)";

                            if(sqlite3_exec(dieneDB, sqlStatement, NULL, NULL, &error) == SQLITE_OK){

                                NSLog(@"All tables are created");

                                sqlite3_close(dieneDB);

                                [self loadData];

                            }

                        }

                    }

                }

            }else{

                NSLog(@"Unable to create some table %s", error);

            }

        }else{

            NSLog(@"Database Exists Already");

            [self clearData]   
        }
    }else{
        NSLog(@"Error opening database");   
    }
}

It crash in the second sqlStatement and go to the}else{

                NSLog(@"Unable to create some table %s", error);

But not log anythink

Rob · Accepted Answer · 2014-06-03 14:58:54Z

3

The problem in your SQL would appear to be that your first SQL statement correctly uses IF NOT EXISTS, but the rest use IF NOT EXIST.

You're probably not seeing your error message because you're only logging errors if the first sqlite3_exec fails. The rest of the if statements don't have else clauses. You probably want to NSLog the error for each failed sqlite3_exec call.

Alternatively, sqlite3_exec, unlike other SQLite functions, accepts multiple SQL statements, terminated with semicolons. Thus greatly simplifying your code since with only one sqlite3_exec, you only need one else statement:

const char *sqlStatement =  "CREATE TABLE IF NOT EXISTS CATEGORIAS      (ID INTEGER PRIMARY KEY, NOMBRE TEXT);"
                            "CREATE TABLE IF NOT EXISTS SUBCATEGORIAS   (ID INTEGER PRIMARY KEY, ID_CATEGORIA INTEGER, NOMBRE TEXT);"
                            "CREATE TABLE IF NOT EXISTS FORMATOS        (ID INTEGER PRIMARY KEY, NOMBRE TEXT);"
                            "CREATE TABLE IF NOT EXISTS BEBIDAS         (ID INTEGER PRIMARY KEY, ID_CATEGORIA INTEGER, ID_SUBCATEGORIA INTEGER, NOMBRE TEXT, DESCRIPCION TEXT, RUTA_IMAGEN TEXT);"
                            "CREATE TABLE IF NOT EXISTS BEBIDASFORMATOS (ID INTEGER PRIMARY KEY, ID_BEBIDA INTEGER, ID_FORMATO INTEGER);";

char *error;

if(sqlite3_exec(dieneDB, sqlStatement, NULL, NULL, &error) == SQLITE_OK){
    NSLog(@"All tables are created");
    [self loadData];
} else {
    NSLog(@"Unable to create some table %s", error);
    sqlite3_free(error);
    error = NULL;
}

Note, I've fixed the IF NOT EXISTS error, above. Also, note, if you get an error message, you're responsible for freeing it with sqlite3_free.

edited Jun 3, 2014 at 14:58

answered Jun 3, 2014 at 14:38

Rob

441k74 gold badges850 silver badges1.1k bronze badges

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

2 Comments

PlugInBoy Over a year ago

This makes me feel a newbie. I'm sorry to have wasted your time to all who have read my message ... Thanks!!!

Rob Over a year ago

@PlugInBoy No worries. BTW, I've updated my answer with a code sample that would not only log the error correctly, but greatly simplifies your code.

Collectives™ on Stack Overflow

Create SQLite DB with multiple tables in iOS

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related