SQL table refusing to insert user input to columns

Question

I am making an app where I input a name and a number, and upon clicking SAVE, the name is saved in an SQLITE database named infinitecounter.db under the COUNTER_NAME column. After that, it saves the number in the CURRENT_CLICKS column. However, when I launch my app, and upon clicking the save button. the app crashes.

I then exported the database from the files and opened it using SQLite browser and this is what I saw: SQLITE browser

Here is my DATABASE code:

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;
import android.widget.Toast;

import androidx.annotation.Nullable;

class database1 extends SQLiteOpenHelper {
    private Context context;
    private static final String DATABASE_NAME= "infinitecounter.db";
    private static final int DATABASE_VERSION= 1;

    private static final String TABLE_NAME = "infinitecounter list";
    private static final String COLUMN_ID = "id";
    private static final String COUNTER_NAME = "title";
    private static final String CURRENT_CLICKS = ("Current Clicks"); // fix later

    public database1(@Nullable Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query =
                "CREATE TABLE " + TABLE_NAME +
                        "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        COUNTER_NAME + " TEXT, " +
                        CURRENT_CLICKS + " INTEGER);";
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    void addinfincounter(String title, int current_clicks){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv =  new ContentValues();

        cv.put(COUNTER_NAME, title);
        cv.put(CURRENT_CLICKS, current_clicks);
        long results = db.insert(TABLE_NAME, null, cv);
        if(results == -1){
            Toast.makeText(context, "FAILED!", Toast.LENGTH_SHORT).show();
        } else {
            Toast.makeText(context, "SUCCESS!", Toast.LENGTH_SHORT).show();
        }

    }
}

Here is the java page where I activate the addinfincounter method

import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;


public class infincounter_setup extends AppCompatActivity {

    EditText name, clicks;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_infincounter_setup);
    }
    public void cancel(View V){
        Intent i = new Intent(this, MainActivity.class);
        startActivity(i);
    }


    public void save(View B) {
        name = findViewById(R.id.infincounter_name);
        clicks = findViewById(R.id.clicks);
        if (name.length() == 0 || name == null) {
            Toast.makeText(this, "PLEASE ASSIGN A NAME", Toast.LENGTH_LONG).show();
        } else{
            database1 myDB = new database1(infincounter_setup.this);
            myDB.addinfincounter(name.getText().toString(), Integer.parseInt(clicks.getText().toString()));
        }
    }
}

and finally, here is the bluprint

A stacktrace would be helpful. – John Williams Mar 12 '23 at 08:28 — John Williams, Mar 12 '23 at 08:28

score 1 · Answer 1 · answered Mar 12 '23 at 08:20

1

I think this is the problem:

private static final String TABLE_NAME = "infinitecounter list";

The table name can only contain alphanumeric characters and '_' or you can put brackets around the name. See this answer: What are valid table names in SQLite?

answered Mar 12 '23 at 08:20

j.karlsson

618
4
14

Thank you so much, it was between quotation marks so I thought it wouldn't matter if there was space. Your answer is clear and concise and straight to the point, I appreciate it. – Hamzah Alkhateeb Mar 13 '23 at 01:59

score 1 · Answer 2 · answered Mar 12 '23 at 11:53

What is happening is that when the database is first accessed (opened), which is when you try to insert the row.

The database is created, if it does not already exist and the android_metadata table is added (it contains the locale).
The overridden onCreate method is called but that fails due to a syntax error as per:-

android.database.sqlite.SQLiteException: near "list": syntax error (code 1 SQLITE_ERROR): , while compiling: CREATE TABLE infinitecounter list(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, Current Clicks INTEGER);
The database is closed as part of the App crashing.

Hence why you just see that single table.

The issues, as per the message, is that there is a space between infinitecounter and list. SQLite would be expecting either:-

an ( to start the column definitions, or
a . to specify the separation of the schema name and the table name, or
AS if creating and populating the table from a SELECT
as per
- https://www.sqlite.org/lang_createtable.html
This may well be highlighted in Android Studio e.g.

To fix the issue you need to define the table name so that it does not result in the syntax error, perhaps infinitecounter_list (as used below).

You could have the space in the table name if the name were enclosed as if it were keyword as per https://www.sqlite.org/lang_keywords.html e.g. [infinitecounter list].

After changing the name, as the onCreate method only runs once when the database is created, you should uninstall the app and rerun (uninstall results in the database being deleted).

Note you will have a similar issue with the current clicks column name.

Following the above i.e. using:-

   private static final String TABLE_NAME = "infinitecounter_list"; //<<<<<<<<<< CHANGED
   private static final String COLUMN_ID = "id";
   private static final String COUNTER_NAME = "title";
   private static final String CURRENT_CLICKS = ("Current_Clicks"); //<<<<<<<<<< CHANGED fix later

And then running the App with:-

public class MainActivity extends AppCompatActivity {

    database1 db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = new database1(this);
        db.addinfincounter("Title1",100);
    }
}

and then using App Inspection show:-

i.e. The table has been created and a row inserted both as expected.

Thank you so much, you've gone through so much effort to explain a tiny little mistake. I really appreciate it, and to be honest I didn't expect this much help. — Hamzah Alkhateeb, Mar 13 '23 at 02:00

SQL table refusing to insert user input to columns

2 Answers2