Configuring Connections to External Databases

Use the service.xml file to store service definitions that store connection properties required for a database connection.

Create a separate service definition for every external database you want the Event Stream Processor Server to connect to.

You can use the sample service configuration file in ESP_HOME/bin as a basis to create your custom service.xml file. To use the file in a running project, modify the services-file parameter in the cluster configuration file of the node on which you will run the project. This ensures that the project can find the service.xml file. For example, <Property name="services-file">${ESP_HOME}/bin/service.xml</Property>.

To set up this type of connection from within Sybase Event Stream Processor:

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.
Set the Type attribute of the service parameter to DB.
(Optional) Add a description of your service entry in the Description parameter.

Set the DriverLibrary parameter to the Event Stream Processor library of the database you want to connect to:

JDBC drivers:

Database	DriverLibrary Value
Adaptive Server Enterprise	esp_db_jdbc_sybase_lib
Microsoft SQL Server	esp_db_jdbc_mssql_lib
IBM DB2	esp_db_jdbc_db2_lib
Oracle	esp_db_jdbc_oracle_lib
Kx Systems KDB+	esp_db_jdbc_kdb_lib

ODBC drivers:

Database	DriverLibrary Value
All 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. The library uses a 32-bit or 64-bit ODBC driver manager based on the installation of Event Stream Processor. For Linux/Unix, set the DriverLibrary parameter to esp_db_odbc_lib if you are using a 32-bit ODBC driver manager, or esp_db_odbc64_lib if you are using a 64-bit manager. The libraries are built against different driver managers, with the SQLLEN size set to 4 and 8 bytes respectively, to provide compatibility against driver managers and database drivers built with the aforementioned SQLLEN sizes. They are not duplicate libraries.

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.
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.
(ODBC drivers only) 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.
(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.

(JDBC drivers only) Set:

Parameter	Description
Host	Database server host name.
Port	Database server port number.
Database	Database name. This parameter is not necessary for Oracle and KDB.

Parameter	Description
ConnectString	(Optional) This is a JDBC style connect string that contains the host, port and database information. This overrides the three separate Host, Port, and Database parameters. If you want to enable password encryption between your driver and an external server, add the EncryptPassword property to the connect string and set it to true. For example: `<Parameter Name="ConnectString>jdbc:sybase:Tds:localhost:5000/cep?ENCRYPT_PASSWORD=true</Parameter>`

Parameter

Description

ConnectString

(Optional) This is a JDBC style connect string that contains the host, port and database information. This overrides the three separate Host, Port, and Database parameters.

If you want to enable password encryption between your driver and an external server, add the EncryptPassword property to the connect string and set it to true. For example:

<Parameter Name="ConnectString>jdbc:sybase:Tds:localhost:5000/cep?ENCRYPT_PASSWORD=true</Parameter>

Important: For the Oracle JDBC driver, replace the Database parameter with the Instance parameter. For example, <Parameter Name="Instance">orcl</Parameter>.