The CHAR, CHARACTER and VARCHAR datatypes are implemented as a single string type with dynamic length. The precision that may be specified controls how the column is described by SQLColumns() , SQLDescribeCol() and so on. If a precision is not specified for a VARCHAR then the default precision will be 0, which means do not check. If a precision is not specified for a CHARACTER then Virtuoso sets the precision to 1. An explicit precision of 0 can be specified to turn off length checking for values stored in the column. If a value other than string or NULL is assigned to the column it is cast to a varchar (using CAST internally) and then stored into the column. If the value is not castable to a varchar then Virtuoso returns an error. Additionally if the column precision is greater than 0 and the value string length is greater than the column precision Virtuoso will also return an error.

The length is stored separately. Space required is 2+length for

A varchar column may contain binary 0 bytes.

A string literal is delimited by single quotes.

The ANY datatype is implemented as a single binary string type with dynamic length. It is reported as a VARCHAR in SQLColumns() , SQLDescribeCol() and so on. The precision returned by these columns is 24 but has no effect. This type can contain arbitrary binary data, including zeros.

The length is stored separately. The space required is 2+length

The various forms of NUMERIC and DECIMAL refer to one variable-precision floating point decimal data type that provides accurate arithmetic and decimal rounding. The default maximum precision and scale are 40 and 20. The precision is the number of decimal digits used in the computation. The scale is the maximum number of decimal digits to the right of the decimal point. Internal calculations are always precise but numbers are truncated to the column's precision and scale when stored. If a value being stored has more digits to the left of the decimal point than allowed in the column, Virtuoso signals an error. If a number being stored has more digits to the right of the decimal point than allowed in a column the decimal part is rounded to the precision of the column.

The space consumption of a number is

3 + precision / 2

bytes. The precision and scale of a column of this type are returned by functions such as SQLColumns() and SQLDescribeCol() .

A DECIMAL or NUMERIC with precision <= 9 and scale = 0 is transformed to INTEGER.

Literal numbers outside of the 32 bit signed integer range are of type decimal. Any numeric literals with a decimal point are of type decimal. Literals with an exponent are of type double precision.

These types are represented as a 32-bit signed binary integer, described as having a precision of 9 and a scale of 0, although the range is +/- 2**31. Storage space is 2 bytes for SMALLINT and 4 bytes otherwise.

A column declared SMALLINT is described as SQL_SMALLINT. A column declared INTEGER or INT is described as SQL_INTEGER.

Literals composed of of an optional sign and digits are of the integer type if they fit in the 32 bit range.

These types refer to the 64-bit IEEE floating-point number, the C double type. This is a fixed-precision binary floating point number is described as having a precision of 15 and a scale of 0. This type is preferable to NUMERIC if decimal rounding is not required since it is precise enough for most uses and more efficient than NUMERIC. The storage requirement is 8 bytes.

Any number literal with an exponent has the double type, e.g. 2e9.

This type is the 32-bit IEEE floating point number corresponding to the C float type. The storage requirement is 5 bytes.

These types implement a binary large object (BLOB) type. The length can be up to 2**31 bytes (2GB). If manipulated with the SQLGetData() and SQLPutData() ODBC functions a BLOB need not fit in the DBMS's or the client's memory. The LONG VARCHAR and LONG VARBINARY types are distinct only because certain ODBC applications gain from being able to distinguish long text from long binary. The types are described as SQL_LONGVARCHAR and SQL_LONGVARBINARY respectively, with a precision of 2GB.

Several long columns may exist on a single row. A long column may not be a key part in an index or primary key.

Data in long columns is stored as a linked list of database pages. Thus, a long column that does not fit in-line on the containing row will require an integer number of 8K database pages. If a long column's value is short enough to fit within the row containing it, the BLOB will be stored on the row and will not take more space than a VARCHAR of the same length. A long column fits on a row if the sum of the lengths of columns, including the long column, is under 4070 bytes.

ORDER BY, GROUP BY and DISTINCT may not reference long data types. Comparison of long data is not allowed unless first converted to the corresponding short type (varchar, nvarchar or varbinary). This conversion is only possible if the value is under 10MB in size. String functions accept long varchars and long nvarchars and convert them to varchar and nvarchar automatically. There is no long literal type per se, the corresponding character or binary type is assignable to a long type.

This type is internally like VARCHAR but is distinct for compatibility with ODBC applications. A VARBINARY column is described as SQL_BINARY to ODBC clients. The storage requirement is the same as for a corresponding VARCHAR column. VARBINARY and VARCHAR data are never equal even if the content is the same, but they can be cast to each other. VARBINARY data sorts in the unsigned order of the bytes comprising the data.

A varbinary literal is introduced by 0x followed by a hexadecimal representation of the bytes, 2 characters per byte, e.g. 0x0123456789abcdef.

All the time- and date-related types are internally represented as a single 'datetime' type consisting of a Julian day, hour, minute, second, 6-digit fraction and timezone. The range of the year is from 0 to over 9999. This type can accommodate all values of any SQL92 time-related type.

Although the internal representation is the same, a column of a time-related type is described as being of the appropriate ODBC type, i.e. SQL_TIMESTAMP for TIMESTAMP and DATETIME and SQL_DATE for DATE and SQL_TIME for TIME.

A DATETIME is described as precision 19, a DATE as precision 10 and a TIME as precision 8.

A column declared a TIMESTAMP is automatically set to the timestamp of the transaction that inserts or updates any column of the table containing it. The timestamp of a transaction is guaranteed to be distinct from that of any other transaction. For compatibility reasons a TIMESTAMP column is described to ODBC clients as a binary of 10 bytes. It is possible to use any date-related functions on TIMESTAMPs and to bind a TIMESTAMP column to a DATE or DATETIME variable (SQL_C_TIMESTAMP type in ODBC). Binding to a binary will also work but the data will then be opaque.

SQL92 provides for types with a timezone. Although the ODBC API does not expose the timezone, it is stored with these types and can be retrieved with the timezone() function. The timezone has a precision of minutes from UTC.

The storage requirement for these types is 10 bytes.

There is no date literal per se, but the ODBC shorthand for datetime literals can be used. The datetime/timestamp literal is of the form {dt 'YYYY-MM-DD HH:MM.SS'}. The date literal is of the form {d 'YYYY-MM-DD'}. Dates and datetimes may be compared between themselves but not with other types without explicit casting.

Some traditional relational databases keep all values of DATETIME type as combination of time and timezone data. Other keep time in some specific timezone without paying any attention to timezone at all. In RDF, the incoming triples may contain literals of types like xsd:dateTime with arbitrary values matching ISO 8601, and this standard permits the use of time values with optional timezone. Thus there should be a way of handling both "timezoned" and "timezoneless" datetimes inside one database. Virtuoso server supports this starting from version 07.20.3214.

Important note: The use of timezoneless datetimes may result in subtle errors in data processing. Applications that worked fine with timezoned datetimes may work incorrectly if timezoneless datetimes are used. The related application errors may stay unnoticed during local testing and reveal after worldwide use. To stay on safe side, the use of timezoneless datetimes with pre-07.20.3214 databases remains blocked even after the server executable is upgraded, so old applications will continue to work as before. When developing new applications, please pay attention to the check-list at the end of this section.

Virtuoso allows 30-bit Unicode data to be stored and retrieved from database fields. The data are stored internally as UTF-8 encoded strings for storage space optimization. Unicode fields are easily intermixable with other character data as all SQL functions support wide-string case and convert to the most wide character representation on demand. The native width of the wide character type may differ between platforms. Windows has a 16 bit wide character, whereas some Unixes have a 32 bit wide character type. The native width applies to the Virtuoso NVARCHAR data type when used as SQL data.

There are 3 additional data types to enable storing of Unicode data:

All the Unicode types are equivalent to their corresponding "narrow" type - CHAR, VARCHAR and LONG VARCHAR - except that instead of storing data as one byte they allow Unicode characters. Their lengths are defined and returned in characters instead of bytes. They collate according to the active wide character collation, if any. By default this is the order of the Unicode serialization values. These types can be used anywhere the narrow character types can be used, except in LIKE conditions.

Unicode literals are introduced by n' and closed with ' (single quote). See Internationalization section on the interpretation of wide literals. This may be either UTF-8 according to some character set.

When there is a need to convert a wide string to a narrow one or vice versa, a character set is used. A character set returns a wide string code for a wide char. For example there can be a definition of the ISO-8859-5 "narrow" character set which describes mapping of non-ASCII character codes to their Unicode equivalents. Virtuoso relies on the fact that the ASCII character codes are represented in Unicode by type-casting and in UTF8 as one-byte tokens with the same value as in ASCII.

When conversion is done on the server-side using cast or some of the SQL built-in functions, the wide characters are converted to narrow using a system-independent server-side character set. In the absence of such a character set, Virtuoso uses the Latin1 character set to project narrow character codes into the Unicode space as equally valued wide-character codes.

When conversion is done client-side - for example, when binding a VARCHAR to a wide buffer - the default client's system character set is used.

Wide-character literals have ANSI SQL92 syntax: N'xxx' (prefixing normal literals with the letter N). These strings process escapes with a values large enough to represent all the Unicode characters.

Virtuoso supports user-definable data types that can be based on any hosted language or classes such as C#. New types can be further derived producing sub-types. User-defined types can include methods and constructors to create any potentially complicated system to house data as exactly required.

User defined types can be used to defined database table columns.

	See Also:
	The User Defined Types section.

All the built-in SQL functions that take character attributes and have a character input calculate their output type such that if any attribute is a wide string or a wide BLOB, then the result is a wide string; otherwise, the output character type is narrow.

Functions like make_string() that have character result types but that do not have character parameters produce narrow strings. Virtuoso provides equivalent functions for wide output, such as make_wstring() .

Virtuoso' ODBC client implements the SQL...W functions (like SQLConnectW() ) that take Unicode arguments. This enables faster wide-character processing and allows binding of the SQL_C_WCHAR output type. Since Virtuoso's SQL parser does not allow Unicode data in SQL commands, they should be bound as parameters or should be represented as escapes.

Attached tables use the default collation of the data source for narrow strings. Virtuoso maps Wide-string columns in remote tables to the appropriate local wide-character type. The data are then passed intact in case of wide-to-wide mapping. When data are converted client-side in the VDB the Server's system character set is used (where available).

The built-in data types denoting sequences of characters, wide or narrow, long or short, are:

All these types have the common trait of representing sequences of characters and hence some common operations and conversions are possible between them.

¶

Built-In SQL Functions

All SQL92 string functions will accept varchar, long varchar, nvarchar or long nvarchar arguments. If the argument is long and its actual length is above the maximum length of a varchar, the conversion fails and Virtuoso signals an error. You can interchange long and varchar types as long as the length remains under the varchar maximum of 16MB.

	Note:
	Varchars or nvarchars stored in columns have a much lower limit due to the 4K row length limit. Intermediate results or values converted from long columns are not affected by this limit.

If Virtuoso converts a value from long varchar to varchar or from long nvarchar to nvarchar when passing the value as an argument to a string function, the value changes in place. This has the effect of replacing the handle with the string. Users normally do not see this, but may detect it with type test functions such as isblob() .

¶

Handling Long Data for Input and Output

LOBs of up to 2GB can be handled as streams without demand on memory from ODBC clients using SQLGetData() and SQLPutData() . All other ways of processing long data will need to make either a contiguous or non-contiguous copy in memory.

To transfer long data between PL procedures and files one can use the string_to_file() function, which will accept a handle and will not need to copy the content to memory in order to write it.

To read a large object from a file to a table, you can use the file_to_string_output() function to get contents that may be longer than the varchar limit into a string output. This can then be assigned to a BLOB column.

For long file-resident XML data you can use the xml_persistent() function with the file:// protocol.

	See Also:
	The XML Support chapter.