How to generate TABLES using SqliteDatabse

Question

Sorry for my English. I do not speak very good English language. Being new in android programming, I'm working on an android application that create, modifie, and simply remove quizzes using SQLITEDATABASE.

I get errors after running the program. Here is an explanation of my program

I proceed as follows:

Here is the structure of the Table that i want to create. Table Example

I ask the user to enter the name of the new table to be created for the quiz, and then I take the value entered by the onscreen user in a EDITTEXT and I create a table from the name entered by the user. Here is a picture of the activity_add_quizz. activity_add_quizz

So when a click the button Save NAME. The onclick button method is saveNameListener. The Code is below

Here is the ERROR:

 Caused by: android.database.sqlite.SQLiteException: near "null": syntax error (code 1): , while compiling: CREATE TABLE null (_id integer primary key autoincrement, Question TEXT,Reponse1 TEXT,Reponse2 TEXT,Reponse3 TEXT,Reponse4 TEXT,Reponse5 TEXT,Reponse6 TEXT,Reponse7 TEXT,TrueReponse TEXT);

The text i have enter in the EditText "Table" was not create as a new TABLE Can someone help me PLZ.

Here is my codes:

public class BasedeDonee extends SQLiteOpenHelper {
public String Table_Name;
SQLiteDatabase db;
public static final int DATABASE_VERSION = 1;
int j = 0;

public String  DATABASE_CREATE1 = "CREATE TABLE "+ Table_Name
        + " (_id integer primary key autoincrement, "
        + "Question TEXT,"
        + "Reponse1 TEXT,"
        + "Reponse2 TEXT,"
        + "Reponse3 TEXT,"
        + "Reponse4 TEXT,"
        + "Reponse5 TEXT,"
        + "Reponse6 TEXT,"
        + "Reponse7 TEXT,"
        + "TrueReponse TEXT);";


public BasedeDonee(Context context) {
    super(context, TableData.TableInfo.DATABASE_NAME, null, DATABASE_VERSION);
}


@Override
public void onCreate(SQLiteDatabase database) {
    this.db = database;
    database.execSQL(DATABASE_CREATE1);
    db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2, TrueReponse) VALUES ('First Question','Reponse 1','Reponse 2','Reponse 1')");
    db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2) VALUES ('Second Question','Reponse 1','Reponse 2')");
    db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2) VALUES ('Third Question','Reponse 1', 'Answer 2')");
}


@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        /* Pas pour le moment */
}

public Cursor getTableName() {
    this.db = this.getWritableDatabase();
    return db.rawQuery("SELECT name FROM sqlite_master WHERE type='table' And name is not 'sqlite_sequence' And name is not'android_metadata'", null);

}

public Cursor getTableContent() {
    this.db = this.getWritableDatabase();
    return db.rawQuery("SELECT Question FROM " + Table_Name, null);
}

public Cursor getTableContents() {
    this.db = this.getWritableDatabase();
    return db.rawQuery("SELECT * FROM " + Table_Name, null);
}


public void insertData(String question, String[] table, String reponsevrai) {

    ContentValues cv = new ContentValues();
    cv.put("Question", question);
    // for(int j=0; j<=table.length; j++){
    cv.put("Reponse1", table[0]);
    cv.put("Reponse2", table[1]);
    cv.put("Reponse3", table[2]);
    cv.put("Reponse4", table[3]);
    cv.put("Reponse5", table[4]);
    cv.put("Reponse6", table[5]);
    cv.put("Reponse7", table[6]);

    // }
    cv.put("TrueReponse", reponsevrai);
    db.insert(Table_Name, null, cv);
    Log.d("AddQuizz", "Insertion of Data succed");
}

public void chargerLesQuizzs(List<String> lcs) {

    Cursor c = this.getTableName();

    if (c.moveToFirst()) {
        while (!c.isAfterLast()) {
            lcs.add(c.getString(c.getColumnIndex("name")));
            c.moveToNext();
        }
    }
    c.close();
}

public void chargerLesQuestions(List<String> lcs) {
    Cursor cursor = this.getTableContent();
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        String Question = cursor.getString(0);
        lcs.add(Question);
        cursor.moveToNext();
    }
    cursor.close();
}

public void chargerLesDonees(Question[] q) {
    String[] quest = new String[7];
    Cursor cursor = this.getTableContents();
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        quest[0] = cursor.getString(cursor.getColumnIndex("Reponse1"));
        quest[1] = cursor.getString(cursor.getColumnIndex("Reponse2"));
        quest[2] = cursor.getString(cursor.getColumnIndex("Reponse3"));
        quest[3] = cursor.getString(cursor.getColumnIndex("Reponse4"));
        quest[4] = cursor.getString(cursor.getColumnIndex("Reponse5"));
        quest[5] = cursor.getString(cursor.getColumnIndex("Reponse6"));
        quest[6] = cursor.getString(cursor.getColumnIndex("Reponse7"));

        q[j] = new Question(cursor.getString(cursor.getColumnIndex("Question")), quest, cursor.getString(cursor.getColumnIndex("TrueReponse")));
        j++;

        cursor.moveToNext();
    }
    cursor.close();
}}

Here is a piece of the AddQuizz activity.

public class AddQuizz extends Activity {
String a;
String trueAnswer;
String []reponses={null,null,null,null,null,null,null};
int i=0;

EditText Questions, Reponses, Name;
Button SaveQuestion, Instruction, NextQuestion, SaveReponse;
RadioGroup radios;
RadioButton vrai, faux;

ListView ListSaisi;
List<String> listQuestion = new ArrayList<String>();
ArrayAdapter<String> adapter;
BasedeDonee db;
SQLiteDatabase SQ;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_add_quizz);
    Questions=(EditText)findViewById(R.id.editTextQuestion);
    Reponses=(EditText)findViewById(R.id.editTextReponse);
    Name=(EditText)findViewById(R.id.SaveName);

    Questions.setVisibility(View.INVISIBLE);
    Reponses.setVisibility(View.INVISIBLE);

    SaveQuestion=(Button)findViewById(R.id.SaveQuestion);
    SaveReponse=(Button)findViewById(R.id.SaveReponse);
    Instruction=(Button)findViewById(R.id.buttonInstruction);
    NextQuestion=(Button)findViewById(R.id.buttonQuestionNext);

    SaveQuestion.setVisibility(View.INVISIBLE);
    SaveReponse.setVisibility(View.INVISIBLE);
    SaveQuestion.setVisibility(View.INVISIBLE);
    NextQuestion.setVisibility(View.INVISIBLE);

    vrai=(RadioButton)findViewById(R.id.VraiRadioButton);
    faux=(RadioButton)findViewById(R.id.FauxRadioButton);
    radios=(RadioGroup)findViewById(R.id.group);
    radios.setVisibility(View.INVISIBLE);

    ListSaisi=(ListView) findViewById(R.id.listViewReponses);
    ListSaisi.setVisibility(View.INVISIBLE);





}

public void saveNameListener(View v){

    db=new BasedeDonee(this);
    db.Table_Name=Name.getText().toString();
    db.chargerLesQuestions(listQuestion);


    adapter=new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1, listQuestion);


    ListSaisi.setAdapter(adapter);

    Questions.setVisibility(View.VISIBLE);
    Reponses.setVisibility(View.VISIBLE);
    radios.setVisibility(View.VISIBLE);
    SaveQuestion.setVisibility(View.VISIBLE);
    SaveReponse.setVisibility(View.VISIBLE);
    SaveQuestion.setVisibility(View.VISIBLE);
    NextQuestion.setVisibility(View.VISIBLE);
    ListSaisi.setVisibility(View.VISIBLE);
}}

Check your public String Table_Name; initialization. Are you providing a valid string for Table_Name? — Sumighosh Charuvil, Oct 27 '15 at 12:33
You have (public String Table_Name;) but where is the table name??. I think it should be (public String Table_Name = "the_table_name";) or just after that add (Table_Name = "the_table_name";) — Tasos, Oct 27 '15 at 12:38
I don't want Table_Name initialized, because I want the created table has for name the text entered by the user Table_Name=Name.getText().toString(); — YOB99, Oct 27 '15 at 13:35
After I uninstalled the app I get: Caused by: android.database.sqlite.SQLiteException: near "null": syntax error (code 1): , while compiling: CREATE TABLE null (_id integer primary key autoincrement, Question TEXT,Reponse1 TEXT,Reponse2 TEXT,Reponse3 TEXT,Reponse4 TEXT,Reponse5 TEXT,Reponse6 TEXT,Reponse7 TEXT,TrueReponse TEXT); I noticed that i had an old Table stored in my Database this is why i didnt get this error on first place. Now the Table_Name is null it is like the db.Table_Name=Name.getText.toString(); does nothing. — YOB99, Oct 27 '15 at 13:52

score 0 · Accepted Answer · edited May 23 '17 at 11:59

0

Make sure that the following method is running without any error. Somehow it seems your app is running a SELECT query before the given table is created. Have a look at section 9.2 in the following tutorial for an example: http://www.vogella.com/tutorials/AndroidSQLite/article.html

@Override
public void onCreate(SQLiteDatabase database) {
  this.db = database;
  database.execSQL(DATABASE_CREATE1);
  db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2, TrueReponse) VALUES ('First Question','Reponse 1','Reponse 2','Reponse 1')");
  db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2) VALUES ('Second Question','Reponse 1','Reponse 2')");
  db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2) VALUES ('Third Question','Reponse 1', 'Answer 2')");
}

UPDATE

Try this code:

@Override
public void onCreate(SQLiteDatabase database) {
  this.db = database;
  String sql = String  DATABASE_CREATE1 = "CREATE TABLE "+ Table_Name
    + " (_id integer primary key autoincrement, "
    + "Question TEXT,"
    + "Reponse1 TEXT,"
    + "Reponse2 TEXT,"
    + "Reponse3 TEXT,"
    + "Reponse4 TEXT,"
    + "Reponse5 TEXT,"
    + "Reponse6 TEXT,"
    + "Reponse7 TEXT,"
    + "TrueReponse TEXT);"; 
  database.execSQL(sql);
  db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2, TrueReponse) VALUES ('First Question','Reponse 1','Reponse 2','Reponse 1')");
  db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2) VALUES ('Second Question','Reponse 1','Reponse 2')");
  db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2) VALUES ('Third Question','Reponse 1', 'Answer 2')");
}

If this still does not work, the problem might be in the call order to the onCreate method. It is called before Table_name is setup. Check this out to make sure: When the SQLiteOpenHelper onCreate method is called?

edited May 23 '17 at 11:59

Community

1
1

answered Oct 27 '15 at 12:46

narko

3,645
1
28
33

I get this after i uninstalled the app. Caused by: android.database.sqlite.SQLiteException: near "null": syntax error (code 1): , while compiling: CREATE TABLE null (_id integer primary key autoincrement, Question TEXT,Reponse1 TEXT,Reponse2 TEXT,Reponse3 TEXT,Reponse4 TEXT,Reponse5 TEXT,Reponse6 TEXT,Reponse7 TEXT,TrueReponse TEXT); – YOB99 Oct 27 '15 at 13:46
An old table i had created was stored in the database. this is why i didn't saw this NEW ERROR. – YOB99 Oct 27 '15 at 13:58
So, could you solve the issue now? As you can see the table name is coming empty: "CREATE TABLE null". This means the issue might be here: `db=new BasedeDonee(this); db.Table_Name=Name.getText().toString();` – narko Oct 27 '15 at 13:59
No I can't solve it. It would be easier to create just one table, by just writting Table_Name="Questionnaire". But I wanna generate a Table Every time that the user enter a text and click the button, the new table created will have for a name the same Text in the EditText. So I thought that by putting the value of EditText in db.TAble_Name I could easily generate tables but it seems that the BaseDonne does not take into account the modification made to its attributes outside the class. – YOB99 Oct 27 '15 at 14:13
I understand what you want to do. The problem is that when you define the constant `DATABASE_CREATE1` the variable `Table_name` is null. You need to handle that correctly in the onCreate method. – narko Oct 27 '15 at 14:29
OKOK so you want me to write public String Table_Name="Questionnaire" right but when i do that the table name will be "Questionnaire" not what the user choose. And i could not generate others Table for the user. – YOB99 Oct 27 '15 at 14:35
No, I didn't say that... In your BasedeDonee.onCreate method you have to replace `database.execSQL(DATABASE_CREATE1);` by the statement for creating your DB and check whether Table_name has been setup already. Don`t use a constant there because at the time that constant is defined Table_name is null. – narko Oct 27 '15 at 14:42
by the statement of creating my DB... I don't know what you mean i'm new on Android prgramming. Do u mean replace database.execSQL(DATABASE_CREATE1) by db.execSQL(DATABASE_CREATE1). – YOB99 Oct 27 '15 at 15:39
The only thing that cause probleme is Table_Name. I just don't get why Table_Name is null. – YOB99 Oct 27 '15 at 15:42
As I said, Table_Name is null in your SQL statement because it is in the scope of a constant definition. That's why I said you have to create the SQL statement inside the onCreate method. I didn't try it myself, but I think at that point the variable Table_Name will be correctly setup. – narko Oct 27 '15 at 15:52
OK it works it creating just one table not 2 or 3 just one. when i m trying to add a new table called "narko" i get the error: Caused by: android.database.sqlite.SQLiteException: no such table: narko (code 1): , while compiling: SELECT Question FROM narko – YOB99 Oct 27 '15 at 16:34
You should know that the method to create your database is called only once, that's why your new tables are not there. You should create another method that you can call from your activity. This method will be in charge of creating your new table with the given name. By the way, if any of the previous comments worked for you, please mark it as good so that readers can know too. – narko Oct 27 '15 at 20:19

score 0 · Answer 2 · answered Oct 27 '15 at 13:09

0

I believe you are getting error because of this

db.Table_Name=Name.getText().toString();

You can set the table name in BasedeDonee class itself like this and try again

public String Table_Name="Questionaries";

answered Oct 27 '15 at 13:09

Nitesh

263
7
22

I don't want Table_Name initialized, because I want the created table has for name the text entered by the user And i want to generate Tables not just one Table, this is why a put Table_Name=Name.getText().toString(); I wanna generate a Table Every time that he enter a text and click the button, the new table created will have for a name the same Text in the EditText. – YOB99 Oct 27 '15 at 13:37
Then just declare Table_Name static in BasedeDonee class. – Nitesh Oct 27 '15 at 13:44
Same thing, nothing changed. – YOB99 Oct 27 '15 at 13:56
Can you write this in constructor? this.db = context.openOrCreateDatabase("Test", Context.MODE_PRIVATE, null); db.execSQL(DATABASE_CREATE1); db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2, TrueReponse) VALUES ('First Question','Reponse 1','Reponse 2','Reponse 1')"); db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2) VALUES ('Second Question','Reponse 1','Reponse 2')"); db.execSQL("INSERT INTO " + Table_Name + " (Question, Reponse1, Reponse2) VALUES ('Third Question','Reponse 1', 'Answer 2')"); – Nitesh Oct 27 '15 at 13:59
"Test" is database name in above comment – Nitesh Oct 27 '15 at 13:59
i wrote this in the oncreate method. can you explain why should i put it in the constructor? – YOB99 Oct 27 '15 at 14:17
I have copied your class and tested by writing said code in constructor and I didn't get the error you faced at least in creating table.Hence I suggested you to give it a try. – Nitesh Oct 27 '15 at 14:20
Can you try passing table name in constructor and make changes accordingly as you got Table_Name is null? – Nitesh Oct 28 '15 at 10:44
I have changed the code I declared DATABASE_CREATE1 in the ocreate Method and it worked but the thing is i can create only one TABLE. – YOB99 Oct 28 '15 at 23:23
And i wanted to generate tables everytime the user saves the text. – YOB99 Oct 28 '15 at 23:24

How to generate TABLES using SqliteDatabse

2 Answers2