CODEWITHSUNDEEP
javasqljdbcprepared-statement
krystah
krystah

Reputation: 3733

JDBC: Create Table with PreparedStatement, SQL Syntax Error or not?

I am encountering some difficulties trying to, in a dynamic way, create a new table using PreparedStatement. I am trying to add a unknown number of columns and their respective types and sizes. I keep getting SQL Syntax errors, but I suspect this may not be the case here.

Variables used:

Should be pretty self-explanatory.

con.setAutoCommit(false);
String sql = "? ?(?),";
PreparedStatement ps = con.prepareStatement(sql);
ps.addBatch("create table " + tablename + "( ");

for (int i = 0; i < colNames.length; i++){

    if (!(i == colNames.length-1)) {
        ps.setString(1, colNames[i]);
        ps.setString(2, colTypes[i]);
        ps.setString(3, colSizes[i]);

    } else {
        String format = "%s %s(%s)";
        String lastLine = String.format(format, colNames[i], colTypes[i], colSizes[i]);
        ps.addBatch(lastLine);

    }
}

ps.addBatch(");");
ps.executeBatch();

NOTE: Yes, this is homework. I don't want any dead giveaways, rather pointers as to in what way I am misusing some functions, which I suspect.

Best regards, Krys

Upvotes: 3

Views: 3208

Answers (1)

Thilo
Thilo

Reputation: 262534

You need to give the full SQL statement to addBatch. It is not a tool to construct a dynamic SQL statement. It is a tool to improve performance when running multiple statements. You don't need it here.

You also don't need a PreparedStatement here, as you are not going to have bind variables (i.e. column data as opposed to column names) and are not going to run the same SQL repeatedly (but it does not hurt, either). setString and friends do not work for column or table names, just for data.

A StringBuilder is a good tool to construct a String with variable parts.

Upvotes: 2

Related Questions