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)

Karakuri · Accepted Answer · 2014-04-30 08:41:09Z

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.

answered Apr 30, 2014 at 8:41

Karakuri

38.6k12 gold badges91 silver badges106 bronze badges

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

6 Comments

superus8r Over a year ago

Thank you for your answer,but i just fixed the database problem as you said, but still it crashes.

Karakuri Over a year ago

Is it the same error? What does the stack trace say?

Karakuri Over a year ago

There is a syntax error in your query. Make sure things like column names are correct. In any case, the original problem is solved and you should mark the answer correct to close the question. Try to fix the new problem; if you don't succeed, you can post a new question.

Karakuri Over a year ago

I suspect when the view is clicked the search string is empty, so your where condition becomes FirstName=, which SQLite will not understand. You can wrap the search string with single quotes so that even an empty string will work, like so: "FirstName='" + search_name + "'". For an empty string, this will yield FirstName='', which SQLite will accept.

Karakuri Over a year ago

You should also get the text from the EditText inside of the onClick callback. Right now you are doing it in onCreate, where the text is probably empty, and using that final string whenever onClick is called, even if the text has changed.

|

dazito · Accepted Answer · 2014-04-30 08:53:53Z

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

Viral Thakker · Accepted Answer · 2014-04-30 08:59:13Z

-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);

answered Apr 30, 2014 at 8:59

Viral Thakker

5473 silver badges10 bronze badges

Collectives™ on Stack Overflow

Cannot read data from SQLite database in android

3 Answers 3

6 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Related