Loading data from SQLite database doesn't work

Question

I have this database called "TabliceBaza"

public class TabliceBaza extends SQLiteOpenHelper {

public static final String DATABASE_NAME="Tablica.db";
public static final String TABLE_NAME="Tablice_table";
public static final String COL1="_id";
public static final String COL2="Tablica";

public TabliceBaza(Context context) {
    super(context,DATABASE_NAME,null,1);
}

@Override
public void onCreate(SQLiteDatabase db) {

 db.execSQL("CREATE TABLE "+TABLE_NAME+"("+COL1+"INT PRIMARY KEY,"+COL2+"TEXT)");


}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

public String loadHandler()
{
    String result = "";
    String query = "SELECT * FROM "+TABLE_NAME;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);
    while (cursor.moveToNext()) {
        int result_0 = cursor.getInt(0);
        String result_1 = cursor.getString(1);

        result += String.valueOf(result_0) + " " + result_1 +
                System.getProperty("line.separator");
    }
    cursor.close();
    db.close();
    return result;

}
public void addHandler(Tablice tablice) {
    ContentValues values = new ContentValues();
    values.put(COL1, tablice.getTablicaID());
    values.put(COL2,tablice.getTablicaIme());
    SQLiteDatabase db = this.getWritableDatabase();
    db.insert(TABLE_NAME, null, values);
    db.close();
}

In the activity class i want to show all values from my database and set it to TextView

Here is code for ButtonClick in activity

   TabliceBaza tb=new TabliceBaza(this);
   TextView txt=(TextView)findViewById(R.id.textView3);

   Tablice t=new Tablice(1,"Misko00");
   tb.addHandler(t);
   txt.setText(tb.loadHandler());

As you can see, when i click on button , it should add a "Tablice" object in database and then show it, but it shows nothing.

Answer 1

The main issue you have is that you have omitted spaces between the column names and the column types. This will work but will result in the column names being the given column name concatenated with the type. So when you run the App you will have a table named Tablice_table that has two columns name _idINT and TablicaTEXT.

When the button is clicked then no record will be inserted because columns _id and Tablica do not exist.

If you looked in the log then you would see something similar to (Note this does not crash the App):-

06-03 20:29:56.109 1143-1143/soanswers.soanswers E/SQLiteLog: (1) table Tablice_table has no column named _id
06-03 20:29:56.109 1143-1143/soanswers.soanswers E/SQLiteDatabase: Error inserting _id=1 Tablica=Test
    android.database.sqlite.SQLiteException: table Tablice_table has no column named _id (code 1): , while compiling: INSERT INTO Tablice_table(_id,Tablica) VALUES (?,?)
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
        at soanswers.soanswers.TabliceBaza.addHandler(TabliceBaza.java:56)
        at soanswers.soanswers.MainActivity$1.onClick(MainActivity.java:529)
        at android.view.View.performClick(View.java:4084)
        at android.view.View$PerformClick.run(View.java:16966)
        at android.os.Handler.handleCallback(Handler.java:615)
        at android.os.Handler.dispatchMessage(Handler.java:92)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4745)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
        at dalvik.system.NativeStart.main(Native Method)

The fix for this issue is to amend the code to include the spaces when the table is created e.g. :-

db.execSQL("CREATE TABLE " + TABLE_NAME + "(" + COL1 + " INT PRIMARY KEY," + COL2 + " TEXT)");

And to then either delete the App's data or to uninstall the App.

After either deleting the App's data or uninstalling the App an rerunning, the code you have provided (after adding the spaces to the create table statement) works.

However, if you click the button a second time then you will see a message in the log (Again this does not crash the App) along the lines of :-

06-03 20:38:24.451 1256-1256/soanswers.soanswers E/SQLiteDatabase: Error inserting _id=1 Tablica=Test
    android.database.sqlite.SQLiteConstraintException: column _id is not unique (code 19)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:775)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
        at soanswers.soanswers.TabliceBaza.addHandler(TabliceBaza.java:54)
        at soanswers.soanswers.MainActivity$1.onClick(MainActivity.java:529)
        at android.view.View.performClick(View.java:4084)
        at android.view.View$PerformClick.run(View.java:16966)
        at android.os.Handler.handleCallback(Handler.java:615)
        at android.os.Handler.dispatchMessage(Handler.java:92)
        at android.os.Looper.loop(Looper.java:137)
        at android.app.ActivityThread.main(ActivityThread.java:4745)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:511)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
        at dalvik.system.NativeStart.main(Native Method)

This is because the _id is a primary key and must be unqiue so trying to add a row with _id as 1 again will fail.