Page: Getting Binary Data with the SQL Module
Page: SQL Module
Page: Handling SQL Errors
Page: HTTP Module
Page: Convert Module
Page: Working With the Request
Page: Getting HTTP Query String Parameters
Page: Getting Request Path Parameters
Page: Getting HTTP Headers
Page: Getting the Module Directive
Page: Getting Route Parameters
Getting Binary Data with the SQL Module
The SQL module can upload and download binary data to and from SQL-based databases. However, different databases have different requirements when using binary data. This section includes the following topics:
Using binary data with SQL Server and MySQL
SQL Server and MySQl can return binary data directly in the route.
The following example selects a single record of binary data:
This example adds the following Camel components to the route:
- setHeader – The Camel setHeader component sets the
Content-Typeheader to the value of the "filetype" field returned from the database. This component uses Camel's Simple expression language to set the value of the header.
- transform – The Camel transform component replaces the message body with the output of its expression. This example uses Simple to set the body of the message to the value of the "binfile" field that is returned by the database (the BLOB).
For more information on using Camel components in your Akula projects, see Using Camel Components.
Using binary data with DB2 and Oracle
Oracle and DB2 require a custom module to access binary data. This is because SELECT statements that return a record with a binary column for these databases returns only a pointer and not the actual binary content.
You must instruct Oracle or DB2 to return the contents of the BLOB and not just a pointer to the BLOB. To do this, you can create a custom module that explicitly gets the bytes of the BLOB from the HashMap that the SQL module returns.
The following example shows how you can use a custom module to return the BLOB for DB2:
The following example shows how you can use a custom module to return the BLOB for Oracle:
In these examples, the
selectedKey is the name of the field that stores the binary data in the database.
In both cases, you set the body of the response message to the value of fileContents; for example:
In addition to requiring a custom module to access binary data, DB2 has additional JDBC connection parameter requirements when working with binary data. For more information, see Defining the JDBC connection for DB2.
Uploading binary data with the SQL module
You can upload binary data to the SQL module in one of the following ways:
- Within the body of the request
- As an attachment
To upload binary data in the body of the request, use the following macro in your SQL mapper file:
To upload binary data as an attachment, use the following syntax in your SQL mapper file:
For more information on using the SQL mapper file, see SQL Module.
Defining the JDBC connection for DB2
The JDBC connection for DB2 requires additional settings when working with binary data. You must set the following additional parameters:
If you do not set these parameters to the specified values, then the connection is terminated as soon as the SELECT query executes and before the custom module can access the data.