Other Characteristics
This section describes other characteristics of the SQL grammar. It is divided into the following sections:
•
Temporary Files
•
Working with Literal Values
•
Working with NULL Values
•
Working with Binary Data
•
Creating Indexes
•
Closing an Open Table
•
Concurrency
•
Comma as Decimal Separator
•
OEM to ANSI Support
Temporary Files
When Pervasive 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, Pervasive PSQL uses the path specified for TempFileDirectory. (The registry location differs depending on the bit architecture of the product. For Pervasive PSQL 32-bit Server, the location is HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. For Pervasive PSQL 64-bit Server, the location is HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI.)
If you have not defined the registry entry, Pervasive 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, Pervasive PSQL uses the path specified in the TEMP environment variable, and so forth.
On Linux distributions, Pervasive PSQL uses the current directory for the server process. No attempt is made to use TMP.
Pervasive 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?
Pervasive PSQL uses three types of temporary files: in-memory, on-disk, and Btrieve (transactional interface).
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, and 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, and 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.
Pervasive 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 Literal Values
Pervasive PSQL supports all of the standard ODBC literal formats. This section provides some examples of the most common literal formats.
•
Date Values
•
Time Values
•
Timestamp Values
•
Date Arithmetic
String Values
String constants may be expressed in SQL statements by surrounding the given string of characters with single quotes. Single quotes contained within the string itself require a preceding single quote as an escape character.
Examples
In the first example, the apostrophe or single quote contained within the string must be escaped by another single quote.
SELECT * FROM t1 WHERE c1 = ‘Roberta’s Restaurant’
SELECT STREET FROM address WHERE city LIKE ‘san’
Date Values
Date constants may be expressed in SQL statements as a character string or embedded in a vendor string. The first case is treated as data type SQL_CHAR and the vendor string representation is treated as a value of type SQL_DATE. This becomes important when conversions are attempted.
Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.
Pervasive PSQL supports the following date literal format: 'YYYY-MM-DD'.
Dates may be in the range of year 0 to 9999.
Examples
The next two statements return all the classes whose start date is after 1995-06-05.
SELECT * FROM Class WHERE Start_Date > '1995-06-05'
SELECT * FROM Class WHERE Start_Date > {d '1995-06-05'}
Time Values
Pervasive PSQL supports the following time literal format: 'HH:MM:SS'.
Time constants may be expressed in SQL statements as a character string or embedded in a vendor string. Character string representation is treated as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIME.
Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.
Examples
The following two statements retrieve records from the class table where the start time for the classes is 14:00:00.
SELECT * FROM Class WHERE Start_time = '14:00:00'
SELECT * FROM Class WHERE Start_time = {t '14:00:00'}
Timestamp Values
Timestamp constants may be expressed in SQL statements as a character string or embedded in a vendor string. Pervasive PSQL treats the character string representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIMESTAMP.
Pervasive PSQL partially supports extended ODBC SQL grammar, as outlined in this function.
Pervasive PSQL supports the following timestamp literal format: 'YYYY-MM-DD HH:MM:SS.MMM'
Examples
The next two statements retrieve records from the Billing table where the start day and time for the log is 1996-03-28 at 17:40:49.
SELECT * FROM Billing WHERE log = '1996-03-28 17:40:49'
SELECT * FROM Billing WHERE log = {ts '1996-03-28 17:40:49'}
Date Arithmetic
Pervasive PSQL supports adding or subtracting an integer from a date where the integer is the number of days to add or subtract, and the date is embedded in a vendor string. (This is equivalent to executing a convert on the date).
Pervasive PSQL also supports subtracting one date from another to yield a number of days.
Examples
SELECT * FROM person P, Class C WHERE p.Date_Of_Birth < ' 1973-09-05' and c.Start_date >{d '1995-05-08'} + 30
Working with NULL Values
Pervasive 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 isn’t a 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, Pervasive 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 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 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, reducing the effective storage by 1 byte.
Closing an Open Table
Calling SQLFreeStmt with the SQL_CLOSE option changes the SQLState but does not close the open tables used by the hStmt. To close the tables currently used by hStmt, SQLFreeStmt must be called with the SQL_DROP option.
In the following example, the Emp and Dept tables remain open:
SQLPrepare(hStmt, “SELECT * FROM Emp, Dept”, SQL_NTS)
SQLExecute(hStmt)
SQLFetch until SQL_No_Data_Found
SQLFreeStmt(hStmt, SQL_CLOSE)
When SQLPrepare is subsequently called on the hStmt, the tables used in the previous statement are closed. For example, when the following call is made, both the Emp and Dept tables are closed by Pervasive PSQL:
SQLPrepare(hStmt, “SELECT * FROM Customer”,SQL_NTS)
The following call would then close the table Customer:
SQLFreeStmt(hStmt, SQL_DROP)
Concurrency
The timeliness of data, dynamic or snapshot, is determined by whether or not execution of a query results in a sort. Queries with DISTINCT, GROUP BY, or ORDER BY result in a temporary sort unless an index exists that satisfies the required ordering.
For those queries which do not result in a temporary sort, the data fetched is from the data files. For those queries that result in a temporary sort, the data fetched is from a temporary table. The temporary table is built from the required data in the original data file at SQLExecute time.
*Note: For some sort operations (for example, SELECT statements where long data columns are included in the select-list, or SELECT statements with GROUP BY), Pervasive PSQL may use bookmarks that it assumes are persistent within a SELECT statement. The situation may arise whereby another application updates or deletes the row that a bookmark references.

To avoid this situation, an application may set an exclusive lock on the table being sorted through a call to SQLSetStmtOption, with fOption = 1153 and vParam = 1.
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.
Pervasive PSQL supports both the period “.” and the comma “,” as decimal separators. Pervasive 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 Pervasive 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 Pervasive 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 cannot be configured, and it is set to a period. If you have a Linux 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 Pervasive PSQL services after you change the regional settings. This allows the database engine to use the changed settings.
Examples
Example A - Server locale uses the comma for decimal separator
Client’s 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:
10.123, 1.232
10.123, 1.232
 
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:
10,123, 1,232
Client’s 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:
10.123, 1.232
10.123, 1.232
Example B - Server locale uses the period for decimal separator
Client’s locale uses comma “,” as decimal separator:
Same as client using comma “,” in Example A.
Client’s 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:
10.123, 1.232
 
SET DECIMALSEPARATORCOMMA=ON
SELECT * FROM t1 FROM c1 = 10.123
The above SELECT statement, if executed after setting the decimal separator for display, returns:
10,123, 1,232
OEM to ANSI Support
Applications can now store or retrieve character data in the OEM character set using Pervasive PSQL, while allowing the data to be manipulated and displayed using the ANSI Windows character set. The Pervasive ODBC driver translation DLL can perform all necessary translations between the two character sets. This feature can be turned on or off for each DSN. See Advanced Connection Attributes for Engine DSN for the DSN Setup dialog.
The Pervasive PSQL Control Center and the SQL Editor are fully OEM-character aware if you use extended ASCII characters for column or table names. Any character data that is passed to and from the database is correctly translated between the OEM and ANSI character sets.
If your application connects to the data source using SQLDriverConnect, you can also specify the translation DLL using the connection string option TRANSLATIONDLL=path_and_DLL_name. The translation DLL name for Pervasive is W32BTXLT.DLL.
NOTE: The OEM to ANSI translation option is available only for client or local engine DSNs.