How to create a database dynamically a from a variable?

Question

I have a string called str_train_no and i want to create a database with the same name and so far i have tried

        String name=str_Train_no+".db";
        SQLiteDatabase traindb = openOrCreateDatabase(name,SQLiteDatabase.CREATE_IF_NECESSARY , null);
        sql="CREATE TABLE IF NOT EXISTS "+str_Train_no+" ("
        +"Stops VARCHAR);";
        traindb.execSQL(sql);

Heres the Source code for the database declaration and insertion part

SQLiteDatabase db = openOrCreateDatabase( "Train_list.db", SQLiteDatabase.CREATE_IF_NECESSARY , null);
    try {
        final String CREATE_TABLE_TRAIN_LIST = "CREATE TABLE IF NOT EXISTS Train_list ("
                + "Train_name VARCHAR,"
                + "Train_no VARCHAR,"
                + "Train_start VARCHAR,"
                + "Train_end VARCHAR,"
                + "Seats_Available VARCHAR);";
        db.execSQL(CREATE_TABLE_TRAIN_LIST);
        Toast.makeText(admin_manipulation.this, "Table created", Toast.LENGTH_LONG).show();
        String sql = "INSERT or replace INTO Train_list (Train_name, Train_no, Train_start, Train_end, Seats_Available) VALUES('"+str_Train_name + "',' " +str_Train_no + "', '" +str_Train_start+"','" +str_Train_end+"',' " +str_Train_seats +"');";
        db.execSQL(sql);
        Toast.makeText(admin_manipulation.this, "Inserted Sucessfully", Toast.LENGTH_SHORT).show();
        String name=str_Train_no+".db";
        SQLiteDatabase traindb = openOrCreateDatabase(name, SQLiteDatabase.CREATE_IF_NECESSARY , null);
        sql="CREATE TABLE IF NOT EXISTS "+str_Train_no+" ("
                +"Stops VARCHAR);";
        traindb.execSQL(sql);
        Toast.makeText(admin_manipulation.this, "Table "+str_Train_no+" verified", Toast.LENGTH_SHORT).show();
    }
    catch (Exception e) {
        Toast.makeText(admin_manipulation.this, "An Error has occured", Toast.LENGTH_SHORT).show();
    }

and this keeps throwing an exception. Any ideas why? Thanks in advance!

its a string for example when i tested it out it was the string "16525" — Arun Mani, Oct 08 '17 at 08:51
I figured out that theres an error only when you give numbers as table names as @eckes suggested can someone tell me how i can reflect this in my question? — Arun Mani, Oct 08 '17 at 09:27
For the next question: pruning down the code was good. You just need to mention the values of all variables, the actual (line) position of the message and the actual exception message. If there is relevant log output (not in this case) then this as well. — eckes, Oct 08 '17 at 09:42

eckes · Answer 1 · 2017-10-08T09:45:17.080

With str_train_no beeing a numerical string you have the problem that you try to create a table with a name which is no valid SQL identifier.

In this particular case you can solve it by prefixing it with a text string like:

sql = "CREATE TABLE IF NOT EXISTS train"+str_Train_no+ ...

In your case you can also use a static table name, since you create one database per train (which might not be the best thing to do).

score -1 · Answer 2 · answered Oct 08 '17 at 09:19

-1

use text instead of varchar. as I know there is not varchar in SQLite.

answered Oct 08 '17 at 09:19

abtech

31
1
6

1

Column **type** can be virtually anything and VARCHAR is specifically mentioned in the SQLite documentation regarding DataTypes [Datatypes In SQLite Version 3](https://sqlite.org/datatype3.html). Perhaps have a read of [How flexible/restricive are SQLite column types?](https://stackoverflow.com/questions/45557278/how-flexible-restricive-are-sqlite-column-types) – MikeT Oct 08 '17 at 10:46

How to create a database dynamically a from a variable?

2 Answers2