H2O Portable Problem

[Joney]

Following the steps, i trying to make H2O portable.

I have successfully setup everything for the ebMS portion of H2O. ebMS
admin page load successfully, database connection is fine. ports are
being listen.

However, I experienced some problem on the AS2 admin page.

Caused by java.sql.SQLException: Unexpected token in statement [select
* from message where message_id like ? and message_box like ? and
as2_from like ? and as2_to like ? and principal_id like ? and status
like ? and trim(upper(is_receipt)) = 'FALSE' order by time_stamp desc
LIMIT ? offset ?]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)

Because I am not familiar with HSQLDB, I don't know what is the
"Unexpected token". Could anyone help me out on this?

[hoc...@gmail.com]

Hi Joney,

Can you tell me how to trigger this error? I'm testing AS2 admin page
again but cannot find the error.

Thanks!
Philip

[Joney]

Hi Philip,

Thanks for your help.
Here are what i did,
1) Start HSQLDB, Jetty.
2) Open admin page (http://localhost:8080/corvus/admin/home/)
3) Click on "AS2 plugin" on the module.
4) The page cannot be generated successfully and error shown.

Error hk.hku.cecid.piazza.commons.servlet.RequestListenerException
Message Error in processing XSL pagelet

In Error Trace, a long Java Exception is shown. Here i highlight some
portions:

hk.hku.cecid.piazza.commons.servlet.RequestListenerException: Error in
processing XSL pagelet
at
hk.hku.cecid.piazza.commons.pagelet.xslt.HttpXsltPageletAdaptor.processPagelet(HttpXsltPageletAdaptor.java:
64)
at
hk.hku.cecid.piazza.commons.pagelet.HttpPageletAdaptor.processTemplate(HttpPageletAdaptor.java:
142)
at
hk.hku.cecid.piazza.commons.pagelet.HttpPageletAdaptor.processRequest(HttpPageletAdaptor.java:
92)
....

Caused by java.lang.RuntimeException: Unable to process the message
search page request
at
hk.hku.cecid.edi.as2.admin.listener.MessageHistoryPageletAdaptor.getCenterSource(MessageHistoryPageletAdaptor.java:
46)
at
hk.hku.cecid.piazza.commons.pagelet.xslt.BorderLayoutPageletAdaptor.getPageletSource(BorderLayoutPageletAdaptor.java:
69)
at
hk.hku.cecid.piazza.commons.pagelet.xslt.HttpXsltPageletAdaptor.processPagelet(HttpXsltPageletAdaptor.java:
60)
....

Caused by hk.hku.cecid.piazza.commons.dao.DAOException: Error in
executing hk.hku.cecid.edi.as2.dao.MessageDataSourceDAO::Process@e7d53
at
hk.hku.cecid.piazza.commons.dao.ds.DataSourceProcess.start(DataSourceProcess.java:
97)
at
hk.hku.cecid.piazza.commons.dao.ds.DataSourceDAO.executeQuery(DataSourceDAO.java:
413)
at
hk.hku.cecid.piazza.commons.dao.ds.DataSourceDAO.executeQuery(DataSourceDAO.java:
392)
....

Caused by hk.hku.cecid.piazza.commons.dao.DAOException: Error in
executing query: select * from message where message_id like ? and

message_box like ? and as2_from like ? and as2_to like ? and
principal_id like ? and status like ? and trim(upper(is_receipt)) =
'FALSE' order by time_stamp desc LIMIT ? offset ?
at

hk.hku.cecid.piazza.commons.dao.ds.DataSourceQuery.doTransaction(DataSourceQuery.java:
128)
at
hk.hku.cecid.piazza.commons.dao.ds.DataSourceProcess.start(DataSourceProcess.java:
89)
....

Caused by java.sql.SQLException: Unexpected token in statement [select
* from message where message_id like ? and message_box like ? and
as2_from like ? and as2_to like ? and principal_id like ? and status
like ? and trim(upper(is_receipt)) = 'FALSE' order by time_stamp desc
LIMIT ? offset ?]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)

at
org.apache.commons.dbcp.cpdsadapter.PooledConnectionImpl.prepareStatement(PooledConnectionImpl.java:
210)
....

[hoc...@gmail.com]

Thanks Joney!

In HSQL, it has different switch from function TRIM().

TRIM([{LEADING | TRAILING | BOTH}] FROM <string expression>)

Please replace all trim(....) to trim(both from....) in as2.dao.xml.

e.g.

select * from message where message_id like ? and message_box like ?
and as2_from like ? and as2_to like ? and principal_id like ? and

status like ? and trim(both from upper(is_receipt)) = 'FALSE' order by

time_stamp desc LIMIT ? offset ?

Philip