I changed the query to work with Hsql. To make it work you gotta add the sqlquery child node in <mailet match="All" class="JDBCVirtualUserTable"> as below:
<mailet match="All" class="JDBCVirtualUserTable"> <table>db://maildb/VirtualUserTable</table> <sqlquery> select limit 0 1 VirtualUserTable.target_address from VirtualUserTable, VirtualUserTable as VUTDomains where (VirtualUserTable.user like ? or VirtualUserTable.user like '\\%') and (VirtualUserTable.domain like ? or (VirtualUserTable.domain like '\\%' and VUTDomains.domain like ?)) order by concat(concat(VirtualUserTable.user,'@'),VirtualUserTable.domain) desc </sqlquery> </mailet> > -----Mensagem original----- > De: Noel J. Bergman [mailto:[EMAIL PROTECTED] > Enviada em: quinta-feira, 8 de abril de 2004 21:41 > Para: James Users List > Assunto: RE: JDBCVirtualUserTable mailet problem > > > > I think that using the + operator we can make a query that > will work > > for most databases. > > hsqldb supports concat(). > > See http://hsqldb.sourceforge.net/doc/hsqlSyntax.html#Stored. > > > And about limiting the result (top/limit) we achieve that > geting the > > first element of a collection! > > hsqldb supports limit. They just form the query differently. > > See: http://hsqldb.sourceforge.net/doc/hsqlSyntax.html#SELECT > > In case you are wondering, hsqldb is non-standard. > > See: http://owen.sj.ca.us/rkowen/howto/sql92.html > > --- Noel > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
