PreparedStatement invalid table name

Question

I'm trying to create a new table using prepared statement but I received an error ORA-00903: invalid table name. I was able to execute the same command in Oracle SQL Developer but not with prepared statement.

try{
    String createMessageTable = "CREATE TABLE ? (chat_with VARCHAR2(255), chatTableName VARCHAR2(255))";
    String tempTable = user.getUsername() + "MessageList";
    PreparedStatement ps1 = dbSingleton.getPreparedStatement(createMessageTable);
    System.out.println(ps1.toString());
    ps1.execute();
}catch(SQLException e){
    e.printStackTrace();
    System.out.println("Unable to create Table");
}

score 1 · Answer 1 · answered Jan 05 '18 at 01:54

It is not possible to have the table name be a parameter in a prepared statement. Allowing for this would likely be a security risk. So in general you would need a separate prepared statement for each different table. So this leaves you with the following option:

try {
    StringBuilder sb = new StringBuilder("");
    sb.append("CREATE TABLE ");
    sb.append(user.getUsername()).append("MessageList");
    sb.append(" (chat_with VARCHAR2(255), chatTableName VARCHAR2(255))";
    PreparedStatement ps1 = dbSingleton.getPreparedStatement(sb.toString());
    System.out.println(ps1.toString());
    ps1.execute();
} catch (SQLException e) {
    e.printStackTrace();
    System.out.println("Unable to create Table");
}

However, it appears that you are trying to create a separate table for each user. This almost certainly is not necessary or even desired. Instead, just create one table for all users, and add a userId column to that table to distinguish one user's records from another user.

This table is to keep record of each user whom he/she chat with and chatTableName will reference to a table name. The chatTableName will hold records of conversation between 2 users. — Wei Jun, Jan 05 '18 at 02:14
@WeiJun That is terrible database design, at least if you're using a relational database. Just keep a single chat table, with two foreign keys, one for each user. That Java isn't letting you use the prepared statement the way you intend is a warning sign that maybe you're doing something wrong (or at least very non standard). — Tim Biegeleisen, Jan 05 '18 at 02:15

PreparedStatement invalid table name

1 Answers1