Unable to start activity: android.database.sqlite.SQLiteException

Question

I am writing an app to read the bible on click of the button, I want to store the bible in a SQLight database and load it into the device with the app so that the user can access the bible even when offline.The program does not load the activity (which was working perfect before I created a table and tried reading from it), It also says there is a syntax error near the CREATE TABLE. the following is my source code

choosingTestament.java

package com.example.roopa.jesus;

import android.content.Intent;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;

public class choosingTestament extends AppCompatActivity {

    private View v;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_choosing_testament);
       // View view = findViewById(android.R.id.button1);
       //view = findViewById(android.R.id.button2);
    }

    public void onClickOldTestament(View v) {
        Intent i = new Intent(choosingTestament.this, ChooseBook.class);
        startActivity(i);


    }
   public void onClickNewTestament(View v){
       Intent i2 = new Intent(choosingTestament.this, choosingNewTestamentBook.class);
        startActivity(i2);
    }

    }

chooseBook.java

package com.example.roopa.jesus;

import android.database.Cursor;
import android.os.Bundle;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;

public class ChooseBook extends AppCompatActivity {
    Button genesis;
    bibleDbHandler dbHandler;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_choose_book);
        dbHandler = new bibleDbHandler(this, null, null, 3);
        genesis = (Button) findViewById(R.id.genesis);
        genesisdisp();
    }

    public void genesisdisp() {
        genesis.setOnClickListener(
                new View.OnClickListener() {
                    public void onClick(View v) {
                        Cursor cursor = dbHandler.getYourData();
                        if (cursor.getCount() == 0) {
                            showMessage("error", "no data found");
                            return;

                        }
                        StringBuffer buffer = new StringBuffer();
                        while (cursor.moveToNext()) {
                            buffer.append("COLUMN_BOOK:" + cursor.getString(0));
                            buffer.append("COLUMN_CHAPTER:" + cursor.getString(1));
                            buffer.append("COLUMN_VERSE:" + cursor.getString(2));
                            buffer.append("COLUMN_WORDINGS:" + cursor.getString(3) + "\n\n");
                        }
                        //
                        showMessage("data", buffer.toString());

                    }
                });
    }



    public void showMessage(String title, String Message) {
        AlertDialog.Builder builder = new AlertDialog.Builder(this);
        builder.setCancelable(true);
        builder.setTitle(title);
        builder.setMessage(Message);
        builder.show();
    }
}

// /cursor.close();
        //String query="SELECT * FROM"+TABLE_OLDTESTAMENT;
        //return data;



    /*@Override
    public void onClick(View v) {
        dbHandler.openDataBase();
        //dbHandler.printDatabase();
        String text = dbHandler.getYourData();
        dbHandler.close();
    }*/

choosebook.xml

<?xml version="1.0" encoding="utf-8"?>

<ScrollView
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:orientation="vertical"
    android:padding="10dp"
    android:fillViewport="false">
<LinearLayout
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_choose_book"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.example.roopa.jesus.ChooseBook">

    <Button
        android:text="Genesis"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:id="@+id/genesis"
        android:layout_width="373dp"
        android:textStyle="bold"
        android:textSize="25dp"

        android:onClick="onClick"
        android:contextClickable="true"/>

    <Button
        android:text="Exodus"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:id="@+id/exodus"
        android:layout_width="373dp"
        android:textStyle="bold"
        android:textSize="25dp"

        android:onClick="onClick"
        android:contextClickable="true"/>

    <Button
        android:text="Leviticus"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:id="@+id/levi"
        android:layout_width="373dp"
        android:textStyle="bold"
        android:textSize="25dp"

        android:onClick="onClick"
        android:contextClickable="true"/>

    <Button
        android:text="Numbers"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:id="@+id/numbers"
        android:layout_width="373dp"
        android:textStyle="bold"
        android:textSize="25dp"

        android:onClick="onClick"
        android:contextClickable="true"/>

    <Button
        android:text="Deuteronomy"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:id="@+id/deut"
        android:layout_width="373dp"
        android:textStyle="bold"
        android:textSize="25dp"

        android:onClick="onClick"
        android:contextClickable="true"/>

    <Button
        android:text="Joshua"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:id="@+id/josh"
        android:layout_width="373dp"
        android:textStyle="bold"
        android:textSize="25dp"

        android:onClick="onClick"
        android:contextClickable="true"/>

    <Button
        android:text="Judges"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:id="@+id/judges"
        android:layout_width="373dp"
        android:textStyle="bold"
        android:textSize="25dp"

        android:onClick="onClick"
        android:contextClickable="true"/>

    <Button
        android:text="Ruth"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:id="@+id/ruth"
        android:layout_width="373dp"
        android:textStyle="bold"
        android:textSize="25dp"

        android:onClick="onClick"
        android:contextClickable="true"/>


    <LinearLayout
        xmlns:tools="http://schemas.android.com/tools"
        android:id="@+id/activity_choose_book_s2"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:orientation="vertical"
        tools:context="com.example.roopa.jesus.ChooseBook">


        <Button
            android:text="1 Samuel"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/samuel1"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>

        <Button
            android:text="2 Samuel"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/samuel2"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>

        <Button
            android:text="1 Kings"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/king1"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>

        <Button
            android:text="2 Kings"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/king2"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="1 Chronicles"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/chron1"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="2 Chronicles"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/chron2"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Ezra"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/ezra"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Nehemiah"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/nehe"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Tobit"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/tobit"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Judith"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/judith"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Esther"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/esther"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="1 Maccabees"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/mac1"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>

        <Button
            android:text="2 Maccabees"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/mac2"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>

        <Button
            android:text="Job"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/job"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Psalms"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/psalms"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Proverbs"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/prov"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Ecclesiastes"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/eccle"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>

        <Button
            android:text="Song of Solomon"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/sol"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Wisdom of Solomon"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/wissol"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Sirach"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/sirach"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Isaiah"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/isaiah"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Jeremiah"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/jere"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Lamentations"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/lamen"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Baruch"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/baruch"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Ezekiel"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/eze"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Daniel"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/dan"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Hosea"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/hosea"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>

        <Button
            android:text="Joel"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/joel"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>

        <Button
            android:text="Amos"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/amos"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>

        <Button
            android:text="Obadiah"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/oba"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Jonah"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/dan"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Micah"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/micah"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Nahum"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/nahum"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Habakkuk"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/haba"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Zephaniah"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/zeph"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Haggai"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/haggai"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Zachariah"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/zach"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>
        <Button
            android:text="Malachi"
            android:layout_height="0dp"
            android:layout_weight="1"
            android:id="@+id/malachi"
            android:layout_width="373dp"
            android:textStyle="bold"
            android:textSize="25dp"

            android:onClick="onClick"
            android:contextClickable="true"/>


</LinearLayout>

    </LinearLayout>

    </ScrollView>

bibleDbHandler.java

package com.example.roopa.jesus;

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


//import android.content.ContentValues;
public class bibleDbHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 3;
    private static final String DATABASE_NAME = "oldtestament.db";
    public static final String OLDTESTAMENT = "_oldtestament";
    public static final String COLUMN_BOOK = "_oldtestbook";
    public static final String COLUMN_CHAPTER = "_oldtestchapter";
    public static final String COLUMN_VERSE = "_oldtestverse";
    public static final String COLUMN_WORDING = "_oldtestwordings";

    public bibleDbHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        SQLiteDatabase db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String queryoldtest = ("CREATE TABLE" + OLDTESTAMENT + "(" +
                COLUMN_BOOK + " TEXT , " +
                COLUMN_CHAPTER + "TEXT ,  " +
                COLUMN_VERSE + "TEXT , " +
                COLUMN_WORDING + "TEXT " +
                ");");
        db.execSQL(queryoldtest);
        db.execSQL("INSERT INTO OLDTESTAMENT [(COLUMN_BOOK, COLUMN_CHAPTER,COLUMN_VERSE,COLUMN_WORDING)]" +
                "VALUES[('GENESIS','1','1','In the beginning God created heaven, and earth.')]");
        db.execSQL("INSERT INTO OLDTESTAMENT [(COLUMN_BOOK, COLUMN_CHAPTER,COLUMN_VERSE,COLUMN_WORDING)]" +
                "VALUES('GENESIS','1','2','And the earth was void and empty, and darkness was upon the face of the deep; and the spirit of God moved over the waters.')");
        db.execSQL("INSERT INTO OLDTESTAMENT [(COLUMN_BOOK, COLUMN_CHAPTER,COLUMN_VERSE,COLUMN_WORDING)]" +
                "VALUES('GENESIS','1','3','And God said: Be light made. And light was made')");
    }


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

    public Cursor getYourData() {

        SQLiteDatabase db = getWritableDatabase();
        //final String TABLE_NAME = "OLDTESTAMENT";
        String dispQuery = "SELECT * FROM" + OLDTESTAMENT + null;

        Cursor cursor = db.rawQuery(dispQuery, null);

        cursor.close();
        return cursor;
    }
}

       // Intent intent=new Intent(bibleDbHandler.this,displayBooks.class);
       // intent.putExtra("Book",dbString.toString());
        //startActivity(intent);
        /*Intent intent = new Intent(this, ViewActivity.class);
        intent.putExtra("fname", etFName.getText().toString());
        intent.putExtra("lname", etLName.getText().toString());
        startActivity(intent);*/

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.roopa.jesus">

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity
            android:name=".MainActivity"
            android:label="@string/app_name"
            android:theme="@style/AppTheme.NoActionBar">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity
            android:name=".choosingTestament"
            android:label="choosingTestament">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>

        <!--
     ATTENTION: This was auto-generated to add Google Play services to your project for
     App Indexing.  See https://g.co/AppIndexing/AndroidStudio for more information.
        -->
        <meta-data
            android:name="com.google.android.gms.version"
            android:value="@integer/google_play_services_version" />

        <activity android:name=".ChooseBook" />
        <activity android:name=".choosingNewTestamentBook" />
        <activity android:name=".displayBooks"></activity>
    </application>

</manifest>

Please help me find my error.

Answer 1

Watch those spaces! You're missing some from your CREATE TABLE statement where you're concatenating strings. Your statement should be:

String queryoldtest = ("CREATE TABLE " + OLDTESTAMENT + "(" +
            COLUMN_BOOK + " TEXT, " +
            COLUMN_CHAPTER + " TEXT,  " +
            COLUMN_VERSE + " TEXT, " +
            COLUMN_WORDING + " TEXT" +
            ");");

Note the new spaces following TABLE and before TEXT on three lines. You'll also need a space before VALUES on all of your INSERT statements for the same reason.

Also for your INSERT statements you're using a literal OLDTESTAMENT rather than the variable, so you would need to change the beginnings of those statements to:

db.execSQL("INSERT INTO " + OLDTESTAMENT + 

Better still, if you're planning to insert a lot of verses into OLDTESTAMENT or other tables, I'd recommend creating a function for it, for example

private void insertIntoOldTestament(SQLiteDatabase db, String book, int chapter, 
        int verse, String wording) {

    final String toExec = String.format("INSERT INTO %s ", OLDTESTAMENT) +
            "[(COLUMN_BOOK, COLUMN_CHAPTER,COLUMN_VERSE,COLUMN_WORDING)] " +
            String.format("VALUES ('%s', '%d', '%d', '%s');", book, chapter, verse, wording);
    db.execSQL(toExec);
}

So you can then do:

insertIntoOldTestament("GENESIS", 1, 1, "In the beginning God created heaven, and earth.");
insertIntoOldTestament("GENESIS", 1, 2, "And the earth was void and empty, and darkness was upon the face of the deep; and the spirit of God moved over the waters.");

and so on, so you only have to worry about that SQL statement once.

(If you're wondering about String.format(), it's a nicer way of inserting variables into Strings without all those pluses);