3

I have decided that I hate SQL and for that purpose I am trying to make a helper class that I can reuse over and over and never have to mess with it again but it isnt working!

Here is what I have right now:

DBAssistant.java

    public class DBAssistant {
            SQLiteDatabase db = null;


        public DBAssistant (){      
        }

        /** Opens database **/
        public void openDB(Context context, String name){
            db = new DBOpenHelper(context, name, DB_VERSION);
        }

        /** Creates the table "tableName" with the columns contained in "cols" **/
        public void createTable(String tableName, String[] cols){
            String table = "CREATE TABLE IF NOT EXISTS " + tableName + " (";
            int numOfColums = cols.length;
            columnNames = cols;
            for (int i = 0; i < (numOfColums - 1); i++){
                table += cols[i] + " VARCHAR, ";
            }
            table += cols[numOfColums - 1] + " VARCHAR);";
            try{
            db.execSQL("" + table);
            System.out.println("ExecSQL:" + table);
            } catch(Exception e){
                System.out.println(e);
            }
            System.out.println("Column names: ");
            for (String c : getColNames(tableName)){
                System.out.print(c + " ");
            }
        }

        /** Inserts "data" into new row **/
        public void insertRow(String tableName, String[] data){
            int cols = getCols(tableName);

            System.out.println("if ((data.length) = " + (data.length) + ") ==" +
                    " (getCols(tableName) = " +  getCols(tableName) + ")?");
            if (data.length == cols){
                System.out.println("Inside if loop");
                String cmd = "INSERT INTO " + tableName + " (";
                for (int i = 0; i < cols - 1; i++){
                    cmd += getColNames(tableName)[i] + ", ";
                }
                cmd += getColNames(tableName)[cols - 1] + ") VALUES ('";
                for (int k = 0; k < data.length - 1; k++){
                    cmd += data[k] + "', '";
                }
                cmd += "');";
                System.out.println(cmd);
                db.execSQL(cmd);
            }
            else{
                System.out.println("Inside else loop");
                String dat = "";
                String sCols = "";
                for (String d : data)
                    dat += "'" + d + "'";
                for (String c : getColNames(tableName))
                    sCols += "'" + c + "'";
                System.out.println("-------------------");
                System.out.println("[insertRow] ERROR: Number of elements in data[" + dat + "]");
                System.out.println("doesnt match the number of columns [" + cols + "] in " + tableName);
                System.out.println("-------------------");
            }           
        }

        /** Return String[] containing the column names in tableName **/
        public String[] getColNames(String tableName){
            Cursor c = db.rawQuery("SELECT * FROM " + tableName , null);
            return c.getColumnNames();
        }

        /** Returns the number of rows in tableName **/
        public int getCols(String tableName){
            return getColNames(tableName).length;
        }

    /***  Other methods that have no relevance here .... ***/

private static class DBOpenHelper extends SQLiteOpenHelper {

    DBOpenHelper(Context context, String dbName, int dbVersion) {
       super(context, dbName, null, dbVersion);
    }

    @Override
    public void onCreate(SQLiteDatabase arg0) {
        // Do Nothing
    }

    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
        // Do Nothing
    }
 }
}

I have included several System.out.println() statements to help me debug the code and find problems so please ignore them. PrintDB is another test that I wrote that I am using to make sure everything is working. Now this is what I have written to make sure everything is working....

DBAssistant db = new DBAssistant();
String dbName = "testing";
String tableName = "test";
String[] cols = {"_num", "num_eng", "num_span"};
String[] row1 = {"1", "one", "uno"};
String[] row2 = {"2", "two", "dos"};
String[] row3 = {"3", "three", "tres"};
String[] row4 = {"4", "four", "quatro"};
String[] row5 = {"5", "five", "cinco"};
TextView databaseView = (TextView)findViewById(R.id.databaseView);

db.openDB(this, dbName);
db.createTable(tableName, cols);
db.insertRow(tableName, row1);
db.insertRow(tableName, row2);
db.insertRow(tableName, row3);
db.insertRow(tableName, row4);
db.insertRow(tableName, row5);
databaseView.setText(db.printTable(tableName));

Running this code everything goes great and all the System.out.println() statements have the right information in them until it gets to the databaseView.setText(db.printTable(tableName)); part and it throws an exception

ERROR/AndroidRuntime(3440): android.database.sqlite.SQLiteException: no such table: test: , while compiling: SELECT * FROM test

Pointing to the line in the printTable() method:

Cursor c = dbr.rawQuery("SELECT * FROM " + tableName , null);

This has me so confused because that same line of code is used in the getColNames() method that was called several times before this and ran with no problem. Also if the table didn't exist wouldn't it have thrown an exception when I called insertRow()? I called that method 5 times in a row with not a single exception thrown! What is happening here?


Edit:

Implement onCreate() in your DBOpenHelper. Put there content of the createTable() method.

Why would it be necessary to create the table in the onCreate() method? If I were to do this would I be able to use my existing method to do so and just call it from this method?

And the second why you don't use content providers and uri >concept which is already SQLless?

Wha...? I have no idea. Please explain what that is or leave me a link to some sort of a tutorial.

3
  • If exception says that there is no table with such name then it;s true, have you checked it with adb console tool? And the second why you don't use content providers and uri concept which is already SQLless? Commented Nov 13, 2010 at 7:31
  • I got the same case too. Even if the name of the table is correct but still got an SQLException. (no such table) Commented Aug 15, 2013 at 6:30
  • AFAIK, content provider may not be required in some cases. Commented Aug 15, 2013 at 6:32

3 Answers 3

2

Implement onCreate() in your DBOpenHelper. Put there content of the createTable() method.

See Dev Guide for reference.

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

Comments

1

This exception can also raise because you query a column that does not exist. For instance, are you sure that your table has a col _id because android expects to find it.

Comments

0

It is happening an error while creating Database. Just debug your code when you create the tables and if it is that you will see that there is an error

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.