E/SQLiteLog: (1) no such table: HOSPITALS

Question

It creates an error. I have a database named Hospitals.sqlite in the assets folder. I tried doing this but still it does not work. I also tried uninstalling and reinstalling the app on my phone. I also tried adding the piece of codes on my onCreate() and onUpgrade(), the commented part.

DBHandler

public class DBHandler extends SQLiteOpenHelper {
    // Database Version
    private static final int DATABASE_VERSION = 2;
    // Database Name
    private static final String DATABASE_NAME = "Hospitals";
    // Contacts table name
    private static final String HOSPITALS = "HOSPITALS";
    // Shops Table Columns names
    private static final String hospitalName = "HospitalName";
    private static final String latitude = "Latitude";
    private static final String longitude = "Longitude";

    public DBHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public List<String> getHospName() {
        List<String> hospName = new ArrayList<>();
        // Select All Query
        String selectQuery = "SELECT * FROM " + HOSPITALS;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                HospitalNodes hospitals = new HospitalNodes();
                hospitals.setHospital(cursor.getString(0));
                // Adding contact to list
                hospName.add(hospitals.getHospital());
            } while (cursor.moveToNext());
        }
        return hospName;
    }

    public List<Float> getLat() {
        List<Float> hospLat = new ArrayList<>();
        // Select All Query
        String selectQuery = "SELECT * FROM " + HOSPITALS;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                HospitalNodes hospitals = new HospitalNodes();
                hospitals.setLatitude(Integer.parseInt(cursor.getString(1)));
                // Adding contact to list
                hospLat.add(hospitals.getLatitude());
            } while (cursor.moveToNext());
        }
        return hospLat;
    }

    public List<Float> getLong() {
        List<Float> hospLong = new ArrayList<>();
        // Select All Query
        String selectQuery = "SELECT * FROM " + HOSPITALS;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                HospitalNodes hospitals = new HospitalNodes();
                hospitals.setLongitude(Integer.parseInt(cursor.getString(2)));
                // Adding contact to list
                hospLong.add(hospitals.getLongitude());
            } while (cursor.moveToNext());
        }
        return hospLong;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        /*String CREATE_HOSPITALS_TABLE = "CREATE TABLE " + HOSPITALS + "(Location varchar2(255), "
        + hospitalName + " varchar2(255), ServiceCapability varchar2(255), Class varchar2(255), Type varchar2(255), "
         + latitude + " numeric(15,5)" + longitude + " numeric(15,5));";
        db.execSQL(CREATE_HOSPITALS_TABLE);*/
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        /*db.execSQL("DROP TABLE IF EXISTS " + HOSPITALS);
        onCreate(db);*/
    }
}

Hospital Nodes

public class HospitalNodes {
    private String hospital;
    private float latitude;
    private float longitude;

    public HospitalNodes() {
    }

    public HospitalNodes(String hospital, float latitude, float longitude) {
        this.hospital = hospital;
        this.latitude = latitude;
        this.longitude = longitude;
    }

    public void setHospital(String hospital) {
        this.hospital = hospital;
    }

    public void setLatitude(float latitude) {
        this.latitude = latitude;
    }

    public void setLongitude(float longitude) {
        this.longitude = longitude;
    }

    public String getHospital() {
        return hospital;
    }

    public float getLatitude() {
        return latitude;
    }

    public float getLongitude() {
        return longitude;
    }
}

Main Activity

public void getAllHospitals(){
    DBHandler hosp = new DBHandler(this);
    List<String> hospName = hosp.getHospName();
    List<Float> hospLat = hosp.getLat();
    List<Float> hospLong = hosp.getLong();
    List<Marker> markers = new ArrayList<>();
    ArrayList<OverlayItem> anotherOverlayItemArray = new ArrayList<>();

    for(int i = 0; i < hospName.size(); i++)
        anotherOverlayItemArray.add(new OverlayItem("Road", "Nodes", new GeoPoint(hospLat.get(i), hospLong.get(i))));

    ItemizedIconOverlay<OverlayItem> anotherItemizedIconOverlay
            = new ItemizedIconOverlay<OverlayItem>(
            this, anotherOverlayItemArray, null);
    map.getOverlays().add(anotherItemizedIconOverlay);
}

`public void onCreate(SQLiteDatabase db) {/*...whole body of method here ...*/}` so what you expected? — Selvin, Dec 08 '16 at 11:12
@Selvin i also tried uncommenting the commented part. It still does not work. — Temmie, Dec 08 '16 at 11:13
Because it is called only once after incrementing version ... — Selvin, Dec 08 '16 at 11:13
Where is your code from copying database from assets folder to android data path? — bindal, Dec 08 '16 at 11:14
how do i copy it to the android data path? I tried the answer below but i got a new error. `E/SQLiteLog: (1) near "Longitude": syntax error` — Temmie, Dec 08 '16 at 11:18
See there is comma missing between + latitude + " TEXT" + longitude + " TEXT; So it should be like + latitude + " TEXT," + longitude + " TEXT;Because of that error is coming — bindal, Dec 08 '16 at 11:23

Surya Prakash Kushawah · Answer 1 · 2016-12-08T11:18:32.910

1

uncomment code

  @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_HOSPITALS_TABLE = "CREATE TABLE " + HOSPITALS + "(Location varchar2(255), "
        + hospitalName + " varchar2(255), ServiceCapability varchar2(255), Class varchar2(255), Type varchar2(255), "
         + latitude + " TEXT" + longitude + " TEXT;";
        db.execSQL(CREATE_HOSPITALS_TABLE);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       db.execSQL("DROP TABLE IF EXISTS " + HOSPITALS);
        onCreate(db);
    }

edited Dec 08 '16 at 11:18

answered Dec 08 '16 at 11:12

Surya Prakash Kushawah

3,185
1
22
42

I did that but it still does not work – Temmie Dec 08 '16 at 11:13
2

uninstall app then re install – Surya Prakash Kushawah Dec 08 '16 at 11:14
a new error occurs `E/SQLiteLog: (1) near "Longitude": syntax error` – Temmie Dec 08 '16 at 11:17
@Temmie put ,(comma) after TEXT before longitude in the create query. i e., "... + latitude + " TEXT, " + longitude + " TEXT;" – Raghavendra Dec 08 '16 at 11:19
edited code now check – Surya Prakash Kushawah Dec 08 '16 at 11:20
you should use text then typecast double in case of lat long . please accept if code help you – Surya Prakash Kushawah Dec 08 '16 at 11:21
It now runs but it does not display the markers. In my original database when opened in mozilla, my Longitude table is numeric(15,5). Do i have to change that? – Temmie Dec 08 '16 at 11:25

E/SQLiteLog: (1) no such table: HOSPITALS

1 Answers1