I am trying to make a program that will take an SQLite database I already made (called os.sqlite) and be able to read/write that database. I followed code from here and Eclipse gives me no errors in the process of copying the database, it seems, but when I try to run my application and display the table "OregonState," LogCat (I am using Eclipse Juno with the ADT plug-in and running my applications on the SDK emulator) keeps telling me that there is no table named "OregonState," which is a table in the database that I know I created and put into my assets folder in the project (I can even see the file sitting there). After more failed attempts, I noticed LogCat also saying that it failed to open the database because the database is locked. That was pointed out to be that I might be properly opening and closing, but with those added, my application crashes. Anyone able to help?
I created an Adapter (that also contains an SQLiteHelper nested class which copies the database) to access the database and allow me to insert/delete/etc. to the table. My main activity then uses this adapter. Here is my code (I've included all of it, excluding import and package names) for the sake of being thorough and complete):
public class SQLAdapter
{
private static final String TABLE_OSU = "OregonState";
private static final String COLUMN_ID = "_id";
private static final String COLUMN_NAME = "Name";
private static final String COLUMN_FIELD = "Field";
private static final String DATABASE_PATH = "/data/data/com.example.sql2/databases/";
private static final String DATABASE_NAME = "os.sqlite";
private static final int DATABASE_VERSION = 1;
MySQLiteHelper helper;
private final Context context;
public SQLAdapter(Context context)
{
this.context = context;
helper = new MySQLiteHelper(context);
}
private class MySQLiteHelper extends SQLiteOpenHelper
{
private final Context context;
public MySQLiteHelper(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase database)
{
try {
createDatabase();
} catch (IOException e) {
e.printStackTrace();
}
}
public void createDatabase() throws IOException
{
boolean dbExist = checkDatabase();
if (dbExist)
{
// Nothing
}
else
{
copyDatabase();
}
}
public boolean checkDatabase()
{
SQLiteDatabase checkDB = null;
try
{
String path = DATABASE_PATH + DATABASE_NAME;
checkDB = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
} catch (SQLiteException e) {}
if (checkDB != null)
checkDB.close();
return checkDB != null ? true : false;
}
public void copyDatabase() throws IOException
{
InputStream input = context.getAssets().open(DATABASE_NAME);
String outFileName = DATABASE_PATH + DATABASE_NAME;
OutputStream output = new FileOutputStream(outFileName);
byte[] buffer = new byte[1024];
int length;
while ((length = input.read(buffer))>0)
output.write(buffer, 0, length);
output.flush();
output.close();
input.close();
}
public void openDatabase()
{
String path = DATABASE_PATH + DATABASE_NAME;
SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
SQLiteDatabase database = this.getWritableDatabase();
Log.w(DATABASE_NAME, "Upgrading database from " + oldVersion + " to " + newVersion + ", which will erase all data.");
database.execSQL("DROP TABLE IF EXISTS " + TABLE_OSU);
onCreate(database);
database.close();
}
}
public SQLiteDatabase open() throws SQLException
{
SQLiteDatabase database = helper.getWritableDatabase();
return database;
}
public void close()
{
helper.close();
}
public long insert(SQLiteDatabase database, String name, String field)
{
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, name);
values.put(COLUMN_FIELD, field);
long x = database.insert(TABLE_OSU, null, values);
database.close();
return x;
}
public int delete(SQLiteDatabase database, int id)
{
int x = database.delete(TABLE_OSU, COLUMN_ID + "=" + id, null);
return x;
}
public Cursor getAll(SQLiteDatabase database)
{
return database.query(TABLE_OSU, new String[] {COLUMN_ID, COLUMN_NAME, COLUMN_FIELD}, null, null, null, null, null);
}
public Cursor get(SQLiteDatabase database, int id) throws SQLException
{
return database.query(TABLE_OSU, new String[] {COLUMN_ID, COLUMN_NAME, COLUMN_FIELD}, COLUMN_ID + "=" + id, null, null, null, null);
}
public int update(SQLiteDatabase database, int id, String name, String field)
{
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, name);
values.put(COLUMN_FIELD, field);
return database.update(TABLE_OSU, values, COLUMN_ID + "=" + id, null);
}
}
public class SQLTest extends Activity
{
SQLAdapter adapter;
@Override
public void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
adapter = new SQLAdapter(this);
getAll();
}
@Override
public boolean onCreateOptionsMenu(Menu menu)
{
getMenuInflater().inflate(R.menu.activity_sqltest, menu);
return true;
}
public long insert(String name, String field)
{
SQLiteDatabase database = adapter.open();
long id = adapter.insert(database, name, field);
adapter.close();
return id;
}
public int delete(int id)
{
SQLiteDatabase database = adapter.open();
int rowsDeleted = adapter.delete(database, id);
adapter.close();
return rowsDeleted;
}
public void getAll()
{
SQLiteDatabase database = adapter.open();
TextView tv = new TextView(this);
String table = "";
try
{
Cursor cursor = adapter.getAll(database);
if (cursor.moveToFirst())
{
do
{
table += "\n" + cursor.getString(0) + " " + cursor.getString(1) + " " + cursor.getString(2);
} while (cursor.moveToNext());
}
cursor.close();
} catch (Exception e) {}
tv.setText(table);
setContentView(tv);
adapter.close();
}
public void get(int id)
{
SQLiteDatabase database = adapter.open();
Cursor cursor = adapter.get(database, id);
TextView tv = new TextView(this);
String table = "";
if (cursor.moveToFirst())
table += "\n" + cursor.getString(0) + " " + cursor.getString(1) + " " + cursor.getString(2);
else
table += "No hall found with ID: " + id;
tv.setText(table);
setContentView(tv);
cursor.close();
adapter.close();
}
public int update(int id, String name, String field)
{
SQLiteDatabase database = adapter.open();
int rowsUpdated = adapter.update(database, id, name, field);
adapter.close();
return rowsUpdated;
}
}
I noticed this guy had a similar issue, although mine has never actually worked, whereas his did. I followed his code a little bit, and that only made my app start to crash. I would simply just comment, but I'm new here, so not enough reputation.
