Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
Mapping a Simple Field
Programmer's Guide > OpenAccess ORM Classic (Old API) > Programming With OpenAccess > Mapping > Simple and Reference Fields > Mapping a Simple Field

Glossary Item Box

This documentation article is a legacy resource describing the functionality of the deprecated OpenAccess Classic only. The contemporary documentation of Telerik OpenAccess ORM is available here.

OpenAccess ORM supports flexible mappings from .NET types (e.g., System.String) to database specific SQL types (e.g., VARCHAR2 on Oracle). The default mappings can be customized for a specific datastore. The mapping for specific fields can also be customized. Mappings are resolved by first using any information specified for the field, then looking up a generic type for the type of the field using the .NET Type Mapping, then converting this generic type into actual SQL type information using the Generic Type Mapping. You can have different mapping information for each database supported by OpenAccess.

OpenAccess ORM supports sharing of columns between different fields. This is very useful while mapping to legacy database schemas. It is possible to use a reference to a persistence capable class as part of the primary key or to use the primary key of a table as part of a foreign key reference to another table.

Mapping a Simple Field

The default mappings from .NET types to generic SQL types to backend specific types, i.e., MS SQL Server types or Oracle types or MySQL types is shown in table below.

Most databases provide synonyms for some types

Default Mapping — Simple Fields (.NET Type to Generic SQL, MSSQL and MySQL Type)

.NET Type

Generic SQL Type

MSSQL Type

MySQL Type

System.Boolean

BIT

TINYINT

BIT

 

TINYINT

TINYINT

TINYINT

System.Char

CHAR(1)

CHAR(1)

CHAR(1)

 

CHAR

CHAR(255)

CHAR(1)

System.SByte

 

SMALLINT

TINYINT

System.Byte

SMALLINT

SMALLINT

SMALLINT

System.Int16

SMALLINT

SMALLINT

SMALLINT

System.UInt16

INTEGER

INT 2

INT

System.Int32

INTEGER

INT 2

INT

System.UInt32

BIGINT

NUMERIC(19,0)

BIGINT

System.Int64

BIGINT

NUMERIC(19,0)

BIGINT

System.UInt64

UINT64

DECIMAL(20,0)

DECIMAL(20,0)

System.Single

REAL

REAL

FLOAT

 

FLOAT

FLOAT

FLOAT

System.Double

DOUBLE

FLOAT 1

DOUBLE

System.String

VARCHAR

VARCHAR(255)

VARCHAR(255)

System.DateTime

TIMESTAMP

DATETIME

DATETIME

 

DATE

DATETIME

DATETIME

 

TIME

DATETIME

DATETIME

System.Decimal

NUMERIC

NUMERIC(20,10)

NUMERIC(20,10)

 

DECIMAL

DECIMAL(20,0)

DECIMAL(20,0)

 

CLOB

TEXT

LONGTEXT

 

LONGVARCHAR

TEXT

LONGTEXT

 

LONGVARBINARY

IMAGE

LONGBLOB

System.Guid

GUID

UNIQUEIDENTIFIER

VARCHAR(40)

OpenAccess.Blob

BLOB

IMAGE

LONGBLOB

[] of primitive type

BLOB

IMAGE

LONGBLOB

 

BINARY

BINARY(255)

BINARY(255)

 

VARBINARY

VARBINARY(255)

TINYBLOB

Synonymous Mapping values for MSSQL Types:

1 DOUBLE PRECISION

2 INTEGER

If the ".NET type" to "generic sql type" mapping specifies a length, scale, or significance, this overrides the respective value from the "generic sql type" to database type mapping.

Default Mapping — Simple Fields (.NET Type to Oracle, SQL Anywhere Server and ADS Type)

.NET Type

Oracle Type

SQL Anywhere Type

ADS Type

System.Boolean

NUMBER 3

BIT

BIT

 

NUMBER 3

 

 

System.Char

CHAR(1)

CHAR(1)

CHARACTER[8 bits only]

 

CHAR(255)

 

 

System.SByte

NUMBER 3

SMALLINT

SHORTINT

System.Byte

NUMBER 3

TINYINT

SHORTINT

System.Int16

NUMBER 3

SMALLINT

SHORTINT[no value -215-1]

System.UInt16

NUMBER 2

UNSIGNED SMALLINT

SHORTINT[no value 216-1]

System.Int32

NUMBER 2

INTEGER

INTEGER[no value -231-1]

System.UInt32

NUMBER(19,0)

UNSIGNED INT

INTEGER[no value 232-1]

System.Int64

NUMBER(19,0)

BIGINT

NUMERIC(20,0)

System.UInt64

NUMBER 4

UNSIGNED BIGINT

NUMERIC(20,0)

System.Single

FLOAT(63) 5

REAL

DOUBLE

 

FLOAT

 

 

System.Double

FLOAT(126) 1

DOUBLE

DOUBLE

System.String

VARCHAR2(255)

VARCHAR(255)

CHARACTER(64)

System.DateTime

TIMESTAMP

TIMESTAMP

TIMESTAMP

System.Decimal

NUMBER(20,10)

NUMERIC(32,6)

NUMERIC(20,10)

 

NUMBER 4

 

 

 

CLOB

 

 

 

LONG

 

 

 

LONG RAW

 

 

System.Guid

VARCHAR2(40)

UNIQUEIDENTIFIER

CHARACTER(36)

OpenAccess.Blob

BLOB

LONG BINARY

BINARY

[] of primitive type

BLOB

LONG BINARY

BINARY

 

BINARY(255)

 

 

 

BLOB

 

 

Synonymous Mapping values for Oracle Types:

1 DOUBLE PRECISION

2 INTEGER

3 SMALLINT

4 DECIMAL

5 REAL

Advantage Database Server (ADS) treats System.Int32.MinValue and System.Int16.MinValue as null values ; therefore they cannot be stored in a database.

It might be necessary to change the scale for System.Decimal values depending on your needs.

Default Mapping — Simple Fields (.NET Type to Firebird Type)

.NET Type

Firebird Type

System.Boolean

SMALLINT

System.Char

CHAR(1)

System.SByte

SMALLINT

System.Byte

SMALLINT

System.Int16

SMALLINT

System.UInt16

INTEGER

System.Int32

INTEGER

System.UInt32

BIGINT

System.Int64

BIGINT

System.UInt64

BIGINT 1

System.Single

FLOAT

System.Double

DOUBLE PRECISION

System.String

VARCHAR(190)

System.DateTime

TIMESTAMP

System.Decimal

NUMERIC(18,3)

System.Guid

CHAR(16) CHARACTER SET OCTETS

OpenAccess.Blob

BLOB

 

VARBINARY

1 Values are stored correctly, however min/max/</>/avg/sorting are not being handled correctly.

Use one or more db-column extensions (see db-column) to change the defaults for each field. You will only need one db-column extension unless you require different mappings for different database types. The following XML metadata example changes the length (precision) of a field 10, for all databases and length 12, specifically for MSSQL:

Copy Code
<field name="name">
 
<extension key="db-column">
   
<extension key="db-length" value="10" />
 
</extension>
 
<extension key="db-column" value="mssql">
   
<extension key="db-length" value="12" />
 
</extension>
</
field>

Using CLOB or TEXT Columns

String fields are normally mapped to a VARCHAR column. To map to a CLOB or TEXT column specify a generic SQL type of CLOB. An appropriate SQL type and converter are selected depending on the database type. Following is an example XML metadata fragment:

Copy Code
<field name="description">
 
<extension key="db-column">
   
<extension key="db-type" value="CLOB" />
 
</extension>
</
field>

Using BLOB, IMAGE and VARBINARY Columns

Embedded fields and arrays of primitive types (byte[], int[], etc.) are mapped to a BLOB column by default. If your field is small (255 bytes or less) you may be able to use a VARBINARY column instead. To map to a VARBINARY column specify a generic type of VARBINARY. An appropriate SQL type and converter are selected depending on the database type. If the target database only has a BLOB type then this will be used instead:

Copy Code
<field name="binaryData">
 
<extension key="db-column">
   
<extension key="db-type" value="VARBINARY" />
 
</extension>
</
field>

Blob is more of an internal class and therefore, should not to be used directly in persistence capable classes. If the blob class is used directly in your persistence capable class, you will get a "no-args" constructor error if you try to use this within a container.

Oracle LONG and LONG RAW Columns

Oracle recommends that applications use CLOB and BLOB columns instead of LONG and LONG RAW columns. This option is only provided for compatibility with existing database schemas. You may only have one LONG or LONG RAW column per table.

To use a LONG column for one of your fields on Oracle change the DB type of the field to LONGVARCHAR. The column will still be mapped to a TEXT or CLOB on other databases. For embedded fields (e.g. byte[]) mapped to a LONG RAW column change the DB type to LONGVARBINARY, as shown in the example below:

Copy Code
<field name="description">
 
<extension key="db-column">
   
<extension key="db-type" value="LONGVARCHAR" />
 
</extension>
</
field>