Click here to Skip to main content
15,907,395 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi friends,

i tried a android project which displays listview with image and text from database.
I hope everything fine but it couldn't works. please help me solve this..

I also used a bean class called mobile.

This my java class:
package com.example.a;

import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.support.v4.widget.SimpleCursorAdapter;
import android.view.View;
import android.widget.ImageView;
import android.widget.ListView;

public class MainActivity extends Activity {
public DBhelper mdb;
ListView l;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mdb = new DBhelper(this);
mdb.Reset();
l=(ListView) findViewById(R.id.list);

Bitmap img1 = BitmapFactory.decodeResource(getResources(), R.drawable.food);
Bitmap img2 = BitmapFactory.decodeResource(getResources(), R.drawable.taxi);
Bitmap img3 = BitmapFactory.decodeResource(getResources(), R.drawable.tickets);
Bitmap img4 = BitmapFactory.decodeResource(getResources(), R.drawable.telephoneandpostal);
Bitmap img5 = BitmapFactory.decodeResource(getResources(), R.drawable.bankreport);
Bitmap img6 = BitmapFactory.decodeResource(getResources(), R.drawable.emergencylines);
Bitmap img7 = BitmapFactory.decodeResource(getResources(), R.drawable.police);
Bitmap img8 = BitmapFactory.decodeResource(getResources(), R.drawable.ambulanc);
Bitmap img9 = BitmapFactory.decodeResource(getResources(), R.drawable.hospital);
Bitmap img10 = BitmapFactory.decodeResource(getResources(), R.drawable.hospital);
Bitmap img11 = BitmapFactory.decodeResource(getResources(), R.drawable.hospital);
Bitmap img12 = BitmapFactory.decodeResource(getResources(), R.drawable.vehicle);
Bitmap img13 = BitmapFactory.decodeResource(getResources(), R.drawable.informationline);
Bitmap img14 = BitmapFactory.decodeResource(getResources(), R.drawable.homeemergency);
Bitmap img15 = BitmapFactory.decodeResource(getResources(), R.drawable.hdbura);
Bitmap img16 = BitmapFactory.decodeResource(getResources(), R.drawable.suicidesos);
Bitmap img17 = BitmapFactory.decodeResource(getResources(), R.drawable.ambulanc);
Bitmap img18 = BitmapFactory.decodeResource(getResources(), R.drawable.environmen);
Bitmap img19 = BitmapFactory.decodeResource(getResources(), R.drawable.traveltour);

mdb.category(new mobile(img1, "FOOD", "10 records"));
mdb.category(new mobile(img2, "TAXI", "10 records"));
mdb.category(new mobile(img3, "TICKET", "10 records"));
mdb.category(new mobile(img4, "TELEPHONE", "10 records"));
mdb.category(new mobile(img5, "BANK", "10 records"));
mdb.category(new mobile(img6, "EMERGENCY", "10 records"));
mdb.category(new mobile(img7, "POLICE", "10 records"));
mdb.category(new mobile(img8, "AMBULANC", "10 records"));
mdb.category(new mobile(img9, "HOSPITAL", "10 records"));
mdb.category(new mobile(img10, "HOSPITAL", "10 records"));
mdb.category(new mobile(img11, "HOSPITAL", "10 records"));
mdb.category(new mobile(img12, "VEHICLE", "10 records"));
mdb.category(new mobile(img13, "INFORMATION", "10 records"));
mdb.category(new mobile(img14, "HOMEEMERGENCY", "10 records"));
mdb.category(new mobile(img15, "HDBURA", "10 records"));
mdb.category(new mobile(img16, "SUICIDE", "10 records"));
mdb.category(new mobile(img17, "AMBULANCE", "10 records"));
mdb.category(new mobile(img18, "ENVIRONMENT", "10 records"));
mdb.category(new mobile(img19, "TRAVELTOUR", "10 records"));


String[] columns = {mdb.KEY_ID,mdb.KEY_IMG,mdb.KEY_NAME,mdb.KEY_DESC};

String table = mdb.NUMBERS_TABLE;

Cursor c = mdb.getHandle().query(table, columns, null,null,null,null,null);

startManagingCursor(c);

@SuppressWarnings("deprecation")
SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,R.layout.home,c,
new String [] {mdb.KEY_IMG,mdb.KEY_NAME,mdb.KEY_DESC},
new int [] {R.id.numimg,R.id.numtxt,R.id.numtxt1});
adapter.setViewBinder(new numberviewbinder());
l.setAdapter(adapter);

}

public class numberviewbinder implements SimpleCursorAdapter.ViewBinder {
public boolean setViewValue(View view, Cursor cursor, int columnIndex) {

if (view instanceof ImageView) {
ImageView iv = (ImageView) view;
byte[] numimg = cursor.getBlob(columnIndex);
iv.setImageBitmap(BitmapFactory.decodeByteArray(numimg, 0,numimg.length));
return true;
} else {

return false;
}
}
}


}

this is my db class:
package com.example.a;

import java.io.ByteArrayOutputStream;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.provider.BaseColumns;

public class DBhelper {

public static final String KEY_ID = BaseColumns._ID;
public static final String KEY_NAME = "name";
public static final String KEY_DESC = "desc";
public static final String KEY_IMG = "image";

private DataBaseHelper mDBhelper;
private SQLiteDatabase db;

private static final String DATABASE_NAME="phone";
private static final int DATABASE_VERSION = 1;

public static final String NUMBERS_TABLE="number";

public static final String CREATE_NUMBERS_TABLE= "create table "
+ NUMBERS_TABLE +"("
+ KEY_ID + " Integer primarykey,"
+ KEY_IMG + "BLOB,"
+ KEY_NAME + "TEXT,"
+ KEY_DESC + "TEXT)";
private Context con;

private boolean opened=false;

private static class DataBaseHelper extends SQLiteOpenHelper{

public DataBaseHelper(Context cont) {
super(cont, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_NUMBERS_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS" + NUMBERS_TABLE);
onCreate(db);

}

}
public void Reset(){
openDB();
mDBhelper.onUpgrade(this.db, 1, 1);
}
public DBhelper(Context context) {
con=context;
mDBhelper=new DataBaseHelper(con);

}
private SQLiteDatabase openDB() {
if(!opened)
db = mDBhelper.getWritableDatabase();
opened = true;
return db;
}

public SQLiteDatabase getHandle() { return openDB(); }

private void closeDB() {
if(opened)
mDBhelper.close();
opened = false;
}

public void category(mobile mob){
openDB();
ByteArrayOutputStream mobout = new ByteArrayOutputStream();
mob.getImage().compress(Bitmap.CompressFormat.PNG, 100, mobout);
ContentValues mobcv = new ContentValues();
mobcv.put(KEY_IMG, mobout.toByteArray());
mobcv.put(KEY_NAME, mob.getName());
mobcv.put(KEY_DESC, mob.getDesc());
db.insert(NUMBERS_TABLE, null, mobcv);
closeDB();

}


}

this is my Logcat output:
10-07 01:27:59.786: E/AndroidRuntime(1981): FATAL EXCEPTION: main
10-07 01:27:59.786: E/AndroidRuntime(1981): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.a/com.example.a.MainActivity}: android.database.sqlite.SQLiteException: near "EXISTSnumber": syntax error (code 1): , while compiling: DROP TABLE IF EXISTSnumber
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2211)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2261)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.app.ActivityThread.access$600(ActivityThread.java:141)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1256)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.os.Handler.dispatchMessage(Handler.java:99)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.os.Looper.loop(Looper.java:137)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.app.ActivityThread.main(ActivityThread.java:5103)
10-07 01:27:59.786: E/AndroidRuntime(1981): at java.lang.reflect.Method.invokeNative(Native Method)
10-07 01:27:59.786: E/AndroidRuntime(1981): at java.lang.reflect.Method.invoke(Method.java:525)
10-07 01:27:59.786: E/AndroidRuntime(1981): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
10-07 01:27:59.786: E/AndroidRuntime(1981): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
10-07 01:27:59.786: E/AndroidRuntime(1981): at dalvik.system.NativeStart.main(Native Method)
10-07 01:27:59.786: E/AndroidRuntime(1981): Caused by: android.database.sqlite.SQLiteException: near "EXISTSnumber": syntax error (code 1): , while compiling: DROP TABLE IF EXISTSnumber
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1672)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1603)
10-07 01:27:59.786: E/AndroidRuntime(1981): at com.example.a.DBhelper$DataBaseHelper.onUpgrade(DBhelper.java:51)
10-07 01:27:59.786: E/AndroidRuntime(1981): at com.example.a.DBhelper.Reset(DBhelper.java:59)
10-07 01:27:59.786: E/AndroidRuntime(1981): at com.example.a.MainActivity.onCreate(MainActivity.java:22)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.app.Activity.performCreate(Activity.java:5133)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
10-07 01:27:59.786: E/AndroidRuntime(1981): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2175)
10-07 01:27:59.786: E/AndroidRuntime(1981): ... 11 more
10-07 01:28:14.176: I/Process(1981): Sending signal. PID: 1981 SIG: 9
10-07 01:29:30.447: E/SQLiteLog(2030): (1) near "EXISTSnumber": syntax error
10-07 01:29:30.667: D/AndroidRuntime(2030): Shutting down VM
10-07 01:29:30.667: W/dalvikvm(2030): threadid=1: thread exiting with uncaught exception (group=0xb3f0e648)
Posted

1 solution

I know diddly squat about Android - but it seems to be complaining about executing this :-

Java
@Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 db.execSQL("DROP TABLE IF EXISTS" + NUMBERS_TABLE);
 onCreate(db);


So, assuming
a) 'number' is a valid name for a table,
b) 'DROP TABLE IF EXISTS' is valid in Sqlite

I think you need a space between EXISTS and the "

so it looks like


Java
@Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 db.execSQL("DROP TABLE IF EXISTS " + NUMBERS_TABLE);
 onCreate(db);



Sqlite is very fussy about spaces and the like
 
Share this answer
 
v2
Comments
Garth J Lancaster 7-Oct-13 3:27am    
ok, instead of being smart, just hard-code the statement to start with :-

"DROP TABLE IF EXISTS numbers"

and see if it likes that
Garth J Lancaster 7-Oct-13 3:30am    
the other thing, is, some SQLite statements need to end with a ';' - so try also

"DROP TABLE IF EXISTS numbers;"

'g'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900