Setting Up ODBC Database Access

This section covers the steps to set up ODBC access to a database. The section reviews some conceptual information before detailing the steps to perform the tasks of setting up Client and Engine (Server) Data Source Names (DSNs) and creating a new database using Pervasive.SQL 9 SP1 (9.1).

This section covers the following topics:

Basic Concepts

Applications Using the Transactional Interface

Setting up Database Access with PCC

Note
The Pervasive.SQL Java utilities do not require client or engine DSNs. The Pervasive.SQL Control Center, for example, uses JDBC not ODBC. DSNs are required only if your application uses ODBC to access the database.

Basic Concepts

ODBC Standard

Pervasive.SQL adheres to the Microsoft standard for ODBC database connections. According to the standard, applications using ODBC 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 an ODBC application must have a DSN available on the same computer as the database engine, and (if applicable) another DSN 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) with all databases, including ones accessed only through Btrieve, to make them easier to manipulate.

Figures 2-4 shows possible DSN configurations.

Figure 2-4 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 ODBC 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 ODBC applications.

Internal Database Name

The method used by Pervasive.SQL to identify a database is an internal Database Name (DBNAME). If you are using ODBC to access the database, you need to create a Data Source Name (DSN) entry that 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.

Applications Using the Transactional Interface

Applications that use only the transactional interface do not require DSNs. Also, the Pervasive.SQL Java utilities do not require DSNs. The Pervasive.SQL Control Center, for example, uses JDBC not ODBC.

Pervasive.SQL v7 users

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