The Enterprise JavaBeans (EJB) query functions must adhere
to certain restrictions for databases.
General database restriction
- All of the enterprise beans involved in a given query must map
to the same data source. The EJB query does not support cross-data
source join operations.
- It is possible that a structured query language (SQL) statement
generated by the WebSphere® Application Server deployment
code generation utility for an ejbSelect Enterprise JavaBeans
query language query returns rows in a result set that consist of
null values in all columns.
During run time persistence manager
saves the set received as a result from this query. When your application
retrieves the primary key of the result bean, persistence manager
calls the extractor. The extractor is a method that is an EJB deploy
generated class. This method returns a value of 0 for any
null column entries. This value is passed back to the EJB container
to forward to the application. The EJB container invokes the bean
instance with the PK value of 0. This could create a problem,
as the end user cannot determine if this bean instance has a null
PK or a PK value of 0.
To avoid this, use the IS
NOT NULL clause in the finder query to eliminate such null values
from the result set.
Specific database restrictions
Different database products place different restrictions on elements
that can be included in EJB query statements. Following is a list
of those restrictions; check with your database administrator to see
if any apply in your environment:
- Certain functions are used in queries that run against DB2 only,
because these functions are not supported by other databases. These
functions include date and time arithmetic expressions, certain scalar
functions (those not listed as portable
across vendors), and implied scalar functions when used for
mapping certain CMP fields. For example, consider mapping an int numeric
type to a decimal (5,2) type field. When deployed against a database
other than DB2, a finder or select query that contains a CMP field
with this particular mapping fails, producing a Cannot push down
query error message.
- A CMP of type String, when mapped to a character large object
(CLOB) in the database, cannot be used in comparison operations because
the database does not support CLOB comparisons.
- Databases can impose limits on the length of string values that
are used either as literals or input parameters with comparison operators.
These limits can hinder query performance. For example: For DB2 on
the z/OS platform, the search "name = ?1" can fail if the value of
?1 at run time is greater than 255 in length.
- Mapping a numeric CMP type to a column that contains a dissimilar
type can cause unexpected results. For example, consider the case
of mapping the int numeric type to a column of type decimal (5,2).
This scenario does not preserve an exact decimal value (for example,
the value 12.25) over the course of transfer from the database to
the enterprise bean CMP field, and back again to the database. This
mapping causes replacement of the initial value with a whole number
(in this case, 12). Consequently, you want to avoid using the CMP
field in comparison operations when the CMP field uses a mapping of
this nature.
- Some databases do not support a datatype that corresponds to the
semantics of java.sql.Time. For example: If a CMP field of type java.sql.Time
is mapped to an Oracle DATE column, comparisons on time might not
produce the expected result because the year-month-day portion of
the column value is truncated in the mapping.
- Some databases treat a zero length string value ( '' ) as a null
value; this approach can affect the query results. For the sake of
portability, avoid the use of zero length string values.
- Some databases perform division between two integer values using
integer arithmetic rules, while others use non-integer rules. This
discrepancy might not be desirable in environments that use both kinds
of databases. For the sake of portability, avoid the division of integer
values in an EJB query.