Just changed my data that are hard-code into database to using SQLite Database Browser. Before I changed all my query works fine but errors occurs when I changed. I can't do those query anymore. The database can only read but not write. I was trying to insert an entry when customer order food into table "Bill" but failed. my database file is at assets/Restaurantdb.sql
One more strange discovery is that I uninstall the whole thing and change some food name in my "FOOD"table (like Burger to Burger1), it show Burger instead of Burger1.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ This is my lastest code but it endup got this new error. My database file in assets folder named "Restaurantdb. Error: No such table Food when retrieve name from food. +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
package com.restaurant.sesame;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;
public class Restaurant {
public static final String KEY_ROWID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_DETAIL = "detail";
public static final String KEY_PRICE = "price";
private static String DATABASE_NAME ="Restaurantdb";
private static String DATABASE_TABLE ="Food";
private static final int DATABASE_VERSION = 1;
private static String DB_PATH = "/data/data/com.restaurant.sesame/databases/";
private static SQLiteDatabase ourDatabase;
private static Context myContext;
private DBHelper ourHelper;
private static class DBHelper extends SQLiteOpenHelper{
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public void createDataBase() throws IOException{
boolean dbExist = checkDataBase();
if(dbExist){
//do nothing - database already exist
}else{
//By calling this method and empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
this.getReadableDatabase();
try {
copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}
/**
* Check if the database already exist to avoid re-copying the file each time you open the application.
* @return true if it exists, false if it doesn't
*/
private boolean checkDataBase(){
SQLiteDatabase checkDB = null;
try{
String myPath = DB_PATH + DATABASE_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}catch(SQLiteException e){
/* database does't exist yet. */
}
if(checkDB != null){
checkDB.close();
}
return checkDB != null ? true : false;
}
/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* This is done by transfering bytestream.
* */
private void copyDataBase() throws IOException{
//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DATABASE_NAME);
// Path to the just created empty db
String outFileName = DB_PATH + DATABASE_NAME;
//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer))>0){
myOutput.write(buffer, 0, length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException{
//Open the database
String myPath = DB_PATH + DATABASE_NAME;
ourDatabase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}
@Override
public synchronized void close() {
if(ourDatabase != null)
ourDatabase.close();
super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
public Restaurant(Context c)
{
myContext = c;
DBHelper helper = new DBHelper (myContext);
this.ourDatabase = helper.getWritableDatabase();
}
public Restaurant open() throws SQLException
{
ourHelper = new DBHelper (myContext);
try {
ourHelper.createDataBase();
} catch (IOException ioe) {
throw new Error("Unable to create database");
}
try {
ourHelper.openDataBase();
}catch(SQLException sqle){
throw sqle;
}
return this;
}
public void close()
{
ourHelper.close();
}
public String[] getWantedInfos(String KEY_WANTED){
String[]columns = new String[]{KEY_WANTED};
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
String [] wanted = new String[c.getCount()] ;
int i = 0,
iWanted = c.getColumnIndex(KEY_WANTED);
for(c.moveToFirst();!c.isAfterLast();c.moveToNext())
{
wanted[i] = c.getString(iWanted);
++i;
}
return wanted;
}
public String getOneInfo(String id, String info)
{
Log.w("my app", "IN getOneInfo TOP!!!!");
String [] columns = new String [] {id,info};
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + " = "+ id, null, null, null, null);
Log.w("getOneInfo", "IN getOneInfo after query!!!!");
String result ="";
int iRow = c.getColumnIndex(id);
int iInfo = c.getColumnIndex(info);
if(c!=null){
c.moveToFirst();
result =c.getString(iInfo);
return result;
}
return null;
}
public String getBillInfo(String name, String info)
{
Log.w("getBillInfo", "IN getBillInfo TOP!!!!");
String [] columns = new String [] {KEY_NAME,info};
Log.w("getBillInfo ","pass in"+ name);
Log.w("getBillInfo", "pass in"+info);
Cursor c = ourDatabase.query("Bill", columns, "name = '"+ name+ "'", null, null, null, null);
Log.w("getBillInfo", "IN getBillInfo after query!!!!");
String result ="";
int iInfo = c.getColumnIndex(info);
Log.w("getBillInfo", "IN getBillInfo before loop!!!!");
for(c.moveToFirst();!c.isAfterLast();c.moveToNext())
{
Log.w("getBillInfo", "IN getBillInfo inside loop!!!!");
c.moveToFirst();
result =c.getString(iInfo);
Log.w("getBillInfo", "IN getBillInfo XXXXnull!!!!");
Log.w("getBillInfo", "quantity in bill is ==>"+result);
return result;
}
Log.w("getBillInfo", "IN getBillInfo null!!!!");
return "0";
}
public String getBillTotal()
{
Log.w("Bill Total", "YYYY33333");
Cursor c = ourDatabase.rawQuery(
"SELECT SUM(total)as total FROM Bill", null);
Log.w("Bill Total", "YYYY4444");
String result ="";
int iInfo = c.getColumnIndex("total");
Log.w("Bill Total", "YYYY5555");
if(c!=null){
c.moveToFirst();
result =c.getString(iInfo);
return result;
}
return "0.00";
}
public String getBillData()
{
Log.w("my app", "IN getData TOP!!!!");
String [] columns = new String [] {"_id","name","price","quantity","total"};
Cursor c = ourDatabase.query("Bill", columns, null, null, "name", null, null);
String result ="";
int iRow = c.getColumnIndex("_id");
int iName = c.getColumnIndex("name");
int iPrice = c.getColumnIndex("price");
int iQuantity = c.getColumnIndex("quantity");
int iTotal = c.getColumnIndex("total");
Log.w("my app", "IN getData MIDDLE!!!!");
int i = 1;
for(c.moveToFirst();!c.isAfterLast();c.moveToNext())
{
result = result + Integer.toString(i)+"\t" +
c.getString(iName) + "\t" +
c.getString(iPrice) +"\t" +
c.getString(iQuantity) + "\t" +
c.getString(iTotal) + "\n";
i++;
}
Log.w("my app", "IN getData END!!!!");
return result;
}
public long createOrderEntry(int id, int quantityItem)
{
String [] columns = new String [] {KEY_ROWID,KEY_NAME,KEY_PRICE};
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + Integer.toString(id) , null, null, null, null);
if(c!= null)
{
ContentValues cv = new ContentValues();
c.moveToFirst();
String nameItem = c.getString(1);
double priceItem = c.getDouble(2);
double totalItem = priceItem *quantityItem;
cv.put("name", nameItem);
cv.put("price", priceItem);
cv.put("quantity", quantityItem);
cv.put("total", totalItem);
return ourDatabase.insert("Bill", null, cv);
}
return 0;
}
public boolean deleteOrderEntry(int lRow)
{
String [] columns = new String [] {KEY_ROWID,KEY_NAME,KEY_PRICE};
Log.w("my app", "deleteOrderEntry Top");
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "= " + Integer.toString(lRow) , null, null, null, null);
Log.w("my app", "deleteOrderEntry 2");
if(c!= null)
{
c.moveToFirst();
String nameItem = c.getString(1);
ourDatabase.delete("Bill", "name = '"+ nameItem+ "'", null);
Log.w("my app", "deleteOrderEntry MIDDLE");
return true;
}
return false;
}
public void truncateBill(){
ourDatabase.execSQL("DELETE FROM Bill");
ourDatabase.execSQL("VACUUM");
}
}
