Easysoft ODBC-Derby Driver User's Guide - Configuration

Configuring the Easysoft ODBC-Derby Driver

The Easysoft ODBC-Derby Driver is installed on the computer where your applications are running. ODBC applications access ODBC drivers through the ODBC Driver Manager and a data source. The data source tells the Driver Manager which ODBC driver to load, which Derby server to connect to and how to connect to it. This chapter describes how to create data sources, use DSN-less connections and configure the Easysoft ODBC-Derby Driver.

Before setting up a data source, you must have successfully installed the Easysoft ODBC-Derby Driver.

For Easysoft ODBC-Derby Driver installation instructions, see Installation.

Chapter Guide

Configuring the Easysoft ODBC-Derby Driver

This section describes how to configure the Easysoft ODBC-Derby Driver to connect to a Derby database by using a data source or a DSN-less connection string. The section assumes you are, or are able to consult with, a database administrator.

Setting Up Data Sources on Unix

There are two ways to set up a data source to your Derby data:

¯ OR ¯

By default, the Easysoft ODBC-Derby Driver installation creates a SYSTEM data source named [DERBY_SAMPLE]. If you are using the unixODBC included in the Easysoft ODBC-Derby Driver distribution, the SYSTEM odbc.ini file is in /etc.

If you built unixODBC yourself, or installed it from some other source, SYSTEM data sources are stored in the path specified with the configure option --sysconfdir=directory. If sysconfdir was not specified when unixODBC was configured and built, it defaults to /usr/local/etc.

If you accepted the default choices when installing the Easysoft ODBC-Derby Driver, USER data sources must be created and edited in $HOME/.odbc.ini.


Note

To display the directory where unixODBC stores SYSTEM and USER data sources, type odbcinst -j.

By default, you must be logged in as root to edit a SYSTEM data source defined in /etc/odbc.ini.


You can either edit the sample data source or create new data sources.

Each section of the odbc.ini file starts with a data source name in square brackets [ ] followed by a number of attribute=value pairs.


Note

Attribute names in odbc.ini are not case sensitive.


The Driver attribute identifies the ODBC driver in the odbcinst.ini file to use for a data source.

When the Easysoft ODBC-Derby Driver is installed into unixODBC, it places an Easysoft ODBC-Derby entry in odbcinst.ini. For Easysoft ODBC-Derby Driver data sources therefore, you need to include a Driver = Easysoft ODBC-Derby entry.

To configure a Derby data source, in your odbc.ini file, you need to specify:

For example:

 [Derby]

 Driver = Easysoft ODBC-Derby

 Server = my_derby_hostname

 Database = /my_dir/toursdb

 User = my_user

 Password = my_password

Environment

The Easysoft ODBC-Derby Driver must be able to find the following shared objects, which are installed during the Easysoft ODBC-Derby Driver installation:

By default, this is located in /usr/local/easysoft/unixODBC/lib.

By default, this is located in /usr/local/easysoft/lib.

By default, this is located in /usr/local/easysoft/lib.

You may need to set and export LD_LIBRARY_PATH, SHLIB_PATH or LIBPATH (depending on your operating system and run-time linker) to include the directories where libodbcinst.so, libeslicshr.so and libessupp.so are located.


Note

The shared object file extension (.so) may vary depending on the operating system (.so, .a or .sl).


Establishing a Test Connection

The isql query tool lets you test your Easysoft ODBC-Derby Driver data sources.

To test the Easysoft ODBC-Derby Driver connection

1.  Change directory into /usr/local/easysoft/unixODBC/bin.

2.  Type ./isql.sh -v data_source, where data_source is the name of the target data source.

3.  At the prompt, type an SQL query. For example:

 SQL> select * from firsttable;

¯ OR ¯

 Type help to return a list of tables:

 SQL> help

 

Setting Up Data Sources on Windows

To connect an ODBC application on a Windows machine to a Derby database:

1.  Open ODBC Data Source Administrator:

 The ODBC Data Source Administrator dialog box is displayed:

Figure 3: The ODBC Data Source Administrator dialog box

2.  Select the User DSN tab to set up a data source that only you can access.

¯ OR ¯

 Select the System DSN tab to create a data source which is available to anyone who logs on to this Windows machine.

3.  Click Add... to add a new data source.

 The Create New Data Source dialog box displays a list of drivers:

Figure 4: The Create New Data Source dialog box

4.  Select Easysoft ODBC-Derby Driver and click Finish.

 The Easysoft ODBC-Derby Driver DSN Setup dialog box is displayed:

Figure 5: The Easysoft ODBC-Derby Driver DSN Setup dialog box

For details of the other attributes that can be set on this dialog box, see Attribute Fields.


64-bit Windows

The Easysoft installer program installs both a 32-bit and a 64-bit version of the Easysoft ODBC-Derby Driver. If you want to use a 64-bit ODBC application, you need to use the 64-bit Easysoft ODBC-Derby Driver. If you want to use a 32-bit ODBC application, you need to use the 32-bit Easysoft ODBC-Derby Driver.

There is both a 32-bit and a 64-bit version of ODBC Administrator. The 64-bit ODBC Administrator is located in Control Panel under Administrative tools. To access the 32-bit ODBC Administrator in Windows 7 and earlier, in the Windows Run dialog box, type:

%windir%\syswow64\odbcad32.exe

On Windows 8, both the 32-bit and 64-bit ODBC Administrator are located in Control Panel under Administrative tools: ODBC Data Sources (32-bit) and ODBC Data Sources (64-bit).

Easysoft ODBC-Derby Driver data sources created in the 64-bit ODBC Administrator will specify the 64-bit version of the Easysoft ODBC-Derby Driver. Easysoft ODBC-Derby Driver data sources created in the 32-bit ODBC Administrator will specify the 32-bit version of the Easysoft ODBC-Derby Driver.

If you want to create an Easysoft ODBC-Derby Driver System data source for use with a 64-bit application, use the 64-bit ODBC Administrator. If you want to create an Easysoft ODBC-Derby Driver System data source for use with a 32-bit application, use the 32-bit ODBC Administrator.

For Easysoft ODBC-Derby Driver User data sources, it does not matter which version of the ODBC Administrator that you use.


Attribute Fields

This section lists the attributes which can be set for the Easysoft ODBC-Derby Driver in a table showing:

Attributes which are text fields are displayed as value.

Attributes which are logical fields can contain either 0 (to set to off) or 1 (to set to on) and are displayed as "0|1".

If an attribute can contain one of several specific values then each possible entry is displayed and separated by a pipe symbol.

For example, in the statement:

DIALECT=1|2|3

the value entered may be "1", "2" or "3".

DSN

The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.

Interface Value

DSN Dialog Box (Windows)

DSN

odbc.ini file (Unix)

[value]

Connect String

DSN=value

Description

Descriptive text that may be retrieved by certain applications to describe the data source.

Interface Value

DSN Dialog Box (Windows)

Description

odbc.ini file (Unix)

Description=value

Connect String

Not Used

Database

The database to connect to. Unless the database is located in the Derby system directory, include the path. The path can be either absolute or relative to the system directory. Examples:

# This database is located in the system

# directory

Database = firstdb

# This database is located in the parent

# directory of the system directory

Database = firstdb

# This database is located in a user's home

# directory on a Linux machine

Database = /home/myuser/firstdb



Interface Value

DSN Dialog Box (Windows)

Database

odbc.ini file (Unix)

Database = value

Connect String

DATABASE=value

User Name

If a user name is required to access your Derby database, supply it with the User attribute.

Apache Derby security mechanisms control what authentication details are required and whether the authentication details are encrypted. If your Derby network server is set to use the ENCRYPTED_USER_AND_PASSWORD_SECURITY security mechanism, you must also include this line in your data source:

EncLogin = 1

The Easysoft ODBC-Derby Driver does not support the following security mechanisms: USER_ONLY_SECURITY, STRONG_PASSWORD_SUBSTITUTE_SECURITY.

To specify the user name in the connection string, use UID rather than User. For more information about specifying Easysoft ODBC-Derby Driver attributes in the connection string, see DSN-less Connections.

Interface Value

DSN Dialog Box (Windows)

User Name

odbc.ini file (Unix)

User = value

Connect String

USER=value

Password

The password for the user name.

To specify the password in the connection string, use PWD rather than Password.



Interface Value

DSN Dialog Box (Windows)

Password

odbc.ini file (Unix)

Password = value

Connect String

PASSWORD=value

Server

The machine on which the Derby network server is running. Set Server to be the same value as that of the derby.drda.host property or the network server's -h option. If you do not set these Derby options, set Server to localhost.



Interface Value

DSN Dialog Box (Windows)

Server

odbc.ini file (Unix)

Server = value

Connect String

SERVER = value

Port

The TCP port that the Derby network server is listening on.

If you are connecting to a default instance that is listening on port 1527, the Port setting can be omitted.

Interface Value

DSN Dialog Box (Windows)

Port

odbc.ini file (Unix)

Port = num

Connect String

PORT=num

IPv6

Set IPv6 to 1 when connecting to a Derby instance that is listening on an IPv6 address.

By default, IPv6 is OFF (set to 0), which means that the Easysoft ODBC-Derby Driver assumes that the target Derby instance is listening on an IPv4 address.



Interface Value

DSN Dialog Box (Windows)

IPv6

odbc.ini file (Unix)

IPv6 = 0|1

Connect String

IPV6=0 | 1

SSL Encryption

Whether the Easysoft ODBC-Derby Driver requests an encrypted connection to Derby.



Interface Value

DSN Dialog Box (Windows)

Not available.

odbc.ini file (Unix)

Encrypt = 0 | 1

Connect String

ENCRYPT = 0 | 1

Use AES

The encryption algorithm to use when encrypting the user name and password. When ON (set to 1), the Easysoft ODBC-Derby Driver encrypts the user name and password by using an Advanced Encryption Standard (AES) encryption algorithm. Otherwise, the Easysoft ODBC-Derby Driver encrypts the user name and password by using a Data Encryption Standard (DES) encryption algorithm.

By default, Use AES is OFF (set to 0).



Interface Value

DSN Dialog Box (Windows)

Use AES

odbc.ini file (Unix)

AESEncAlg = 0 | 1

Connect String

AESENCALG = 0 | 1

Private Key File

The private key for use with the SSL session.



Interface Value

DSN Dialog Box (Windows)

Private Key File

odbc.ini file (Unix)

PrivateKeyFile = value

Connect String

PRIVATEKEYFILE = value

Certificate File

The file that contains the public key certificate of the CA that signed the Derby certificate. The CA certificate file must be in base-64 PEM format.

If the CA certificate is not installed on your client machine, you need to export the certificate on the Derby machine and install it on the client.



Interface Value

DSN Dialog Box (Windows)

Certificate File

odbc.ini file (Unix)

CertificateFile = value

Connect String

CERTIFICATEFILE = value

Cypher

The cypher suite that the Easysoft ODBC-Derby Driver will request during the SSL handshake with the Derby machine.

A cypher suite is a set of authentication, encryption, and data integrity algorithms used to protect data exchanged between machines. During the SSL handshake part of the connection process, the SSL layer in the ODBC driver and the Schannel layer on the Derby machine negotiate to decide which cipher suite they will use.

To see which cypher suite is being used for a particular connection, enable Easysoft ODBC-Derby Driver logging.

Connect and then examine the driver log file. Look for a log file entry similar to:

SSL using cypher 'RC4-MD5 SSLv3 Kx=RSA Au=RSA Enc=RC4(128) Mac=MD5'

This entry shows that the ODBC driver and the Derby machine negotiated the following cryptographic protection for the connection:

Encryption: RC4

Encryption strength: 128-bit

Cryptographic checksum: MD5

Authentication: RSA

(You can also display the cryptographic settings negotiated during the SSL handshake by enabling Schannel logging. Enable the "Log informational and success events" Schannel logging option to write this information to the Windows Event Viewer logs. For information about how to do this, see http://support.microsoft.com/kb/260729.)

Use the Cypher setting, if you want to request a different encryption or data integrity algorithm to the ones negotiated during the SSL handshake. For example:

Cypher = 3DES+SHA

If you specify a cypher suite that is not available on the server machine, the Easysoft ODBC-Derby Driver returns the error "Required SSL (failed to receive packet)".

If you specify a cypher suite that the Easysoft ODBC-Derby Driver does not recognise, the driver returns the error "SSL3_CLIENT_HELLO:no ciphers available".



Interface Value

DSN Dialog Box (Windows)

Cypher

odbc.ini file (Unix)

Cypher = value

Connect String

Cypher = value

Trust Cert

Whether the Easysoft ODBC-Derby Driver tries to validate the server certificate to verify the identity of the Derby machine. Set Trust Cert to Yes if your Derby machine is using a self-signed SSL certificate.



Interface Value

DSN Dialog Box (Windows)

Trust Cert

odbc.ini file (Unix)

TrustServerCertificate = 0 | 1

Connect String

TRUSTSERVERCERTIFICATE = 0 | 1

EncLogin

Set the EncLogin attribute to 1 if your Derby network server uses the ENCRYPTED_USER_AND_PASSWORD_SECURITY security mechanism. Otherwise, your connection will fail with the error:

Invalid authorization specification: SECMEC value not supported

By default, EncLogin is OFF (set to 0).



Interface Value

DSN Dialog Box (Windows)

Not available.

odbc.ini file (Unix)

EncLogin = 0 | 1

Connect String

ENCLOGIN=0 | 1

RcvBuffer

The size of the receive buffer for the socket in bytes. Possible values for num are:

0, do not set the receive buffer size, use the system default value.

n, where n is a number greater than 0, set the receive buffer to the specified size by passing n to the setsockopt() function .

By default, the system default receive buffer size is used.



Interface Value

DSN Dialog Box (Windows)

Not available.

odbc.ini file (Unix)

RcvBuffer = num

Connect String

RCVBUFFER = num

DPrec

The precision to use when converting SQL_DOUBLE data in a result set to a string

If an application specifies a string as the target type for non-character data in a SQLBindCol or SQLGetData call, the Easysoft ODBC-Derby Driver converts the data to the target type. Use the DPrec attribute to specify the precision to use when the driver does this conversion for SQL_DOUBLE data.

The default precision is 7.



Interface Value

DSN Dialog Box (Windows)

Not available.

odbc.ini file (Unix)

DPrec = num

Connect String

DPREC = num

FPrec

The precision to use when converting SQL_FLOAT data in a result set to a string

If your application specifies a string as the target type for non-character data in a SQLBindCol or SQLGetData call, the Easysoft ODBC-Derby Driver converts the data to the target type. Use the FPrec attribute to specify the precision to use when the driver does this conversion for SQL_FLOAT data.

The default precision is 7.



Interface Value

DSN Dialog Box (Windows)

Not available.

odbc.ini file (Unix)

FPrec = num

Connect String

FPREC = num

LimitVarchar

Use LimitVarchar to restrict the size returned by the Easysoft ODBC-Derby Driver when describing VARCHAR data.



Interface Value

DSN Dialog Box (Windows)

Not available.

odbc.ini file (Unix)

LimitVarchar = num

Connect String

LIMITVARCHAR = num

Locale

The locale on the Easysoft ODBC-Derby Driver machine.

If you do not set the Locale attribute, the Easysoft ODBC-Derby Driver will use the value set for the LC_CTYPE environment variable. If LC_CTYPE, is not set, the Easysoft ODBC-Derby Driver will use the value set for the LANG environment variable. If neither LC_CTYPE nor LANG are set, the Easysoft ODBC-Derby Driver will set the locale value to C.



Interface Value

DSN Dialog Box (Windows)

Not available.

odbc.ini file (Unix)

Locale = value

Connect String

LOCALE = value

SbUTF8

Controls how single bytes in a string are converted to Unicode. When ON (set to 1), UTF-8 sequences are regarded as single Unicode values. Otherwise, UTF-8 sequences are regarded as individual 8-bit values.

For example, setting SbUTF8 controls whether a UTF-8 Euro symbol (0xE2 0x82 0xAC) converts to 0x20AC (single character) or 0x00E2, 0x0082, 0x00AC (three characters).

By default, SbUTF8 is OFF (set to 0).



Interface Value

DSN Dialog Box (Windows)

Not available

odbc.ini file (Unix)

SbUTF8 = 0 | 1

Connect String

SBUTF8 = 0 | 1

GSSLib

The Easysoft ODBC-Derby Driver uses libgssapi_krb5.so, the Kerberos GSS-API library, to request service tickets for accessing Derby instances. If the Easysoft ODBC-Derby Driver is unable to open this library, the connection will fail with the error:

Krb5: failed to open gss lib (libgssapi_krb5.so)

If the Kerberos GSS-API library is not called libgssapi_krb5.so in your GSS-API distribution, use the GSSLIB attribute in your data source to specify the alternative GSS-API library. For example:

GSSLIB = /opt/extension/lib/libgssapi.so



Interface Value

DSN Dialog Box (Windows)

Not available.

odbc.ini file (Unix)

GSSLib = value

Connect String

GSSLIB = value

GSSFlag

The Easysoft ODBC-Derby Driver allows you to pass req_flags to the gss_init_sec_context() function, which is used to initiate a security context for the driver. The Key Distribution Center (KDC) uses this security context to verify the identity of the client. To pass req_flags to gss_init_sec_context(), use the GSSFLAG attribute:

GSSFLAG = req_flags

where req_flags is a bitmask specifying the requested GSS services. To look up the available bitmask values, refer to the gssapi.h header file for the GSS-API distribution on the Easysoft ODBC-Derby Driver machine. The driver default GSSFLAG value is 4, which sets the GSS_C_REPLAY_FLAG flag.

As an example, to request credential delegation, set the GSS_C_DELEG_FLAG flag by including this entry in your data source GSSFLAG = 1.



Interface Value

DSN Dialog Box (Windows)

Not available.

odbc.ini file (Unix)

GSSFlag = req_flags

Connect String

GSSFLAG = req_flags

DSN-less Connections

In addition to using a data source, you can also connect to a database by using a DSN-less connection string of the form:

SQLDriverConnect(..."DRIVER={Easysoft ODBC-Derby};

Server=server;UID=user;PWD=password;

Database=database;"...)

where server is the host name or IP address of the machine on which the Derby network server is running, user and password are a valid operating system user name and password and database is the Derby database you want to connect to. You need to use the Easysoft ODBC-Derby DRIVER keyword to identify the Easysoft ODBC-Derby Driver.

Other Easysoft ODBC-Derby Driver attribute settings, as described in Setting Up Data Sources on Unix, can be added to the connection string using the same PARAMETER=value; format. For example, the following connection string connects to a Derby instance that is listening on a non-standard port:

"DRIVER={Easysoft ODBC-Derby};Server=myhost;UID=myuser;PWD=mypassword;Database=SAMPLE;Port=1528"