This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes Set parameter not working in the PreparedStatement , why? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Set parameter not working in the PreparedStatement , why?" Watch "Set parameter not working in the PreparedStatement , why?" New topic
Author

Set parameter not working in the PreparedStatement , why?

Solo Chiang
Greenhorn

Joined: Sep 14, 2005
Posts: 18
Not sure why my SQL is not working properly after I set parameters in the SQL instead of put variable .

The following is my new code.

String HOLDINGS_QUERY = "select holding_name, holding_value, as_of_date " + "from fund_grp_holding "
+ "where holding_type = ? " + " and fund_grp = ? order by ? " ;

stmt = conn.prepareStatement(HOLDINGS_QUERY);
stmt.setInt(1, type); // type is 6
stmt.setInt(2, fundGroupNumber); // fundGroupNumber is 31
stmt.setString(3, orderyBy); // orderBy is "HOLDING_VALUE DESC"


The search result just ignore "order by" and have a result without ordering my the holding value



The following code will fix the problem and give the search result order by holding value by if I just concat the variable into the query

String HOLDINGS_QUERY = "select holding_name, holding_value, as_of_date " + "from fund_grp_holding "
+ "where holding_type = ? " + " and fund_grp = ? order by " + orderyBy;

stmt = conn.prepareStatement(HOLDINGS_QUERY);
stmt.setInt(1, type); // type is 6
stmt.setInt(2, fundGroupNumber); // fundGroupNumber is 31




WHY? What happened? Anybody can tell me?





Jhakda Velu
Ranch Hand

Joined: Feb 26, 2008
Posts: 166
Order by is a clause and not a parameter that can be set.

Jhakda


If I become filthy rich, I'll sponsor research for painless dental treatment at Harvard Medical School. Thats why,I'm learning Java.I have 32 teeth, 22 are man made.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Set parameter not working in the PreparedStatement , why?