ORDER BY clause not returning desired resullt
Posted by: niraj singh
Date: June 19, 2007 01:21AM

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

Options: ReplyQuote


Subject
Written By
Posted
ORDER BY clause not returning desired resullt
June 19, 2007 01:21AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.