Other Characteristics

Other Characteristics

This section describes other characteristics of the SQL grammar. It is divided into the following sections:

•

Temporary Files

•

Working with NULL Values

•

Working with Binary Data

•

Creating Indexes

•

Comma as Decimal Separator

Temporary Files

When PSQL must generate a temporary table in order to process a given query, it creates the file in a location determined by the following rules.

First, if you have manually added the string key value PervasiveEngineOptions\TempFileDirectory to the Windows registry, PSQL uses the path set for TempFileDirectory. (The registry location differs depending on the bit architecture of the product. For PSQL 32-bit Server, the location is HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. For PSQL 64-bit Server, the location is HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI.)

If you have not defined the registry entry, PSQL uses the following sequence on Window platforms:

•

The path specified by the TMP environment variable.

•

The path specified by the TEMP environment variable.

•

The path specified by the USERPROFILE environment variable.

•

The Windows directory.

For example, if the TMP environment variable is not defined, PSQL uses the path specified in the TEMP environment variable, and so forth.

On Linux and OS X distributions, PSQL uses the current directory for the server process. No attempt is made to use TMP.

PSQL deletes all temporary files needed to process a query when it has completed processing the query. If the query is a SELECT statement, then the temporary files exist as long as the result set is active, that is, until the result set is freed by the calling application.

When are Temporary Files Created?

PSQL uses three types of temporary files: in-memory, on-disk, and Btrieve (MicroKernel Engine).

In-memory Temporary File

In-memory temporary files are used for the following circumstances:

•

forward-only cursor

•

number of bytes in the temporary file is less than 250,000

•

SELECT statements with ORDER BY, GROUP BY, or DISTINCT that do not use an index, that have no BLOB or CLOB in the ORDER BY, GROUP BY, or DISTINCT, and that have no BLOB or CLOB in selection list with UNION.

On-disk Temporary File

On-disk temporary files are used for the following circumstances:

•

forward-only cursor

•

number of bytes in the temporary file is greater than 250,000

•

SELECT statements with ORDER BY, GROUP BY, or DISTINCT that do not use an index, that have no BLOB or CLOB in the ORDER BY, GROUP BY, or DISTINCT, and that have no BLOB or CLOB in selection list with UNION.

Btrieve Temporary File

Btrieve temporary files are used for the following circumstances:

•

Forward-only cursor with BLOB or CLOB in ORDER BY, GROUP BY, or DISTINCT or with BLOB or CLOB in selection list with UNION

•

Dynamic or static cursor with UNION queries or SELECT statements with ORDER BY, GROUP BY, or DISTINCT that do not use an index.

PSQL does not create a Btrieve temporary file for each base table in a static cursor SELECT query. Instead, each base table is opened by using the MicroKernel to reserve pages in the file as a static representation of the file. Any change made through a static cursor cannot be seen by that cursor.

Working with NULL Values

PSQL interprets a NULL as an unknown value. Thus, if you try to compare two NULL values, they will compare as not equal.

An expression that evaluates to WHERE NULL=NULL returns FALSE.

Working with Binary Data

Consider the following scenario: you insert the literal value '1' into a BINARY(4) column named c1, in table t1. Next, you enter the statement, SELECT * FROM t1 WHERE c1='1'.

The engine can retrieve data using the same binary format as was used to input the data. That is, the SELECT example above works properly and returns the value, 0x01000000, even though there is no literal match.

Note: The engine always adds a zero (‘0’) to the front of odd-digit binary values that are inserted. For example, if you insert the value '010', then the value '0x00100000' is stored in the data file.

Currently, PSQL does not support suffix 0x to denote binary constants. Binary constants are a string of hexadecimal numbers enclosed by single quotation marks.

This behavior is the same as SQL Server.

Creating Indexes

The maximum column size for indexable VARCHAR columns is 254 bytes if the column does not allow Null values and 253 bytes if the column is nullable.

The maximum column size for CHAR columns is 255 bytes if the column does not allow Null values and 254 bytes if the column is nullable.

The maximum column size for indexable NVARCHAR columns is NVARCHAR(126). This limit applies to both nullable and not-null columns. The NVARCHAR size is specified in UCS-2 character units.

The maximum column size for NCHAR columns is NCHAR(127). This limit applies to both nullable and not-null columns. The NCHAR size is specified in UCS-2 character units.

The maximum Btrieve key size is 255. When a column is nullable and indexed a segmented key is created with 1 byte for the null indicator and a maximum 254 bytes from the column indexed. VARCHAR columns differ from CHAR columns in that either length byte (Btrieve lstring) or a zero terminating byte (Btrieve zstring) are reserved, increasing the effective storage by 1 byte. NVARCHAR (Btrieve wzstring) columns differ from NCHAR columns in that a zero terminating character is reserved, increasing the effective storage by 2 bytes.

Comma as Decimal Separator

Many locales use a comma to separate whole numbers from fractional numbers within a floating point numeric field. For example, these locales would use 1,5 instead of 1.5 to represent the number one-and-one-half.

PSQL supports both the period and the comma as decimal separators. PSQL accepts input values using the period or the comma, based on the regional settings for the operating system. By default, the database engine displays values using the period.

Note: When the decimal separator is not a period, numbers appearing in SQL statements must be enclosed in quotes.

For output and display only, the session-level command SET DECIMALSEPARATORCOMMA can be used to specify output (for example, SELECT results) that uses the comma as the decimal separator. This command has no effect on data entry or storage.

Client/Server Considerations

Support for the comma as decimal separator is based on the locale setting in the operating system. Both the client operating system and the server operating system have a locale setting. The expected behavior varies according to both settings.

•

If either the server or client locale setting uses the comma as decimal separator, then PSQL accepts both period-separated values and quoted comma-separated values.

•

If neither the server nor the client locale setting uses the comma decimal separator, then PSQL does not accept comma-separated values.

Changing the Locale Setting

Decimal separator information can be retrieved or changed only for a machine running a Windows operating system. The decimal setting for Linux and OS X cannot be configured, and it is set to a period. If you have a Linux and OS X server engine and you want to use the comma as decimal separator, you must ensure that all your client computers are set to a locale that uses the decimal separator.

To change the regional settings on a Windows operating system, access the settings from the Control Panel. Ensure that you stop and restart the PSQL services after you change the regional settings. This allows the database engine to use the changed settings.

Example A - Server locale uses the comma for decimal separator

Client locale uses comma as decimal separator:

CREATE TABLE t1 (c1 DECIMAL(10,3), c2 DOUBLE)

INSERT INTO t1 VALUES (10.123, 1.232)

INSERT INTO t1 VALUES ('10,123', '1.232')

SELECT * FROM t1 WHERE c1 = 10.123

SELECT * FROM t1 FROM c1 = '10,123'

The above two SELECT statements, if executed from the client, return:

SET DECIMALSEPARATORCOMMA=ON

SELECT * FROM t1 FROM c1 = '10,123'

The above SELECT statement, if executed from the client after setting the decimal separator for display, returns:

Client locale uses period as decimal separator, and these statements are issued from a new connection (meaning default behavior for SET DECIMALSEPARATORCOMMA):

CREATE TABLE t1 (c1 DECIMAL(10,3), c2 DOUBLE)

INSERT INTO t1 VALUES (10.123, 1.232)

INSERT INTO t1 VALUES ('10,123', '1.232')

SELECT * FROM t1 WHERE c1 = 10.123

SELECT * FROM t1 WHERE c1 = '10,123'

The above two SELECT statements, if executed from the client, return:

Example B - Server locale uses the period for decimal separator

Client locale uses comma as decimal separator:

Same as client using comma in Example A.

Client locale uses period as decimal separator:

CREATE TABLE t1 (c1 DECIMAL(10,3), c2 DOUBLE)

INSERT INTO t1 VALUES (10.123, 1.232)

INSERT INTO t1 VALUES ('10,123', '1,232') -- error in assignment

SELECT * FROM t1 WHERE c1 = 10.123

SELECT * FROM t1 WHERE c1 = '10,123' -- error in assignment

The first select statement above, if executed from the client, returns:

SET DECIMALSEPARATORCOMMA=ON

SELECT * FROM t1 FROM c1 = 10.123

The above SELECT statement, if executed after setting the decimal separator for display, returns: