CODEWITHSUNDEEP
javamysqljdbc
John Collins
John Collins

Reputation: 131

JDBC PreparedStatement results in MySQL syntax error

I am getting the error "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 ''orderr'' at line 1" - so I assume the error is that I have used two ' but in my code I have not used any '. NB the table is actually named orderr.

public void insertIntoDatabase(String table, Object... entries) {       // take a table and 
    Connection con = connect();                                         //add entries
    PreparedStatement preparedStatement = null;
    PreparedStatement preparedStatement2 = null;
    ResultSet rs = null;

    StringBuffer columnNames = new StringBuffer();
    StringBuffer sbEntries = new StringBuffer();
    for (int i = 0; i < entries.length; i++) {
        if (entries[i] instanceof Integer)
            sbEntries.append((Integer) entries[i]); 
        else if (entries[i] instanceof String)      
            sbEntries.append((String) entries[i]); 

        if (i != entries.length - 1)//if not last entry add 
            sbEntries.append(" ,"); // a ' ,'.
    }
    try {
        preparedStatement = con.prepareStatement("select * from ? ;");
        preparedStatement.setString(1, table);
        preparedStatement2 = con
                .prepareStatement("Insert into ?( ? ) values ( ? );");
        ResultSet resultSet = preparedStatement.executeQuery(); // get the
                                                                // number of
                                                                // columns
        ResultSetMetaData rsmd; // for the table
        rsmd = resultSet.getMetaData();
        int columnCount = rsmd.getColumnCount();
        for (int i = 1; i < columnCount + 1; i++) { // get column names, add to 
            columnNames.append(rsmd.getColumnName(i)); // to sb
            if (i != columnCount)
                columnNames.append(" ,");
        }
        columnCount = rsmd.getColumnCount();
        preparedStatement2.setString(1, table);                     
        preparedStatement2.setString(2, columnNames.toString());    //add sb's to statement
        preparedStatement2.setString(3, sbEntries.toString());
        preparedStatement2.executeUpdate();

    } catch (SQLException e) {
        System.out.println("2" + e.getMessage());
    }
    finally{
        try {
            if (rs != null) {
                rs.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if(preparedStatement2 != null){
                preparedStatement2.close();
            }
            if (con != null) {
                con.close();
            }

        } catch (SQLException e) {
            System.out.print("3" +e.getMessage());
        }

    }

}

Upvotes: 1

Views: 815

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108994

In most databases you can't parametrize object names like table names, in MySQL you theoretically can as MySQL Connector/J by default doesn't use server side parameters, but instead it rewrites the query before sending it to the server. However the value will be inserted as a quoted string, and object names cannot be a quoted string, so it still won't work.

So INSERT INTO ? or SELECT ... FROM ? will not work, as it produces INSERT INTO 'theTable' or SELECT ... FROM 'theTable'.

The object names need to be part of the actual query. Do not use parameters for them. Most other databases (or their drivers) would have thrown an exception for having a parameter in this position.

Upvotes: 4

CuriousMind
CuriousMind

Reputation: 3173

There are two mistakes in your code.

  1. You cannot set table name in SQL statements using prepared statement.
  2. You have ; in the SQL statement.

Once you take care of above points, your code should work.

Upvotes: 1

Related Questions