Database Restrictions and Optimizations

This section discusses restrictions and performance optimizations that affect using the Java Persistence API.

• Using BLOB or CLOB Types with the Inet Oraxo JDBC Driver

• Case Insensitivity of Field Names

• Using a SERIAL Field in PostgreSQL

• Sybase Finder Limitation

• MySQL Database Restrictions

Using BLOB or CLOB Types with the Inet Oraxo JDBC Driver

To use BLOB or CLOB data types larger than 4 KB for persistence using the Inet Oraxo JDBC Driver for Oracle Databases, you must set the database's streamstolob property value to true.

Case Insensitivity of Field Names

In the Application Server, schema generation converts all field names to uppercase. Therefore, if you are using a case sensitive database, make sure all field names in the code and the database are in uppercase.

Using a SERIAL Field in PostgreSQL

To use a SERIAL field in a PostgreSQL database, define the following in your entity class:

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)

The field created is of type SERIAL, and the implicit native sequence associated with this field is of the form FullyQualifiedName_IdFieldColumnName_SEQ.

Sybase Finder Limitation

If a finder method with an input greater than 255 characters is executed and the primary key column is mapped to a VARCHAR column, Sybase attempts to convert type VARCHAR to type TEXT and generates the following error:

com.sybase.jdbc2.jdbc.SybSQLException: Implicit conversion from datatype 
'TEXT' to 'VARCHAR' is not allowed.  Use the CONVERT function to run this 
query.

To avoid this error, make sure the finder method input is less than 255 characters.

MySQL Database Restrictions

The following restrictions apply when you use a MySQL database with the Application Server for persistence.

• MySQL treats int1 and int2 as reserved words. If you want to define int1 and int2 as fields in your table, use `int1` and `int2` field names in your SQL file.

• When VARCHAR fields get truncated, a warning is displayed instead of an error. To get an error message, start the MySQL database in strict SQL mode.

• The order of fields in a foreign key index must match the order in the explicitly created index on the primary table.

• The CREATE TABLE syntax in the SQL file must end with the following line.

  )  Engine=InnoDB;

  InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine having commit, rollback, and crash recovery capabilities.

• For a FLOAT type field, the correct precision must be defined. By default, MySQL uses four bytes to store a FLOAT type that does not have an explicit precision definition. For example, this causes a number such as 12345.67890123 to be rounded off to 12345.7 during an INSERT. To prevent this, specify FLOAT(10,2) in the DDL file, which forces the database to use an eight-byte double-precision column. For more information, see http://dev.mysql.com/doc/mysql/en/numeric-types.html.

• To use || as the string concatenation symbol, start the MySQL server with the --sql-mode="PIPES_AS_CONCAT" option. For more information, see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html and http://dev.mysql.com/doc/mysql/en/ansi-mode.html.

• MySQL always starts a new connection when autoCommit==true is set. This ensures that each SQL statement forms a single transaction on its own. If you try to rollback or commit an SQL statement, you get an error message.

  javax.transaction.SystemException: java.sql.SQLException: Can't call rollback when autocommit=true

  javax.transaction.SystemException: java.sql.SQLException: Error open transaction is not closed

  To resolve this issue, add relaxAutoCommit=true to the JDBC URL. For more information, see http://forums.mysql.com/read.php?39,31326,31404.

• MySQL does not allow a DELETE on a row that contains a reference to itself. Here is an example that illustrates the issue.

  create table EMPLOYEE (
  		empId   int         NOT NULL,
  		salary  float(25,2) NULL,
  		mgrId   int         NULL,
  		PRIMARY KEY (empId),
  		FOREIGN KEY (mgrId) REFERENCES EMPLOYEE (empId)
  		) ENGINE=InnoDB;
  
  		insert into Employee values (1, 1234.34, 1);
  		delete from Employee where empId = 1;

  This example fails with the following error message.

  ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

  To resolve this issue, change the table creation script to the following:

  create table EMPLOYEE (
  		empId   int         NOT NULL,
  		salary  float(25,2) NULL,
  		mgrId   int         NULL,
  		PRIMARY KEY (empId),
  		FOREIGN KEY (mgrId) REFERENCES EMPLOYEE (empId)
  		ON DELETE SET NULL
  		) ENGINE=InnoDB;
  
  		insert into Employee values (1, 1234.34, 1);
  		delete from Employee where empId = 1;

  This can be done only if the foreign key field is allowed to be null. For more information, see http://bugs.mysql.com/bug.php?id=12449 and http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html.