How to store chat history in Sqlite at runtime like whatsapp in Android?

Question

I created a simple chat application and now I want to store the chat messages in Sqlite to provide chat history to users. But I don't have idea how to create database and tables, and inserting data at runtime as well.

I want to store _sender, _receiver and _msg for now. I tried this post's answer but there was syntax error in

this.insertStmt = this.myDataBase.compileStatement(INSERT);

Even I do not know if it is right approach for my case as in chat history it seems the database is a bit complex.

I tried with this code:

public void createDynamicDatabase(Context context, String tableName, ArrayList<String> title)  
{
    Log.i("INSIDE createLoginDatabase() Method","*************creatLoginDatabase*********");

    try 
    {
        int i;
        String queryString;

        // Opens database in writable mode.
        myDataBase = context.openOrCreateDatabase("Db",Context.MODE_WORLD_WRITEABLE, null);          

        //System.out.println("Table Name : "+tableName.get(0));

        queryString = title.get(0)+" VARCHAR(30),";
        Log.d("**createDynamicDatabase", "in oncreate");

        for(i = 1;i < title.size() - 1; i++)
        {               
            queryString += title.get(i);
            queryString +=" VARCHAR(30)";
            queryString +=",";
        }

        queryString+= title.get(i) +" VARCHAR(30)";

        queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "("+queryString+");";

        System.out.println("Create Table Stmt : "+ queryString);

        myDataBase.execSQL(queryString);
    } 
    catch (SQLException ex) 
    {
        Log.i("CreateDB Exception ",ex.getMessage());
    }
}

public void insert(Context context, ArrayList<String> array_vals, ArrayList<String> title, String TABLE_NAME) 
{
    Log.d("Inside Insert","Insertion starts for table name: "+TABLE_NAME);

    // Opens database in writable mode.
    myDataBase = context.openOrCreateDatabase("Db",Context.MODE_WORLD_WRITEABLE, null);         

    String titleString = null;
    String markString = null;

    int i;
    titleString = title.get(0)+",";
    markString = "?,";

    Log.d("**createDynamicDatabase", "in oncreate");

    for(i = 1;i < title.size() - 1; i++)
    {               
            titleString += title.get(i);
            titleString +=",";
            markString += "?,";
    }

    titleString+= title.get(i);
    markString += "?";

    //System.out.println("Title String: "+titleString);
    //System.out.println("Mark String: "+markString);

    INSERT="insert into "+ TABLE_NAME + "("+titleString+")"+ "values" +"("+markString+")";
    System.out.println("Insert statement: "+INSERT);
    //System.out.println("Array size iiiiii::: "+array_vals.size());
    //this.insertStmt = this.myDataBase.compileStatement(INSERT);
    int s=0;

    while(s<array_vals.size())
    {
        System.out.println("Size of array1"+array_vals.size());
                //System.out.println("Size of array"+title.size());
        int j=1;
        this.insertStmt = this.myDataBase.compileStatement(INSERT);
        for(int k =0;k< title.size();k++)
        {

            //System.out.println("Value of column "+title+" is "+array_vals.get(k+s));
            //System.out.println("PRINT S:"+array_vals.get(k+s));
            System.out.println("BindString: insertStmt.bindString("+j+","+ array_vals.get(k+s)+")");
            insertStmt.bindString(j, array_vals.get(k+s));



            j++;
        }

        s+=title.size();

        }
        insertStmt.executeInsert();
    }

I created database in onCreate() and than insert the data when user sends msg and receives.but getting this error.;

android.database.sqlite.SQLiteException: near "08104710280": syntax error (code 1): , while compiling: insert into 08104710280(sender,receiver,msg)values(?,?,?)

at this line

this.insertStmt = this.myDataBase.compileStatement(INSERT);

score 2 · Accepted Answer · answered Nov 19 '15 at 19:47

2

Look at SQLiteOpenHelper:

http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html

Coding example:

http://www.vogella.com/tutorials/AndroidSQLite/article.html

answered Nov 19 '15 at 19:47

mjstam

1,049
1
6
5

score 0 · Answer 2 · answered Feb 19 '19 at 03:54

The preferred and much easier way in android is to use Room library, which greatly reduces boilerplate and works smoothly with other architechture components like LiveData, handles background queries for you and provides compile time checks just for a few lines of code.

How to store chat history in Sqlite at runtime like whatsapp in Android?

2 Answers2

Linked