Binary Data Insertion and Extraction

The Database communication point, Database Message Extraction filter and Database Lookup filter support binary data types insertion and extraction to MS-SQL Server and Oracle database.

Binary Data Types

Binary Encoding

The <binaryEncoding> element determines how the Rhapsody database components encode binary values from columns and stored procedure results as strings when required. Conversion into a string is required if the binary value is:

• Inserted into a message property.
• Inserted into a field in the input message.
• Used to generate an XML message containing results from a number of columns and/or rows.

The conversion does not occur if subsequent SQL queries implicitly or explicitly change the effective column type (for example, by casting the result to another SQL datatype). The conversion only occurs in the scenarios listed above, and then only when the Rhapsody database components know that the data type is a binary type. The <columnType> XML element can be explicitly added to the Rhapsody database configuration to assist it to detect a binary type in some cases. In particular, conversion is not required when using the Message Body Column configuration parameter for the Database communication point or Database Message Extraction filter, as in those cases the binary value is directly inserted into the Rhapsody message body without any conversion being required.

The binary encoding can be set to either base64 or UTF-8. For compatibility reasons, the encoding defaults to UTF-8 if this element is not included in the database configuration. However, the Database Configuration automatically enables it by default as of Rhapsody 5.5.

<config>
  <binaryEncoding>base64</binaryEncoding>
  <statement>
    <sql>SELECT ONE,TWO,THREE,FOUR FROM BinaryTest WHERE ID = 2</sql>
    <substituteColumn name='ONE'>$one</substituteColumn>
    <substituteColumn name='TWO'>$two</substituteColumn>
    <substituteColumn name='THREE'>$three</substituteColumn>
    <substituteColumn name='FOUR'>$four</substituteColumn>
  </statement>
</config>

Binary Data Type Examples

The following examples show how they can be accessed from within Rhapsody.

Example: Insert text data into binary data type columns of MSSQL database

This example uses message properties to insert some text data into binary data type columns in a table.

<statement name="Insert">
	<sql>INSERT INTO DataTable VALUES ($idTwo, $varBinaryData, 'test', $binaryData, $longvarbinaryData, 'test', 'test')</sql>
	<columnType parameter="$binaryData" type="BINARY" ide:description="Binary data" />
	<columnType parameter="$varBinaryData" type="VARBINARY" ide:description="varbinary data" />
	<columnType parameter="$longvarbinaryData" type="LONGVARBINARY" ide:description="longvarbinary data" />
</statement>

• The columnType element should be defined to specify the data type of the table column to be inserted.

Example: Insert text data into binary data type columns of MSSql database using CAST function

This example uses CAST function to insert a literal into a binary data type column in a table.

<statement name="Insert">
    <sql>INSERT INTO DataTable VALUES ($idOne, null, null, CAST('abcde' AS BINARY), null, null, null)</sql>
</statement>

• The columnType element should not be defined when CAST function is used.

Example: Insert whole message body into blob data type columns of Oracle database

This example uses the $messageContent property to insert whole message body.

<statement name="Insert">
    <sql>INSERT INTO FileTable VALUES (600, 'Jane', $messageContent)</sql>
    <columnType parameter="$messageContent" type="BLOB" ide:description="Blob data" />
</statement>

• The 'columnType element should be defined to specify the data type of the table column to be inserted. The configuration can be used to store image (jpeg or gif, etc.) or PDF files into the database.

Example: Create a message with binary data extracted from database

Database Communication point Database running in input mode can be used to generate a message where it contains binary data extracted from a table.

<config xmlns:ide="http://www.orionhealth.com/Rhapsody/IDE" xmlns:user="http://www.orionhealth.com/Rhapsody/IDE/UserData" ide:version="3.3.0.59809" ide:ideCompatibleVersion="3.0" ide:schemaName="dbo" ide:connectionName="Gate">
	<rowsInMessage>1</rowsInMessage>
	<statement name="Select">
		<sql>SELECT document FROM DocumentTable where title = 'document 4'</sql>
	</statement>
</config>

• The SELECT statement should use a column, of which the data type is binary.
• The rowsInMessage element should be set to 1 so that a separate message is generated for each returned row.
• The Message Body Column configuration property should be set to document (binary data column) so that the message is generated with this column value.