Why table is not getting created in sqlite?

Question

I have a DatabaseHelper class like this :

package com.example.fitness;

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

public class DatebaseHelper extends SQLiteOpenHelper
{

private static final String DB_NAME = "userinfo";
private static final String TABLE_NAME = "user";
private static final String FIT_REF_NO = "FIT_REF_NO";
private static final String REGN_NO = "REGN_NO";
private static final String O_NAME = "O_NAME";
private static final String MAKER_NAME = "MAKER_NAME";
private static final String MAKER_MODEL = "MAKER_MODEL";
private static final String REQ_TIME = "REQ_TIME";
private static final String RES_TIME = "RES_TIME";
private static final String IMAGE_NAME_1 = "IMAGE_NAME_1";
private static final String IMAGE_NAME_2 = "IMAGE_NAME_2";
private static final String IMAGE_NAME_3 = "IMAGE_NAME_3";
private static final String IMAGE_NAME_4 = "IMAGE_NAME_4";
private static final String IMAGE_NAME_5 = "IMAGE_NAME_5";
private static final String TEST_RESULT = "TEST_RESULT";
private static final String TEST_ON = "TEST_ON";
private static final String OFFICER_CODE = "OFFICER_CODE";
private static final String OFFICER_NAME = "OFFICER_NAME";
private static final String NO_OF_PHOTO = "NO_OF_PHOTO";

public DatebaseHelper(Context context, String db_name, CursorFactory factory, int version)
{
    super(context, DB_NAME, factory, version);
}

@Override
public void onCreate(SQLiteDatabase db)
{
    String create_table = "create table " + TABLE_NAME + "(id integer primary key autoincrement," + FIT_REF_NO + " text," + REGN_NO + " text," + O_NAME + " text," + MAKER_NAME + " text," + MAKER_MODEL + " text," + REQ_TIME + " text," + RES_TIME + " text," + IMAGE_NAME_1 + " text," + IMAGE_NAME_2 + " text," + IMAGE_NAME_3 + " text," + IMAGE_NAME_4 + " text," + IMAGE_NAME_5 + " text," + TEST_RESULT + " text," + TEST_ON + " text," + OFFICER_CODE + " text," + OFFICER_NAME + " text," + NO_OF_PHOTO + " text);";
    db.execSQL(create_table);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{

    db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);
    onCreate(db);
}

}

Then there is the DatabaseHandler class like this :

package com.example.fitness;

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

public class DatabaseHandler
{

private static final String DB_NAME = "userinfo";
private static final String TABLE_NAME = "user";
private static final String FIT_REF_NO = "FIT_REF_NO";
private static final String REGN_NO = "REGN_NO";
private static final String O_NAME = "O_NAME";
private static final String MAKER_NAME = "MAKER_NAME";
private static final String MAKER_MODEL = "MAKER_MODEL";
private static final String REQ_TIME = "REQ_TIME";
private static final String RES_TIME = "RES_TIME";
private static final String IMAGE_NAME_1 = "IMAGE_NAME_1";
private static final String IMAGE_NAME_2 = "IMAGE_NAME_2";
private static final String IMAGE_NAME_3 = "IMAGE_NAME_3";
private static final String IMAGE_NAME_4 = "IMAGE_NAME_4";
private static final String IMAGE_NAME_5 = "IMAGE_NAME_5";
private static final String TEST_RESULT = "TEST_RESULT";
private static final String TEST_ON = "TEST_ON";
private static final String OFFICER_CODE = "OFFICER_CODE";
private static final String OFFICER_NAME = "OFFICER_NAME";
private static final String NO_OF_PHOTO = "NO_OF_PHOTO";

private static final int DATABASE_VERSION = 2;
private SQLiteDatabase sqliteDB;
private DatebaseHelper dbHelper;

public DatabaseHandler(Context context)
{

    dbHelper = new DatebaseHelper(context, DB_NAME, null, DATABASE_VERSION);

}

// Open Database
public void open()
{
    sqliteDB = dbHelper.getWritableDatabase();

}

// Close Database
public void close()
{
    if (sqliteDB != null)
        sqliteDB.close();
}

public Cursor getUserInfo()
{

    Cursor cursor = sqliteDB.query(TABLE_NAME, null, null, null, null, null, null);
    cursor.moveToFirst();
    // while(cursor.moveToNext())
    // {
    // Log.e("In getUserInfo()",
    // "name:"+cursor.getString(1)+", age:"+cursor.getString(2));
    // }
    return cursor;
}

public VehicleData getFitnessInfo(String refno)
{

    Cursor cursor = sqliteDB
            .query(TABLE_NAME, new String[] { FIT_REF_NO, REGN_NO, O_NAME, MAKER_NAME, MAKER_MODEL }, FIT_REF_NO + "=?", new String[] { String.valueOf(refno) }, null, null, null, null);
    if (cursor != null)
        cursor.moveToFirst();
    VehicleData vData = new VehicleData(cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5));
    return vData;
}

public long insertUserInfo(String refno, String regno, String oname, String makername, String makermodel, String reqtime, String restime, String img1, String img2, String img3, String img4,
        String img5, String testresult, String teston, String officercode, String officername, String picsnum)
{
    ContentValues contentValues = new ContentValues();
    sqliteDB = dbHelper.getWritableDatabase();
    contentValues.put(FIT_REF_NO, refno);
    contentValues.put(REGN_NO, regno);
    contentValues.put(O_NAME, oname);
    contentValues.put(MAKER_NAME, makername);
    contentValues.put(MAKER_MODEL, makermodel);
    contentValues.put(REQ_TIME, reqtime);
    contentValues.put(RES_TIME, restime);
    contentValues.put(IMAGE_NAME_1, img1);
    contentValues.put(IMAGE_NAME_2, img2);
    contentValues.put(IMAGE_NAME_3, img3);
    contentValues.put(IMAGE_NAME_4, img4);
    contentValues.put(IMAGE_NAME_5, img5);
    contentValues.put(TEST_RESULT, testresult);
    contentValues.put(TEST_ON, teston);
    contentValues.put(OFFICER_CODE, officercode);
    contentValues.put(OFFICER_NAME, officername);
    contentValues.put(NO_OF_PHOTO, picsnum);

    return sqliteDB.insert(TABLE_NAME, null, contentValues);
}

}

I'm trying in to insert values from a CSV into SQLite database with the method insertUserInfo which is being called here :

package com.example.fitness;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;

import android.content.Context;
import android.database.Cursor;
import android.os.Environment;
import android.util.Log;

public class ImportExportExcel
{
DatabaseHandler dbHandler;

Context context;

protected static final File DATABASE_DIRECTORY = new      File(Environment.getExternalStorageDirectory() + "/FitnessData/");

protected static final File IMPORT_ITEM_FILE = new File(DATABASE_DIRECTORY, "fitnesscsv.csv");

public ImportExportExcel(Context context)
{
    this.context = context;
}

public Boolean exportDataToCSV(String outFileName)
{

    Log.e("excel", "in exportDatabasecsv()");
    Boolean returnCode = false;

    String csvHeader = "";
    String csvValues = "";

    try
    {

        dbHandler = new DatabaseHandler(context);
        dbHandler.open();

        if (!DATABASE_DIRECTORY.exists())
        {
            DATABASE_DIRECTORY.mkdirs();
        }
        Log.e("export fun:file name", outFileName);
        File outFile = new File(DATABASE_DIRECTORY, outFileName);
        FileWriter fileWriter = new FileWriter(outFile);
        Log.e("after FileWriter :file name", outFile.toString());
        BufferedWriter out = new BufferedWriter(fileWriter);

        Cursor cursor = dbHandler.getUserInfo();
        // Log.e("excel", "cursor col count" + cursor.getCount());

        int col_count = cursor.getColumnCount();
        Log.e("col count", "" + col_count);
        csvHeader += "\"" + "Id" + "\",";
        csvHeader += "\"" + "Name" + "\",";
        csvHeader += "\"" + "Age" + "\",";

        csvHeader += "\n";

        if (cursor != null)
        {
            out.write(csvHeader);
            while (!cursor.isAfterLast())
            {
                // csvValues = Long.toString(cursor.getLong(0)) +    ",";

                csvValues = cursor.getString(0) + ","; // id
                csvValues += cursor.getString(1) + ","; // name
                csvValues += cursor.getString(2) + ",\n"; // age

                out.write(csvValues);
                cursor.moveToNext();
            }
            // Log.e("excel", "csvValues are:-  " + csvValues);

        }
        out.close();
        cursor.close();
        returnCode = true;
    }
    catch (Exception e)
    {
        returnCode = false;
        Log.e("Exception", e.getMessage());
    }

    dbHandler.close();
    return returnCode;
}

public void importDataFromCSV()
{
    int i = 0;

    boolean flag_is_header = false;

    File file = new File(IMPORT_ITEM_FILE.getPath());
    if (file.exists())
    {
    }
    BufferedReader bufRdr = null;
    try
    {
        bufRdr = new BufferedReader(new FileReader(file));
    }
    catch (FileNotFoundException e)
    {
        e.printStackTrace();
    }
    String line = null;

    try
    {
        while ((line = bufRdr.readLine()) != null)
        {

            String[] insertValues = line.split(",");
            if (flag_is_header)
            {

                dbHandler = new DatabaseHandler(context);
                dbHandler.open();
                long row =       dbHandler.insertUserInfo(insertValues[0], insertValues[1], insertValues[2],insertValues[3], insertValues[4], insertValues[5], insertValues[6], insertValues[7], insertValues[8], insertValues[9], insertValues[10], insertValues[11], insertValues[12], insertValues[13], insertValues[14],insertValues[15], insertValues[16]);
                Log.e("no. of rows inserted", "" + row);

            }
            else
            {
                flag_is_header = true;
            }
        }
        dbHandler.close();
        bufRdr.close();
    }
    catch (IOException e)
    {
        e.printStackTrace();
    }

}

}

Calling importDataFromCSV() in this activity

    public class ImportExportActivity extends Activity implements OnClickListener
{

DatabaseHandler databaseConnector;
Context context;
Button importBtn, exportBtn, captureBtn;
ImportExportExcel importExportExcel;

String exportFileName = "ExportExcel.csv";

@Override
protected void onCreate(Bundle savedInstanceState)
{
    super.onCreate(savedInstanceState);
    setContentView(R.layout.intro);
    initializeUI();
}

public void initializeUI()
{
    context = this;
    databaseConnector = new DatabaseHandler(context);
    importExportExcel = new ImportExportExcel(context);
    importBtn = (Button) findViewById(R.id.bImport);
    exportBtn = (Button) findViewById(R.id.bExport);
    captureBtn = (Button) findViewById(R.id.bCapture);
    importBtn.setOnClickListener(this);
    exportBtn.setOnClickListener(this);
    captureBtn.setOnClickListener(this);
}

@Override
public void onClick(View v)
{
    switch (v.getId())
    {
    case R.id.bImport:
        importExportExcel.importDataFromCSV();
        Toast.makeText(context, "File imported successfully", 1).show();
        break;
    case R.id.bCapture:
        Intent intent = new Intent(ImportExportActivity.this, Reference.class);
        startActivity(intent);
        break;
    case R.id.bExport:
        try
        {
            importExportExcel.exportDataToCSV(exportFileName);
            Toast.makeText(context, "File exported successfully", 1).show();
        }
        catch (Exception e)
        {
            Toast.makeText(context, "Please,Import data first", 1).show();
        }
        break;
    default:
        break;
    }
}

}

Logcat says

    05-07 13:42:45.255: E/SQLiteLog(2251): (1) table user has no column named O_NAME
    05-07 13:42:45.282: E/SQLiteDatabase(2251): Error inserting O_NAME="RAM NARAYAN DIXIT" RES_TIME="" MAKER_NAME="SWARAJ MAZDA LTD" 
    FIT_REF_NO="2" TEST_RESULT="" REQ_TIME="" OFFICER_NAME="" MAKER_MODEL="WV 26 STC" REGN_NO="CG04HC3582" TEST_ON="" IMAGE_NAME_1=""
     NO_OF_PHOTO="4" OFFICER_CODE="" IMAGE_NAME_2="" IMAGE_NAME_3="" IMAGE_NAME_4="" IMAGE_NAME_5=""
    05-07 13:42:45.282: E/SQLiteDatabase(2251): android.database.sqlite.SQLiteException: table user has no column named O_NAME (code 1): , 
    while compiling: INSERT INTO user(O_NAME,RES_TIME,MAKER_NAME,FIT_REF_NO,TEST_RESULT,REQ_TIME,OFFICER_NAME,MAKER_MODEL,REGN_NO,TEST_ON,
    IMAGE_NAME_1,NO_OF_PHOTO,OFFICER_CODE,IMAGE_NAME_2,IMAGE_NAME_3,IMAGE_NAME_4,IMAGE_NAME_5) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Please help me here ?

If you've recently added the column, just uninstall your app so any old database file gets removed and your `onCreate()` is run again. http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run — laalto, May 07 '14 at 08:25
change the `DATABASE_VERSION = 2;` to` DATABASE_VERSION = 3;` — Sree, May 07 '14 at 08:26
add dbHandler = new DatabaseHandler(context); in first activity — raj, May 07 '14 at 08:27
IM NOT CHANGING ANY FIELDS NOT ADDING ANYTHING NEW JUST HAVE TO READ THE ROWS FROM THE DATABASE — Vibhor Kashyap, May 07 '14 at 12:47
@raj where do i add dbHandler = new DatabaseHandler(context); in the first activity ? constructor or onCreate ? Please elaborate why do I have to write this line in the class that extends SQLiteOpenHelper ? — Vibhor Kashyap, May 07 '14 at 16:57
@laalto Much Thanx, very good explanation on your wiki post ! problem solved :D — Vibhor Kashyap, May 07 '14 at 17:15

score 0 · Answer 1 · answered May 07 '14 at 08:32

0

What Sree said basically.

You have to change DATABASE_VERSION everytime you change something related to it (a new field, a new table, ...)

answered May 07 '14 at 08:32

Fanta1090

41
8

IM NOT CHANGING ANY FIELDS NOT ADDING ANYTHING NEW JUST HAVE TO READ THE ROWS FROM THE DATABASE – Vibhor Kashyap May 07 '14 at 12:48

Why table is not getting created in sqlite?

1 Answers1