taking data from Android-sql

Question

i made an database for photos in android. Code is below.

public class PhotoDataHandler extends SQLiteOpenHelper {
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 77;

    // Database Name
    private static final String DATABASE_NAME = "photos";

    // DATA table name
    private static final String TABLE_PHOTOS = "PHOTOS";

    // DATA Table Columns names
    private static final String KEY_ID = "id";
    private static final String PHOTO_ID = "photo_id";
    private static final String KEY_JSON = "json";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_DATA_TABLE = "CREATE TABLE " + TABLE_PHOTOS + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_JSON + " TEXT"
                + PHOTO_ID + " TEXT" + ")";
        db.execSQL(CREATE_DATA_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PHOTOS);

        // Create tables again
        onCreate(db);
    }

    // truncate all data
    public void truncateAllData() {
        SQLiteDatabase db = this.getReadableDatabase();

        db.execSQL("DELETE FROM " + TABLE_PHOTOS);
        db.close();
    }

    // Add new DATA
    public void addPhoto(Photo photo) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_JSON, photo.getID());
        values.put(PHOTO_ID, photo.getphotoId());
        values.put(KEY_JSON, photo.getPhotoString());
        // Inserting Row
        db.insert(TABLE_PHOTOS, null, values);
        db.close(); // Closing database connection
    }

    public Photo getLastPhotos() {
        SQLiteDatabase db = this.getReadableDatabase();

        String selectQuery = "SELECT * FROM " + TABLE_PHOTOS
                + " ORDER BY id DESC LIMIT 1";

        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.getCount() != 0) {
            cursor.moveToFirst();
            Photo photo = new Photo(cursor.getString(1));
            db.close();
            return photo;
        } else {
            Photo photo = new Photo();
            db.close();
            return photo;
        }
    }

    // Getting All DATA
    public List<Photo> getAllData() {
        List<Photo> PhotoList = new ArrayList<Photo>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_PHOTOS;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Photo photo = new Photo();
                photo.setID(Integer.parseInt(cursor.getString(0)));
                photo.setPhotoString((cursor.getString(1)));

//error is in this line : from logcat
    **photo.setphotoId((Integer.parseInt(cursor.getString(2))));**


                // Adding contact to list
                PhotoList.add(photo);
            } while (cursor.moveToNext());
        }

        // return user list
        db.close();
        return PhotoList;
    }
}

i am adding photos with this method :

final PhotoDataHandler photodb = new PhotoDataHandler(context.getApplicationContext());

photodb.addPhoto(new Photo(61, 8, "String"));
photodb.addPhoto(new Photo(62, 9, "String of photo"));

when i want to get all date there is in data base i use method writen in sql PhotoDataHandler class:

String alldata = "null";
                List<Photo> photos = photodb.getAllData();
                int nr_photos=0;
                for(Photo x:photos){
                    System.out.println("nr of line : " + nr_photos +" photos id "+x.getID()+ "Stringas yra : "+ x.getPhotoString()+ " dar vienas id yra : " + x.getphotoId()) ;
                    nr_photos++;
                }

and i get an eror for every time i use that for. log cat looks like this :

05-08 11:16:52.489: E/SQLiteLog(2256): (1) table PHOTOS has no column named photo_id
05-08 11:16:52.499: E/SQLiteDatabase(2256): Error inserting id=131 photo_id=62 json=vienas
05-08 11:16:52.499: E/SQLiteDatabase(2256): android.database.sqlite.SQLiteException: table PHOTOS has no column named photo_id (code 1): , while compiling: INSERT INTO PHOTOS(id,photo_id,json) VALUES (?,?,?)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at com.project.sqlite.PhotoDataHandler.addPhoto(PhotoDataHandler.java:68)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at com.project.elements.Element$3.onClick(Element.java:730)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.view.View.performClick(View.java:4438)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.view.View$PerformClick.run(View.java:18422)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.os.Handler.handleCallback(Handler.java:733)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.os.Handler.dispatchMessage(Handler.java:95)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.os.Looper.loop(Looper.java:136)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at android.app.ActivityThread.main(ActivityThread.java:5017)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at java.lang.reflect.Method.invokeNative(Native Method)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at java.lang.reflect.Method.invoke(Method.java:515)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
05-08 11:16:52.499: E/SQLiteDatabase(2256):     at dalvik.system.NativeStart.main(Native Method)

Could someone more experienced tell me whats the problem ??

M D · Accepted Answer · 2014-05-08 08:24:54Z

3

You go wrong over here

+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_JSON + " TEXT" // forget to add , comma after TEXT

Correct with below

String CREATE_DATA_TABLE = "CREATE TABLE " + TABLE_PHOTOS + "("
            + KEY_ID + " INTEGER PRIMARY KEY, " + KEY_JSON + " TEXT, "
            + PHOTO_ID + " TEXT" + ")";

answered May 8, 2014 at 8:24

M D

47.9k9 gold badges96 silver badges116 bronze badges

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

4 Comments

user3416113 Over a year ago

if i change integer primary key with text. problem remains the same

M D Over a year ago

@user3416113 why you changed that? you just correct your create table command as per my answer and you must uninstall your app and again install so that older DB deleted and newer DB is Create

user3416113 Over a year ago

but what was the problem ? a one space after comma ?

M D Over a year ago

your forget the comma that why your column not created. and you insert data on that column so it's not find any column with photo_id

Collectives™ on Stack Overflow

taking data from Android-sql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related