SET DEFAULTCOLLATE

SET DEFAULTCOLLATE

The SET DEFAULTCOLLATE statement specifies the collating sequence file to use for all columns of data type CHAR, VARCHAR, or LONGVARCHAR.

SET DEFAULTCOLLATE = < NULL | 'acs-path' >

acs-path ::= location of the file that specifies the collating sequence rules

The SET DEFAULTCOLLATE statement offers the advantage of a session setting, which is more convenient than specifying a collating sequence for numerous columns individually.

Individual columns can still specify a collating sequence if you choose. The use of the COLLATE key word as part of a column definition overrides the SET DEFAULTCOLLATE setting for that particular column. See the column definitions for ALTER TABLE and CREATE TABLE.

The “NULL” parameter for SET DEFAULTCOLLATE causes the session to use no alternate collating sequence (ACS) file. No collating sequence is applied to any text columns except for those with individual COLLATE definitions. An ACS file is a 265-byte image of the same format used for an ACS in the transactional interface. See User-Defined ACS in Pervasive PSQL Programmer's Guide.

The default session setting for DEFAULTCOLLATE is null.

The following requirements apply to the acs-path parameter.

•

The parameter must specify a path that is valid comparative to the location of the database engine, not to the location of the calling application. (That is, from the database engine's point of view, not from the calling application's perspective.)

•

The directory structure in the path must exist. (SET DEFAULTCOLLATE does not create a path.)

•

The path must include a file name. The file specifies the collating rules (as, for example, upper.alt, which is provided with Pervasive PSQL).

•

The path must be enclosed in single quotes.

•

The path must be at least 1 character long and cannot exceed 255 characters.

•

Relative paths are allowed and are relative to the directory where the DDFs are located. Relative paths and can contain a period (current directory), double period (parent directory), slash, or any combination of the three. Slash characters in relative paths may be either forward (/) or backslash (\). You may mix the use of the two types, if desired.

•

Universal naming convention (UNC) path names are permitted.

For default locations of Pervasive PSQL files, see Where are the Pervasive PSQL files installed? in Getting Started With Pervasive PSQL.

The following example sets a default collating sequence using the upper.alt file supplied with Pervasive PSQL. A table, t1, is created with three text columns and three columns not text. A SELECT statement executes against Pervasive PSQL system tables to return the ID, type, size, and attributes of the columns in t1. The result shows that the three text columns have an attribute of “UPPER.”

SET DEFAULTCOLLATE = 'file_path\upper.alt'

CREATE TABLE t1 (c1 INT, c2 CHAR(10), c3 BINARY(10), c4 VARCHAR(10), c5 LONGVARBINARY, c6 LONGVARCHAR)

SELECT * FROM x$attrib WHERE xa$id in (SELECT xe$id FROM x$field WHERE xe$file = (SELECT xf$id FROM x$file WHERE xf$name = 't1'))

Xa$Id Xa$Type Xa$ASize Xa$Attrs

===== ======= ======== ========

327 O 265 UPPER

329 O 265 UPPER

331 O 265 UPPER

3 rows were affected.

============

The following example continues with the use of table t1. An ALTER TABLE statement changes the text column c2 from a CHAR to an INTEGER. The result of the SELECT statement shows that now only two columns are affected by the default collating.

ALTER TABLE t1 ALTER c2 INT

SELECT * FROM x$attrib WHERE xa$id in (SELECT xe$id FROM x$field WHERE xe$file = (SELECT xf$id FROM x$file WHERE xf$name = 't1'))

Xa$Id Xa$Type Xa$ASize Xa$Attrs

===== ======= ======== ========

329 O 265 UPPER

331 O 265 UPPER

2 rows were affected.

============

The following example uses an ALTER TABLE statement to change column c1 in table t1 from an INTEGER to a CHAR. The result of the SELECT statement shows that three columns are affected by the default collating.

ALTER TABLE t1 ALTER c1 CHAR(10)

SELECT * FROM x$attrib WHERE xa$id in (SELECT xe$id FROM x$field WHERE xe$file = (SELECT xf$id FROM x$file WHERE xf$name = 't1'))

Xa$Id Xa$Type Xa$ASize Xa$Attrs

===== ======= ======== ========

326 O 265 UPPER

329 O 265 UPPER

331 O 265 UPPER

3 rows were affected.