Jump to: navigation, search

EclipseLink/Development/DatabasePlatform/SymfowarePlatform

Description

This is a subclass of DatabasePlatform that can be used by customers using the Symfoware database.

Documentation

Configuration

SymfowarePlatform is included in our automatic database platform detection feature and, as such, should automatically be detected when you login if you are using JPA.

If either you are not using JPA, or automatic detection is not working, it can be configured as follows:

  1. In JPA set the persistence unit property "eclipselink.target-database" to "Symfoware"
  2. In other configuration set the DatabasePlatform to org.eclipse.persistence.platform.database.SymfowarePlatform
    1. This setting is available in sessions.xml and deployment.xml
    2. This setting is also available through direct API on the session

Test Results

Test results for EclipseLink 2.1.0.

Test results for EclipseLink 2.3.1.

Test results for EclipseLink 2.3.2.

Test results for EclipseLink 2.4.2.

Test results for EclipseLink 2.5.

Automatic Table Generation

The following table shows the default SQL data types and sizes used for table generation.


Java data type Symfoware SQL data type
boolean
java.lang.Boolean
SMALLINT default 0
byte
java.lang.Byte
SMALLINT
char
java.lang.Character
CHARACTER(1)
short
java.lang.Short
SMALLINT
int
java.lang.Integer
INTEGER
long
java.lang.Long
NUMERIC(18)
float
java.lang.Float
NUMERIC(18,4)
double
java.lang.Double
NUMERIC(18,4)
java.lang.String VARCHAR(255)
java.math.BigDecimal DECIMAL(18)
java.math.BigInteger NUMERIC(18)
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP
java.util.Date Refer to the specified TemporalType mapping
java.util.Calendar
byte[] BLOB(1024)
java.lang.Byte[] BLOB(1024)
char[] VARCHAR(255)
java.lang.Character[] VARCHAR(255)
enum(ordinal) Refer to the java.lang.Integer mapping
enum(String) Refer to the java.lang.String mapping
Serializable object BLOB(1024)
(LOB)byte[] BLOB(1024)
(LOB)java.lang.Byte[] BLOB(1024)
(LOB)Serializable object BLOB(1024)
(LOB)char[] VARCHAR(255)
(LOB)java.lang.Character[] VARCHAR(255)
(LOB)java.lang.String VARCHAR(255)


Limitations

SymfowarePlatform inherits the limitations of Symfoware Server and its JDBC driver. For example, note the following restrictions. Refer to the database manual for details.

  • Reserved SQL keywords cannot be used as table, column or sequence names. Use a different name, or enclose the name in double quotes. For example:
    • @Column(name="\"LANGUAGE\"")
    • The default table name of the TABLE generator is "SEQUENCE", with double quotes, which makes it a legal name.
  • Spaces cannot be used in table, column or sequence names.
  • The MOD(x, y) function is executed as 'CASE WHEN y = 0 THEN x ELSE (x - y * TRUNC( x / y )) END' on Symfoware database, which gives the same result as the MOD function on Oracle database. Input parameters cannot be used for both its arguments at the same time. In such case, calculate the modulus in Java code first and pass the result to the query instead.
  • No more than one input parameter can be used as argument to the LOCATE function.
  • The first argument to the SUBSTRING function cannot be an input parameter.
  • Input parameters cannot be used as adjacent arguments to the CONCAT function. Concatenate the values in Java code first and pass the result to the query instead.
  • Input parameters cannot be used at both sides of an operand at the same time in an SQL statement (e.g. '? * ?'). Perform the operation in Java code first and pass the result to the query instead.
  • Input parameters cannot be used as arguments to the COALESCE function when it is used in the select list.
  • When an input parameter is used as argument to the UPPER, LOWER or LENGTH functions, it is substituted with its value before the SQL statement is sent to the JDBC driver.
  • When input parameters are used as arguments to the TRIM function, they are substituted with their values before the SQL statement is sent to the JDBC driver.
  • Identity fields cannot be used. When primary key generation type IDENTITY is specified, a database sequence will be used instead.
  • Pessimistic Locking adds 'FOR UPDATE' to the SELECT statement, and cannot be used with queries that use DISTINCT.
  • Pessimistic Locking cannot be used with queries that select from multiple tables.
  • The LockNoWait option of Pessimistic Locking cannot be used; it is ignored when specified (i.e. only 'FOR UPDATE' is added to the SELECT statement).
  • Query timeout cannot be used; the timeout value is silently ignored.
  • Bulk update and delete operations that require multiple tables to be accessed cannot be used (e.g. bulk operation on an entity that is part of an inheritance hierarchy, etc.). (See bug 298193). When the tables have only a single primary key column, it might be possible to override the query with a native query without EXISTS:
E.g. JPQL statement: DELETE FROM Employee e WHERE (e.salary > 1000) (where 'salary' is stored in secondary table SALARY)
     Generated SQL: uses EXISTS with a subquery whose FROM clause includes the same table as the DELETE's (fails on Symfoware)
     Override with native SQL:
       DELETE FROM EMPLOYEE
         WHERE ID = (
           SELECT s.EMP_ID
           FROM SALARY s
           WHERE s.salary > 1000
         )
  • Dropping of tables, sequences and procedures while the database connection is still open can fail due to unreleased locks. Shut down the Java process that executed the create operation before performing the drop operation, or have the create operation use an unpooled connection that is closed after use (GlassFish's deploy-time table generation function uses an unpooled connection).
  • Using subquery in select list for query specification or single-row SELECT statement is not allowed. (See bug 372172) For example, this SQL is not supported by Symfoware.
SELECT t0.EMP_ID, t0.F_NAME, (SELECT COUNT(t2.PROJ_ID) FROM PROJ_EMP t3, PROJECT t2 WHERE ((t3.EMP_ID = t0.EMP_ID) AND (t2.PROJ_ID = t3.PROJ_ID))) 
FROM EMPLOYEE t0, SALARY t1 
WHERE (t1.EMP_ID = t0.EMP_ID)

The following restrictions are related to EclipseLink specific functionality (outside of JPA scope):

  • The standard deviation (STDDEV) and variance (VARIANCE) functions cannot be used.
  • '= NULL' and '<> NULL' cannot be used for null comparisons in the WHERE clause. Use 'IS (NOT) NULL' instead.
  • A scrollable cursor policy of CONCUR_UPDATABLE mode cannot be used with queries that select from multiple tables.
  • UpdateAll and DeleteAll queries on multi-table objects (see limitation of JPA's bulk update and delete operations).
  • Columns and literals of different type may need casting to allow them to be compared or assigned. For example:
'SELECT ... WHERE CAST(PHONE_ORDER_VARCHAR AS INTEGER) BETWEEN 0 AND 1'
  • Stored functions are not supported(See bug 342409).
  • The CascadeOnDelete doesn't work on a relation where CascadeType.Remove or CascadeType.All is specified(See bug 342413).
  • A subquery cannot be specified on both sides of a comparison predicate or a quantified predicate. (See bug 378313)
  • A base table name to be updated cannot be identical to table name in from clause in query or subquery specification (See bug 381302)

Location

SymfowarePlatform is in the main EclipseLink jar file and in the org.eclipse.persistence.core bundle.