The Enterprise JavaBeans (EJB) query functions must adhere to certain
restrictions for databases. This section describes the restrictions.
General database restrictions
- 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.
- Current releases of UDB DB2 for i5/OS only support
a TIMESTAMP value of the format 'yyyy-mm-dd-hh.mm.ss.nnnnnn'. This is not
compatible with the standard format supported by the java.sql.Timestamp class,
which is 'yyyy-mm-dd-hh mm.ss.nnnnnn'. The TIMESTAMP scalar function should
be used to convert a string representation of a java.sql.Timestamp object
to a value that can be recognized by DB2 UDB for i5/OS.