Tony Mathew Tony Mathew - 4 months ago 14
SQL Question

Dynamic table creation on button click in SQLite

I am creating a login application and I wanted to create a table for each user when they click the register button. The problem is, I am using their unique email id as table name. Later I learned that table names cannot have special characters like "@". Please suggest me a way to create table with names having special characters like "@". Or else, please suggest me a way to create tables with unique names for each user when they click the register button.

Please Note :"TABLE_NAME" in the code given below is another table containing registered user details.

The code is given below:

public boolean insertData(String name, String email, String mobile, String pass){
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("create table "+email+"(Choice TEXT, Q1 INTEGER, Q2 INTEGER, Q3 INTEGER, Q4 INTEGER, Q5 INTEGER)");
ContentValues cv = new ContentValues();
cv.put(NAME, name);
cv.put(EMAIL, email);
cv.put(MOBILE, mobile);
cv.put(PASSWORD, pass);
long result = db.insert(TABLE_NAME,null,cv);
db.close();
if(result == -1)
return false;
else
return true;
}

CL. CL.
Answer

In SQL, identifiers can be quoted with double quotes. Any double quotes inside the identifier must be doubled to escape them; and in Java strings, double quotes must be escaped with a backslash:

String sqlTableName = "\"" + email.replace("\"", "\"\"") + "\"";
db.execSql("CREATE TABLE " + sqlTableName + "...");

However, putting data into the table name is a bad idea, because it cannot be queried and modified like all your other data. Better use a single table, and put that information into another colum:

CREATE TABLE Users(EMail TEXT, Choice TEXT, Q...);
Comments