Configuring Connections to Databases with User Name, Password, and DSN

The most common way of configuring CCL queries to interact with an external relational database includes a database user name, password, and Database Source Name (DSN).

To set up this type of connection from within Sybase CEP Engine:

  1. In the c8-services.xml file, create a separate service entry section for every external database to which you want to connect. This entry provides information to enable Sybase CEP Server to communicate with the external database server. If you want to connect to multiple database servers, or to multiple databases on a single server, create a separate section for each database.
    The c8-services.xml file contains several sample database service entry sections, each for a different type of database service. To find these entries, search for the string 
    Type="DATABASE"
    inside the file. You can either customize one of these sections, or make a copy of it elsewhere in the file and customize the copy.
  2. Set the "Service Name" preference to a unique service name for the remote service. This name is case-sensitive, must begin with a letter, and may contain a character string consisting of letters, numbers, underscores, dots, and/or colons. The service name should be the same name as the name of the database you use in your CCL queries. For example, if your database subquery is:
    ...
	FROM (DATABASE "Database1" SCHEMA "valuation.ccs" 
	SELECT * FROM Stocks WHERE T.symbol=Stocks.symbol)

    The database name (Database1) in the database subquery should match the service name in the c8-services.xml file.

  3. Set the "Type" preference to DATABASE.
  4. Optionally, add a description of your service entry in the "Description" element.
  5. Set the "DBDriverType" preference to the type of driver you use to communicate with your external database. Sybase supports four the following driver types:

    • DBDriverOracle, which you should use if you are connecting to the database using the native (non-ODBC) Oracle drivers. The connect string for "DBDriverOracle" can be an Oracle TNS Name or an EZ Connect string.

    • DBDriverODBCSybaseIQUnix, which should be used if you are connecting to the database using a Sybase IQ driver on UNIX-like operating systems.

    • DBDriverODBC, which should be used if you are connecting to the database using an ODBC driver, including a Sybase IQ driver on Microsoft Windows. If you are setting up an ODBC connection that uses the DSN name as the connect string name, set up a DSN. Refer to your ODBC documentation for instructions on installing and setting up the necessary ODBC driver(s) for your database.

    • DBDriverKDB, which you should use if you are connecting to a kdb+ database.

  6. If necessary, include a "DatabaseType" preference in your service entry. Set this preference if you are connecting to the Oracle TimesTen database. This is the only way you can connect to the TimesTen database from Sybase CEP Engine. This preference can only be used in conjunction with a "DBDriverType" setting of DBDriverODBC. It may improve performance and compatibility for database drivers that do not fully implement the ODBC standard, or that provide features or optimizations that are not part of the standard. This preference can be set to the following values:

    • MyODBC35.

    • PostgreSQL.

    • SQLServer2005.

    • TimesTen.

    Here is an example of a "DatabaseType" setting:

    <Param Name="DatabaseType">TimesTen</Param>
  7. Set the "DBDriverConnectString" preference to the information needed to actually connect to the external database.
    For DBDriverOracle, the "DBDriverConnectString" may be the Oracle TNS Name, or may use the following format: 
    
dbserver:port/OracleSID
    where:

    • dbserver is the name of the computer on which the other server resides.

    • port is the port number used to communicate with the other server.

    • OracleSID is the service name of the Oracle database to which you want to connect.

    For DBDriverODBC and DBDriverODBCSybaseIQUnix, the "DBDriverConnectString" is the name of the DSN entry you have set up for your connection in the ODBC control panel.

    For DBDriverKDB, the "DBDriverConnectString" is the name of the host name of the kdb+ server.

    Note: Most database servers allow a single server to contain multiple databases. If you want to perform queries on more than one database on a given server, you may need to configure a separate connection for each database.

    Note that, if your "DBDriverConnectString" contains one of the following characters, the character must be preceded by a back slash escape character (\). These characters are: semicolon (;), forward slash (/), question mark (?), colon (:), at sign (@), ampersand (&), equal sign (=), plus sign (+), dollar sign ($), and comma (,). For kdb+ services, set the "Port" preference to the port number of the kdb+ server.

  8. Optionally, set the "CacheMaximumAge" preference to specify how long Sybase CEP Server should keep a cached copy of the information that it read from the external database.

    For more information on caching, see "Caching Data from an External Database, RPC Server, or Public Window".

  9. Set the "Username" preference to the user name that should be used when communicating with the external database.

    Note that this value is unencrypted, so anyone with access to the c8-services.xml file may read the user name.

  10. Set the "Password" preference to the password for the user name you specified in the "Username" preference.

    Like the user name, the password is unencrypted.

  11. Set any desired optional preferences for your database service entry in the c8-services.xml file.

    These are discussed further in "Setting Optional Preferences for Services".

Here are three examples of how you might set the configuration preferences.

This is an example of an entry that uses the Oracle OCI driver:

<Service Name="OracleDb" Type="DATABASE" >
 <Description>local oracle dod db</Description>
 <Param Name="DBDriverType">DBDriverOracle</Param>
 <Param Name="DBDriverConnectString">myhost:6789/mydb</Param>
 <Param Name="CacheMaximumAge">0</Param>
 <Param Name="Username">myuser</Param>
 <Param Name="Password">mypass</Param>
</Service>

This example uses an ODBC driver. Note the different value for DBDriverType:

<Service Name="MyDB" Type="DATABASE" > 
 <Description>sample odbc db</Description> 
 <Param Name="DBDriverType">DBDriverODBC</Param> 
 <Param Name="DBDriverConnectString">MyDSNName</Param>
 <Param Name="CacheMaximumAge">0</Param> 
 <Param Name="Username">myuser</Param>
 <Param Name="Password">mypass</Param>
</Service>

This example uses a Sybase IQ driver. Note the different value for DBDriverType:

<Service Name="MyIQDB" Type="DATABASE" > 
 <Description>sample sybase iq db</Description> 
 <Param Name="DBDriverType">DBDriverODBCSybaseIQUnix</Param> 
 <Param Name="DBDriverConnectString">MyDSNName</Param>
 <Param Name="CacheMaximumAge">0</Param> 
 <Param Name="Username">myuser</Param>
 <Param Name="Password">mypass</Param>
</Service>

Finally, here is an example using the kdb+ driver:

<Service Name="MyKdbService" Type="DATABASE" > 
 <Description>sample kdb</Description> 
 <Param Name="DBDriverType">DBDriverKDB</Param> 
 <Param Name="DBDriverConnectString">myhostname.mydomain.com</Param>
 <Param Name="Port">12345</Param>
 <Param Name="CacheMaximumAge">0</Param> 
 <Param Name="Username">myuser</Param>
 <Param Name="Password">mypass</Param>
</Service>

If you are using a driver other than the ones discussed here, consult the driver documentation for the correct database driver connect string.

Make sure that the external database to which you are connecting is running properly before attempting to connect to it from Sybase CEP Engine.

Parent topic: Enabling External Database Access