Connection properties v16

The following table describes the connection properties that you can specify through the dialog boxes in the graphical connection manager tools or in the odbc.ini file that defines a named data source. The columns identify the connection property (as it appears in the ODBC Administrator dialogs), the corresponding keyword (as it appears in the odbc.ini file), the default value of the property, and a description of the connection property.

Adding a data source definition in Windows

The Windows ODBC Data Source Administrator is a graphical interface that creates named data sources. To open the ODBC Data Source Administrator, in the Control Panel, open the Administrative Tools menu and double-click the appropriate ODBC Data Sources icon (32-bit or 64-bit).

Select Add to open the Create New Data Source dialog box. Select EnterpriseDB (ANSI) or EnterpriseDB (UNICODE) from the list of drivers and select Finish.

Use the fields on the EnterpriseDB ODBC Driver dialog box to define the named data source:

• Enter the database name in the Database field.
• Enter the host name or IP address of EDB Postgres Advanced Server in the Server field.
• Enter the name of a user in the User Name field.
• Enter a descriptive name for the named data source in the Description field.
• If libpq is installed in the same directory as the EDB-ODBC driver, the list next to the SSL Mode label is active, allowing you to use SSL and other EDB Postgres Advanced Server utilities.
• Accept the default port number (5444), or enter an alternative number in the Port field.
• Enter the password of the user in the Password field.

Select Datasource (located in the Options box) to open the Advanced Options dialog box and specify connection properties.

Select Global to open a dialog where you can specify logging options for the EDB-ODBC driver (not the data source, but the driver).

• Select Disable Genetic Optimizer to disable the genetic query optimizer. By default, the query optimizer is on.
• Select KSQO (Keyset Query Optimization) to enable server-side support for keyset queries. By default, Keyset Query Optimization is on.
• Select Recognize Unique Indexes to force the SQLStatistics() function to report unique indexes. If the option is not selected, the SQLStatistics() function reports that all indexes allow duplicate values. By default, Recognize Unique Indexes is on.
• Select Use Declare/Fetch to specify for the driver to use server-side cursors whenever your application executes a SELECT command. By default, Use Declare/Fetch is off.
• Select CommLog (C:\psqlodbc_xxxx.log) to record all client/server traffic in a log file. By default, logging is off.
• Select Parse Statements to specify for the driver (rather than the server) to attempt to parse simple SELECT statements when you call the SQLNumResultCols(), SQLDescribeCol(), or SQLColAttributes() function. By default, this option is off.
• Select Cancel as FreeStmt (Exp) to specify for the SQLCancel() function to call SQLFreeStmt(SQLClose) on your behalf. By default, this option is off.
• Select MyLog (C:\mylog_xxxx.log) to record a detailed record of driver activity in a log file. The log file is named c:\mylog\_\ *process-id*.log. By default, logging is off.

The radio buttons in the Unknown Sizes box specify how the SQLDescribeCol() and SQLColAttributes() functions compute the size of a column of unknown type.

• Select Maximum to specify for the driver to report the maximum size allowed for a VARCHAR or LONGVARCHAR (dependent on the Unknowns as LongVarChar setting). If Unknowns as LongVarChar is enabled, the driver returns the maximum size of a LONGVARCHAR (specified in the Max LongVarChar field in the Miscellaneous box). If Unknowns as LongVarChar is cleared, the driver returns the size specified in the Max VarChar field in the Miscellaneous box.
• Select Don’t know to specify for the driver to report a length of unknown.
• Select Longest to specify for the driver to search the result set and report the longest value found. Don't specify Longest if UseDeclareFetch is enabled.)

The properties in the Data Type Options box determine how the driver treats columns of specific types:

• Select Text as LongVarChar to treat TEXT values as if they are of type SQL_LONGVARCHAR. If cleared, the driver treats TEXT values as SQL_VARCHAR values. By default, TEXT values are treated as SQL_LONGVARCHAR values.
• Select Unknowns as LongVarChar to specify for the driver to treat values of unknown type as SQL_LONGVARCHAR values. If cleared, the driver treats values of unknown type as SQL_VARCHAR values. By default, values of unknown type are treated as SQL_VARCHAR values.
• Select Bools as Char to specify for the driver to treat BOOL values as SQL_CHAR values. If cleared, BOOL values are treated as SQL_BIT values. By default, BOOL values are treated as SQL_CHAR values.

You can specify values for some of the properties associated with the named data source in the fields in the Miscellaneous box:

• Indicate the maximum length allowed for a VARCHAR value in the Max VarChar field. By default, this value is set to 255.
• Enter the maximum length allowed for a LONGVARCHAR value in the Max LongVarChar field. By default, this value is set to 8190.
• Specify the number of rows fetched by the driver (when UseDeclareFetch is enabled) in the Cache Size field. The default value is 100.
• Use the SysTablePrefixes field to specify a semicolon-delimited list of prefixes that indicate that a table is a system table. By default, the list contains dd_;.

You can reset the values on this dialog box to their default settings by selecting Defaults.

Select Apply button to apply any changes to the data source properties. Select OK to apply any changes and exit.

Select Page 2 (in the upper-left corner of the Advanced Options dialog box) to access a second set of advanced options.

• Select Read Only to prevent the driver from executing the following commands: INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE or LOCK. Invoking the Read Only option also prevents any calls that use ODBC’s procedure call escape syntax (call=procedure-name?). By default, this option is off.
• Select Show System Tables to include system tables in the result set of the SQLTables() function. If the option is enabled, the driver includes any table whose name starts with pg\_ or any of the prefixes listed in the SysTablePrefixes field of Page 1 of the Advanced Options dialog box. By default, this option is off.
• Select Show sys/dbo Tables [Access] to access objects in the sys schema and dbo schema through the ODBC data source. By default, this option is on.
• Select Show sys/SQL Tables to enable accessing database tables in a linked server with MS SQL.
• Select Cumulative Row Count for Insert to cause a single, cumulative row count to be returned for the entire array of parameter settings for an INSERT statement when a call to the SQLRowCount() method is performed. If this option is cleared, then an individual row count is available for each parameter setting in the array and thus a call to SQLRowCount() returns the count for the last inserted row.
• Select LF<->CR/LF conversion to instruct the driver to convert line-feed characters to carriage-return/line-feed pairs when fetching character values from the server and convert them back to line-feed characters when sending character values to the server. By default, this option is on.
• Select Updatable Cursors to specify for the driver to permit positioned UPDATE and DELETE operations with the SQLSetPos() or SQLBulkOperations() functions. By default, this option is on.
• Select bytea as LO to specify for the driver to treat BYTEA values as if they're SQL_LONGVARBINARY values. If cleared, EDB-ODBC treats BYTEA values as if they are SQL_VARBINARY values. By default, BYTEA values are treated as SQL_VARBINARY values.
• Select Row Versioning to include the xmin column when reporting the columns in a table. The xmin column is the ID of the transaction that created the row. You must use row versioning if you plan to create cursors where SQL_CONCURRENCY = SQL_CONCUR_ROWVER. By default, this option is off.
• Select Disallow Premature to specify for the driver to retrieve metadata about a query (the number of columns in a result set or the column types) without actually executing the query. If this option is cleared, the driver executes the query when you request metadata about the query. By default, this option is off.
• Select True is -1 to tell the driver to return BOOL values of True as a -1. If this option is cleared, the driver returns BOOL values of True as 1. The driver always returns BOOL values of False as 0.
• Select Server side prepare to tell the driver to use the PREPARE and EXECUTE commands to implement the Prepare/Execute model. By default, this option is on.
• Select use gssapi for GSS request to instruct the driver to send a GSSAPI connection request to the server.
• Enter the database system (either EnterpriseDB or PostgreSQL) in the dbms_name field. The value entered here is returned in the SQL_DBMS_NAME argument when the SQLGetInfo() function is called. The default is EnterpriseDB.

Use the options in the Int8 As box to specify how the driver returns BIGINT values to the client. Select default to specify the default type of NUMERIC if the client is MS Jet. Select BIGINT if the client is any other ODBC client. You can optionally specify for the driver to return BIGINT values as a bigint (SQL_BIGINT), numeric (SQL_NUMERIC), varchar (SQL_VARCHAR), double (SQL_DOUBLE), or int4 (SQL_INTEGER).

The default value of the Extra Opts field is 0x0. For Extra Opts, you can specify the options shown in the table.

The Protocol box contains options that tell the driver to interact with the server using a specific front-end/back-end protocol version. By default, the protocol selected is 7.4+. You can optionally select from versions 6.4+, 6.3, or 6.2.

The Level of Rollback on Errors box contains options that specify how the driver handles error handling.

The OID Options box contains options that control the way the driver exposes the OID column contained in some tables:

• Select Show Column to include the OID column in the result set of the SQLColumns() function. If cleared, the OID column is hidden from SQLColumns().
• Select Fake Columns to specify for the SQLStatistics() function to report that a unique index exists on each OID column.

Use the Connect Settings field to specify a list of parameter assignments for the driver to use when opening this connection. Any configuration parameter that you can modify with a SET statement can be included in the semicolon-delimited list. For example:

set search_path to company1,public;

After you define the connection properties for the named data source, select Apply to apply the options. Select OK to save the options and exit.

Select Global (on the EnterpriseDB ODBC Driver dialog box) to open the Global Settings dialog box. The options on this dialog box control logging options for the EDB-ODBC driver. Use this dialog box to enforce logging when the driver is used without a named data source or for logging driver operations that occur before the connection string is parsed.

• Select CommLog to record all client/server traffic in a log file. The log file is named C:\psqlodbc_<process-id>, where <process-id> is the name of the process in use.
• Select Mylog to keep a log file of the driver’s activity. The log file is named c:\mylog_<process-id>, where <process-id> is the name of the process in use.
• Specify a location for the log files in the Folder for logging field. After you entered the connection information for the named data source, select Test to verify that the driver manager can connect to the defined data source.

Select OK to exit the Connection Test dialog box. If the connection is successful, select Save to save the named data source. If there are problems establishing a connection, adjust the parameters and test again.

Adding a data source definition in Linux

You can define named data sources on Linux in a text file that the driver manager reads to determine how to connect to the database. The driver manager usually looks for named data sources in two places:

• /user/.odbc.ini — Named data source that is available only to the current user.
• /etc/odbc.ini — Named data source that is available to all users.

There is no difference in the structure of these files and the same connection properties can be used in both files, only the location of the two files is different. If both files are available on a system, the user data source /user/.odbc.ini overrides the system data source /etc/odbc.ini.

A data source definition contains a data source name enclosed in square brackets and a list of driver and connection properties in the form of a property name followed by equal sign (=) followed by property value.

A typical user or system data source definition for the EDB-ODBC driver:

[EnterpriseDB]
Description = EnterpriseDB DSN
Driver = /usr/edb/odbc/lib/edb-odbc.so
Trace = yes
TraceFile = /tmp/odbc.log
Database = edb
Servername = localhost
UserName = enterprisedb
Password = manager
Port = 5444

Possible properties include:

• On this page
• Adding a data source definition in Windows
• Adding a data source definition in Linux