Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Android

Tom Schinler
Tom Schinler
21,052 Points

Android SQLite3 file is encrypted or is not a database

So I am working on an app that pulls from a database a random line when the user hits a button. I am using

https://github.com/jgilfelt/android-sqlite-asset-helper

as a database helper. I have created a database with

http://sqlitebrowser.org/

and I have followed every instruction in the docs. When I try to run the app, it crashes when i hit the button. The button press brings up a new activity that is supposed to display the data. When I pull the properties for the file, it is listed as a database. Here is a selection from the logcat that shows the errors:

03-10 18:52:07.476  10812-10812/tomschinler.thegreatman_kiniskamikazekaraoke W/SQLiteAssetHelper copying database from assets...
03-10 18:52:07.488  10812-10812/tomschinler.thegreatman_kiniskamikazekaraoke W/SQLiteAssetHelper database copy complete
03-10 18:52:07.544  10812-10812/tomschinler.thegreatman_kiniskamikazekaraoke E/SQLiteLog (26) file is encrypted or is not a database
03-10 18:52:07.556  10812-10812/tomschinler.thegreatman_kiniskamikazekaraoke E/DefaultDatabaseErrorHandler Corruption reported by sqlite on database: /data/data/tomschinler.thegreatman_kiniskamikazekaraoke/databases/mankiniDB.db
03-10 18:52:07.556  10812-10812/tomschinler.thegreatman_kiniskamikazekaraoke E/DefaultDatabaseErrorHandler deleting the database file: /data/data/tomschinler.thegreatman_kiniskamikazekaraoke/databases/mankiniDB.db
03-10 18:52:07.568  10812-10812/tomschinler.thegreatman_kiniskamikazekaraoke E/SQLiteLog (14) cannot open file at line 30191 of [00bb9c9ce4]
03-10 18:52:07.568  10812-10812/tomschinler.thegreatman_kiniskamikazekaraoke E/SQLiteLog (14) os_unix.c:30191: (2) open(/data/data/tomschinler.thegreatman_kiniskamikazekaraoke/databases/mankiniDB.db) -

It copies the database successfully, and then fails to open it.

This is the DB Helper Class:

package tomschinler.thegreatman_kiniskamikazekaraoke;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

/**
 * Created by TomandGina on 3/9/2015.
 */
public class MyDatabase extends SQLiteAssetHelper {

    private static final String DATABASE_NAME = "mankiniDB.db";
    private static final int DATABASE_VERSION = 1;

    public MyDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }


    public Cursor kamikazeSelected() {

        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor =db.rawQuery("SELECT * FROM songList ORDER BY random () limit 1", null);

        return cursor;
    }
}

and here is the class that calls the database:

package tomschinler.thegreatman_kiniskamikazekaraoke;


import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.TextView;

import org.w3c.dom.Text;

import java.io.IOException;


public class SongActivity extends ActionBarActivity {

    private MyDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_song);

        //when activity launches query database using user params

        TextView songTitle = (TextView)findViewById(R.id.songTitle);
        TextView songArtist = (TextView)findViewById(R.id.songArtist);
        TextView songYear = (TextView)findViewById(R.id.songYear);

        db= new MyDatabase(this);


        Cursor cursor = db.kamikazeSelected();
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            songTitle.setText(cursor.getString(2));
            songArtist.setText(cursor.getString(3));
            songYear.setText(cursor.getInt(4));
        }
        cursor.close();

    }

This is the database file:

BEGIN TRANSACTION;
CREATE TABLE songList (
    `_id`   INTEGER PRIMARY KEY AUTOINCREMENT,
    `Title` TEXT,
    `Artist`    TEXT,
    `Decade`    INTEGER,
    `Year`  INTEGER,
    `Genre` TEXT
);
INSERT INTO songList VALUES (1,'Great Balls of Fire','Jerry Lee Lewis',50,1957,'Rock');
INSERT INTO songList VALUES (2,'Ring of Fire','Johnny Cash',60,1963,'Country');
INSERT INTO songList VALUES (3,'Shaft','Shaft',70,1971,'Blues');
INSERT INTO songList VALUES (4,'Hotel California','Eagles',70,1973,'Rock');
INSERT INTO songList VALUES (5,'Take On Me','AHA',80,1985,'Pop');
INSERT INTO songList VALUES (6,'Don''t Stop Believing','Journey',80,1981,'Rock');
INSERT INTO songList VALUES (7,'Friends in Low Places','Garth Brooks',90,1991,'Country');
INSERT INTO songList VALUES (8,'Baby Got Back','Sir Mix-A-Lot',90,1992,'Hip Hop');
INSERT INTO songList VALUES (9,'Hey Ya','OutKast',0,2000,'Hip Hop');
INSERT INTO songList VALUES (10,'I Kissed a Girl','Katy Perry',0,2007,'Pop');
INSERT INTO songList VALUES (11,'Just the way you are','Bruno Mars',10,2010,'Pop');
INSERT INTO songList VALUES (12,'Super Bass','Nicki Minaj',10,2010,'Hip Hop');
CREATE TABLE android_metadata ("locale" TEXT DEFAULT 'en_US');
INSERT INTO android_metadata VALUES ('en_US');
COMMIT;

I have looked on Stack Overflow and become very familiar with Google, but I can not figure this out. Please help.

2 Answers

The file you have isn't a database. It's a list of database commands that can be used to create a database.

What it looks like is you used the export option in SQLiteBrowser, when you really just want to use the save option. The file you end up with should have a .db extension, NOT a .sql extension.

Tom Schinler
Tom Schinler
21,052 Points

Patrick Corrigan Thank you. I ended up switching to the Mozilla Addon SQLite manager. It gave me an option to save whereas the one I was using before only gave me a save project with some custom extension. The database is now opening correctly.