Setting Up ODBC Database Access

Setting Up ODBC Database Access

This section reviews some conceptual information on setting up ODBC access to your database.

Topics covered include the following basic concepts:

•

•

Servers and Clients

•

Data Source Names

•

Internal Database Name

•

Applications Using the MicroKernel Engine

Note: The PSQL Java utilities do not require DSNs. The PSQL Control Center, for example, uses JDBC not ODBC. DSNs are required only if your application uses ODBC to access the database.

PSQL 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: PSQL 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 PSQL database that you expect to access using an ODBC application must have a DSN that identifies the database. A DSN that points to the PSQL database engine is called an Engine DSN. Engine DSNs are 32-bit only and are deprecated. A 32-bit DSN that points to an Engine DSN or to a named database is called a Client DSN. A DSN on a 64-bit operating system is simply referred to as a 64-bit DSN, without designation of Engine or Client.

New or revised 32-bit applications, local or remote, should connect to a named database instead of using Engine DSNs. Alternately, applications could use DSN-less connections by specifying the PSQL ODBC driver name (see PSQL ODBC Driver Names in ODBC Guide). Avoiding the use of Engine DSNs positions your application for the future when Engine DSNs will no longer be supported in PSQL.

Windows 64-bit operating systems contain two different executable files for ODBC Administrator, one for 32-bit DSNs and one for 64-bit DSNs. Each ODBC Administrator lists the system DSNs that only match its bitness. For the 64-bit PSQL Server or Workgroup, PCC contains separate options in the Tools menu to start the 32-bit or the 64-bit ODBC Administrator. If an ODBC Administrator is already open, Windows defaults to it. That is, if the 32-bit ODBC Administrator is open and you attempt to start the 64-bit one, Windows displays the 32-bit version (and vice versa). This is a limitation of the Windows operating system, not PSQL.

Figures 4 shows possible DSN configurations.

Figure 4 Example DSN Configurations

Servers and Clients

PSQL servers are also clients. The client components of PSQL are installed with every Server engine or Workgroup engine. So you can use your server machine to connect to other servers as a client. PSQL clients can connect to remote machines where a PSQL 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:

1

Create a DSN from the server console.

2

Create a DSN remotely from a client machine.

3

Create a Client DSN on each client machine.
While PSQL 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 PSQL 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 MicroKernel Engine

PSQL databases that are accessed only through the MicroKernel Engine do not need DSNs. However, the database tables are not visible in PCC nor can they be manipulated using PCC. To view table data with PCC in readable form, the table must be defined in the data dictionary files (DDFs). See DDF Builder User’s Guide.

Also, databases created with the PSQL Java utilities do not require DSNs. The PSQL Control Center, for example, uses JDBC not ODBC.