[I am sending this to the EJBoss list as well as adding it to Bugzilla,
because anyone trying to use CMP in jBoss2.0 will need to be aware of
the problem and a workaround. It is Bug#21 in Bugzilla.]
CONTEXT
If you have custom finders, you have to define their SQL WHERE clauses
in EJX. Parameters in these clauses are represented by numbers (zero-
based) in braces, e.g.
balance > {0}
The number corresponds to the position of the parameter in the finder
method's parameter list.
PROBLEM
jBoss uses java.text.MessageFormat to do the substitution of these
placeholders (in org.jboss.ejb.plugins.jaws.JAWSPersistenceManager, in
the findEntities() method).
This bypasses the type-sensitive handling of SQL parameters defined by
JAWSPersistenceManager.setParameter(), which is what
JAWSPersistenceManager normally uses for setting parameters in
PreparedStatements.
The most obvious consequence is that String literal parameters are not
getting the single quotes that SQL requires.
WORKAROUND
To work around this, you need to put single quotes around the
placeholder - and because MessageFormat uses single quotes to escape
braces (e.g. '{ ), you have to double them up. For example
description = ''{0}''
FIX
Sun's J2EE RI currently uses ? as a placeholder, allowing the WHERE
clause to be plugged straight in to a PreparedStatement. This does mean
that the order of parameters in the WHERE clause has to match the order
of parameters in the finder. (It doesn't, however, place any limit on
the number of parameters... MessageFormat has a limit of ten.)
I suggest that JAWS should change to use ?, and that users should be
told they have to organise their finders and WHERE clauses to have the
same order of parameters.
If this is not acceptable, the {n} placeholders should have their order
noted, before being replaced by ? characters in the SQL for the
PreparedStatement. Then the finder parameter order can be transformed
into the SQL parameter order before going into the normal process of
setting parameters using JAWSPersistenceManager.setParameter().
--
Justin Forder
--
--------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Problems?: [EMAIL PROTECTED]
