Android SQLite INSERT causes application crash

Question

I am trying to insert some data into a table I created. I have this class:

public class DataBase extends SQLiteOpenHelper {
private static final String DB_NAME = "db_mydatabase";
private static final int DB_VERSION = 1;

private static final String TBL_USERS = "users";
private static final String TBL_USERSE_CREATE = "CREATE TABLE IF NOT EXISTS "
                            + TBL_USERS + "(id integer PRIMARY KEY AUTOINCREMENT, "
                            + "usr varchar(128) NOT NULL UNIQUE, psw varchar(512));";

public DataBase(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}

public List<String> GetUsersList() {
    List<String> users = new ArrayList<String>();
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.rawQuery("SELECT id, usr FROM " + TBL_USERS, null);
    if (c != null ) {
        if  (c.moveToFirst()) {
            do {
                String firstName = c.getString(c.getColumnIndex("USR"));
                users.add(firstName);
                } while (c.moveToNext());
        }
    }
    return users;
}

public void CreateNewUser(String username, String password) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("INSERT INTO " + TBL_USERS + " (usr, psw) VALUES ('" + username + "', '" + password + "')");
            //If I comment this line, it doesn't crashes anymore, but I don't see why
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(TBL_USERSE_CREATE);
}

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

}

So, when I press the REGISTER button, the username and password from a EditText are sent to CreateNewUser. My Activity class looks like this:

public class RegisterActivity extends Activity {
List<String> values;
DataBase dtb;
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.register);

    ListView listView = (ListView) findViewById(R.id.listView1);

    dtb = new DataBase(MainActivity.MainContext);
    values = dtb.GetUsersList();

    listView.setAdapter(new ArrayAdapter<String>(MainActivity.MainContext, R.layout.listitem, values));

    Button Create = (Button) findViewById(R.id.Create);
    Create.setOnClickListener(new View.OnClickListener() {
       public void onClick(View arg0) {
           EditText username = (EditText)findViewById(R.id.Username);
           EditText password = (EditText)findViewById(R.id.Password);
           if (username.getText().length()>0 && password.getText().length()>0) {
               if (values.indexOf(username.getText())<0) {
                   dtb.CreateNewUser(username.getText().toString(), password.getText().toString());
                   values = dtb.GetUsersList();
               }
           }
       }
    });
}

}

I am a beginner as a SQLite programmer and I get this understandable critical error and crash

> **04-24 12:30:51.340: E/CursorWindow(7986): Bad request for field slot 0,-1. numRows = 1, numColumns = 2** 04-24 12:30:51.350:
> D/AndroidRuntime(7986): Shutting down VM 04-24 12:30:51.350:
> W/dalvikvm(7986): threadid=3: thread exiting with uncaught exception
> (group=0x4001b188) 04-24 12:30:51.350: E/AndroidRuntime(7986):
> Uncaught handler: thread main exiting due to uncaught exception
> **04-24 12:30:51.380: E/AndroidRuntime(7986): java.lang.IllegalStateException: get field slot from row 0 col -1
> failed** 04-24 12:30:51.380: E/AndroidRuntime(7986):  at
> android.database.CursorWindow.getString_native(Native Method) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.database.CursorWindow.getString(CursorWindow.java:329) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:49)
> 04-24 12:30:51.380: E/AndroidRuntime(7986):   at
> com.bma.myagenda.DataBase.GetUsersList(DataBase.java:32) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> com.bma.myagenda.RegisterActivity$1.onClick(RegisterActivity.java:46)
> 04-24 12:30:51.380: E/AndroidRuntime(7986):   at
> android.view.View.performClick(View.java:2364) 04-24 12:30:51.380:
> E/AndroidRuntime(7986):   at
> android.view.View.onTouchEvent(View.java:4179) 04-24 12:30:51.380:
> E/AndroidRuntime(7986):   at
> android.widget.TextView.onTouchEvent(TextView.java:6541) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.view.View.dispatchTouchEvent(View.java:3709) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> com.android.internal.policy.impl.PhoneWindow$DecorView.superDispatchTouchEvent(PhoneWindow.java:1659)
> 04-24 12:30:51.380: E/AndroidRuntime(7986):   at
> com.android.internal.policy.impl.PhoneWindow.superDispatchTouchEvent(PhoneWindow.java:1107)
> 04-24 12:30:51.380: E/AndroidRuntime(7986):   at
> android.app.Activity.dispatchTouchEvent(Activity.java:2061) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> com.android.internal.policy.impl.PhoneWindow$DecorView.dispatchTouchEvent(PhoneWindow.java:1643)
> 04-24 12:30:51.380: E/AndroidRuntime(7986):   at
> android.view.ViewRoot.handleMessage(ViewRoot.java:1691) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.os.Handler.dispatchMessage(Handler.java:99) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> android.os.Looper.loop(Looper.java:123) 04-24 12:30:51.380:
> E/AndroidRuntime(7986):   at
> android.app.ActivityThread.main(ActivityThread.java:4363) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> java.lang.reflect.Method.invokeNative(Native Method) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> java.lang.reflect.Method.invoke(Method.java:521) 04-24 12:30:51.380:
> E/AndroidRuntime(7986):   at
> com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
> 04-24 12:30:51.380: E/AndroidRuntime(7986):   at
> com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618) 04-24
> 12:30:51.380: E/AndroidRuntime(7986):     at
> dalvik.system.NativeStart.main(Native Method)

Answer 1

Change:

String firstName = c.getString(c.getColumnIndex("USR"));

To:

String firstName = c.getString(c.getColumnIndex("usr"));  //USR != usr

Answer 2

change

String firstName = c.getString(c.getColumnIndex("USR"));

to

String firstName = c.getString(1);

the Error

CursorWindow(7986): Bad request for field slot 0,-1. numRows = 1, numColumns = 2
...java.lang.IllegalStateException: get field slot from row 0 col -1 failed

means that you request the column with index -1 in in the first row of a Cursor. The only part of your code that requests data from a Cursor is c.getString(columnIndex). That means that the columnIndex you get there must be -1. Now if you read the documentation of Cursor.getColumnIndex you see will find:

Returns the zero-based index for the given column name, or -1 if the column doesn't exist.

Meaning there is no column named "USR". If you look at your table definition you can see that you named it "usr". So to fix your error you can do

String firstName = c.getString(c.getColumnIndex("usr"));

But (that is optional and can lead to errors): Since you requested the Cursor as SELECT id, usr FROM .. you are guaranteed that id is column 0 and usr is column 1 in your Cursor and you can simply use the 1 directly.

There is also other things you could improve:

if (c != null ) {
    while(c.moveToNext()) {
        String firstName = c.getString(1);
        users.add(firstName);
    }
    c.close();
}

a) you can simplify the if () do {} while () to a simple while() {}
b) you should close the Cursor once you don't need it. You prevent at least a warning that way.

You should also make your INSERT save against errors / injection attacks by using the bindArgs version of execSQL. Try entering a ' in a username and you will see that it will crash the way you do it. Using bindArgs will prevent that since it automatically escapes the '.

db.execSQL("INSERT INTO " + TBL_USERS + " (usr, psw) VALUES (?, ?)", new String[]{ username, password });

If I comment this line, it doesn't crashes anymore, but I don't see why

That is because the Cursor is empty then and you don't reach the code that caused the error.

One last thing: varchar(128) has no effect in SQLite since:

Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions on the length of strings, BLOBs or numeric values. source

Answer 3

Try to replace your insert code with this:

public void CreateNewUser(String username, String password) {
    SQLiteDatabase db = this.getWritableDatabase();

ContentValues initialValues = new ContentValues();
initialValues.put(usr, username);
initialValues.put(psw, password);

db.insert(TBL_USERS, null, initialValues);
}