ORDER BY clause not returning desired resullt

[niraj singh]

Hi,

I am trying to sort database rows using order by clause, but the ResultSet I get is not sorted properly. The order is always the same irrespective of column I pass in the order by clause.

I am using mysql 5.0.23-standard edition along with mysql connectorj 5.

I am pasting the table, query and code below. Please tell me where I am doing wrong.


-------
TABLE
-------

+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| widget_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| acct_id | int(10) unsigned | NO | | NULL | |
| partner_type | int(10) unsigned | YES | | NULL | |
| widget_table | varchar(25) | YES | | NULL | |
| widget_type | int(10) unsigned | YES | | NULL | |
| description_name | varchar(100) | YES | | NULL | |
| notes | varchar(255) | YES | | NULL | |
| creation_timestamp | datetime | YES | | NULL | |
| tech_type | varchar(50) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+

-------
query
-------

select * from widget_summary where acct_id=0 or acct_id=894 order by acct_id, creation_timestamp asc;

-----------
method/code
-----------
public static List getAllWidgetList(long acctId, String sortField, boolean reverse) throws ProcessingException{
List widgetList = new ArrayList();
WidgetSummaryVO wsvo = null;
Connection con = null;
StringBuffer widgetSummaryQuery = new StringBuffer();
widgetSummaryQuery.append("select * from widget_summary where acct_id=? OR acct_id=0 order by acct_id ASC, ?");
if(reverse) widgetSummaryQuery.append(" DESC");
try {
DBConnection dBConnection = new DBConnection();
con = dBConnection.getConnection();
PreparedStatement widgetStmt = con.prepareStatement(widgetSummaryQuery.toString());
widgetStmt.setLong(1, acctId);
widgetStmt.setString(2, sortField);
log.debug("sort query : " + widgetSummaryQuery.toString());
ResultSet widgetRS = widgetStmt.executeQuery();
while (widgetRS.next()) {
wsvo = new WidgetSummaryVO();
wsvo.setWidgetId(widgetRS.getLong("widget_id"));
wsvo.setAcctId(widgetRS.getLong("acct_id"));
wsvo.setPartnerType(widgetRS.getInt("partner_type"));
wsvo.setWidgetTable(widgetRS.getString("widget_table"));
wsvo.setWidgetType(widgetRS.getInt("widget_type"));
wsvo.setDescriptionName(widgetRS.getString("description_name"));
wsvo.setNotes(widgetRS.getString("notes"));
wsvo.setCreationTimeStamp(widgetRS.getTimestamp("creation_timestamp").getTime());
wsvo.setTechType(widgetRS.getString("tech_type"));
widgetList.add(wsvo);
}
}catch(Exception e){
log.fatal("Error while retriving widget summary : " + e.getMessage());
throw new ProcessingException(PickleErrorConstants.FATAL_EXCEPTION);
}finally {
if (con != null) {
try {
con.close();
} catch (Exception ignore) {
log.fatal("Fatal Error : " + ignore);
}
}
}
return widgetList;

}

I pass sortField as column_name (creation_timestamp/widget_table etc)..

----------------------------------------------------------------------
Also to be noted is that the query returns properly when run dirctly on mysql client console. The problem arises once I use JDBC to retrieve the rows.

Please help in finding out where I am doing wrong

Thanks
Niraj