| Prev | Advanced Operations Guide | Next |
Pervasive.SQL Database Concepts
- Named Database
- Identifiers and Object Names
- The Default Database and the Current Database
- ODBC DSN Creation Options
- ODBC Connection Strings
Named Database
A named database (also referred to as a DBname) is a database that has a logical name that allows users to identify it without knowing its actual location. Pervasive.SQL requires that all databases be named. When you name a database, you associate that name with a particular dictionary directory path and one or more data file paths. Database names are typically used internally by Pervasive.SQL, and most users refer to the database by ODBC Data Source Name (DSN) that points to it.
For ODBC access, you must set up a DSN to refer to the database name. Multiple DSNs may point to the same named database.
Note
To work with named databases, you must log into the computer where the database engine is located, using an operating system user name that has administrator-level privileges or is a member of the Pervasive_Admin security group.
Identifiers and Object Names
An identifier is the name of a column, table, procedure, cursor, or other named object within the database. A regular identifier is an identifier that is not surrounded by double quotes. A delimited identifier is an identifier surrounded by double quotes.
Regular and Delimited
A regular identifier must begin with a letter, either upper or lower case. The remainder of the identifier can consist of any combination of upper or lower case letters, digits, and the underscore character ("_"). You cannot use a reserved word as a regular identifier. Regular identifiers are case-insensitive, so that the identifiers Abc, ABC, and abc are identical and can be used interchangeably.
A delimited identifier can consist of any string enclosed in double quotes. While it is not recommended, reserved words can be used as delimited identifiers. For example, INSERT is not permitted as a regular identifier, but "INSERT" is permitted as a delimited identifier. If you need to represent a double-quote within a delimited identifier, use a pair of double quotes without a space between them.
Maximum Length
Almost all identifiers are limited in length. For delimited identifiers, the size limit includes the opening and closing quotations.
Identifier Examples
The following identifiers are permitted:
"INSERT" myInsert "my Insert" "3tableA" Table56 "Table 56 & (*#)" Long_table_name_1234 [20 characters] "Long_table_name123" [20 characters including quotes]The following identifiers are not permitted:
INSERT [reserved word requires quotes] my Insert [spaces require quotes] 3tableA [regular identifier must start with a letter] Table56(*#) [regular identifier contains only alpha-numeric characters] Long_table_name_12345 [21 characters] "Long_table_name1234" [21 characters including quotes]Unique Scope
Identifiers generally must be unique within a certain scope. That is, instances of the same type of object using the same name cannot be used within the same arena. Table 1-2 shows the arena, or the scope, within which a given object name must be unique.
The Default Database and the Current Database
In order to support existing applications that do not specify a database name when creating or opening Btrieve files, this release maintains the concept of a default database for each transactional database engine. The default database is a pre-defined database named "DefaultDB." To make use of the new security models without having to modify your application code, you can associate your Btrieve data directories with the default database, then set up users and privileges in the default database to control access to the data files in those directories.
This release also introduces the concept of a current database for each client connection. If no database name is specified in a Btrieve Login (78), Create (14), or Open (0) operation, the transactional engine assumes the operation is associated with the current database. For each client, the current database is the database to which the most recent Login (78) operation occurred (explicit login). If the client computer has requested no explicit login operations, the current database is the database to which the most recent Create (14) or Open (0) operation occurred (implicit login). If no explicit or implicit logins have occurred, then the current database is the default database, described in the preceding paragraph. Note that the current database may change at any time when the given client performs an implicit or explicit login, or closes the last file handle, making "DefaultDB" the current database. The current database for each client is independent of other clients' activities.
The simplest way to configure the new security model for existing applications is to associate all Btrieve data directories with the default database, and set up rights for the group
PUBLICwithin this database. The group PUBLIC is automatically created along with the Master user when you enable security for a database.File Structure
All Pervasive.SQL databases use a common data format. This commonality allows different access methods, such as transactional and relational, to access the same data. The database management system through which all access methods operate is called the MicroKernel Database Engine (MKDE).
Each Pervasive.SQL database table is a separate file with a default file extension of
MKD. Developers, however, can specify any file name extension desired. A MicroKernel file may contain both data and indexes, and is organized into various types of pages. A MicroKernel files contains data in the common data format.Each Pervasive.SQL database also contains a set of data dictionary files, with a file extension of
DDF. The DDF files contain the schema of the database. Each database has, at a minimum, three DDF files:FILE.DDF,FIELD.DDF, andINDEX.DDF.(The MKDE is completely unconcerned with the schema of the data apart from the key fields. However, the provision for referential integrity or access via SQL requires knowledge of the schema.)
The names and locations of Pervasive.SQL databases are contained in a binary file named
dbnames.cfg. On Windows-based systems, this file is located in the%WINSYSDIR%(Windows 98) or%WINDIR%(Windows NT/2000/XP) directory. The file is located atsys:system\for Netware. On Linux systems, the file is located at/usr/local/psql/etc/. These locations represent the installation defaults.All of the files associated with a Pervasive.SQL database can be viewed from the operating system. Table 1-3 summarizes the associated files.
Access Methods
The two primary methods in which data is accessed from Pervasive.SQL databases are transactional and relational.
With transactional, an application program navigates up and down along either physical or logical pathways through data records. Using a transactional API, an application program provides direct control and allows a developer to optimize data access based on knowledge of the underlying structure of the data. Btrieve is an example of a transactional database engine.
Relational is an access method in which data is represented as collections of tables, rows, and columns. The relational model insulates the developer from the underlying data structure and presents the data in a simple table format. ODBC is an example of a relational access method.
A single application program may include both types of access. For example, an application may use transactional access for adding and changing data, and relational access for querying the data and report writing.
You need to know the access method(s) used by the application programs that rely on your installation of Pervasive.SQL. The access methods may have different configurations. You may need to customize the configuration to optimize a particular access method.
Also, troubleshooting is easier when you are aware of the access method(s) used by a given application program. For example, if an application program uses relational access through ODBC, you may need to troubleshoot a problem at the ODBC level rather than at the database management system.
See Chapters 4 and 5 for the tasks and references pertaining to customizing configurations.
Client/Server Communications
The MKDE supports two types of processing modes, stand-alone (also called Workgroup) and client/server. An application accessing the database in stand-alone mode accesses a local copy of the MKDE. The local MKDE calls upon the operating system of the workstation which performs the I/O on a local or networked hard disk.
Client/server mode uses a client/server MKDE executing on a shared file server. When an application program accesses the database engine in client/server mode, a communication program called a requester is called upon instead of the local MKDE. This requester passes transactional-level requests and data records between the application program and the client/server MKDE using the network protocol supported by the operating system. File I/O functions are completely bypassed in client/server mode and the workstation has no operating system handles allocated to shared data files. Database manipulation is performed by the server-based MKDE on behalf of each workstation.
Note that the processing mode is determined by the configuration of the workstation and not the application program itself. This means that an application is capable of accessing both Workgroup and client/server database engines. The application program does not have to be recompiled to switch the application to client/server mode from Workgroup mode.
Both Workgroup and Server engine can operate in either mode. When an application on the same computer as the database engine accesses the engine, it is operating in local mode. When an application on a different machine access the engine, it is operating in client/server mode.
The client/server configurations may be customized for the Workgroup and Server versions of Pervasive.SQL. A Configuration utility exists in the Pervasive Control Center (PCC) to facilitate the configuration of client/server configurations as well as stand-alone configurations.
See Chapters 4 and 5 for the tasks pertaining to configuring the client/server communications and database engine.
ODBC DSN Creation Options
This section explains in detail the driver options that can be configured for Pervasive.SQL DSNs.
Engine DSN Open Mode Options
The DSN Open Mode options available for Engine DSNs allow you to specify one of several characteristics that go into effect when tables are opened through the specified DSN. These options are mutually exclusive-you are not permitted to select more than one.
These options correspond directly to the Btrieve open modes allowed in the Open (0) operation. By setting an Open Mode for a DSN, you are setting the default behavior for tables (corresponding to Btrieve files) opened through that DSN.
Normal
Normal mode is the default. Opening a table in Normal mode allows read/write access according to the permissions defined in the database.
If this mode is selected, the ODBC connection string includes OPENMODE=0, and the following ODBC function call is executed when you connect to the database:
SQLSetConnectOption(pSubDbc, SQL_ACCESS_MODE, SQL_MODE_READ_WRITE);Accelerated
Opening a table in Accelerated mode provides increased insert/update performance by disabling database engine logging functions for the current user. Logging is not affected for other users accessing the same table, but please see the warning below.
Caution
The database engine cannot guarantee transaction atomicity, transaction durability, or archival log safety for any client during use of Accelerated mode by any client. The reason for this restriction is that in the event a restore from log is needed, the log may not contain adequate information to complete the restore, because it is only a partial record of operations on a data file.
For example, if a system failure occurs while the same file is being accessed by a client performing inserts using Accelerated mode and a client performing updates using Normal mode, it is possible for the transaction log to contain updates to records that do not yet exist in the data files, because the Accelerated insert operation in memory was never flushed to disk, while the transactional update operation was written to the transaction log.
An attempt to roll forward an archival log containing this combination of operations will fail.
When this mode is selected, the ODBC connection string includes OPENMODE=-1, and the SQLSetConnectOption call is ignored by the ODBC driver. You cannot use SQLSetConnectOption to specify this mode.
Read-only
When a table is opened in read-only mode, operations that modify the database structure or the data in the database are not permitted.
If this mode is selected, the ODBC connection string includes OPENMODE=1, and the following ODBC function call is executed when you connect to the database:
SQLSetConnectOption(pSubDbc, SQL_ACCESS_MODE, SQL_MODE_READ_ONLY);Exclusive
When a table is opened in exclusive mode, no other connections to the table are permitted. If other users are currently accessing the given table, it cannot be opened in Exclusive mode. You must try again later.
When this mode is selected, the ODBC connection string includes OPENMODE=-4, and the SQLSetConnectOption call is ignored by the ODBC driver. You cannot use SQLSetConnectOption to specify this mode.
Client DSN Options
In the Pervasive Client DSN Setup window, the following options can be modified if you click Options.
Enable Array Fetch
An array fetch is a memory cache on the client machine for result sets. When array fetch is enabled, data from the latest result set is cached to the client machine's local memory, thereby speeding performance on subsequent queries. We recommend you leave array fetch "On," if you will be doing multiple queries.
The default size of the buffer used to cache array fetches is 8KB. You can set it anywhere between 1 and 64KB.
TCP/IP Port Number
You can use this setting to change the network port number on which Pervasive.SQL transmits ODBC communications. The network layer on the server engine has a similar setting, described in TCP/IP Port (Server engines only) . You must change both settings at the same time, and you must change them both to the same port number, or else your client and server cannot communicate.
Do not change the client port number unless you also change the corresponding port number on the server. If the server and client are not using the same port number, they cannot communicate.
Generally, the only reason you would need to change this port number is if you have another network service that is already using this port, and it is easier to change the port number for your Pervasive.SQL applications than for the other application.
The Btrieve interface communicates over port 3351. This value is not configurable.
Use OEM/ANSI Conversion
This setting allows applications to store or retrieve character data in any 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.
The Pervasive Control Center (PCC) and the SQL Data Manager (SQLDM) 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.SQL is W32BTXLT.DLL.
NOTE: The OEM to ANSI translation option is available only for client or local Engine DSNs. It is not available when setting up a remote Engine DSN.
ODBC Connection Strings
This section describes the ODBC connection strings supported by Pervasive.SQL. This information is provided for two audiences:
- advanced users using a database access tool that allows connection strings to be specified (such as ODBC Test)
- developers writing ODBC, ASP, or OLE DB applications to access Pervasive.SQL.
Users of Pervasive.SQL 7 should note that the connection string parameters have changed significantly between Pervasive.SQL 7 and newer versions.
Driver Name
If you wish to connect to a database engine that is running on the same computer as the program that is attempting to connect, use the following driver connection string:
Driver={Pervasive ODBC Engine Interface}If you wish to connect to a remote database engine using the Pervasive.SQL client, use the following driver connection string:
Driver={Pervasive ODBC Client Interface}Other Parameters
The tables below shows the other parameters that may be used. Engine connection strings may be used for both connections to a local database engine and to a remote engine. Client connection strings may only be used for connections to a remote Server engine.
Table 1-5 Engine Connection Strings
path_and_DLL_nameExamples
This example shows how to connect to a database with the internal name "SOMEDATA" on a remote server named "ServerMain" using TCP/IP port 1590, trying TCP/IP first then SPX:
Driver={Pervasive ODBC Client Interface}; ServerName=ServerMain.1590;DBQ=SOMEDATA; TransportHint=TCP:SPX;This example shows how to connect to an Engine DSN named "mydata" on a remote server named "MyServer" with database security turned on:
Driver={Pervasive ODBC Client Interface}; ServerName=MyServer;ServerDSN=mydata;UID=ajones;PWD=jones52;This example shows how to connect to a local database with the internal name "DATA5":
Driver={Pervasive ODBC Engine Interface};DBQ=DATA5;See Also
For further information on Engine DSN open modes, see Engine DSN Open Mode Options .
For further information on Client connection string options, see Client DSN Options .
| Prev Pervasive.SQL Databases |
Contents Up Check for Revisions | Next Pervasive.SQL Database GUI Reference |