Java create table sql statement

Question

i want to create a new mySQL table with this method. Database connection is working perfectly. I made many working statements like this, but this one, with creating a table isnt working. So the mistake has to be in this few lines of code. On the botton i added the error message.

 public void createNewTable(){
        DatabaseConnection connectNow = new DatabaseConnection();
        Connection connectDb = connectNow.getConnection();
        String tableName = "users";
        try {
               String createTable = "CREATE TABLE ? (firstname varchar(30), lastname varchar(30), tel integer" +
                "email varchar(40), arrive_stamp timestamp, quit_stamp timestamp)";
            PreparedStatement myStmt = connectDb.prepareStatement(createTable);
            myStmt.setString(1, tableName);
            myStmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            e.getCause();
        }

    }






 Illegal operation on empty result set.
    java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near ''users' (firstname varchar(30), 
lastname varchar(30), tel integer, email varchar' at line 1

Tim Biegeleisen · Accepted Answer · 2020-12-15 13:33:31Z

1

Database object names (including table names), cannot be bound to placeholders. You will have to hard code the table name or use string concatenation. The following will work:

try {
    String sql = "CREATE TABLE users (firstname varchar(30), lastname varchar(30), tel integer, " +
                 "email varchar(40), arrive_stamp timestamp, quit_stamp timestamp)";
    PreparedStatement myStmt = connectDb.prepareStatement(sql);
    myStmt.executeUpdate();
}
catch (Exception e) {
    e.printStackTrace();
    e.getCause();
}

answered Dec 15, 2020 at 13:33

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

6 Comments

testerprox Over a year ago

Thank you for the answer :) But like this im not safe against SQL injection right?

Tim Biegeleisen Over a year ago

No, the opposite, the statement has zero inputs from the outside, so it is 100% safe from SQL injection. It is unusual to have the table name come in from the outside. I'm not saying there are no such use cases, just that in your case it probably indicates a design problem.

testerprox Over a year ago

You re right. But on the longer term, i plan to replace "user" with an input value from another method. Thats why i used a prepared statement. Is there any way, to do this and still be safe from SQL injection?

Tim Biegeleisen Over a year ago

If this really is your requirement, then you will have to sterlize the input name yourself. This means ensuring that it has no whitespace, is a single "word," and is not a SQL keyword, among other things. It still carries a risk.

testerprox Over a year ago

Ok thank you very much for your help :) . One more question: Your code example is working, but why do I get this Error Message: "Illegal operation on empty result set."?

|

Popeye · Accepted Answer · 2020-12-15 13:38:25Z

0

You are missing , between tel and email column. And as mentioned by Tim in the comment, you should use table name in the sql statement itself.

Your sql statement should be

String createTable = "CREATE TABLE your_table_name (firstname varchar(30), lastname varchar(30), tel integer, " + -- added , at the end
                "email varchar(40), arrive_stamp timestamp, quit_stamp timestamp)";

edited Dec 15, 2020 at 13:38

answered Dec 15, 2020 at 13:33

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

Java create table sql statement

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related