In connection caches used to support automatic persistence, stateful failover, or the EAServer message service, you must configure the Database Type connection cache property. This property defines database-specific information required by the storage component, for example, the commands to verify that a table exists and create new tables. Several types are predefined, as described in Table 4-6.
You can create your own database type definitions by copying one of the existing files in the EAServer Repository/DatabaseType directory, then editing the property settings. Any changes you make to the predefined database type files may be overwritten by subsequent EAServer installs.
jagtool and the Repository API do not support manipulation of database type properties. To deploy a custom database type with your application, include the database type file in the .files property of the entity that you are deploying.
The database type properties include the following:
Logical column type definitions, which map types used in EJB-CMP field mappings to database column types. These properties use the logical type name as the property name.
Configuration properties, prefixed with com.sybase.jaguar, as in other entity configurations.
Optional ejbQuery properties, which can be included if the JDBC driver and database do not implement EJB-QL functions.
com.sybase.jaguar.conncache.db_type, “Database type setting”
Type definitions specify the format to define a column of the specified type in a SQL/DDL create table statement for the target database. These types can be used in the EJB CMP field mapping properties rather than the actual database types. EAServer uses the specified syntax to create columns for each container managed field in the bean.
Use this syntax to define a column type in the database type properties:
typeName=columnSpec
Where typeName is the logical type name used in field mapping properties, and columnSpec is the database syntax to define a column of the type in a create table statement. Table B-4 describes the logical type names used in the predefined EAServer database types. In component field mapping properties, using the logical type names rather than actual database types allows you to more easily run the same configuration against databases of different types.
Name |
Represents |
|---|---|
|
8-bit integer. |
|
16-bit integer. |
|
32-bit integer. |
|
64-bit integer. |
|
32-bit floating point. |
|
64-bit floating point. |
|
Unlimited precision integer. |
|
Integer with the specified precision. |
|
Decimal. |
|
Decimal with the specified precision and scale. |
|
Boolean (bit). |
|
A single character. |
|
Variable length character data. |
|
Variable length character data, with the specified maximum value length. |
|
Fixed length character data, with the specified maximum value length. |
|
Variable length binary data. |
|
Variable length binary data, with the specified maximum value length. |
|
Fixed length binary data, with the specified maximum value length. |
|
Date values. |
|
Time values. |
|
Date plus time values. |
|
The database timestamp type. Map this logical type to the database timestamp type by setting the com.sybase.jaguar.databasetype.dbts property. |
In the type name, you can use named placeholders for length,
precision, or scale specifiers. For example, for variable-length
character data, you can use string(maxLength) where maxLength represents
the field length specified in the field mapping property that uses
the type. For decimal or money data, you might use decimal(precision,scale).
You can use the placeholder names in the column specification, as
shown in the examples provided below.
columnSpec can include variable expressions to be evaluated at runtime, using this syntax:
${expression]
The expression language is that used for C language preprocessor #if and #ifdef directives. You can embed the name of a placeholder for the length, precision, or scale specifier used in the type name, as well as the predefined placeholders described in Table B-5.
Placeholder |
To indicate |
|---|---|
|
The column name specified in field mapping properties. |
|
A Boolean that evaluates to true if the database supports identity types or the equivalent; that is, the property com.sybase.jaguar.databasetype.dbid is set to true. |
|
Evaluates to the string “null” if the field mapping allows null values and to “not null” otherwise. |
|
Boolean that is true when |
This example defines a variable length character type, for a database that uses varchar for this column type, and has no database-imposed limit on the maximum length of varchar columns:
string(maxLength)=${column} varchar(${maxLength}) ${isNull}
If a field mapping uses the value lastName[string(100)
not null], EAServer creates the mapped
column as:
lastName varchar(100) not null
In this instance, the column placeholder
is replaced with the column name, lastName, maxLength is
replaced by the declared length, 100, and isnull is replaced
by not null.
This example defines a variable length character type, for a database that uses type varchar for columns to 255 characters wide, but requires type text for wider columns. This example is copied from Sybase_ASE.props:
string(maxLength)=${column} ${maxLength > 255 ? "text" : ("varchar(" + Number.toString(maxLength) + ")")} ${isNull}
The example uses the ? operator to declare the column as varchar or text, depending on whether the declared length is greater than 255.
“Setting field-mapping properties” in the EAServer Programmer’s Guide.
Used when inserting rows for a CMP entity bean when auto-key generation is enabled. This property specifies the database syntax to select the value of a generated key after a row is inserted.
The text of a SQL select statement that returns a single-column, single-row result set containing the generated key value.
Syntax to specify commands for the afterInsert, checkDelete, and checkUpdate properties For these properties that specify database command text, you can use the placeholders in the following table:
Placeholder |
Specifies |
|---|---|
|
A text expression in the language used for C language preprocessor #if and #ifdef directives. |
|
Useful for Sybase databases and those that use similar native syntax to generate key values, such as Microsoft SQL Server. When used in expressions, evaluates to true if auto key generation is enabled and keys are generated using Sybase identity column or the equivalent. |
|
Useful for Oracle databases. When used in expressions, the sequence name specified by the component property com.sybase.jaguar.component.db.sequence. Can also be used when using a key generation table, in which case it evaluates to the key table name plus column name. |
|
When used in expressions, a boolean that is true if automatic key generation is enabled for the component (com.sybase.jaguar.component.generateKey is true). |
|
When used in expressions, the table name specified in the component property com.sybase.jaguar.component.storage. |
|
When used in expressions, the component’s timestamp column specified by component property com.sybase.jaguar.component.timestamp |
If the evaluated text begins with ‘+’ (plus sign), the text after ‘+’ is appended to the same batch as the insert command, with ‘+’ replaced by a space. If the evaluated text begins with ‘;’ (semicolon), the entire text is appended as-is to the same batch as the insert command, including the semicolon. Otherwise, the text is sent to the database in a separate batch, after processing the results of the insert command.
Specifies the SQL text to verify that a delete affected one row. Used when the component uses optimistic concurrency control, and the isolation level requires update verification.
The text of the database command to verify that the delete affected one and only one row. If the delete affected more or less rows, the command text must raise an error, for example, using the SQL print or raiserror command. The text can be specified to run in the same batch as the update, or in a new batch, as described in “Syntax to specify commands for the afterInsert, checkDelete, and checkUpdate properties”.
For example, if using Sybase Adaptive Server Enterprise:
+ if @@rowcount <> 1 print 'OCC: Delete Failed: ${table}'
There are two mechanisms that you can configure to verify updates. Use the one that works best for your database and JDBC driver:
Set the properties com.sybase.jaguar.databasetype.checkDelete and com.sybase.jaguar.databasetype.checkUpdate to specify the commands required to verify updates or deletes, respectively.
Set the com.sybase.jaguar.databasetype.checkUpdateCount property to true, so that the CMP engine verifies updates and deletes by checking the row count returned to the JDBC driver.
Specifies the SQL text to verify that an update affected one row. Used when the component uses optimistic concurrency control, and the isolation level requires update verification.
The text of the database command to verify that the update affected one and only one row. If the update affected more or less rows, the command text must raise an error, for example, using the SQL print or raiserror command. The text can be specified to run in the same batch as the update, or in a new batch, as described in “Syntax to specify commands for the afterInsert, checkDelete, and checkUpdate properties”. For example, if using Sybase Adaptive Server Enterprise:
+ if @@rowcount <> 1 print 'OCC: Update Failed: ${table}'
The Usage section for com.sybase.jaguar.databasetype.checkDelete describes how to configure the mechanism that performs update verification.
Specifies whether the CMP engine should check the row count returned from the JDBC driver to verify that an update or delete affected one and only one row.
true or false.
The Usage for com.sybase.jaguar.databasetype.checkDelete describes how to configure the mechanism that performs update verification.
Specifies the syntax to generate compact column names, which can improve performance by reducing the size of query result sets.
Specify the syntax to rename a column in the query’s
column list. For example, if you specify _[index]=[column, queries
use column lists of the form select _1=column1, _2=column2,
...
If you specify [column] as
C[index], queries use column
lists of the form select col1 as C1, col2 as C2,
....
Specify the syntax that is legal for your database server.
Used in the following scenarios:
For Oracle and similar databases that generate key values using a named sequence, specifies the command text to create the sequence.
For any database, specifies the syntax to create a table that is used to generate key values when the component properties require automatic key generation.
The command text to create the sequence. You can use the placeholders described in the following table:
Placeholder |
Specifies |
|---|---|
|
A text expression in the language used for C language preprocessor #if and #ifdef directives. |
|
When used in expressions, the Oracle sequence name or key table name specified by the component property com.sybase.jaguar.component.db.sequence. |
|
When using a key table, the column name that contains the next key value, as specified by the component property com.sybase.jaguar.component.db.sequence. |
For example, if using Oracle:
create sequence ${sequence}
com.sybase.jaguar.databasetype.generateKey, com.sybase.jaguar.component.db.sequence
“Enabling automatic key generation” in Chapter 27, “Creating Entity Components,” in the EAServer Programmer’s Guide
Specifies whether the database supports auto key generation using the Sybase identity column type or the equivalent.
true or false.
If the database generates row timestamps, specifies the type of the timestamp column.
typeName[, convertFunc]
Where
typeName is the type of the timestamp column, as defined in the properties file. See Logical column type definitions for more information.
convertFunc is the database
conversion function to be used when selecting the timestamp column
in queries. Use the ${dbts} placeholder
to indicate the placement of the timestamp column name in the text.
If you specify a conversion function, typeName should
match the result after conversion.
If the timestamp column can be selected directly, the datatype of the timestamp column. If the timestamp column requires conversion, specify the conversion function syntax after the datatype, separated by a comma. For example, the following is the definition in Sybase_ASA.props:
string(26),dateformat(${dbts},'yyyy-mm-dd hh:nn:ss.ssssss')
Logical column type definitions
Specifies error text to search for in a SQLException to determine whether an error indicates a database deadlock condition.
The text to search for. The search algorithm is case-insensitive
and requires an exact match. If not set, the default is <unknown>.
Specifies error text to search for in a SQLException to determine whether an error indicates an attempt to insert duplicate keys.
The text to search for. The search algorithm requires an exact
match but is not case sensitive. If not set, the default is <unknown>.
Specifies whether the database and JDBC driver can store zero-length binary values.
true or false.
If not set, the default is true.
If set to false, zero-length binary
values are converted to a value of length one, containing a null
byte.
Some databases, such as Sybase Adaptive Server Enterprise and Microsoft SQL Server, cannot store a zero-length binary value.
com.sybase.jaguar.databasetype.emptyString
Specifies whether the database and JDBC driver can store zero-length variable-length character values.
true or false.
If not set, the default is true.
If set to false, zero-length values
are converted to a single space.
Some databases, such as Sybase Adaptive Server Enterprise and Microsoft SQL Server, cannot store a zero-length character value.
com.sybase.jaguar.databasetype.emptyBinary
Specifies the command text to specify an automatically generated key value in a database insert command.
The command text. You can use the placeholders described in the following table:
Placeholder |
Specifies |
|---|---|
|
A text expression in the language used for C language preprocessor #if and #ifdef directives. |
|
When used in expressions, the Oracle sequence name or key table name specified by the component property com.sybase.jaguar.component.db.sequence. |
For example, the following text selects the next value from an Oracle sequence:
${sequence}.nextval
com.sybase.jaguar.databasetype.createSequence
Specifies the maximum length for values passed to the JDBC driver implementation of the PreparedStatement.setBytes method.
The maximum length.
Some JDBC drivers cannot handle large values in the PreparedStatement.setBytes method. If this property is set, values greater than the maximum are passed as follows:
For databases besides Oracle, values are passed using the PreparedStatement.setBinaryStream.
For Oracle databases, the value is passed using special code that is specific to the Oracle JDBC driver. To ensure that large character values are passed correctly, verify that the CMP field mappings map to BLOB or an equivalent logical type if the field can hold large values. That is, the field is a binary (Java byte[] or Object) field with no length restriction, or a maximum length greater than supported by the JDBC driver’s PreparedStatement.setBytes implementation.
com.sybase.jaguar.databasetype.jdbc.setString.maxLength
Some databases, such as Sybase Adaptive Server Enterprise, store date and time values in the same column type. This property defines a time base for conversion between java.sql.Time values and database date values.
The time base.
Specifies the maximum length for values passed to the JDBC driver implementation of the PreparedStatement.setString method.
The maximum length.
Some JDBC drivers cannot handle large values in the PreparedStatement.setString method. If this property is set, values greater than the maximum are passed as follows:
For databases besides Oracle, the value is passed using the PreparedStatement.seCharacterStream.
For Oracle databases, the value is passed using special code that is specific to the Oracle JDBC driver. To ensure that large character values are passed correctly, verify that the CMP field mappings map to CLOB or an equivalent logical type if the field can hold large values. That is, the field is a string field with no length restriction, or a maximum length greater than that supported by the JDBC driver’s PreparedStatement.setString implementation.
com.sybase.jaguar.databasetype.jdbc.setBytes.maxLength
Allows you to redefine the integer constant that represents a BIGINT value to the JDBC driver, instead of the value of java.sql.Types.BIGINT.
The integer type constant. If not set, the default is the value of java.sql.Types.BIGINT.
Some drivers do not support this type; in this case, set the property to specify an alternate type that supports the BIGINT precision, such as java.sql.Types.DECIMAL.
Specifies the entity name for this database type.
The name, which must match the base name of the properties
file. For example, if the file is MySybase_ASE.props,
set this property to MySybase_ASE.
Specifies whether to use Oracle syntax when creating triggers.
true or false.
The default is false.
com.sybase.jaguar.databasetype.sybaseTriggers
Specifies database syntax to select rows with an exclusive lock.
A select statement template, for example (from Oracle9i.props):
select [columns] [from-clause] [where-clause] for update
You can use the placeholders described in Table B-6.
Placeholder |
Represents |
|---|---|
|
The table name |
|
Columns affected by updates |
|
The from clause |
|
The where clause |
|
The column list returned by the query |
If the database does not support exclusive locks, set the property to an empty value.
com.sybase.jaguar.databasetype.selectWithLock, com.sybase.jaguar.component.selectForUpdate, com.sybase.jaguar.component.selectWithLock
Specifies database syntax to select rows with a shared lock.
A select statement template, for example (from Sybase_ASE.props):
select [columns] [from-clause] holdlock [where-clause]
You can use the placeholders described in Table B-6.
If the database does not support shared locks, set the property to an empty value.
If neither com.sybase.jaguar.databasetype.selectForUpdate and com.sybase.jaguar.databasetype.selectWithLock are set, the persistence engine uses optimistic concurrency control when the component properties request locking.
com.sybase.jaguar.databasetype.selectForUpdate, com.sybase.jaguar.component.selectForUpdate, com.sybase.jaguar.component.selectWithLock
This property is obsolete.
true or false.
This property was used in EAServer 4.x versions and is obsolete beginning in EAServer 5.0.
Specifies whether to use Sybase syntax when creating triggers.
true or false.
The default is false.
com.sybase.jaguar.databasetype.oracleTriggers
Specifies the persistence engine trims trailing spaces from variable-length character values.
true or false.
The default is false.
When using optimistic concurrency control with a component
isolation level less than repeatable_read,
specifies the query syntax to verify that no change has occurred
to the row that was read into the container managed fields.
A select statement template that selects a count of rows that match the where clause, for example (from Sybase_ASE.props):
select count(*) [from-clause] [where-clause]
A count of 1 passes verification. A count of 0 fails verification. You can use the placeholders described in Table B-6.
com.sybase.jaguar.databasetype.verifyWithLock
When using optimistic concurrency control with a component
isolation level of repeatable_read or
higher, specifies the query syntax to verify that no change has
occurred to the row that was read into the container managed fields.
A select statement template that selects a count of rows that match the where clause, for example (from Sybase_ASE.props):
select count(*) [from-clause] holdlock [where-clause]
A count of 1 passes verification. A count of 0 fails verification. You can use the placeholders described in Table B-6.
If the database cannot support verification with locking, set the property to an empty value.
com.sybase.jaguar.databasetype.verify
An optional description of the database type entity.
The text of the description.
Allow you to map EJB-QL functions to syntax appropriate for the database. Most JDBC 2.0-compliant drivers support prepared statement function escape syntax, and these properties are not required. If needed, you can set additional properties to specify the database syntax for functions that the driver does not support.
ejbQuery.FUNCNAME(arguments)=dbSyntax
Where:
FUNCNAME is the EJB-QL function name.
arguments is a list of up to three argument names, separated by commas.
dbSyntax is the equivalent database function. You can use the argument names as placeholders, as shown in the following example from Sybase_ASE.props:
ejbQuery.LOCATE(string1,string2)=CHARINDEX(${string1},${string2})
If you do not specify a mapping for a function, the EJB-QL syntax is used in database queries.
