Sqlite create db with multi tables from raw file

Question

I want to create a DB with 2 tables in Sqlite. For 1 table it work but 2 not. I have in my raw folder one create_schema.sql file with:

CREATE TABLE USER_USA (
    _id integer PRIMARY KEY NOT NULL UNIQUE,
    user varchar NOT NULL UNIQUE,
    pass varchar NOT NULL UNIQUE,
    cod varchar
    );

CREATE INDEX UNIQUE_ENTRY_DATE ON USER_USA (_id);

and i have this:

   if (mDb == null || !mDb.isOpen()) {

            final File dbFile = mContext.getDatabasePath(MyDbClass.DB_NAME);
            final boolean wasExisting = dbFile.exists();
            if (!wasExisting) {
                // We read the sql to create the DB
                final String createDbStatement;
                try {
                    createDbStatement = ResourceUtils.getRawAsString(mContext, R.raw.create_schema);

                    // If it's not present we create it
                    DatabaseUtils.createDbFromSqlStatements(mContext, MyDbClass.DB_NAME, DB_VERSION, createDbStatement);

                } catch (IOException e) {
                    e.printStackTrace();
                    Log.e(TAG_LOG, "Error creating DB", e);
                    return;
                }
            }

            mDb = mContext.openOrCreateDatabase(MyDbClass.DB_NAME, Context.MODE_PRIVATE, null);

        }

Now if i try to modify the create_schema.sql file like below not work:

  CREATE TABLE USER_USA (
        _id integer PRIMARY KEY NOT NULL UNIQUE,
        user varchar NOT NULL UNIQUE,
        pass varchar NOT NULL UNIQUE,
        cod varchar
        );
CREATE TABLE GOOGLE_KEY (
        _id integer PRIMARY KEY NOT NULL UNIQUE,
        regid varchar NOT NULL UNIQUE,

        );
CREATE INDEX UNIQUE_ENTRY_DATE ON USER_USA (_id);
CREATE INDEX UNIQUE_ENTRY_DATE ON GOOGLE_KEY (_id);

where i'm wrong?

Error:

android.database.sqlite.SQLiteException: no such table: GOOGLE_KEY (code 1): , while compiling: SELECT * FROM GOOGLE_KEY

did you uninstall the application before it after the schema update? — Blackbelt
– Blackbelt, Commented Apr 25, 2015 at 13:22

taxidev · Accepted Answer · 2015-04-25 13:34:15Z

1

This is an improper ending to a create table statement:

CREATE TABLE GOOGLE_KEY (
    _id integer PRIMARY KEY NOT NULL UNIQUE,
    regid varchar NOT NULL UNIQUE,

    );

There is a hanging comma there, so the table won't be constructed. Look at the ending of your first create table statement, and fix this one to look like that one.

answered Apr 25, 2015 at 13:34

taxidev

975 bronze badges

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

2 Comments

DevOps85 Over a year ago

thanks, now i have this error: index UNIQUE_ENTRY_DATE already exists (code 1): , while compiling: CREATE INDEX UNIQUE_ENTRY_DATE ON USER_USA (_id)

taxidev Over a year ago

If you already ran the script before then the index already exists, that would cause the error. You can add "IF NOT EXISTS" to the CREATE statement to avoid that.

Collectives™ on Stack Overflow

Sqlite create db with multi tables from raw file

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