Pervasive logo

Prev SQL Engine Reference Next

Other Characteristics

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

Temporary Files

When the SRDE 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, the SRDE uses the path specified in the registry key string value HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PervasiveEngineOptions\TempFileDirectory.

If the registry entry is not defined, then the SRDE uses the path specified in the TMP environment variable.

If the TMP environment variable is not defined, then the SRDE uses the path specified in the TEMP environment variable.

If the TEMP environment variable is not defined, then the SRDE uses the Windows directory for Windows NT/2000/XP otherwise the current directory.

The SRDE 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?

Temporary files are created under the following circumstances:

Forward-only, dynamic, or static cursor

SELECT statements with ORDER BY, GROUP BY, DISTINCT that do not use an index.

SELECT statements with UNION that would generate a temporary table in memory or an OptTech Sort temporary table on disk if temporary table size is greater than 256K.

In addition, for a static cursor, if you have several base tables in the join, the MicroKernel generates a Btrieve temporary file for each table in the join and another temporary file if you use an ORDER BY clause in the query.

Working with Literal Values

Pervasive.SQL supports all of the standard ODBC literal formats. This section provides some examples of the most common literal formats.

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.

The SRDE partially supports extended SQL grammar, as outlined in this function.

Pervasive.SQL 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.SQL 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.SQL 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.SQL 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.SQL partially supports extended ODBC SQL grammar, as outlined in this function.

Pervasive.SQL supports the following timestamp literal format: 'YYYY-MM-DD HH:MM:SS'

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

The SRDE 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).

The SRDE 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

The SRDE 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.

Do not use the prefix 0x to denote binary constants. Binary constants are a string of hexadecimal numbers enclosed by single quotation marks. The SRDE adds prefix 0x to the results of a SELECT statement.

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 the SRDE: 

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 by SRDE, unless an index exists that satisfies the required ordering.

For those queries which do not result in a temporary sort by SRDE, the data fetched is from the data files. For those queries that result in a temporary sort by SRDE, 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), SRDE may use bookmarks which SRDE 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.

Starting with Pervasive.SQL V8, the SRDE can support both the period "." and the comma "," as decimal separators. The SRDE 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.

Changing the Locale Setting

Decimal separator information can only be retrieved or changed for a Win32 machine (Windows 95/98/NT/2000).

The decimal setting for NetWare and Linux cannot be configured, and it is set to a period. If you have a NetWare or 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 view or change your locale setting

  1. From the Start menu, choose Settings | Control Panel.
  2. In the Control Panel window, double-click Regional Settings.
  3. On the Regional Settings tab, select the desired country.
  4. You must stop and restart the Pervasive.SQL services.

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.SQL, 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. To access the switch, click Options... on the Pervasive ODBC DSN Setup dialog box.

The Pervasive Control Center (PCC) and the SQL Data Manager (SQL DM) 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.

Prev
Global Variables		 Contents
Up
Check for Revisions 		Next
Performance Reference