Connecting and communicating with databse servers that support BLOBs requires either encoding the data in (or decoding it from) base64 format when receiving the data from (or passing it to) external database servers.
Sybase CEP Server sends and receives data in base64 format. To store the data in its original form on the external server, decode the data on the external server before storing it. You can also encode the data on the external server after reading the data from the external server.
The current implementation of the Sybase CEP Server does not provide transparent reading and writing of BLOB information on external database servers. When writing to an external database server, Sybase CEP Server automatically encodes the BLOB data in base64 format even if the external server supports the BLOB data type. Similarly, when reading data from an external database server, Sybase CEP Server automatically decodes data even if the external server supports the BLOB data type. To process the data on the external server, store the data as a proper BLOB on the external server. For example, to process an image on the external server, store the image in its original (BLOB) form so that the image-processing software can interpret it correctly. If you leave the data in base64 format, the image-processing software on the external server can not interpret and process the data.
The following is an example of a database subquery that reads data from a BLOB column on an external database server:
INSERT INTO FromDB2 SELECT test.id, test.image FROM (DATABASE "external1" SCHEMA (id string, image blob) [[select id, base64_encode(photo_blob) from testblob T where T.id = '650']]) AS test, instream;
The external database server is told specifically to encode the value in the photo_blob column as a base64 value; this is because Sybase CEP Server expects to receive the data in a base64-encoded form. For this statement to execute properly, the external database server must have a function called base64_encode(). If the database server does not provide this function, you may need to write one yourself.
To write data to a BLOB column on an external database server, tell the external database server to decode the data from base64 into the external server native BLOB format.
EXECUTE STATEMENT DATABASE "external1" [[ insert into testblob2 values(?id, base64_decode((?image))) ]] SELECT F.id as id, F.image as image FROM FromDB2 as F ;
Encoding and decoding the data consumes resources. If the input or output parameter of the encoding/decoding subroutine has a maximum size limit (typically 2GB), then you are limited to 3/4 of that size. If you only want to read and write the data on the external database server to store it and do not want to process it as a BLOB on the external database server, store it as character data rather than BLOB data on the external server.
When reading BLOBs from external databases, the maximum size of the BLOB being read in Sybase CEP is defined by the limits of the database driver. When writing BLOBs to external databases, Sybase CEP's DBDriverCharBufferSize setting in the c8-services.xml file limits the size of the BLOB, which by default is only 4k. See the Sybase CEP Installation Guide for information about this configuration setting.
When reading BLOB data from an external database, transform the BLOB to a base64 encoded string before Sybase CEP acquires the data. Similarly, when writing BLOB data to an external database server, transform the data from base64 format to BLOB format if you want the external database server to store the data in BLOB format.
Different database servers have different packages or functions that you must use to create a base64 encoded string. An example using Oracle's utl_encode.base64_encode() function:
INSERT INTO PhotoDB
SELECT InStream2.id, InStream2.image
FROM
InStream1,
(DATABASE "orcl" SCHEMA (id string, image blob)
[[SELECT id,
-- Tell Oracle to encode the BLOB in base64,
-- which is the format in which Sybase CEP Server
-- expects to receive the data.
utl_raw.cast_to_varchar2(utl_encode.base64_encode(photo))
FROM testblob T
WHERE T.id = ?instream1.id]]) AS InStream2;
An example of how to write a BLOB into an Oracle database:
EXECUTE STATEMENT DATABASE "orcl"
[[
INSERT INTO testblob2 values
(
?id,
-- Since Sybase CEP sends the blob in base64 format, the external
-- database server needs to decode the data before storing the
-- data as a BLOB.
utl_encode.base64_decode(utl_raw.cast_to_raw(?image))
)
]]
SELECT
F.id AS id,
F.image AS image
FROM
PhotoDB AS F
;