ODBC Connection Strings

ODBC Connection Strings

This section describes the ODBC connection strings supported by Pervasive PSQL. This information is provided for advanced users using a database access tool that allows connection strings to be specified and for developers writing ODBC or OLE DB applications to access Pervasive PSQL.

Pervasive PSQL ODBC Driver Names

The following table lists the Pervasive PSQL ODBC drivers.

Table 103

Pervasive PSQL ODBC Drivers

Pervasive ODBC Engine Interface

•

Setup creates 32-bit Engine DSNs

•

Connects to a local named database

•

For use by 32-bit applications

•

Pervasive ODBC Client Interface

•

Setup creates 32-bit Client DSNs

•

Connects to a local or remote named database or an Engine DSN

•

Interface GUI lists both named databases and Engine DSNs

•

For use by 32-bit applications

Pervasive ODBC Interface

•

Setup creates 64-bit DSNs

•

Connects to a local or remote named database

•

For use by 64-bit applications

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 “Pervasive ODBC Client Interface.” Avoiding the use of Engine DSNs positions your application for the future when Engine DSNs will no longer be supported in Pervasive PSQL.

Use the ODBC Driver parameter to connect to the Pervasive PSQL database engine. For example:

Driver={Pervasive ODBC Engine Interface}

Driver={Pervasive ODBC Client Interface}

Driver={Pervasive ODBC Interface}

Other Parameters

The tables below shows the other parameters that may be used.

Table 104

32-bit Engine DSN Connection Strings

Connection String

DBQ=[@]db_name

Specify the internal database name (not DSN) to which you wish to connect. Required.

The @ character is optional. It has no particular effect and is supported only for backward compatibility.

UID=user_name

If security is enabled for the given database, specify the user name. Optional, depending on security.

PWD=password

If security is enabled for the given database, specify the password. Optional, depending on security.

OPENMODE=-4 | -1 | 0 | 1

Specify the default file open mode for files opened with the current connection. Default is 0, “Normal.” Can be used only with local connections, not remote client connections. Optional.

For more information on file open modes, see DSN Open Mode.

TRANSLATIONDLL=path_and_DLL_name

Specify the full path name of the DLL to use for OEM/ANSI translation. For more information, see OEM/ANSI Conversion.

Table 105

32-bit Client DSN Connection Strings

Connection String

ServerName=server[.port]

Specify the machine name or IP address of the computer to which you wish to connect. If you are not using the default port, specify the port number to use. Required. When using IPv6 addresses and appending a port number in ODBC connections, use an IPv6-literal.net name or UNC-safe name. See Drive-based Formats in Getting Started With Pervasive PSQL.

ServerDSN=dsn_name

Specify the Engine DSN to which you wish to connect. Required, unless DBQ is specified.

TransportHint=<TCP | SPX>[:SPX | TCP]

Specify the transport protocol to use, or which to try first. Default is TCP:SPX. Optional.

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.

DBQ=[@]db_name

Specify the internal database name (not DSN) to which you wish to connect. Required.

The @ character is optional. It has no particular effect and is supported only for backward compatibility.

Specify the TCP/IP port on which to find the server. Optional. See also Changing the Default Communication Ports in Getting Started With Pervasive PSQL (Server Edition).

ArrayFetchOn=1 | 0

Specify whether to cache result sets on the client. Default is 1, “On.” Optional.

ArrayBufferSize=size

Specify the size of the client cache, in KB. Default is 8 KB. Optional.

PvTranslate=auto

PvTranslate is absent or empty

ODBC uses the connection string attribute PvTranslate to specify the data encoding when the client connects to the database engine. The attribute can either be absent or empty, or have a value set to “auto” (for “automatic”).

If the attribute is absent or empty, ODBC does not translate any character data. This is the legacy behavior. The “OEM/ANSI” setting may still apply. See OEM/ANSI Conversion.

If the attribute is set to “auto,” the client and server automatically establish compatible encoding. Data translation, if required, occurs on the client. Note that “auto” overrides the “OEM/ANSI” setting in a DSN. PvTranslate defaults to “auto” if the local DSN is configured for Automatic. See Automatic.

The PvTranslate attribute can be included with the ODBC function SQLDriverConnect (see Example D) or with SQLConnect (through the DSN setting for automatic encoding).

Table 106

64-bit DSN Connection Strings

Connection String

ServerName=server[.port]

Specify the machine name or IP address of the computer to which you wish to connect. If you are not using the default port, specify the port number to use. Required. When using IPv6 addresses and appending a port number in ODBC connections, use an IPv6-literal.net name or UNC-safe name. See Drive-based Formats in Getting Started With Pervasive PSQL.

TransportHint=<TCP | SPX>[:SPX | TCP]

Specify the transport protocol to use, or which to try first. Default is TCP:SPX. Optional.

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.

DBQ=[@]db_name

Specify the internal database name (not DSN) to which you wish to connect. Required.

The @ character is optional. It has no particular effect and is supported only for backward compatibility.

Specify the TCP/IP port on which to find the server. Optional. See also Changing the Default Communication Ports in Getting Started With Pervasive PSQL (Server Edition).

ArrayFetchOn=1 | 0

Specify whether to cache result sets on the client. Default is 1, “On.” Optional.

ArrayBufferSize=size

Specify the size of the client cache, in KB. Default is 8 KB. Optional.

PvTranslate=auto

PvTranslate is absent or empty

ODBC uses the connection string attribute PvTranslate to specify the data encoding when the client connects to the database engine. The attribute can either be absent or empty, or have a value set to “auto” (for “automatic”).

If the attribute is absent or empty, ODBC does not translate any character data. This is the legacy behavior. The “OEM/ANSI” setting may still apply. See OEM/ANSI Conversion.

If the attribute is set to “auto,” the client and server automatically establish compatible encoding. Data translation, if required, occurs on the client. Note that “auto” overrides the “OEM/ANSI” setting in a DSN. PvTranslate defaults to “auto” if the local DSN is configured for Automatic. See Automatic.

The PvTranslate attribute can be included with the ODBC function SQLDriverConnect (see Example D) or with SQLConnect (through the DSN setting for automatic encoding).

Metadata version is not a parameter in the connection strings. The metadata version is implicitly declared based on the name of the database to which you are connecting. See Versions of Metadata in SQL Engine Reference.

This example shows how to connect to a database on a 32-bit operating system 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;

An ODBC client application uses SQLDriverConnect to connect to a server DSN named “mydata” on a remote server named “MyServer” and establishes automatic encoding support:

Driver={Pervasive ODBC Client Interface}; ServerName=MyServer;ServerDSN=mydata;PvTranslate=auto;

Note that the PvTranslate attribute is explicitly stated because a server (an Engine) DSN is specified.

This example shows how to connect to a local database on a 64-bit operating system with the named database “acctdomestic”:

Driver={Pervasive ODBC Interface};DBQ=acctdomestic;