Cannot read data from SQLite database in android

Question

I'm writing an application that gets the first name and last name from user and inserts them into a database, then the user can write the first name in another field and search for the last name, and the program should show the last name of the searched name in the TextView.

package ir.itstuff.dbtest;

import java.util.List;

import android.R.string;
import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class Main extends Activity {

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

        final EditText fName = (EditText) findViewById(R.id.editText1);
        final EditText sName = (EditText) findViewById(R.id.editText2);
        final TextView tv = (TextView) findViewById(R.id.textView1);
        EditText et3 = (EditText) findViewById(R.id.editText3);
        Button btdt = (Button) findViewById(R.id.button2);
        Button save = (Button) findViewById(R.id.button1);

        final SQLiteDatabase db = openOrCreateDatabase("MyDB", MODE_PRIVATE,null);
        db.execSQL("CREATE TABLE IF NOT EXISTS MyTable(id INTEGER PRIMARY KEY AUTOINCREMENT, FirstName varchar,SecondName varchar)");

        final String first_name = fName.getText().toString();
        final String second_name = sName.getText().toString();
        final String search_name = et3.getText().toString();
        // Inserting data from inputs
        save.setOnClickListener(new OnClickListener() {
            @Override
            public void onClick(View v) {

                db.execSQL("INSERT INTO MyTable (FirstName,SecondName) VALUES ('"
                        + first_name + "','" + second_name + "')");
                Toast.makeText(getBaseContext(), "Data Inserted",
                        Toast.LENGTH_LONG).show();
                                db.close();

            }
        });
        btdt.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub

                // db.execSQL("SELECT * FROM MyTable WHERE FirstName='"+search_name+"'");
                Cursor c;
                c = db.query(false, "MyTable",null, "FirstName="+search_name, null, null, null, null, null);
                while(c.moveToNext()){
                    String result = c.getString(c.getColumnIndex("LastName"));
                    tv.setText(result);
                }
                db.close();

            }
        });




    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

}

It works fine till the program inserts the data to the database, but when i search for a name, the program crashes. This is the log cat output:

04-30 08:55:14.931: E/AndroidRuntime(1848): FATAL EXCEPTION: main
04-30 08:55:14.931: E/AndroidRuntime(1848): Process: ir.itstuff.dbtest, PID: 1848
04-30 08:55:14.931: E/AndroidRuntime(1848): android.database.sqlite.SQLiteException: near "=": syntax error (code 1): , while compiling: SELECT * FROM MyTable WHERE FirstName=
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at ir.itstuff.dbtest.Main$2.onClick(Main.java:60)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.view.View.performClick(View.java:4438)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.view.View$PerformClick.run(View.java:18422)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.os.Handler.handleCallback(Handler.java:733)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.os.Handler.dispatchMessage(Handler.java:95)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.os.Looper.loop(Looper.java:136)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at android.app.ActivityThread.main(ActivityThread.java:5017)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at java.lang.reflect.Method.invokeNative(Native Method)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at java.lang.reflect.Method.invoke(Method.java:515)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
04-30 08:55:14.931: E/AndroidRuntime(1848):     at dalvik.system.NativeStart.main(Native Method)

Answer 1

First of all your create table query is not correct. Because SQLite does not support varchar datatype. Instead of varchar use TEXT datatype and then ,

try this instead of your syntax to get the cursor from the table records

  c = db.query("MyTable",null,"FirstName=?",new String[]{FirstName }, null, null, null, null);

Answer 2

As per your edit, your problem seems to be in the query:

Use ? in your query and android will parse the value in.

Something like:

final String selectQuery = "SELECT " + BACKUP_TABLE_NAME + ".*, " + BACKUPCONTACTS_TABLE_NAME + "." + BACKUPCONTACTS_ID + ", "
                + BACKUPCONTACTS_TABLE_NAME + "." + BACKUPCONTACTS_VCARD + ", " + BACKUPCONTACTS_TABLE_NAME + "." + BACKUPCONTACTS_DISPLAY_NAME 
                + " FROM " + BACKUP_TABLE_NAME + ", " + BACKUPCONTACTS_TABLE_NAME 
                + " WHERE " + BACKUP_TABLE_NAME + "." + BACKUP_ID + " = " + BACKUPCONTACTS_TABLE_NAME+ "." + BACKUPCONTACTS_ID_BACKUP_FK 
                + " AND " + BACKUP_TABLE_NAME + "." + BACKUP_ID + " = ?";

        final Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(id)});

So, in your case it will be:

String query = "SELECT * FROM MyTable WHERE FirstName " + " = ? "

Cursor cursor = db.rawQuery(selectQuery, new String[]{search_name});

Tip: Use camel case to name your variables, and not underscores. That is the java convention. So, search_name would be renamed to searchName

Answer 3

You are closing the database and then trying to use it again without opening it. When someone clicks on btdt, the database is closed. You need to open it again before querying by the first name.