Setting Up ODBC Database Access

This section covers the steps to set up access to a database. We will review some conceptual information before detailing the steps to perform the tasks of setting up Client and Engine (Server) DSNs and creating a new database using Pervasive.SQL V8.

This section covers the following topics:

Basic Concepts

What to Know if You are Recreating DSNs

Prerequisites to Setting up Database Access

Basic Concepts

ODBC Standard

Pervasive.SQL adheres to the Microsoft standard for ODBC database connections. According to the standard, applications must connect to databases through Data Source Names (DSNs) defined in the operating system.

Note
Pervasive.SQL does not support File DSNs. You must use User or System DSNs. System DSNs are generally preferred, because they are available to all users on a given computer.

Every Pervasive.SQL database that you expect to access using ODBC must have a DSN available on the same computer as the database engine, and (if applicable) another DSN on the client computer. The only exceptions are Pervasive tools, which can access remote databases without using DSNs on the client computer. A DSN that points to a database engine is called an Engine DSN. A DSN that points to an engine DSN is called a Client DSN.

Note
Pervasive.SQL databases that are accessed only through Btrieve do not need DSNs. However, in this case, the database is not visible in PCC nor can it be manipulated using PCC. Pervasive recommends using Data Dictionary Files (DDFs) and DSNs with all databases, including ones accessed only through Btrieve, to make them easier to manipulate.

Figures 2-6 shows possible DSN configurations.

Figure 2-6 Example DSN Configurations

Servers and Clients

Pervasive.SQL servers are also clients. The client components of Pervasive.SQL are installed with every Server engine or Workgroup engine. So you can use your server machine to connect to other servers as a client. Pervasive.SQL clients can connect to remote machines where a Pervasive.SQL Server engine is installed.

Data Source Names

The client-server architecture calls for the naming of each specific data set so that it can be referred to by a well-known name. There are generally three ways to create DSNs:

Create an Engine DSN from the server console.

Create an Engine DSN remotely from a client machine.

Note
On NetWare, the only way to create an Engine DSN is to create it remotely from the client.

Create a Client DSN on each client machine.
While Pervasive tools can access remote databases without a client DSN present on the client machine, ODBC-based applications such as Microsoft Excel and Microsoft Access cannot do so. You must create a client DSN on each client computer that needs to access network databases from local applications.

Components of a Database Name

There are two components of a database name. Pervasive.SQL uses an internal Database Name (DBNAME) to identify the location of the Data Dictionary Files (DDFs) and the data files for each database. An ODBC Data Source Name (DSN) entry refers to one DBNAME.

You may set up more than one DSN that refers to the same DBNAME. If the physical location of the data files on the server is changed, only the DBNAME needs to be updated. All DSNs remain unchanged.

What to Know if You are Recreating DSNs

Btrieve application users

Applications that only use Btrieve do not require you to follow the procedures in this section. However, you must follow these procedures to access Pervasive.SQL through ODBC. This includes the SQL Data Manager utility and all Microsoft applications. If you are not sure whether your application uses ODBC, check with your network administrator.

Pervasive.SQL v7 users

You must recreate all DSNs created with Pervasive.SQL 7 to access them in Pervasive.SQL V8. However, you do not need to rename existing database names. To re-create DSNs, follow the instructions provides in Deleting DSNs .

Prerequisites to Setting up Database Access

Before you begin, verify the following:

The data files and DDFs already exist. If these do not exist, ask your network or systems administrator to create them, or refer to Advanced Operations Guide for information on creating a database.

If the data files are located on a remote server, you must know the name of the server. You must also know the location of the data files on the server. If the database already has a DBNAME, this information can be used instead of the location of the data files. If you do not have this information, see your network or systems administrator.

If the data files are located on a remote server and an Engine DSN has not been created on the server, you must possess administrator rights on the remote machine that houses the database you wish to access, to set up an Engine DSN. Setting up local access requires OS system rights to create a System DSN.

The sections that follow allow you to set up database access as long as the conditions above are met. The following topics are provided:

Setting Up Database Access on Windows

Setting Up Database Access on a NetWare Server

Setting Up Database Access on a Linux Server

Setting Up Client Access