Syncing attachments
The binary data of an attachment isn’t retrieved automatically while syncing.Instead, when an item is synced from the exchange server to the database, any attachments the item might contain are converted into a formatted string containing the metadata needed to retrieve the actual attachment file from the server. The reason for this is that it makes synchronization lighter since the metadata of an attachment is almost always smaller than the file itself.
This metadata is sent to the database through an exh_attachments parameter in the applicable exh_from_exh stored procedure (e.g. exh_from_exh_add_email) and is formatted in the following way:
- filename1.ext:attachmentid1;filename2.ext:attachmentid2;etc.
Splitting the metadata
To help extract the attachment ids and filenames from the saved metadata, a table-valued function called exh_split_attachment_info is provided by the exchange base project. The result of calling this function with an attachment metadata string is a table with 2 columns,exh_attachment_id containing the split id and exh_attachment_name containing the split filename.
exh_split_attachment_info example in SQL Server
After splitting the metadata you could for example use a cursor to iterate the resulting table and retrieve the binary data for each record.
Retrieving attachment data
To retrieve the binary data of an attachment file on the server from inside a exchange database you will need to call the CLR procedure sendGetAttachtment when using SQL Server or the Java procedure exh_java_send_get_attachment when using DB2.When calling these procedures you need the following information to get the right attachment file from the server:
- The IP address of the exchange server (contained in an exh_setup table);
- The IP port of the exchange server (also contained in the exh_setup table);
- The id of the exchange item that holds the attachment (exh_item_id);
- The id of the attachment itself (contained in exh_attachments metadata);
- The user the exchange item belongs to (exh_user).
The procedures also have the following output:
- An execution code indicating whether or not the call to the server was successful;
- The binary data of the attachment that was being retrieved if the call was successful;
- An error message of what went wrong if the call was unsuccessful.
If the execution code is something other than the number 0 the call is considered a failure.
Here is an example query using SQL Server 2016 to help get you started.
code:
declare @exchange_ip_address nvarchar(20);
declare @exchange_ip_address_port int;
declare @exit_code int;
declare @byte_array varbinary(max);
declare @error_message nvarchar(max);
-- Get the exchangelinks ip from exh_setup.
set @exchange_ip_address
= (select exh_value
from exh_set_up
where exh_parameter = 'exh_ip_address');
-- Get the exchangelinks ip port from exh_setup.
set @exchange_ip_address_port
= (select exh_value
from exh_set_up
where exh_parameter = 'exh_ip_address_port');
-- Get item id, user and attachment metadata from record.
declare @exh_item_id nvarchar(max);
declare @exh_user nvarchar(100);
declare @exh_attachments nvarchar(max);
select @exh_item_id = exh_item_id,
@exh_user = exh_user,
@exh_attachments = exh_attachments
from exh_email_item
where exh_app_item_id = 1;
-- Make a cursor for the dataset of the exh_split_attachment_info call.
declare attachment_cursor cursor
for select exh_attachment_id, exh_attachment_name
from exh_split_attachment_info(@exh_attachments);
declare @exh_attachment_id nvarchar(max);
declare @exh_attachment_name nvarchar(500);
open attachment_cursor
fetch next from attachment_cursor
into @exh_attachment_id, @exh_attachment_name;
while @@fetch_status = 0
begin
exec @exit_code = sendGetAttachment @exchange_ip_address,
@exchange_ip_address_port,
@exh_item_id,
@exh_attachment_id,
@exh_user,
@byte_array output,
@error_message output;
if @exit_code = 0
begin
-- Call successful, do something with @byte_array.
end
else
begin
-- Call unsuccessful, do something with @error_message
end
fetch next from attachment_cursor
into @exh_attachment_id, @exh_attachment_name;
end
close attachment_cursor
deallocate attachment_cursor
Saving attachments
To help with saving retrieved attachment data to disk a saveAttachment CLR procedure has been made available for SQL Server users.To use the procedure you’ll need to provide the following parameters:
- The path to the directory to save the attachment file in;
- The name of the file;
- The binary data of the attachment to save (@byte_array in the previous example);
- Whether or not to overwrite existing files or to append a number to it e.g. file.txt becomes file_1.txt.
The final path that was used to save the file is returned in an output parameter.
Using the example for sendGetAttachment you could use saveAttachment like this after verifying that sendGetAttachment‘s execution code was 0 and adding a @save_directory and @overwrite option.
code:
declare @final_filepath nvarchar(500);
exec saveAttachment @save_directory, @exh_attachment_name, @byte_array, @overwrite, @final_filepath output;
-- Optionally do something with @final_filepath.
Note: Please note that when using this CLR procedure any paths used are relative to the server the procedure is run on. So saving an attachment to for example C:\Users\someuser\Documents\ will save it to that path on the server and not the client machine running a Thinkwise GUI.
Upgrading from 2.00
When upgrading your TSF application’s exchange base project from 2.00 to 3.00 please consult the manual for version 3.00 and take note of the following points of interest:- (SQL Server) The previously available CLR procedures for SQL Server have had their signature changed in a similar fashion to how sendGetAttachment works. Please check and update any code templates using these;
- (DB2) The external Java functions have been changed to external procedures and have been updated to work in a similar fashion to those in SQL Server;
- (DB2) To accommodate the increasing complexity of the Java project the procedures are now shipped in a JAR (compiled with target 1.6) instead of a single Java file. This changes the way the procedures are installed onto the DB2 server/schema please consult the manual for further instructions on how to do this.