Prepared Statement problem

[jim gemlo]

I am trying to use a prepared statment with variables in the sql string and cant get it to work. I can get it to work if I do it like this

String sqlDeleteBackup = "DELETE FROM dbo.BACKUP_DIM where bkup_id = ?";
.
.
.
PreparedStatement stmtUpdateSrvrFact = conn.prepareStatement sqlUpdateServerFact);
stmtUpdateSrvrFact.setString(1, "srvr_fact_key");
stmtUpdateSrvrFact.executeUpdate();
stmtUpdateSrvrFact.close();
  

This will remove the value from the selected row as required. But when I try to pass two values, a string and a variable it doesnt work. I do not get any error from the code either.

 
String sqlUpdateServerFact = "UPDATE dbo.SERVER_FACT SET bkup_key = null WHERE ? = ?";
.
.
.
PreparedStatement stmtUpdateSrvrFact = conn.prepareStatement sqlUpdateServerFact);
stmtUpdateSrvrFact.setString(1, "srvr_fact_key");//column_name
stmtUpdateSrvrFact.setString(2, srvr_fact_key);//variable used to hold desired value
stmtUpdateSrvrFact.executeUpdate();
stmtUpdateSrvrFact.close();
 

Any Ideas?

[ September 27, 2004: Message edited by: jim gemlo ]
[ September 27, 2004: Message edited by: jim gemlo ]

[Stan James]

I'd guess you can't dynamically set the column name that way. The point of the prepared statement is that the database can take time to optimize the statement and use the optimized version over and over. If you go changing column names it can't be sure the optimization is still good.

[Dirk Schreckmann]

Moving this to the JDBC forum...

[Jeanne Boyarsky]

Stan is correct. You can only substitute values in a prepared statements. Not field names or table names.

[Shailesh Chandra]

instead of adding column name dynamically in prepared statement add it in your sql query. Then make prepared statememen and use like

StringBuffer sbSqlUpdateServerFact = new StringBuffer("UPDATE dbo.SERVER_FACT SET bkup_key = null WHERE ").append(" srvr_fact_key or your variable to hold column name ").append("= ?");

PreparedStatement stmtUpdateSrvrFact = conn.prepareStatement(sbSqlUpdateServerFact.toString());

stmtUpdateSrvrFact.setString(1, srvr_fact_key);//variable used to hold vaule

desired valuestmtUpdateSrvrFact.executeUpdate