Administration Console Online Help

Previous Next Open TOC in new window
Content starts here

Persistence: Plugins: SQLServer Dictionary

Configuration Options     Related Tasks     Related Topics

This page shows the attributes of a SQLServer Dictionary.

Configuration Options

Name Description
Char Type Name

The default name for the column type indicated by java.sql.Types.CHAR. The mapping tool uses the name when it generates a schema.

Outer Join Clause

The SQL clause to express a left outer join.

Binary Type Name

The default name for the column type indicated by java.sql.Types.BINARY. The mapping tool uses the name when it generates a schema.

Clob Type Name

The default name for the column type indicated by java.sql.Types.CLOB. The mapping tool uses the name when it generates a schema.

Supports Locking With Distinct Clause

When true, the database supports FOR UPDATE clauses in SQL select statements with DISTINCT clauses.

Simulate Locking

Setting this property to true bypasses Kodo's attempt, when executing a transaction with the pessimistic lock manager, to lock records in the database upon load. Because some databases do not support pessimistic locking, attempting a pessimistic transaction within Kodo will result in an exception with these databases unless this property is set to true. At the same time, setting this property to true means that the semantics of a pessimistic transaction with the database are not obtained.

System Tables

A comma-separated list of table names that the dictionary should ignored.

Concatenate Function

The SQL function call or operation to concatenate two strings. Use the tokens {0} and {1} to represent the two arguments. The result of the function or operation is to concatenate the {1} string to the end of the {0} string.

Sub Function Name

Sub Function Name

Supports Query Timeout

When true, the JDBC driver supports calls to java.sql.Statement.setQueryTimeout.

Use Set Bytes For Blobs

When true, the dictionary will use PreparedStatement.setBytes to set blob data, rather than PreparedStatement.setBinaryStream.

Max Constraint Name Length

The maximum number of characters in a constraint name.

Search Escape

Search Escape

Supports Cascade Update Action

When true, the database supports the CASCADE update action on foreign keys.

String Length Function

The SQL function call to get the length of a string. Use the token {0} to represent the argument.

get Long Varbinary Type Name

get Long Varbinary Type Name

Supports Unique Constraints

When true, the database supports unique constraints.

Supports Restrict Delete Action

When true, the database supports the RESTRICT delete action on foreign keys.

Trim Leading Function

The SQL function call to trim any number of a particular character from the start of a string. Note: some databases do not support specifying the character in which case only spaces or whitespace can be trimmed. Use the token {1} when possible to represent the character, and the token {0} to represent the string.

Supports Default Delete Action

When true, the database supports the SET DEFAULT delete action on foreign keys.

Next Sequence Query

A SQL string for obtaining a native sequence value. May use a placeholder of {0} for the sequence name. For example, "SELECT {0}.NEXTVAL FROM DUAL" for Oracle.

Long Varchar Type Name

The default name for the column type indicated by java.sql.Types.LONGVARCHAR. The mapping tool uses the name when it generates a schema.

Cross Join Clause

The SQL clause to express a cross join (cartesian product).

Max Embedded Clob Size

When greater than -1, the maximum size of a CLOB value that can be sent directly to the database within an insert or update statement. Values whose size is greater than MaxEmbeddedClobSize force OpenJPA to work around this limitation. A value of -1 means that there is no limitation.

Date Type Name

The default name for the column type indicated by java.sql.Types.DATE. The mapping tool uses the name when it generates a schema.

Supports Schema For Get Tables

When true, the database driver supports using the schema name for schema reflection on table names.

Supports Alter Table With Drop Column

When true, the database supports dropping a column in an ALTER TABLE statement.

Current Time Function

The SQL function call to obtain the current time from the database.

Requires Condition For Cross Join

When true, the database requires that there always be a conditional clause in any SQL statement expressing a cross join.

Ref Type Name

The default name for the column type indicated by java.sql.Types.REF. The mapping tool uses the name when it generates a schema.

Concatenate Delimiter

This property is not used.

Catalog Separator

Catalog Separator

Supports Mod Operator

When true, the database supports the modulus operator (%) instead of the MOD function.

Schema Case

The case to use when querying the database metadata about schema components. Available values are: upper, lower, and preserve.

Java Object Type Name

The default name for the column type indicated by java.sql.Types.JAVAOBJECT. The mapping tool uses the name when it generates a schema.

Driver Vendor

Some dictionaries must alter their behavior depending on the driver's vendor. When used, the dictionary often sets this property using its own logic. To override this logic, see the VENDOR_XXX constants, if any, defined in the dictionary's Javadoc.

Supports Locking With Multiple Tables

When true, the database supports FOR UPDATE clauses in SQL select statements that select from multiple tables.

Max Column Name Length

The maximum number of characters in a column name.

Double Type Name

The default name for the column type indicated by java.sql.Types.DOUBLE. The mapping tool uses the name when it generates a schema.

Use Get String For Clobs

When true, the dictionary will use ResultSet.getString to obtain clob data rather than ResultSet.getCharacterStream.

Decimal Type Name

The default name for the column type indicated by java.sql.Types.DECIMAL. The mapping tool uses the name when it generates a schema.

Smallint Type Name

The default name for the column type indicated by java.sql.Types.SMALLINT. The mapping tool uses the name when it generates a schema.

Date Precision

The database is able to store time values to this degree of precision, which is expressed in nanoseconds. This value is usually one million, meaning that the database is able to store time values with a precision of one millisecond. Particular databases may have more or less precision. Kodo and OpenJPA will round all time values to this degree of precision before storing them in the database.

Supports Alter Table With Add Column

When true, the database supports adding a new column in an ALTER TABLE statement.

Bit Type Name

The default name for the column type indicated by java.sql.Types.BIT. The mapping tool uses the name when it generates a schema.

Supports Null Table For Get Columns

When true, the database supports passing a null parameter to DatabaseMetaData.getColumns as an optimization to get information on all tables.

To Upper Case Function

The SQL function call to convert a string to upper case. Use the token {0} to represent the argument.

Supports Select End Index

When true, the database supports SQL select statements that do not return more than the first N results.

Supports Auto Assign

When true, the database supports auto-assign columns where the value of column is assigned upon insertion of the row into the database.

Store Large Numbers As Strings

When true, the dictionary prefers to store large numbers (Java fields of type BigInteger and BigDecimal) as string values in the database. Likewise, the dictionary will instruct the mapping tool to map the BigInteger or BigDecimal fields to character columns. Because many databases have limitations on the number of digits that can be stored in a numeric column (for example, Oracle can only store 38 digits), this option may be necessary for some applications. Note that this option may prevent Kodo from executing meaningful numeric queries against the columns.

Constraint Name Mode

Constraint Name Mode

Allows Alias In Bulk Clause

When true, SQL delete and update statements may use table aliases.

Supports Select For Update

When true, the database supports SQL select statements with a pessimistic locking (FOR UPDATE) clause.

Distinct Count Column Separator

The string the database uses to delimit between column expressions in a SELECT COUNT(DISTINCT column-list) clause. Defaults to null for most databases, meaning that they do not support multiple columns in a distinct COUNT clause.

Supports Subselect

When true, the database supports SQL select statements with subselect clauses.

Time Type Name

The default name for the column type indicated by java.sql.Types.TIME. The mapping tool uses the name when it generates a schema.

Auto Assign Type Name

The default name for the column type used for auto-increment columns. For example, "BIGSERIAL" for PostgreSQL. The mapping tool uses the name when it generates a schema.

Use Get Object For Blobs

When true, the dictionary will use ResultSet.getObject to obtain blob data rather than ResultSet.getBinaryStream.

Max Auto Assign Name Length

The maximum number of characters in the name of a sequence used for auto-increment columns. Any name provided that is longer than this value is truncated.

Validation SQL

The SQL used to validate that a connection to the database is still in working order. The SQL statement should impose minimum load on the database.

Struct Type Name

The default name for the column type indicated by java.sql.Types.STRUCT. The mapping tool uses the name when it generates a schema.

Varchar Type Name

The default name for the column type indicated by java.sql.Types.VARCHAR. The mapping tool uses the name when it generates a schema.

Range Position

Indicates where to specify in the SQL select statement the range, if any, of the result rows to be returned. When limiting the number of returned result rows to a subset of all those that satisfy the query's conditions, the position of the range clause varies by database. Examine the source code of the org.apache.openjpa.jdbc.sql.DBDictionary class to view the possible values and their effects.

Supports Restrict Update Action

When true, the database supports the RESTRICT update action on foreign keys.

Auto Assign Clause

The magic word(s) to append to the column definition of a SQL statement that creates an auto-assignment column. For example, "AUTO_INCREMENT" for MySQL. Used only when the mapping tool generates a schema.

Supports Multiple Nontransactional Result Sets

When true, a nontransactional JDBC connection to the database is capable of having multiple open ResultSet instances.

Bit Length Function

This property is not used.

Create Primary Keys

When true, create database primary keys for identifiers.

Null Type Name

The default name for the column type indicated by java.sql.Types.NULL. The mapping tool uses the name when it generates a schema.

Float Type Name

The default name for the column type indicated by java.sql.Types.FLOAT. The mapping tool uses the name when it generates a schema.

Use Get Bytes For Blobs

When true, the dictionary will use ResultSet.getBytes to obtain blob data rather than ResultSet.getBinaryStream

Table Types

Comma-separated list of table types to use when looking for tables during schema reflection, as defined in the JDBC method java.sql.DatabaseMetaData.getTableInfo. Examples are "TABLE" and "TABLE,VIEW,ALIAS".

Numeric Type Name

The default name for the column type indicated by java.sql.Types.NUMERIC. The mapping tool uses the name when it generates a schema.

Table For Update Clause

The clause to append to the end of each table alias in queries that obtain pessimistic locks.

Integer Type Name

The default name for the column type indicated by java.sql.Types.INTEGER. The mapping tool uses the name when it generates a schema.

Blob Type Name

The default name for the column type indicated by java.sql.Types.BLOB. The mapping tool uses the name when it generates a schema.

For Update Clause

The clause to append to SQL select statements to issue queries that obtain pessimistic locks. The clause is usually FOR UPDATE.

Boolean Type Name

The default name for the column type indicated by java.sql.Types.BOOLEAN. The mapping tool uses the name when it generates a schema.

Use Get Best Row Identifier For Primary Keys

When true, metadata queries will use DatabaseMetaData.getBestRowIdentifier to obtain information about primary keys, rather than DatabaseMetaData.getPrimaryKeys.

Supports Foreign Keys

When true, the database supports foreign keys.

Drop Table SQL

The SQL statement used to drop a table. Use the token {0} as the argument for the table name.

Use Set String For Clobs

When true, the dictionary will use PreparedStatement.setString to set clob data, rather than PreparedStatement.setCharacterStream.

Supports Locking With Order Clause

When true, the database supports FOR UPDATE clauses in SQL select statements with ORDER BY clauses.

Platform

The name of the database that this dictionary targets.

Fixed Size Type Names

A comma separated list of additional database types that have a size defined by the database. In other words, when a column of a fixed size type is declared, its size cannot be defined by the user. Common examples would be DATE, FLOAT, and INTEGER. Each database dictionary has its own internal set of fixed size type names (fixedSizeTypeNameSet) that include the names mentioned above and many others. Names added to this property are added to the dictionary's internal set.

Store Chars As Numbers

When true, the dictionary prefers to store Java char fields as numbers rather than as CHAR values.

Max Indexes Per Table

The maximum number of indexes that can be placed on a single table.

Requires Cast For Comparisons

When true, comparisons of two values of different types or of two literals requires a cast in the generated SQL.

Supports Having

When true, the database supports HAVING clauses in SQL select statements.

Supports Locking With Outer Join

When true, the database supports FOR UPDATE clauses in SQL select statements with outer join clauses.

Supports Correlated Subselect

When true, the database supports correlated subselects. Correlated subselects are select statements nested within select statements that refer to a column in the outer select statement. For performance reasons, correlated subselects are generally a last resort.

Supports Null Table For Get Imported Keys

When true, the database supports passing a null parameter to DatabaseMetaData.getImportedKeys as an optimization to get information on all tables.

Bigint Type Name

The default name for the column type indicated by java.sql.Types.BIGINT. The mapping tool uses the name when it generates a schema.

Last Generated Key Query

The query to issue to obtain the last automatically generated key for an auto-increment column. For example, "SELECT LAST_INSERT_ID()" for MySQL.

Reserved Words

A comma-separated list of reserved words for the database, beyond the standard SQL92 keywords.

Supports Null Update Action

When true, the database supports the SET NULL update action on foreign keys.

Use Schema Name

When true, the dictionary generates SQL that uses the schema name with the table name.

Supports Deferred Constraints

When true, the database supports deferred constraints. The database supports deferred constraints by checking for constraint violations when the transaction commits, rather than checking for violations immediately after receiving each SQL statement within the transaction.

Real Type Name

The default name for the column type indicated by java.sql.Types.REAL. The mapping tool uses the name when it generates a schema.

Requires Alias For Subselect

When true, the database requires that subselects in a FROM clause be assigned an alias.

Supports Null Table For Get Index Info

When true, the database supports passing a null parameter to DatabaseMetaData.getIndexInfo as an optimization to get information on all tables.

Trim Trailing Function

The SQL function call to trim any number of a particular character from the end of a string. Note: some databases do not support specifying the character in which case only spaces or whitespace can be trimmed. Use the token {1} when possible to represent the character, and the token {0} to represent the string.

Supports Locking With Select Range

When true, the database supports FOR UPDATE clauses in SQL select statements select a range of data using LIMIT, TOP or equivalent database specific clause.

Storage Limitations Fatal

When true, then any data truncation or rounding that is performed by the dictionary in order to store a value in the database will be treated as a fatal error. Otherwise, the dictionary issues a warning.

Supports Locking With Inner Join

When true, the database supports FOR UPDATE clauses in SQL select statements with inner join clauses.

Current Timestamp Function

The SQL function call to obtain the current timestamp from the database.

Cast Function

The SQL function call to cast a value to another SQL type. Use the tokens {0} and {1} to represent the two arguments. The result of the function is convert the {0} value to a {1} type.

Other Type Name

The default name for the column type indicated by java.sql.Types.OTHER. The mapping tool uses the name when it generates a schema.

Max Index Name Length

The maximum number of characters in an index name.

Distinct Type Name

The default name for the column type indicated by java.sql.Types.DISTINCT. The mapping tool uses the name when it generates a schema.

Character Column Size

The default size of VARCHAR and CHAR columns, typically 255.

Varbinary Type Name

The default name for the column type indicated by java.sql.Types.VARBINARY. The mapping tool uses the name when it generates a schema.

Max Table Name Length

The maximum number of characters in a table name.

Close Pool SQL

A special command to issue to the database when shutting down the pool. Usually the pool of connections to the database is closed when the application is ending. For embedded databases, whose lifecycle is coterminous with the application, there may be a special command, usually "SHUTDOWN", that will cause the database to close cleanly.

Current Date Function

The SQL function call to obtain the current date from the database.

Join Syntax

The style of SQL join syntax to use in select statements: sql92 - ANSI SQL92 join syntax where joins are expressed in the SQL FROM clause. Outer joins are supported. Not all databases support this syntax. traditional - Traditional SQL join syntax where joins are expressed in the SQL WHERE clause. Outer joins are not supported. database - Join syntax is encoded to some extent within the dictionary class for the database. Outer joins may be supported.

Max Embedded Blob Size

When greater than -1, the maximum size of a BLOB value that can be sent directly to the database within an insert or update statement. Values whose size is greater than MaxEmbeddedBlobSize force OpenJPA to work around this limitation. A value of -1 means that there is no limitation.

Trim Both Function

The SQL function call to trim any number of a particular character from both the start and end of a string. Note: some databases do not support specifying the character in which case only spaces or whitespace can be trimmed. Use the token {1} when possible to represent the character, and the token {0} to represent the string.

Supports Select Start Index

When true, the database supports SQL select statements that skip the first N results.

To Lower Case Function

The SQL function call to convert a string to lower case. Use the token {0} to represent the argument.

Array Type Name

The default name for the column type indicated by java.sql.Types.ARRAY. The mapping tool uses the name when it generates a schema.

Inner Join Clause

The SQL clause to express an inner join.

Supports Default Update Action

When true, the database supports the SET DEFAULT update action on foreign keys.

Supports Schema For Get Columns

When true, the database driver supports using the schema name for schema reflection on column names.

Tinyint Type Name

The default name for the column type indicated by java.sql.Types.TINYINT. The mapping tool uses the name when it generates a schema.

Supports Null Table For Get Primary Keys

When true, the database supports passing a null parameter to DatabaseMetaData.getPrimaryKeys as an optimization to get information on all tables.

System Schemas

A comma-separated list of schema names that should be ignored.

Requires Cast For Math Functions

When true, math operations on two values of different types or on two literals requires a cast in the generated SQL.

Supports Null Delete Action

When true, the database supports the SET NULL delete action on foreign keys.

Requires Auto Commit For Meta Data

When true, the JDBC driver requires that autocommit be enabled before any schema interrogation operations can take place.

Timestamp Type Name

The default name for the column type indicated by java.sql.Types.TIMESTAMP. The mapping tool uses the name when it generates a schema.

Initialization SQL

A SQL statement to initialize a connection after obtaining it from the DataSource. The dictionary normally uses this to work around any JDBC bugs.

Supports Cascade Delete Action

When true, the database supports the CASCADE delete action on foreign keys.

Related Tasks

Related Topics


Back to Top