* @throws Exception
*/
public SimpleTable getMoteData(String session_id) throws Exception
{
String query,select;
SQLHelper sql = null;
ResultSet rs = null;
SimpleTable result = null;
Object[] param = { session_id };
try {
sql = new SQLHelper();
sql.openDB();
query = "select m.id mote_id, m.site_id site_id,s.sitename site, " +
"case when m.curr_session_id=? then 'controlled' " +
"when isnull(m.curr_session_id) then 'available' else 'occupied' end mote_usage ";
select = "select id, name from moteattrtype order by sortseq";
rs = sql.retrieve(select);
while (rs.next()) {
query+=getMoteAttrSubquery(rs.getString("name"),rs.getString("id"));
}
select = "select id, name from siteattrtype order by sortseq";
rs = sql.retrieve(select);
while (rs.next()) {
query+=getSiteAttrSubquery(rs.getString("name"),rs.getString("id"));
}
query+=" from mote m, site s " +
" where s.id=m.site_id";
rs = sql.retrieve(query, param);
rs.last();
int rows = rs.getRow();
int cols = rs.getMetaData().getColumnCount();
result = new SimpleTable(rows,cols);
rs.beforeFirst();
for (int i=0;i<cols;i++){
String title = rs.getMetaData().getColumnLabel(i+1);
String name = rs.getMetaData().getColumnName(i+1);
String classname = rs.getMetaData().getColumnClassName(i+1);
result.getColumnHeaders()[i] = new ColumnHeader(title,name,true,classname);
}
int i = 0;
while (rs.next()) {
for (int j=0;j<cols;j++)
{
result.getData()[i][j] = rs.getObject(j+1);
}
i++;
}
return result;
} finally {
sql.closeDB();
}
}