Hi All,
My Apologies for this very long E-Mail, but this seems to be a complex
problem, and I wanted to hopefully present you each little piece of
information that the experts need to solve this problem... I have
Cross-Posted this message, since it could be a turbine or torque
problem...
I am getting very very frustrated here. Since I have manged to count the
rows for a given criteria in a table by doing the following in my Action
which fills the context for the view...
cri.add(cri.getNewCriterion(WebserverDailyPeer.DAY,
(Object)("(UNIX_TIMESTAMP(" + WebserverDailyPeer.DAY + ") between " +
(von_date / 1000) + " and " +
(bis_date / 1000) + " )"), Criteria.CUSTOM));
long pages = WebserverDailyPeer.getPages(cri, limit);
Log.debug("Number of Pages in Table with Criteria: " + pages);
It works and prints
[Sat Feb 15 10:42:15 CET 2003] -- DEBUG -- Number of Pages in Table with
Criteria: 58
In turbine.log
getPages is from my extended BasePeer Class in which I put the following
functions:
public class ByteActionBasePeer extends BasePeer
{
public static long getNumberOfRowsInTable(String table) throws
TorqueException, DataSetException
{
String query = "SELECT COUNT(*) FROM " + table;
return
((Record)executeQuery(query).get(0)).getValue(1).asLong();
}
public static long getNumberOfRowsInTable(Criteria c) throws
TorqueException, DataSetException
{
c.addSelectColumn("COUNT(*)");
String query = createQueryString(c);
c.getSelectColumns().clear();
return
((Record)executeQuery(query).get(0)).getValue(1).asLong();
}
public static long getPages(Criteria c, int limit) throws
TorqueException, DataSetException
{
long numRows = getNumberOfRowsInTable(c);
long pages = (numRows / limit);
pages = ( numRows % limit == 0 ) ? pages : pages + 1;
return pages;
}
public static long getPages(String table, int limit) throws
TorqueException, DataSetException
{
long numRows = getNumberOfRowsInTable(table);
long pages = (numRows / limit);
pages = ( numRows % limit == 0 ) ? pages : pages + 1;
return pages;
}
}
Now I have my number of Pages, I have my Limit and I have my Index. And
now I am adding these to the criteria,
cri.setLimit(limit);
cri.setOffset(index);
Doing a WebserverDailyPeer.doSelect(cri); did not use the Limit and
Index, so I thought maybe I should add the columns, since I have done a
addSelectColumns("COUNT(*)") and a clear() in my extended BasePeer
Class, and since Criteria sometimes behaves weird. So I did.
cri.addSelectColumn(WebserverDailyPeer.ID);
cri.addSelectColumn(WebserverDailyPeer.DAY);
cri.addSelectColumn(WebserverDailyPeer.AMOUNT);
cri.addSelectColumn(WebserverDailyPeer.DOMAIN);
cri.addSelectColumn(WebserverDailyPeer.KID);
cri.addSelectColumn(WebserverDailyPeer.CREATION);
cri.addSelectColumn(WebserverDailyPeer.SERVERID);
And then verifying the output via:
Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
And this gets correctly translated to:
[Sat Feb 15 10:42:15 CET 2003] -- DEBUG -- FROM logger:AuswerungAction:
SELECT webserver_daily.ID, webserver_daily.DAY, webserver_daily.AMOUNT,
webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
webserver_daily.SERVERID FROM webserver_daily WHERE
(UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600 and 1041375600 )
LIMIT 1, 20
BUT when I then do a
List list = WebserverDailyPeer.doSelect(cri);
The Limit and Index mysteriously disappears... As you can see here:
22802 [Thread-7] DEBUG util.BasePeer - SELECT webserver_daily.ID,
webserver_daily.DAY, webserver_daily.AMOUNT
, webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
webserver_daily.SERVERID FROM webserv
er_daily WHERE (UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600
and 1041375600 ) LIMIT 1, 20
22802 [Thread-7] DEBUG util.BasePeer - SELECT webserver_daily.ID,
webserver_daily.DAY, webserver_daily.AMOUNT
, webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
webserver_daily.SERVERID FROM webserv
er_daily WHERE (UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600
and 1041375600 )
It seems as if The Peer is doing 2 Selects...
The Complete Action Method follows below, so you can verify that I am
not calling do Select twice:
public void doGetwebservers(RunData data, Context context)
throws Exception
{
ParameterParser pp = data.getParameters();
int wsid = pp.getInt("wsid");
int kdid = pp.getInt("kdid");
int von_tag = pp.getInt("von_tag");
int bis_tag = pp.getInt("bis_tag");
int von_monat = pp.getInt("von_monat");
int bis_monat = pp.getInt("bis_monat");
int von_jahr = pp.getInt("von_jahr");
int bis_jahr = pp.getInt("bis_jahr");
int limit = pp.getInt("limit",20);
int index = pp.getInt("index",0);
int curpage = pp.getInt("page",1);
String domain = pp.getString("domain");
Calendar von = new GregorianCalendar(von_jahr, von_monat - 1,
von_tag);
long von_date = (long)((Date)von.getTime()).getTime();
Calendar bis = new GregorianCalendar(bis_jahr, bis_monat - 1,
bis_tag);
long bis_date = (long)((Date)bis.getTime()).getTime();
Criteria cri = new Criteria();
if(wsid > 0)
{
cri.add(WebserverDailyPeer.SERVERID, wsid);
}
if(kdid > 0)
{
cri.add(WebserverDailyPeer.KID, kdid);
}
if((domain != null) && (domain.length() > 0))
{
cri.add(cri.getNewCriterion(WebserverDailyPeer.DOMAIN,
(Object)(WebserverDailyPeer.DOMAIN + " LIKE '%" +
domain + "%' "), Criteria.CUSTOM));
cri.addJoin(WebserverDailyPeer.DOMAIN,
DomainConfigPeer.NAME);
}
cri.add(cri.getNewCriterion(WebserverDailyPeer.DAY,
(Object)("(UNIX_TIMESTAMP(" + WebserverDailyPeer.DAY +
") between " + (von_date / 1000) + " and " +
(bis_date / 1000) + " )"), Criteria.CUSTOM));
long pages =
WebserverDailyPeer.getPages(cri, limit);
Log.debug("Number of Pages in Table with
Criteria: " + pages);
context.put("index", new
Integer(index));
context.put("pages", new Long(pages));
context.put("limit", new
Integer(limit));
context.put("curpage", new
Integer(curpage));
Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
cri.setLimit(limit);
cri.setOffset(1);
cri.addSelectColumn(WebserverDailyPeer.ID);
cri.addSelectColumn(WebserverDailyPeer.DAY);
cri.addSelectColumn(WebserverDailyPeer.AMOUNT);
cri.addSelectColumn(WebserverDailyPeer.DOMAIN);
cri.addSelectColumn(WebserverDailyPeer.KID);
cri.addSelectColumn(WebserverDailyPeer.CREATION);
cri.addSelectColumn(WebserverDailyPeer.SERVERID);
Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
List list = WebserverDailyPeer.doSelect(cri);
context.put("webservers", list);
}
Kind regards
J�rgen Hoffmann
ByteACTION GmbH
cert. Perl Programmer
cert. Linux System Administrator
cert. Java Programmer
Besuchen Sie uns doch auf der CeBIT 2003
in Halle 4/Stand 70
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
