Configuring an ODBC Connection to an External Database

Create a service definition for an ODBC connection to the database of your choice.

1. Set the Service Name parameter to a unique service name for the database service. This name is:
   • case-sensitive
   • must begin with a letter
   • may contain a character string consisting of either letters, numbers, underscores, dots, and colons.
   This service name is the value you specify to components, such as the Database adapter, that accesses external databases.
2. Set the Type attribute of the service parameter to DB.
3. (Optional) Add a description of your service entry in the Description parameter.
4. Set the DriverLibrary parameter to the Event Stream Processor library of the database to which you want to connect:

   Database	DriverLibrary Value
   All supported databases	esp_db_odbc_lib or esp_db_odbc64_lib

   Note: For Windows, the DriverLibrary parameter can only be set to esp_db_odbc_lib. For Linux and Solaris, there are two types of driver managers. One is built with the SQLLEN size set to 32-bits and the other with SQLLEN set to 64-bits. Drivers are only compatible with one or the other of the driver managers. For Linux and Solaris, set the DriverLibrary parameter to esp_db_odbc_lib if your driver requires 32-bit SQLLEN, or esp_db_odbc64_lib if your driver requires 64-bit SQLLEN.
5. Set the User parameter to the user name that you want to use when communicating with the external database.
   This value is unencrypted, so anyone with access to the services.xml may read the user name.
6. Set the Password parameter to the password for your user name.
   To encrypt this password, add the encrypted="true" attribute after the password value and use the esp_cluster_admin utility to generate encrypted text.
7. Set the DSN parameter to the data source name to be used by your service.
   You should already have this data source set up with the ODBC driver manager.
8. (Oracle and TimesTen ODBC drivers only) Set the WriteBigIntAsChar parameter to true to force the ODBC driver plugin to insert bigint type data to a database as chars. Valid values are true or false.
   For example, the Oracle ODBC driver does not support SQL_C_SBIGINT/SQL_C_UBIGINT parameters, causing errors when the Database Output adapter tries to write long and interval Event Stream Processor types to bigint type columns. To avoid this issue, set this parameter to true (<Parameter Name="WriteBigIntAsChar">true</Parameter>) when using Oracle and TimesTen ODBC drivers or tables with bigint type columns.

If you make changes to the service.xml file while the ESP Server is running, the Server does not recognize the changes until you restart it. If you are running on the local cluster, which the ESP Studio starts on its own, restart the ESP Studio as well.