Datatype Mappings

Recommended datatype mappings between CCL and SQL

Not all data types can be mapped exactly. For example, CCL Integer is a 32-bit integer data type with a maximum positive value of 2,147,483,647. The closest data type in Oracle is either NUMBER(9), which can't hold some values that CCL INTEGER data type can hold, or NUMBER(10), which can hold some values that CCL INTEGER data type can't hold.

The following tables show the recommended mappings between CCL and SQL. These are useful if you are going to read data from a database server into your Sybase CEP Server or if you are going to write data to a database server from your Sybase CEP Server. For example, if you have an Oracle database server and you want to exchange BLOB data between your Oracle and Sybase CEP Servers, you should use VARCHAR(N) data type on the Oracle server with BLOB data type on Sybase CEP Server; however, if you are using a Sybase server then you should use TEXT on the Sybase server to correspond to BLOB on Sybase CEP Server.

These recommended mappings are based on our in-house testing. In some cases, other mappings will also work. For example, although we map Sybase CEP BOOLEAN to MySQL INTEGER, it is likely that MySQL BOOLEAN will also work.

Three tables are provided: The first two tables show the mappings between several specific database servers and Sybase CEP. The third table shows the mapping between Sybase CEP data types and ANSI SQL data types. We recommend that you look in the first two tables for your database server. If your database server is not listed, and if your database server supports ANSI SQL data types, use the third table.

The table below shows the mappings between data types and ANSI SQL data types.

Notes:

1. This data type is not part of the ANSI SQL92 specification.

2. Sybase CEP does not support YEAR-MONTH intervals.

3. On some platforms, Sybase CEP strings may be limited to 65,535 bytes.

4. Although internally Sybase CEP supports BLOBs up to 4GB, the practical maximum size is often 2GB due to other limits. For example, Sybase CEP Server cannot read a file (or write a file) larger than 2GB, so you cannot read a 4GB blob from, or write a 4GB blob to a file.

5. As a practical matter, the larger the amount of data to be handled, the fewer rows per second the server can handle. Large values of type BLOB or of type STRING or type XML will severely limit throughput.

6. Oracle BLOB or MySQL BLOB are not supported for use with Sybase CEP BLOB.

7. When Sybase CEP BLOB data is stored in VARCHAR or another ASCII format (as opposed to a binary format), the BLOB is converted to a string by using bas64 encoding, which converts three bytes of BLOB data to four bytes of ASCII data. This expands the size of the data by 4/3 (which typically means that if the database server's data size limit is 2GB for VARCHAR, then only 1.5 GB of BLOB data can be stored in it). For a little more information about base64 encoding, see Reading and Writing BLOBs on External Database Servers.

Below is a sample CCL code that creates a table that you can write data to on an Oracle database server.

-- PURPOSE:
--    This creates a table on an Oracle database server and 
--    loads data into that table.  
-- ASSUMPTIONS:
--    The database must already have been created and the user
--    must already have permissions to access the database.  
-- Create the table.
EXECUTE STATEMENT DATABASE "LocalOracleDB"
[[create table BigData1 (
-- We can't map Sybase CEP INTERVAL to Oracle INTERVAL
-- directly, so we'll store it as a 21-digit integer.
interval_col NUMBER(21), 
-- We can't map Sybase CEP BLOB to Oracle BLOB/CLOB 
-- directly, so we'll store it as a VARCHAR.  
blob_col VARCHAR(1000)
)]]
SELECT SetupStream.command AS command
FROM SetupStream
WHERE command = "CREATE TABLE"
;
-- Load data into the Oracle table. 
EXECUTE STATEMENT DATABASE "LocalOracleDB"
[[INSERT INTO BigData1 (interval_col, blob_col) 
VALUES (?interval_col, ?blob_col)
]]
SELECT 
-- Can't directly map Sybase CEP INTERVAL to Sybase CEP INTERVAL!
TO_LONG(Interval1) as interval_col, 
-- Remember that the blob_col is actually VARCHAR on Oracle.
Blob1 AS blob_col
FROM InputFromCSVFile
;
-- To read back the data from Oracle to Sybase CEP, 
-- we could use the Sybase CEP Read From Database adapter, 
-- which can be hooked up directly to a Sybase CEP input stream.
-- The schema of the Sybase CEP input stream would be required
-- to match the schema of the Oracle table from which we 
-- would read.