Configuring the JDBC Output adapter

A JDBC Output adapter enables you to write output to another database server through JDBC.

In order to use this adapter you must specify several pieces of information, including:

Information about connecting to the database server. This could be a connection string or other connection information, a user ID with appropriate privileges on the external database, and a password.
An SQL statement (typically an INSERT statement) that inserts a row into the external database. This statement contains placeholders for parameters; the values for the parameters are read from each row received by the output adapter.

After the JDBC Output Adapter is loaded, it attempts to establish a JDBC session with the given database. It then executes the specified SQL statement on each row it receives from the output data stream. The query is parameterized in the fashion of a JDBC PREPARE statement. The columns in each row are inserted into the query in the order they appear in the row descriptor. See JDBC standard documentation for details

The JDBC Output adapter submits a database INSERT query in order to write each row of output. The parameters necessary to connect to the external database are specified in theSybase CEP Server configuration file. For more information about Sybase CEP Server configuration file, see the Sybase CEP Installation Guide . Whenever a row is output to this Adapter, the row is INSERTed into the table specified in the input query statement.

This adapter is run as an out-of-process adapter. To run it, execute the following steps:

Set your CLASSPATH environment variable according to the instructions in Java SDK. The instructions for setting CLASSPATH apply to all out-of-process Java adapters, whether provided by Sybase CEP or written by you.

Execute the following command:


java com.sybase.c8.adapter.JDBCOutputAdapter <parameters>

Each parameter is of the form

--<paramname>=<paramvalue>

For example, the beginning of such a command line will look similar to the following:


java com.sybase.c8.adapter.JDBCInputAdapter --queryFile=q1.sql ...

Note that two dashes must precede each parameter name.

Use the command-line parameters shown in the table below. These parameters are case-sensitive.

Parameter	Type	Required/Optional
queryFile	A file with the SQL query, which can be almost any SQL statement that is valid for the database that the adapter is connected to. The query can be parameterized.	Required
username	The username with which to log into the database.	Required
password	The password for the specified user name.	Required
databaseUrl	A database address in the standard form used by JDBC. For example, --databaseUrl=jdbc:oracle:oci8:@oracle.	Required
streamUrl	The URL of the stream to read tuples from.	Required
driver	The database driver to use. For example: --driver=oracle.jdbc.driver.OracleDrive. Sybase CEP does not bundle JDBC drivers with the product, so customers must provide the drivers themselves. For the Sybase CEP environment, you can use the Oracle driver with the Oracle product. In order for the Sybase CEP JDBC Input adapter to work, the JDBC driver must be included in the Java CLASSPATH, as well as pass the driver on the command line.	Required

Here's a sample command to call JDBCOutputAdapter to connect to a database (no tnsnames used):


java com.sybase.c8.adapter.JDBCOutputAdapter 
--queryFile=/home/lita/QueryFileOutputAdapter --username=lita 
--password=SYBASE
--databaseURL=jdbc:oracle:oci8:@oracle.eng.sybase.com:1521:orcl 
--driver=oracle.jdbc.driver.OracleDriver 
--streamUrl=ccl://pc03.sybase.com:7777/Stream/DbWrite/OutStream

When a value from the Sybase CEP output data stream is inserted into an external database, the data type of the Sybase CEP output value must be compatible with the data type of the corresponding column in the table in the external database. The table below shows the mapping between CCL data types and SQL data types. It is assumed that the output table exists in the destination database and has types that conform to the ones shown.

Datatype Mappings between CCL and ANSI SQL

CCL Type	ANSI SQL Type	Description
INTEGER	INTEGER	Integer values between -2147483648 and +2147483647 (-2^31 to +2^31 - 1).
LONG	(See footnote 1)	Integer values between -9223372036854775808 and +9223372036854775807 (-2^63 to +2^63-1).
FLOAT	FLOAT	64-bit floating point numbers.
STRING	CHARACTER VARYING(2147483647)	Character strings.
TIMESTAMP	TIMESTAMP	Date and time, specified as years, months, days, hours, minutes, seconds, and fractions of a second.
INTERVAL	INTERVAL DAY TO SECOND (See footnote 2)	Interval of time, specified as days, hours, minutes, seconds, and fractions of a second.

Footnotes:

The LONG data type is not part of the ANSI SQL-92 specification.
Sybase CEP does not support YEAR TO MONTH intervals.

Your query must use the same number of binding variables as there are fields in the stream schema that you use. For example, if a stream that feeds the output adapter has 3 fields in it, the query must have 3 binding variables. Since this is an unmanaged adapter, it cycles through each row received and tries to place each column in the row in a corresponding binding variable.

Like any out-of-process adapter, this adapter requires that the server and query module already be running, and that the streams exist so the adapter can attach to them.

To shut down the adapter, send an interrupt signal (for example, ctrl-C).