Insert in SQLite Database - Android

Question

I'm currently working on my Android class project. I have some trouble with the insert statement in the database; I am using SQLite. When I'm running the application my database is correctly created but values are not inserted. I tried to refer my self to online proposals and solutions and i tried implementing it but with no avail.

Please I need some help. Thanks.

DBHandler.java

package ck.edu.com.birch;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.text.SimpleDateFormat;
import java.util.Date;

public class DBHandler {

    String TAG = "Tennis Tracker";

    String DB_NAME = "TennisEFREI.db";
    String DB_TABLE = "statistics";
    int DB_VERSION = 1;

    String SQL_CREATE = "CREATE TABLE " + DB_TABLE +
            " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "playerOnename TEXT NOT NULL, " +
            "playerTwoname TEXT NOT NULL, " +
            "winner TEXT NOT NULL, " +
            "wonPointsPlayer1 INTEGER NOT NULL, " +
            "wonPointsPlayer2 INTEGER NOT NULL, " +
            "wonSetsPlayer1 INTEGER NOT NULL, " +
            "wonSetsPlayer2 INTEGER NOT NULL, " +
            "acePlayer1 INTEGER, " +
            "acePlayer2 INTEGER, " +
            "winPlayer1 INTEGER, " +
            "winPlayer2 INTEGER, " +
            "errorPlayer1 INTEGER, " +
            "errorPlayer2 INTEGER, " +
            "date TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
    String SQL_DROP = "DROP TABLE IF EXISTS " + DB_TABLE;

    DatabaseHelper databaseHelper;

    private SQLiteDatabase db;

    public DBHandler(Context context) {
        databaseHelper = new DatabaseHelper(context);
    }

    class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }


        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(SQL_CREATE);

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL(SQL_DROP);
            onCreate(db);
        }
    }

    public void open() throws SQLException {
        db = databaseHelper.getWritableDatabase();
    }

    public void close() {
        databaseHelper.close();
    }

    public long insertStatistics(String playerOnename, String playerTwoname, String winner, int wonPointsPlayer1, int wonPointsPlayer2, int wonSetsPlayer1, int wonSetsPlayer2, int acePlayer1, int acePlayer2, int winPlayer1, int winPlayer2, int errorPlayer1, int errorPlayer2) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy");
        String date = simpleDateFormat.format(new Date());
        ContentValues contentValues = new ContentValues();
        contentValues.put("playerOnename", playerOnename);
        contentValues.put("playerTwoname", playerTwoname);
        contentValues.put("winner", winner);
        contentValues.put("wonPointsPlayer1", wonPointsPlayer1);
        contentValues.put("wonPointsPlayer2", wonPointsPlayer2);
        contentValues.put("wonSetsPlayer1", wonSetsPlayer1);
        contentValues.put("wonSetsPlayer2", wonPointsPlayer2);
        contentValues.put("acePlayer1", acePlayer1);
        contentValues.put("acePlayer2", acePlayer2);
        contentValues.put("winPlayer1", winPlayer1);
        contentValues.put("winPlayer2", winPlayer2);
        contentValues.put("errorPlayer1", errorPlayer1);
        contentValues.put("errorPlayer2", errorPlayer2);
        contentValues.put("date", date);
        Log.d(TAG, "VALUES : " + contentValues);
        return db.insert(DB_TABLE, null, contentValues);
    }
}

How do you know the insert does not work? If you're pulling the database file to see what's there, remember to either also pull the temp files (wal, shm), or just close your database to have the changes persisted to the db file. Other than that, use insertOrThrow() for helpful exceptions if the insert itself fails. — laalto
– laalto, Commented Mar 26, 2020 at 21:11
I went into the Device File Explorer saved the db file and the tried to view the content using DB Browser but nothing was inside. I also inserted data several times but the file size also never changed. — The_Thinker
– The_Thinker, Commented Mar 26, 2020 at 21:19
Yes. When calling that method here is what i'm doing, just to be sure i'm doing it right: ` long savingStatus = dbHandler.insertStatistics(...myParams); if (savingStatus < 0) messageToast("Error Saving Statistics"); else messageToast("Statistics Saved Successfully"); dbHandler.close(); ` — The_Thinker
– The_Thinker, Commented Mar 26, 2020 at 21:40

Aashit Shah · Accepted Answer · 2020-03-27 13:39:29Z

1

The documentation says:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

So either convert your date in this format and try to insert or set your table's date column data type to text (which i won't recommend).

answered Mar 27, 2020 at 13:39

Aashit Shah

5883 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

Insert in SQLite Database - Android

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related