Why am I not able to fetch the code from the DataBase?

Question

I'm unable to fetch the data although I'm able to add data. I have two questions here: What is the purpose of the primary key and how can I implement it? In my code, if I give the same values multiple times then it also gets accepted. //Check this statement

db.execSQL("create table mtable (_Id Integer  auto_increment, name Text Primary key , mail Text)");

Now,my 2nd major problem..

Why am I not able to fetch the data from the database ? My two java classes are as follows:

MainActivity.java

package com.apna.mydatabase;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends Activity  implements OnClickListener {

    Button save,fetch,update,Delete;
    EditText edtname,edtmail,afetch;
    MySqlOpenHelper ab,db;
    SQLiteDatabase sql,sql1;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        edtname=(EditText) findViewById(R.id.editText1);
        edtmail=(EditText)findViewById(R.id.editText2);
        afetch=(EditText) findViewById(R.id.editText3);
    save=(Button) findViewById(R.id.button1);
    fetch=(Button) findViewById(R.id.button2);
    update=(Button) findViewById(R.id.button3);
Delete=(Button)findViewById(R.id.button4);
    save.setOnClickListener(this);
    fetch.setOnClickListener(this);
    ab=new MySqlOpenHelper(MainActivity.this);
    sql=ab.getWritableDatabase();

    }
    @Override
    public void onClick(View v) {
        // TODO Auto-generated method stub
        switch(v.getId())
        {
        case R.id.button1:
            ContentValues cv=new ContentValues();
            cv.put("name", edtname.getText().toString());
            cv.put("mail",edtmail.getText().toString());
            long result=sql.insert("mtable", "nullCo", cv);     
        if (result>0)
        {            
            Toast.makeText(MainActivity.this, "Saved at"+result, 5000).show();
            edtname.setText(" ");
            edtmail.setText(" ");
        }
        else 
        {
            Toast.makeText(MainActivity.this, "Not Saved"+result, 5000).show();
        }
        break;

        case R.id.button2:
            db= new  MySqlOpenHelper(MainActivity.this);
            sql1=db.getReadableDatabase();
            String a=afetch.getText().toString();

            Cursor c=sql1.rawQuery("Select * from mtable where _id ="+a,null );
            String str=null,str1=null;
            if(c.getCount()>0)
            {
            while(c.moveToNext())
            {
                str=c.getString(c.getColumnIndex("name"));
                str1=c.getString(c.getColumnIndex("mail")); 
            }

            edtname.setText(str);
            edtmail.setText(str1);          
            }
    else
            {
            Toast.makeText(MainActivity.this,"Invalid record",5000 ).show();
            }


}

    }
    }

MySqlOpenHelper.java

package com.apna.mydatabase;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class MySqlOpenHelper extends SQLiteOpenHelper {

    public MySqlOpenHelper(Context context)
    {
        super(context, "Mynew", null,1);
            }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("create table mtable (_Id Integer  auto_increment, name Text Primary key , mail Text)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }

}

It would be my pleasure to get the required results.

Try this (to have both `_id` and `name` as primary key): http://stackoverflow.com/a/734704/2649012 — Phantômaxx, Jun 24 '15 at 11:14

Syed Osama Maruf · Answer 1 · 2015-06-25T20:24:57.323

what the purpose of primary key

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.

A primary key’s main features are:

It must contain a unique value for each row of data.
It cannot contain null values. See reference.

how to implement it

Primary Key can also be a composite key meaning two columns say in your case _Id and name could both be primary key. Satisfying the above conditions.

EDIT: It is not advisable to have only name as the primary key since names can recur and defeat the purpose of the primary key. For a composite key you could :

db.execSQL("create table mtable (_Id Integer  auto_increment, name Text not null,primary key (_Id,name) , mail Text)")

Simply put I have achieved it as:

create table mtable (_Id Integer  identity, name varchar not null,primary key (_Id,name) , mail Text)

For example: If you enter name say Anand and insert the record. Say another person is name Anand you insert it again it would defeat the purpose of the primary key as in relational databases it is meant to uniquely identify a record which in this case won't happen.

if i give same values multiple times then it also accept it

You can insert multiple values having the same primary key because you haven't specified your primary key to be unique or identity as in :

db.execSQL("create table mtable (_Id Integer  auto_increment, name Text Primary key , mail Text)");

Why am I not able to fetch the data from the database ?

Your code seems to be fine. Debug it and see if the cursor contains anything and is not null. You could check first:

if (cursor != null) {
                //move cursor to first row
                if (cursor.moveToFirst()) {
                    do {
                        // Get version from Cursor
                        String name = cursor.getString(cursor.getColumnIndex("name"));
                  //more code

                        // move to next row
                    } while (cursor.moveToNext());
                }
            }

Update:

If you try to reinsert a row with the same primary key you would get the error that primary key should be unique and the primary key includes in itself the not null and unique traits so no need to specify these with it.

how can i specify that ? – Priya Patlani Jun 24 '15 at 11:20 — Priya Patlani, Jun 24 '15 at 11:20
specify the primary key?? – Syed Osama Maruf Jun 24 '15 at 11:23 — Syed Osama Maruf, Jun 24 '15 at 11:23
what was the problem in mine? – Priya Patlani Jun 24 '15 at 11:42 — Priya Patlani, Jun 24 '15 at 11:42
it is not advisable to use name as only pk. – Syed Osama Maruf Jun 24 '15 at 11:45 — Syed Osama Maruf, Jun 24 '15 at 11:45
Read the answer carefully and the also the link attached. – Syed Osama Maruf Jun 24 '15 at 11:51 — Syed Osama Maruf, Jun 24 '15 at 11:51
WHAT IS THE PROBLEM IN MY CODE?? – Priya Patlani Jun 24 '15 at 12:00 — Priya Patlani, Jun 24 '15 at 12:00

Why am I not able to fetch the code from the DataBase?

MainActivity.java

MySqlOpenHelper.java

1 Answers1