Create (14)

The Create operation (B_CREATE) lets you generate a new data file from within your application. The Create operation also has subfunctions that allow you to delete or rename a file (see Delete and Rename Subfunctions for the Create Operation ).

---

Note
In the same directory, no two files should share the same file name and differ only in their file name extension. For example, do not name a data file Invoice.btr and another one Invoice.mkd in the same directory. This restriction applies because the database engine uses the file name for various areas of functionality while ignoring the file name extension. Since only the file name is used to differentiate files, files that differ only in their file name extension look identical to the database engine.

---

Parameters

Prerequisites

If you are creating an empty file over an existing file, ensure that the existing file is closed before executing the Create operation.

Procedure

Details

Table 2-1 illustrates the order in which the file and key specifications must be stored.

---

Note
You must allocate the "not used" and "reserved" areas of the Data Buffer, even though the transactional interface does not use them for a Create operation. Initialize the reserved areas to zero to maintain compatibility with future releases.

---

File Specification

Store the file specification in the first 16 bytes of the Data Buffer. The bytes are numbered beginning with 0. Store the information for the record length, page size, and number of indexes as integers.

Logical Record Length. (Offset 0x00) The logical record length is the number of bytes of fixed-length data in the file. (Do not include variable-length data in the logical record length.)

Page Size. (Offset 0x02) Page size is determined by your file format version. See Choosing a Page Size in Pervasive PSQL Programmer's Guide.

Number of Indexes. (Offset 0x04) The number of indexes is the number of keys (not key segments) you are defining for the file. To create a data-only file, set the number of indexes to 0.

File Version. (Offset 0x05) The transactional interface file version to be created. In prior releases, the transactional interface used a two byte integer to receive the number of indexes on a create operation. The high order byte of this integer was always zero because the maximum number of indexes is 119. This high-order byte has always been used in the Stat operation to return the file version and it can now be used to specify the file version in the Create operation without breaking any previous applications. The supported file versions for Create are 6.x, 7.x, 8.x, 9.x, 9.5 which are represented in the specified byte with hex values 0x50, 0x60, 0x70, 0x80, 0x90, 0x95, respectively.

Physical Page Size. (Offset 0xA). This field was previously marked "Not Used." This field is used in conjunction with the "Page Compression" file flag. If the "Page Compression" flag is not specified then this field is ignored.

In data files version 6.x and later, logical pages map to physical pages, stored in a Page Allocation Table (PAT). A physical page is exactly the same size as a logical page. Page compression can be used with file format 9.5 and later. Database pages are compressed at the page level. Each logical page is compressed into one or more physical page units. These individual physical pages are smaller in size than a logical page.

The physical page size field can be used to specify the physical page size to be used for the file. The value specified in this field is multiplied by 512 to determine the actual physical page size used. If zero is specified the engine will use a default value for the physical page size. The default value is 512 bytes.

The value specified for the physical page size cannot be larger than the value specified for the logical page size. If it is then the engine will round down the value specified for the physical page size so that it is the same as the logical page size. The logical page size needs to be an exact multiple of the physical page size. If it is not then the logical page size is rounded down so that it becomes an exact multiple of the physical page size. If, as a result of these manipulations, the logical and physical values end up to be the same, then page level compression will not turned on for this file. See also Creating a File with Page Level Compression in Pervasive PSQL Programmer's Guide

File Flags. (Offset 0x0A) The bit settings in the File Flags word specify file attributes. Table 2-2 shows the binary, hexadecimal, and decimal representations of the file flag values.

Avoid using incompatible flags; flags are incompatible if they use the same bit positions. Unused bits are reserved for future use. Set them to 0.

To combine file attributes, add their respective File Flag values. For example, to specify a file that allows variable-length records and uses blank truncation, initialize the File Flags word to 3 (2 + 1). The transactional interface ignores the Blank Truncation and Free Space bits if the Variable Length Records bit is set to 0.

If you set the Page Preallocation bit, use the last 2 bytes in the file specification block (allocation) to store an integer value that specifies the number of pages to preallocate to the file. If you set the Data Compression bit, the transactional interface ignores the Variable Length Records bit. If you set the Key-only File bit, the transactional interface ignores the System Data bits.

The database engine automatically uses data compression on files that use system data and have a record length that exceeds the maximum length allowed. See Table 4-10 .

Set the Duplicate Pointers bit if you anticipate adding an index sometime after creating a file, and if that index has many duplicate values. Setting this bit causes the transactional interface to reserve space in each record of the file for pointers that link the duplicate values. By reserving this space, you can possibly lower retrieval time and save disk space, especially if the keys are long and you anticipate that many records will have duplicate key values.

---

Note
You can reserve duplicate pointer space only for indexes that are added after file creation. Therefore, when reserving space for pointers to duplicate values, do not include space for the indexes created during this Create operation. Also, the transactional interface does not reserve duplicate pointer space for any key you specify as a repeating-duplicatable key.

---

Set the Key Number bit if you need to assign a specific number to a key, and place the desired key number in the Manually Assigned Key Number element (offset 0x0E) of the Key Specification block. The transactional interface does not require consecutive key numbers; files can have gaps between key numbers. When a key is created, by default the transactional interface assigns the lowest available key number to that index (beginning with 0). However, some applications may require a key number different from the default assignment.

Set the Use VATs bit if the file uses Variable-tail Allocation Tables. To use VATs, a file must use variable-length records.

Number of Duplicate Pointers to Reserve. (Offset 0x0C) You can specify the number of duplicate pointers to reserve for each file. Use this element only if you specified the Reserve Duplicate Pointers file flag. For more information about duplicatable keys, refer to the following topic in Pervasive PSQL Programmer's Guide: Duplicatable Keys .

Allocation. (Offset 0x0E) You can specify the number of pages to preallocate. Use this element only if you specified the Page Preallocation file flag. For more information about page preallocation, see the following topic in Pervasive PSQL Programmer's Guide: Page Preallocation .

Key Specification Blocks

Place the key specification blocks immediately after the file specification. Allocate a 16-byte key specification block for each key segment in the file. Store the information for the Key Position and the Key Length as integers.

The maximum number of key segments allowed depends on the file's page size.

See also the status codes 26: The number of keys specified is invalid and 29: The key length is invalid , both in Status Codes and Messages.

Key Position. (Offset 0x00) The key position is the byte offset at which the key or key segment begins. Positions are relative to 1; A key at the beginning of the record starts at position 1. There is no position 0.

Key Length. (Offset 0x02) The length of the key or key segment. The maximum length of a key, including all key segments, is 255 bytes.

Key Flags. (Offset 0x04) The bit settings in the Key Flags word specify key attributes. Table 2-3 shows the binary, hexadecimal, and decimal representations of the Key Flags values.

Avoid using incompatible flags; flags are incompatible if they use the same bit positions. Unused bits are reserved for future use. Set them to 0.

To combine key attributes, add their respective Key Flags values. For example, if the key is an extended type, part of a segmented key, and to be collated in descending order, initialize the Key Flags word to 336 (256 + 16 + 64).

The Segmented Key attribute indicates that the next key specification block in the Data Buffer refers to the next segment of the same key. Follow these rules for segmented keys:

ACSs are applicable only to STRING, LSTRING, and ZSTRING keys. You cannot define a key that is both case-insensitive and uses an ACS. In a file in which a key has an ACS designated for some segments but not for others, the segments that designate an ACS are sorted by the specified ACS; the segments with no ACSs are sorted according to their respective types.

Extended Data Type. (Offset 0x0A) You specify the Extended Data Type in byte 10 of the Key Specification block as a binary value. Table 2-4 shows the codes for the extended data types.

Extended data type codes 12, 13, 16, and 21 through 24 are reserved for future use.

You can define the STRING and UNSIGNED BINARY data types as either standard or extended types. This maintains compatibility with applications that were developed with earlier versions of Btrieve API, while allowing newer applications to use extended data types exclusively.

Regarding the data type you assign to a key, transactional interface does not ensure that the records you input adhere to the data types defined for the keys. For example, you could define a TIMESTAMP key in a file, but store a character string there. Your Btrieve API application would work fine, but an ODBC application that tries to access the same data using the ODBC TIMESTAMP format might fail, because the byte format could be different and the algorithms used to generate the timestamp value could be different. For complete descriptions of the data types, refer to SQL Engine Reference.

Null Value. (Offset 0x0B) of the Key Specification block represents an exclusion value for the key. If you have defined a key as a null key, you must supply a value for the transactional interface to recognize as the null value for each key segment. This is in reference to the legacy null and does not reflect true nulls. For a discussion of null support, see the following topic in Pervasive PSQL Programmer's Guide: Null Value .

Manually Assigned Key Number. (Offset 0x0E) The transactional interface allows an application to assign specific key numbers when creating a file with indexes. To manually assign key numbers to each index for a file, specify each key's number as a binary value in byte 14 of the key specification block, and set the Key Number bit (0x400) in the File Flags word.

Key numbers must be unique to the file and must be specified in ascending order, beginning with key 0. They must also be valid (less than the maximum number of keys for the file's page size).

The ability to manually assign key numbers complements to the ability to delete a key and not have the transactional interface renumber all keys that have a key number greater than the deleted key. For example, if an application drops an index and instructs the transactional interface not to renumber higher-numbered keys, and if a user then clones the affected file without assigning specific key numbers, the cloned file has different key numbers than the original.

ACS Number. (Offset 0x0F) For keys that use a specific ACS, offset 0x0F in the key specification block provides the ACS number by which to collate the key. The ACS number is based on its position in the Data Buffer. The first ACS following the last key specification block is ACS number 0. Following ACS 0 is ACS 1, which is followed by ACS 2, and so on.

Alternate Collating Sequence

Your application specifies ACSs one after the other immediately following the last key specification block in the Data Buffer.

User-Defined ACSs. To create an ACS that sorts string values differently from the ASCII standard, your application must place 265 bytes directly into the Data Buffer, using the format displayed in Table 2-5.

For examples of user-defined ACSs, refer to the following topic in Pervasive PSQL Programmer's Guide: Alternate Collating Sequences .

International Sort Rules (ISRs). To specify an ISR, your application must place 265 bytes directly into the Data Buffer, using the following format:

Data Buffer Length

The Data Buffer Length must be long enough to include the file specifications, the key specifications, and any ACSs that have been defined. Do not specify the file's record length in this parameter.

For example, to create a file that has two keys of one segment each and an ACS, the Data Buffer for the Create operation should be at least 313 bytes long, as follows:

Key Number

The Create operation's Key Number parameter is used to determine if the transactional interface warns you when a file of the same name already exists, and also to determine whether the transactional interface should use a local or remote engine when creating the file.

Use Table 2-7 to determine which value you should use for the Key Number parameter.

---

Note
The Create operation makes no distinction between workstation, workgroup, and server engines when you specify that the local engine should create the file.

---

Delete and Rename Subfunctions for the Create Operation

The Create operation has two additional subfunctions that you can use to delete or rename files.

In releases prior to Pervasive.SQL v8.5, it was always possible to manipulate transactional interface files through the operating system, because the transactional interface depended on the rights and privileges given by the operating system to the Pervasive PSQL user.

Now, if you have a secure Pervasive PSQL database, these operating system access rights may have been removed in the process of securing the database from unauthorized access. This makes programmatically deleting or moving the files difficult since the rights required are not always available.

The rename and delete subfunctions are implemented as Create operations with alternate key numbers. You do not need to provide a file specification as you do when creating a new data file. The following table shows how you set up the Create operation to use the rename or delete subfunctions.

These subfunctions have been modified to work with the security model in that they will accept a URI in place of a file name in the key buffer and data buffer, if needed, to indicate a transactional interface file to delete or rename. This allows you to provide security information with the operation.

The security information is processed just like a normal Create or Open operation. The user must be authenticated and have DB_RIGHT_CREATE, DB_RIGHT_ALTER and DB_RIGHT_OPEN privileges for the existing files and for the directory where the new file will be located if applicable.

Notes on Rename and Delete Subfunctions

Result

If the Create operation is successful, the transactional interface either warns you of the existence of a file with the same name or creates the new file according to your specifications. The new file does not contain any records. The Create operation does not open the file. Your application must perform an Open operation before it can access the file.

If the Create operation is unsuccessful, the transactional interface returns one of the following status codes:

Positioning

The Create operation establishes no currency on the file.