Command Line Interface Utilities : dsnadd
 
dsnadd
Description
Dsnadd simplifies the setup of a new ODBC data source to connect to a PSQL database. It modifies the odbc.ini file by providing the appropriate properties for the new data source.
PSQL follows the UNIXODBC standard by using the odbcinst.ini file in /usr/local/psql/etc to specify a 32-bit and a 64-bit ODBC driver. DSNs that reference the “Pervasive ODBC Interface” driver description point to the odbcinst.ini information from the odbc.ini file. A single DSN can be used by both a 32- and a 64-bit application. See also Notes.
Optionally, dsnadd provides options that let you create legacy-style DSNs that specify a 32-bit driver name in odbc.ini rather than pointing to odbcinst.ini. However, such DSNs are not accessible to 64-bit applications.
Synopsis
•To create a DSN on the server that connects to a named database:
dsnadd -dsn=myDSN -db=DBname
This creates a DSN with the description “Pervasive ODBC Interface” that can be used by both a 32- and a 64-bit application on the server. The DSN is not visible as an “engine DSN” in ODBC Administrator running on a Windows client.
•To add a DSN on a client that connects to a named database on a server:
dsnadd -dsn=myDSN -db=DBname -host=psqlhost
This creates a DSN with the description “Pervasive ODBC Interface” that can be used by both a 32-bit application and a 64-bit application on the client.
•To add a deprecated, legacy-style Client DSN with the description “Pervasive ODBC Client Interface,” execute the following command:
dsnadd -dsn=myDSN -sdsn=engineDSN -host=psqlhost -clntdsn
Note that the DSN is accessible only to 32-bit applications.
•To add a deprecated, legacy-style engine DSN on the server with the description “Pervasive ODBC Engine Interface:”
dsnadd -dsn=myDSN -db=DBname -engdsn
Note that the DSN is accessible only to 32-bit applications.
•To list existing DSNs:
dsnadd -l
Where:
myDSN is a name you want to assign to the new data source.
DBname is the name of the named database on the PSQL host.
psqlhost is the name of the host where your PSQL Server is installed.
engineDSN is the name of the engine DSN on the PSQL host.
Options
 
The following options are the most commonly used:
-db=
Name of the database (local or remote) to which the DSN is associated
-dsn-name= | -dsn=
The data source name
-help
Display syntax and options help for the dsnadd tool
-l
List existing DSNs
openmode=<0|1|-1|-4> | -omode=<-0|1|-1|-4>
Specify the default file open mode for files opened with the current connection. The default is 0, or “Normal.” Can be used only with local connections, not remote client connections. For more information on file open modes, see DSN Open Mode in ODBC Guide.
-srv-host= | -host=
Server host name
-srv-port= | -port=
Server port number. The default is 1583. (See also Changing the Default Communication Ports in Getting Started with PSQL.)
-translate=< none | auto >
Encoding translation to use for character data. The default is “none,” meaning that no character data is translated between the client and server on the assumption that the client and server use the same operating system encoding.
See Automatic in ODBC Guide.
The following options are used to create deprecated, legacy-style DSNs:
-clntdsn
Create a deprecated, legacy-style Client DSN with the driver description “Pervasive ODBC Client Interface.”
Rather than using Client DSNs, new applications or revised 32-bit applications should create a DSN that connects to a named database using -db=.
-engdsn
Create a deprecated, legacy-style Engine DSN with the driver description “Pervasive ODBC Engine Interface.”
Rather than using Engine DSNs, new applications or revised 32-bit applications should create a DSN that connects to a named database using -db=.
-sdsn=
For use with deprecated, legacy-style Client DSNs. Name of an Engine DSN on the PSQL Server.
The following options are typically used only for application development and testing:
-dsn-desc= | -desc=
dsnadd provides a default DNS description in odbc.ini. If you want to override the default description, use this option to specify a descriptive string of your choosing. If the descriptive string contains the space character, you must quote the entire string with double quotes.
-drv-desc=
dsnadd handles the driver descriptions based on the absence or presence of the -db, -clntdsn and -engdsn options. If specified, must be one of the following:
•Pervasive ODBC Interface
•Pervasive ODBC Client Interface
•Pervasive ODBC Engine Interface
-drv-path= | -drv=
The path where the driver libraries are located. The default is /usr/local/psql/lib:$HOME/lib.
-odbc-ini= | -ini=
ODBC.ini file name (for example, /usr/local/psql/etc/odbc.ini)
Examples
The following example creates a server-side DSN named acctingdb that connects to a local database named region1accting.
dsnadd -dsn=acctingdb -db=region1accting
The following example creates a client-side DSN named USInvoices that connects to a database named DomesticOrders on a remote server named USInventory:
dsnadd -dsn=USInvoices -db=DomesticOrders -host=USInventory
The following example creates a deprecated, legacy-style Client DSN named bkorderclnt” that references an engine DSN named backordersrv on a machine named JapanSvr2 and uses automatic encoding.
dsnadd -dsn=bkorderclnt -sdsn=backordersrv -host=JapanSvr2 -translate=auto -clntdsn
The following example creates a deprecated, legacy-style engine DSN named partsctlg that connects to a database named partscatalog.
dsnadd -dsn=partsctlg -db=partscatalog -engdsn
Notes
On Linux or macOS distributions, individual ODBC drivers are loaded through the driver manager UNIXODBC. The driver manager maintains a mapping from data source names (DSNs) to the specific PSQL ODBC drivers.
The installation of PSQL Server 64-bit or Client 64-bit leaves user-defined, preexisting 32-bit DSNs as is, meaning they are not immediately accessible from a 64-bit application. For new DSNs, the installation of both products assigns a 32-bit and a 64-bit ODBC driver in odbcinst.ini. This assignment allows a single DSN to be used by both a 32- and a 64-bit application.
If you want preexisting 32-bit DSNs to be accessible to both 32- and 64-bit applications, you must recreate them as DSNs that connect to a named database.
ODBC and Data Source Names (DSNs)
The application bitness does not have to match the bitness of the PSQL Server product. For example, the 64-bit ODBC driver or the 32-bit ODBC driver can be used to connect to either PSQL Server 64-bit or PSQL Server 32-bit.
For the PSQL Client, however, the application bitness does have to match the bitness of the PSQL Client on the client machine. That is, if you want to use a 64-bit application on the client, the PSQL 64-bit Client must be installed.
PSQL allows DSNs to specify three different ODBC driver descriptions, as explained in the following table.
Table 17 PSQL ODBC Driver Descriptions for Linux, macOS, or Raspbian
ODBC Driver Description in INI Files
Driver Library Installed With
Behavior for All Products Installed With
Pervasive ODBC Engine Interface
PSQL Server 64-bit
PSQL Server 32-bit (Linux only)
•Installation assigns an ODBC driver in odbc.ini for compatibility with preexisting Engine DSNs
•Connects to a local named database
•No longer created by default with dsnadd (the -engdsn option must be specified)
•Visible when viewing Engine DSNs” in ODBC Administrator running on a Windows client
•For use by 32-bit applications that are already coded to use Engine DSNs
•32-bit Engine DSNs are deprecated on Linux.
Pervasive ODBC Client Interface
PSQL Server 64-bit
PSQL Server 32-bit (Linux only)
PSQL Client 32-bit (Linux only)
•Installation assigns an ODBC driver in odbc.ini for compatibility with preexisting Client DSNs
•Connects to a local or remote named database or an Engine DSN
•For use only by 32-bit applications that are already coded to use Client DSNs
•No longer created by default when -host is specified with dsnadd (the -clntdsn option must be specified)
•32-bit Client DSNs are deprecated on Linux.
Pervasive ODBC Interface
PSQL Server 64-bit
PSQL Client 64-bit
•Installation assigns ODBC drivers in odbcinst.ini for use by new 32-bit and 64-bit DSNs
•Connects to a local or remote named database
•The recommended driver description to use for 32-bit and 64-bit applications
•Created by default with dsnadd
•Not visible as Engine DSNs” in ODBC Administrator running on a Windows client.
Frequently Asked Questions
The following table answers some frequently asked questions about ODBC and DSN support for Linux, macOS, or Raspbian.
Table 18 FAQs About ODBC and DSN Support for Linux, macOS, or Raspbian 
Question
Answer
What do I need to do about DSNs if I port my 32-bit application to 64-bit?
If the application uses DSN-less connections that connect using “Pervasive ODBC Client Interface,” change the ODBC driver description to Pervasive ODBC Interface.”
If the application uses DSNs, you must create new DSNs that connect to a named database.
What is a DSN-less connection?
A DSN-less connection is one that connects to a named database using the ODBC driver “Pervasive ODBC Client Interface” (for preexisting 32-bit applications) or the “Pervasive ODBC Interface” driver (for 32-bit or 64-bit applications).
Can I still create deprecated, legacy-style Engine DSNs and Client DSNs?
Yes, but you must specify the -engdsn or -clntdsn option with dsnadd. DSNs created with either option support only 32-bit applications.
If I am using ODBC Administrator on a Windows client, why do I not see my DSNs?
On 64-bit Windows operating systems, 64-bit system DSNs are distinct from 32-bit system DSNs because of the registry design. If you are using the 64-bit ODBC Administrator, you will not see the 32-bit system DSNs, and vice versa.
What if my application uses DTI to manage DSNs?
The DTI functions for DSNs manage only 32-bit Engine DSNs. Therefore, the DTI functions for DSNs are deprecated along with the 32-bit Engine Interface ODBC driver.
An alternative to using DTI to manage DSNs is to use SQL and ODBC statements. For example, you could use CREATE DATABASE to create a named database and SQLConfigDatasource to configure the DSNs.
Going forward, is there a recommended strategy for ODBC connections?
Yes. New applications or revised 32-bit applications, local or remote, should connect to a named database.
ODBC Header Files
The sql.h, sqltypes.h, and sqlext.h header files for ODBC contain differences for the compilation of 32-bit and 64-bit applications. Refer to the ODBC documentation on the UNIXODBC Web site for a discussion of 64-bit ODBC. For example, you may find the following information useful: http://www.unixodbc.org/doc/ODBC64.html.
See Also
btadmin, dbmaint, isql