Configuring a new user DSN or system DSN

Access the ODBC Data Source Administrator dialog box to configure a new user DSN or system DSN.

About this task

To configure a new user DSN or system DSN:


  1. Choose Start > Settings > Control Panel.
  2. Double-click ODBC to open the ODBC Data Source Administrator dialog box.
    • To configure a user DSN, go to step 3.
    • To configure a system DSN, click the System DSN tab and go to step 3.

    All subsequent steps are the same to configure either a user DSN or a system DSN.

  3. Click Add.

    The Create New Data Source dialog box opens.

  4. Double-click OneDB ODBC driver on the Create New Data Source wizard.

    The General page for the HCL OneDB™ ODBC Driver Setup dialog box opens.

  5. Enter the values in the General page, as the following example shows:
    • Data Source Name: odbc33int
    • Description: file DSN 3.81 on turbo

    For a description of the values, see Required DSN values and Optional DSN values.

    Restriction: Do not click OK after you enter the values on this page. If you click OK before you enter all the values, you get an error message.
  6. Click the Connection tab to display the Connection page and enter the values, as the following example shows:
    • Server Name: ol_clipper (or use the menu to choose a server that is on the sqlhosts registry. If you use the menu, the ODBC application sets the Host Name, Service, Protocol, and Options values.)
    • Host Name: clipper
    • Service: turbo
    • Protocol: onsoctcp (or use the menu to choose a protocol)
    • Database Name: odbc_demo (or use the menu to find a database name)
    • User ID: myname
    • Password: *******

      To save the values you chose and verify that your DSN connects successfully, click Apply & Test Connection. An ODBC Message dialog box opens. The box tells you if your connection was successful or, if it was not, tells you which Connection-tab value is incorrect.

  7. Click the Environment tab to display the Environment page and enter the values, as the following example shows:
    • Client Locale: en_US.CP1252
    • Database Locale: en_US.CP1252
    • Use Server Database Locale: if check box is checked, database locale value is set to the server locale. If the check box is cleared, the database locale is set to the default locale, en_US.CP1252.
    • Translation Library: ONEDB_HOME\lib\esql\ig04n304.dll
    • Translation Option: 0
    • Cursor Behavior: 0 - Close
    • VMB Character: 0 - Estimate
    • Fetch Buffer Size: 4096
    • Isolation Level: 0 - Default will be considered, Read Committed (non-ANSI databases) or Repeatable Read (ANSI databases)
  8. Click the Advanced tab to display the Advanced page and click all applicable boxes.
    Option Description
    Auto commit optimization This option defers automatic commit operations while cursors remain open and can reduce database communication when the application is using non-ANSI logging databases. This option is available only as a connection option:
    or as a connection attribute string: "OptimizeAutoCommit"

    The default is: 1 (enabled).

    Open-Fetch-Close optimization This option causes the driver to buffer the open, fetch, and close cursor messages to the server. In addition, this option eliminates one or more message round trips when you use SQLPrepare, SQLExecute, and SQLFetch statements to fetch data with a cursor. This option is available only as a connection option:
    or as a connection attribute string: "OPTOFC"

    The default is: 0 (disabled).

    Insert cursors This option reduces the number of network messages sent to and from the server by buffering the inserted rows that are used with arrays of parameters and insert statements. This option can greatly improve the performance of bulk insert operations, and is available as both connection and statement options:
    or as a connection attribute string: "EnableInsertCursors"

    The default is: 0 (disabled).

    Scrollable cursor If this option is activated, HCL OneDB ODBC Driver, Version 2.90 and later, supports only scrollable, static cursors. This option is available only as a connection option:
    or as a connection attribute string: "EnableScrollableCursors"

    The default is: 0 (disabled).

    Report KeySet cursors This option causes the driver to report (through SQLGetInfo) that it supports forward-only, static, and keyset-driven cursor types, although the driver only supports forward-only and static cursors. When you set this option, the driver enables dynaset-type functions, such as functions for Microsoft™ Visual Basic. These functions require drivers that support keyset-driven cursor types. This option is also available as a connection attribute:
    or as a connection attribute string: "ReportKeysetCursors"

    The default is: 0 (disabled).

    Report standard ODBC types only If you activate this feature, the driver causes SQLGetTypeInfo to map all occurrences of user-defined types (UDTs) as follows:

    The driver maps multiset, set, row, and list data types to SQL_C_CHAR or SQL_C_BINARY, which is the default UDT

    FetchType to SQL_C_CHAR features.

    The default is: 0 (disabled).

    Describe decimal floating point as SQL_REAL / SQL_DOUBLE This option describes all floating-point decimal columns as Float (SQL_REAL or SQL_DOUBLE). A floating-point decimal column is a column that was created without a scale, ex: DECIMAL(12). Some prepackaged applications such as Visual Basic cannot properly format Decimal columns that do not have a fixed scale. To use these applications you must enable this option or redefine the column with a fixed scale.

    There is a disadvantage to enabling this option however, SQL_DECIMAL is an exact numeric data type while SQL_REAL and SQL_DOUBLE are approximate numeric data types. A SQL_DECIMAL with a precision of 8 or less aree described as SQL_REAL, with a precision greater than 8 it is SQL_DOUBLE.

    The default is: 0 (disabled).

    Do not use LVARCHAR Causes SQLGetTypeInfo to not report LVARCHAR as a supported type of DATA_TYPE of SQL_VARCHAR.

    Some applications such as MS Access97 use LVARCHAR instead of VARCHAR even for columns that are less than 256 bytes long. The minimum number of bytes transmitted for LVARCHAR is higher than for VARCHAR and many LVARCHAR columns can result in the rowset size exceeding the maximum. Enable this option only if your SQL_VARCHAR columns are less than 256 bytes in length.

    The default is: 0 (disabled).

    Report CHAR columns as wide CHAR columns Causes SQLDescribeCol to report char columns as wide char columns. SQL_CHAR column is reported as SQL_WCHAR, SQL_VARCHAR as SQL_WVARCHAR and SQL_LONGVARCHAR column as SQL_WLONGVARCHAR

    The default is: 0 (disabled).

    Length in Chars for SQLGetDiagRecW If enabled, the SQLGetDiagRecW API treats the BufferLength Parameter as the number of characters.

    The default is: FALSE (disabled).

    Leave Trailing Spaces

    If enabled, the driver preserves blank characters at the end of VARCHAR/LVARCHAR column results.

    The default is: 0 (disabled).

    Describe input parameters for SQL statements

    This is required particularly for BLOB/CLOB data types. If enabled, server will send the description of these data types which will be used by ODBC Driver. This option should only be enabled when needed. It should not be turned on all the time as that would cause more round trips between client and server.

    The default is: 0 (disabled).

  9. To check your connection to the database server, click Test Connection.
  10. Click OK to return to the ODBC Data Source Administrator dialog box and to update the DSN information in the appropriate files.


When your application connects to this DSN, the values that you entered are the default entries for the DSN connection.