This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers.
Attachments in Remedy are attached to attachment fields.
In an attachment pool if you create a attachment field, it will create 3 columns in the B<schemaId>
Example:
column C<attachmentfield_Id1>
column CO<attachmentfield_Id1>
column CC<attachmentfield_Id1>
The C column stores the full path name of the attached file.
The CO column stores the original size (in bytes) of the attached file.
The CC column stores the compressed size (in bytes) of the attachment file.
When an attachment is attached an entry is created for the attachment.
when adding multiple attachment then multiple fields need to be added.
Adding 2nd attachment field will add 3 more columns in the B<schemaId> table.
Example:
column C<attachmentfield_Id1>
column CO<attachmentfield_Id1>
column CC<attachmentfield_Id1>
column C<attachmentfield_Id2>
column CO<attachmentfield_Id2>
column CC<attachmentfield_Id2>
Attaching the same attachment multiple times, the attachment will be added in the column format at database level.
Test scenario
Steps:
1- Create a regular form -> Add an attachment pool -> Add 3 attachment fields -> save the form.
2- Open the form using the Midtier or User tool.
3- Add the same attachment in the 3 attachment fields and save the request.
4- Open the database -> Open the [B<SchemaId>] table and perform below query.
select * from B<SchemaId>;
For the 3 attachment fields we have below columns created.
.....................
C1,
C<attachmentfieldId1> |
CO<attachmentfieldId1> |
CC<attachmentfieldId1> |
C<attachmentfieldId2> |
CO<attachmentfieldId2> |
CC<attachmentfieldId2> |
C<attachmentfieldId3> |
CO<attachmentfieldId3> |
CC<attachmentfieldId3> |
.....................
Thus for same attachments those will get added to above in column format.
Below is the Snippet of logs which shows how the attachments are getting saved in the database.
.......................
*/+CE ARCreateEntry -- schema Test_attachment from Mid-tier (protocol 20) at IP address {IP_Address} // :q:0.0s
*/ Start filter processing (phase 1) -- Operation - CREATE on Test_attachment - <NULL>
*/ End of filter processing (phase 1) -- Operation - CREATE on Test_attachment - <NULL>
*/BEGIN TRANSACTION
*/OK
*/INSERT INTO B358 (C1) VALUES ('000000000000004')
*/OK
*/UPDATE B358 SET CO536870914=1086,CC536870914=625,C536870914='mail.txt' WHERE C1 = '000000000000004'
*/OK
*/INSERT INTO B358C536870914 (entryId) VALUES ('000000000000004')
*/OK
*/SELECT C536870914 FROM B358C536870914 WHERE entryId = '000000000000004'
*/OK
*/Setting LOB into the above row ...
*/OK
*/UPDATE B358 SET CO536870918=1086,CC536870918=625,C536870918='mail.txt' WHERE C1 = '000000000000004'
*/OK
*/INSERT INTO B358C536870918 (entryId) VALUES ('000000000000004')
*/OK
*/SELECT C536870918 FROM B358C536870918 WHERE entryId = '000000000000004'
*/OK
*/Setting LOB into the above row ...
*/OK
*/UPDATE B358 SET CO536870919=1086,CC536870919=625,C536870919='mail.txt' WHERE C1 = '000000000000004'
*/OK
*/INSERT INTO B358C536870919 (entryId) VALUES ('000000000000004')
*/OK
*/SELECT C536870919 FROM B358C536870919 WHERE entryId = '000000000000004'
*/OK
*/Setting LOB into the above row ...
*/OK
*/INSERT INTO T358 (C2,C7,C8,C3,C5,C6,C1) VALUES ('Demo',0,'Demo W-RRODGE-37.adprod.bmc.com SQL -- SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)',1428073291,'Demo',1428073291,'000000000000004')
*/OK
*/INSERT INTO H358 (entryId,T0,U0) VALUES ('000000000000004',1428073291,'Demo')
*/OK
*/COMMIT TRANSACTION
*/OK
*/-CE OK -- New ID 000000000000004
.......................
Additional Information:
BMC Software use a proprietary algorithm to compress and decompress the attachment at the database level.
The only way to get access to the attachment and decompress it is via the GetEntryBlob API call.
You can use Remedy's Java API to do this .This is documented in the Java doc file ardoc81_build001.jar
Syntax: getEntryBlob:
public void getEntryBlob(String formName, String entryID, int fieldID, String filePath) throws ARException
Returns the attachment that is stored in the indicated IDs on the specified server.
The returned attachment will be placed into a given file path. The system returns information based on the access privileges of the user you specify in this ARServerUser object.
User permissions are verified for the specified field. If the user cannot access the field, the system returns an error message.
Parameters:
formName - the name of the form that containing the entry
entryID - the id of the entry that contains the attachment
fieldID - the id of the attachment field
filePath - the path to a file that stores the return attachment
Throws:
ARException - if blob attachment is not returned
Note: exists a small utility not developed by BMC Software that can help to extract the data of an attachment without the need to have AR System installed.
You can check this here:
http://remedylegacy.com/tools/db-attachments/
Be advised that this tool and any other utilities hosted in the above url are not official tools developed by BMC Software and using them falls strictly under your responsibility and BMC Support will not address any questions, queries or issues using any of them.
Related Documentation:
The attachment tables for a form