PSQL DSN Setup

This dialog is available via the ODBC Administrator and allows you to set up a DSN using any of the following PSQL ODBC interfaces.

•

Pervasive ODBC Unicode Interface
With the 32-bit ODBC Administrator, creates 32-bit DSNs. With the 64-bit ODBC Administrator, creates 64-bit DSNs.

•

Pervasive ODBC Client Interface
For 32-bit DSNs

•

Pervasive ODBC Interface
For 64-bit DSNs

ODBC Administrator

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. That is, the 64-bit ODBC Administrator lists 64-bit system DSNs, and vice versa. If you start ODBC Administrator from the Windows Control Panel, the 64-bit version is run.

PSQL Control Center (PCC) contains separate options in the Tools menu to start the 32-bit or the 64-bit ODBC Administrator. Note that, 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). In other words, only one version of the ODBC Administrator runs at a time. This is a limitation of the Windows operating system, not PSQL.

Data Source Name

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.

Type a name (called a data source name, or DSN) for the data source to which you wish to set up a connection. This DSN will help you identify the data source.

For additional information about using DSNs with the database engine, see ODBC Database Access.

Type a description of the DSN, if desired, to help identify the DSN, database, or application.

Specifies the machine on which the database engine is running. Type a machine name or IP address of the server machine to which you want the client to connect.

Specify the transport protocol to use, or which to try first. Default is TCP:SPX. For example, a value of “TCP” forces the client to try a TCP/IP connection only. A value of “SPX:TCP” forces the client to try an SPX connection first, and if that fails, to try a TCP/IP connection.

Click Database Name, click Get List, then select in the list the database to which you wish to connect. The list returns the databases on the server specified for Server Name/IP.

Optionally you may create a new database by clicking Create.

Database Configuration Details

See Create Database Through DSN Setup for the following database configuration details:

•

Dictionary Location

•

Data File Location

•

Integrity Enforced

•

This option appears only on the 32-bit Client DSN dialog. It is not present on any of the other PSQL driver DSN dialogs.

Click Engine DSN, click Get List, then select in the list the Engine DSN to which you want the client to connect. The list returns the Engine DSNs on the server specified for Server Name/IP.

Optionally, you may create a new Engine DSN by clicking Create, or modify an existing Engine DSN by clicking Modify.

See also PSQL Engine DSN Setup.

Note: New or revised 32-bit applications, local or remote, should connect to a named database, not to an Engine DSN. Alternately, applications could use DSN-less connections (see DSN-less Connections). Avoiding the use of Engine DSNs positions your application for the future when Engine DSNs will no longer be supported in PSQL.

Advanced Connection Attributes

The following connection attributes apply to 32-bit Client DSNs, 64-bit DSNs, and Unicode DSNs.

•

Enable Array Fetch

•

TCP/IP Port Number

•

Encoding Translation

(For connection attributes that apply to Engine DSNs, see Engine DSN Advanced Connection Attributes.)

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 64 KB.

TCP/IP Port Number

You can use this setting to change the network port number on which PSQL transmits ODBC communications. The network layer on the server engine has a similar setting. 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.

Caution: 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. See TCP/IP Port in Advanced Operations Guide.

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 PSQL applications than for the other application.

For additional information about ports, see Changing the Default Communication Ports in Getting Started With PSQL.

Encoding Translation

Encoding translation refers to the operation of converting the encoding of character data from that present in the database to the encoding present at the client and vice versa. This allows a client to read and write text from and to the database, under certain conditions, even if the database and client are using different encodings. Obviously, if the two encodings are the same, no translation is needed. The effectiveness of the translation is dependent on the amount of overlap between the character sets on the client and server, i.e., the greater number of characters they have in common. Characters that cannot be translated are replaced by question marks (“?”). For example, if the database uses OEM code page 850 and the client uses ANSI code page 1252, the letters will translate, but some graphics symbols will not.

The database connection string or DSN can be configured to automatically negotiate the translation, perform OEM/ANSI translation between different code page encodings, or disable any translation. Automatic translation is the default when using the Unicode driver; no translation is the default for all other PSQL ODBC drivers. You can specify automatic translation in the DSN setup, or in the ODBC connection string with the PvTranslate attribute.

The following table summarizes the operation of text encoding translation for various combinations of client and driver encodings. Your application may be either ANSI or Unicode, indicated in column one. The PSQL driver is either the Client driver (Client 32-bit or 64-bit driver) or the Unicode driver, column two. (The client and Unicode drivers are described in ODBC Database Access, above.) The Microsoft ODBC Driver Manager connects your application to the PSQL ODBC driver and may perform text conversions, as indicated in column three. The three remaining columns describe the PSQL driver text handling for a given encoding configuration (column four) for SQL text or CHAR user data (columns five and six, respectively). When data is retrieved from the database, the translation is reversed. Following the table are descriptions of these configuration options.

Table 11

Microsoft Driver Manager
Text Handlling

Translation Configuration in DSN or Connection String

PSQL Driver
SQL Text Handling

PSQL Driver
CHAR Data Handling

Client encoding to OEM

Client encoding to OEM

Client encoding to database encoding

Client encoding to database encoding

Client encoding to UCS-2 for SQL text

Client encoding to database encoding

UCS-2 to client encoding

UCS-2 to client encoding

Client encoding to OEM

Client encoding to OEM

UCS-2 to client encoding

Client encoding to database encoding

Client encoding to database encoding

UCS-2 to database encoding

Note: When using the PSQL Client driver, Unicode SQL text is always converted to the client encoding by the Microsoft Driver Manager. This restricts NCHAR literals in a SQL query string to the character set of the client. To preserve NCHAR literals in SQL query text, use the PSQL Unicode driver.

DSN Encoding Translation Options

The encoding translation options specify how character data is translated between the PSQL database engine and a PSQL client application that uses ODBC. This option is only available when configuring a Client 32-bit or 64-bit DSN. The Unicode DSN defaults to automatic.

This setting instructs the PSQL ODBC client to automatically translate character data encoding when the database encoding on the engine machine differs from the OS encoding on the client machine. Automatic is the default for the Unicode driver.

Character data translation, if required, occurs on the client. (No character data translation is required if the database encoding on the engine machine is the same as the OS encoding on the client machine.)

“Automatic” requires that the client and the server be version 10.1 or greater.

See also Database Code Page and Client Encoding in Advanced Operations Guide.

This setting means that no character data is translated between the client and server. (The assumption is that the client and server use the same operating system encoding.)

Note that, in versions prior to PSQL v10.10, “OEM/ANSI Conversion” was a single choice and had two states: not selected or selected. The not selected state is now labeled “None” and is the default for PSQL ODBC drivers other than the Unicode driver.

OEM/ANSI Conversion

This setting allows applications to store or retrieve character data in any OEM character set in the PSQL engine, while allowing the data to be manipulated and displayed using the ANSI Windows character set in the application.

The PSQL 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. Any character data that is passed to and from the database is correctly translated by the ODBC driver 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 PSQL is W32BTXLT.DLL.

Note The OEM-to-ANSI translation option is available only for Client and 64-bit DSNs.

(You can also use this translate option with a local Engine DSN. It is not available when setting up a remote Client connection to an Engine DSN. Keep in mind that Engine DSNs are deprecated and should not be used for new applications.)

Interaction Between Database Code Page and Encoding Translation

The following table explains the interaction between database code page and DSN encoding translation. See Create Database Through DSN Setup for a discussion of code page.

Table 12

Database Code Page and DSN Setting for Encoding Translation

If Database Code Page Is

And the Connection Encoding Translation Is

Then the PSQL ODBC Driver

(The equivalent default behavior in versions prior to PSQL v10.10.)

Performs no translation of data or metadata. The assumption is that the OS encoding on the server matches the OS encoding on the client.

For compatible data interpretation, the encoding used by the client machine must match the encoding of the data and metadata in the database.

A specific code page

(The equivalent default behavior in versions prior to PSQL v10.10.)

Performs no translation of data or metadata. The assumption is that the OS encoding on the server matches the OS encoding on the client.

For compatible data interpretation, the encoding used by the client machine must match the encoding of the data and metadata in the database.

A specific code page

Ignores database code page and translates data and metadata from the OEM encoding of the database to ANSI Windows encoding for the client application.

Translates data and metadata from the default OS encoding on the server to the OS encoding on the client.

A specific code page

Translates data and metadata from the database code page to the OS encoding on the client.