user manual

186 BES Developer’s Guide
Dynamic Queries
Note A problem with your SQL may generate an exception which can potentially
crash the system.
You specify your own optimized SQL in the Borland proprietary deployment
descriptor, ejb-borland.xml. The XML grammar is identical to that found in ejb-
jar.xml, except that the <ejb-ql> element is replaced with a <user-sql> element.
This proprietary element contains a SQL-92 statement (not an EJB-QL
statement) that is used to access the database instead of the CMP engine-
generated SQL.
Important The SELECT clause for this statement must be identical to the SELECT clause
generated by the Borland CMP engine.
Subsequent clauses are user-optimized. The ordering of the fields in the
SELECT clause is proprietary to the CMP engine and therefore must be
preserved.
For example:
<entity>
<ejb-name>EmployeeBean</ejb-name>
...
<query>
<query-method>
<method-name>findWealthyEmployees</method-name>
<method-params />
</query-method>
<user-sql>SELECT E.DEPT_NO, E.EMP_NO, E.FIRST_NAME, E.FULL_NAME,
E.HIRE_DATE, E.JOB_CODE, E.JOB_COUNTRY,
E.JOB_GRADE, E.LAST_NAME, E.PHONE_EXT, E.SALARY
FROM EMPLOYEE E WHERE E.SALARY > 200000
</user-sql>
</query>
...
</entity>
Note The extensive SELECT statement reflects the type of SQL generated by the
CMP engine.
When the CMP engine encounters an EJB-QL statement in the ejb-jar.xml
deployment descriptor, it checks ejb-borland.xml to see if there is any user
SQL provided in the same bean's descriptor.
If none is present, the CMP engine generates its own SQL and executes it.
If the ejb-borland.xml descriptor does contain a query element, it uses the SQL
within the <user-sql> tags instead.
Important The <query> element in ejb-borland.xml does not replace the <query> element in
the standard ejb-jar.xml deployment descriptor. If you want to override the
CMP engine's SQL, you must provide the elements in both descriptors.